I'm curious about how other people use and live with databases, specifically the types of tools they use around their database to simplify work with it. For background info, what database(s) do you use, and what is the scope of your project? What kind of tools do you use to manage change with your database (implicit or explicit schema, server deployments, etc.)? What kind of "generator" tools are you using (thick- or thin-ORM, data access layers, whatever)? What pain points do you still experience? Thanks!

Very interesting, thank you.

I had seriously been considering doing something similar to that. My current client's project has a fair bit of test data and the other developers and I are frequently stepping on each other's toes.

Liquibase came up on the Hacker News post I made on this same subject: http://www.liquibase.org/

It looks like it manages a lot of what I'm finding issue with, but has taken the unfortunate tactic of expressing it in XML. Perhaps it wouldn't be too hard to develop a UI for that XML, though.

In you're concerned about revision history, a clever approach I've seen used in the past is to have a 'changelog' table which stores rows of DDL that, when applied in order, can rebuild your database from scratch up to any particular point in time. So whenever someone makes a change to your database, whether it be schema or data, you turn those changes into a list of equivalent DDL statements and record them.

A quick example? I'm not familiar with all the terminology and such.

Alpha_ProgDes: he's talking about a table in the DB that has a record of every script you've ever ran against the DB as a whole, to create/alter tables/views/procedures, etc. DDL stands for Data Definition Language, notionally being separate from the Structured Query Language (though often conflated).

so say you have a database that is storing User accounts. You could start off with two tables:

User(id, name, email)

RevisionHistory(date, script)

and revision history would already have a row:

("2013-22-03 09:24:35.45", "CREATE TABLE User(ID INT IDENTITY(1, 1) PRIMARY KEY, Name NVARCHAR(10), Email NVARCHAR(256);")

Now, that's not a very useful Name column, it's pretty short, so you then need to create alter it, thus necessitating a new RevisionHistory record:

("2013-22-03 09:26:48.01", "ALTER TABLE User ALTER COLUMN Name NVARCHAR(256);")

This is necessary but not sufficient for making a good system. It would be equivalent to you having to manually write out text files with Git diffs and changelogs.

I see the problem as being one of lack of development in the realm of databases. Essentially SQL is a dynamic, interpreted programming language with strict typing. In static application languages, like C#, the compiler does a lot of checking to make sure changes to code don't break other code (and certain coding styles can leverage more of the compiler for static checking than others). In dynamic application programming languages, like Python, we tend to use Unit Tests to verify those changes were safe. But Unit Tests don't work in very stateful environments, and databases are extremely stateful (that being their purpose).

So that's why I'm so hell bent on trying to figure out better tools for working with databases. It should be possible to add abstraction layers that essentially do static checking for us. And if you can do static checking and track changes, then you can be much more cavalier about what changes you try to make, because the system finds the breakages for you and provides you an easy path out of the weeds.

Liquibase adds a lot of the change tracking I'm looking for, but not of the static checking. It also doesn't appear to automate the process, your front-end to the system is an XML document, which is just about the worst interface a person could possibly make.

But that doesn't mean a new front end couldn't be made. I think it's a good start, it just needs a lot more work.

Going non-relational, non-ACID is probably not going to be an option for me. Most of the stuff I do *does* fit well into the relational model, and ACID is a crucial feature. Auditors don't like "well, for the sake of speed, we dropped a couple of records on the floor. We made it up in the next cycle" as an excuse for why you don't have complete records. it's just that change management is complete garbage.

Some other things I've found in my research:

Flyway (another Java system, doesn't use XML under it like Liquibase)

Migrator.NET

Subsonic (looks like it might do more than just migration)

Roundhouse

octalforty-Wizardby (claims to be very simple)

MigSharp

I'm reminded that I worked at a place years ago that had their own home-rolled system for doing migrations. Again, it was XML based, and only ran against SQL server. Migration is great, but it's missing (or is maybe orthogonal to) a major feature I want: static analysis. I want to be able to change the schema and immediately have all of the impacted queries pointed out to me. I like breaking code, but it has to break hard to make it work.

In you're concerned about revision history, a clever approach I've seen used in the past is to have a 'changelog' table which stores rows of DDL that, when applied in order, can rebuild your database from scratch up to any particular point in time. So whenever someone makes a change to your database, whether it be schema or data, you turn those changes into a list of equivalent DDL statements and record them.