Document integration of dbmate on a existing schema

[maraino]

Description

The documentation should describe the process to integrate dbmate into an existing schema.

After a first look at the docs it looks like the process should be something like this:

dbmate dump
dbmate new my_migration
dbmate up

But this will only work on an existing database if we want to create a new database from scratch we need to apply the initial dump first. Is there any better way to do this?

[amacneil]

There are two ways to integrate dbmate into an existing schema:

1) Do nothing, start using dbmate going forward

You can simply start using dbmate, and add/edit your existing schema. Dbmate is intentionally rather dumb, so we will attempt to apply each migration and it is up to you to ensure that the migration is valid based on the existing schema of the database. This is the simplest approach, but the downside is that you will not be able to recreate a development or testing database with a full schema by simply using the dbmate up command (since the migrations you create will rely on some existing tables & columns).

2) Import your current schema into dbmate

The other option is to create an initial migration which can recreate your current schema. The advantage of this is that you can recreate your entire schema in development or test whenever needed. There are three steps to this:

First, export your current schema. You can export the current schema using dbmate dump, or by calling pg_dump or mysqldump etc yourself. Be sure to export only the schema (and any necessary seed data) but not your full database. You should run this command on a restored backup of your production database (don't run it on production directly).

Next, copy this schema into a new migration. For example, run dbmate new init to create a migration called init, and make sure the migration date (version number) is the lowest of any other migrations. Then, manually copy all the commands needed to recreate your production schema into this new migration file.

Finally, you will need to manually insert the migration number into your production database so that dbmate doesn't try to apply this migration in production (i.e. you need to tell dbmate that this migration was already applied). To do this you will need to make sure your schema_migrations table exists (which you can auto-create by running dbmate status in production), and then manually insert the migration number. For example, if your init migration file was called db/migrations/20201216194437_init.sql, then you would run the sql command insert into schema_migrations (version) values ('20201216194437');

Now, your production database will know that the init migration is already applied, but when you create a new development or testing database your entire schema will be applied.

Does that answer your question?

[maraino]

That is basically how I finally did it. It would be nice to have that in the README.md, and perhaps a command to do 2.