Choose Base Database State

[NoraCodes]

As part of making the migrations setup more sane and testable, we need to stop modifying the base SQL state. This will mean:

1. Picking a schema from which to create the database
2. Making the createDB script run migrations from that schema to the current one.

[jameeters]

I have some doubts about this. On the one hand, I think you're absolutely right that the migrations need to be testable and that those tests need to start somewhere. On the other hand, I don't love the idea of having to read the base table creation query, and then N migration queries, to figure out what a table looks like now.

I was thinking about this: decide on a base db state, but only use it in testing, and keep another set of table creation queries that produce an up-to-date db for use outside of the tests. I don't love the idea of separating the tests from the living code that way, either, though. We could end up with problems where my create table query and my migration do not actually do the same thing. So on the whole I think this is a thoroughly un-brilliant idea.

I'd love to avoid having to look at a bunch of files and build the database state in my head though, so maybe someone else will have an idea as to how to do that.

The simple solution would be to just write the create table query and stick it in a comment at the bottom of the migration. Not foolproof, but at least the guidance would be useful.

[NoraCodes]

Here's an idea, though it may be a foolish one.

pg_dump -s database dumps the schema of the database. Perhaps we could:

• Use the Rails and Diesel method, whereby every database is created by migrations, but
• Require that a master schema is maintained, and
• Use pg_dump to verify that they result in the same database, in CI.

[jameeters]

I think that's an excellent idea. I can see someone finding it a bit silly to maintain a redundant set of sql files, but I think having CI confirm that you've written the migration you think you've written will be very helpful.

[huss]

While this is interesting, migrations are normally only done rarely on upgrade. Manual testing is generally done. Also, now that the developer test data is easy to redo with any DB version, this isn't so important. Given all of this, I'm closing this issue. It can be reopened in the future if desired.