Pushing database code to production is not as easy as replacing some files. We need to generate the SQL DDL (Data Definition Language) statements that take our database code from the old version to the new one.
This is done through "migration" files. An excellent tool to manage and track those files is Sqitch.

This would be enough if we would have used the database as a "dumb store" since, after the first version is deployed, there would be very rare and few changes to the schema, so writing those files by hand would not be that hard. We however, have a lot of the code in the database that guarantees the integrity of our data. We've also split that code into multiple files and directories. Having to remember what changed and where would be impossible so this is were the subzero-cli comes in handy again. You do not have to remember what files you changed in order to implement a feature, you change any sql file you need, and at the end, when you have your feature ready to be pushed to production, you just run one command, and that migration file will be generated for you.

Right now our code is split into multiple files. We need to create the first migration that has the initial state of our database.

Make sure your stack is up (we need it running when creating migrations), if not, bring it up with docker-compose up -d

Create the first migration

subzero migrations init

If all went well, you should see this output

Created sqitch.conf
Created sqitch.plan
Created deploy/
Created revert/
Created verify/
Created deploy/0000000001-initial.sql
Created revert/0000000001-initial.sql
Created verify/0000000001-initial.sql
Added "0000000001-initial" to sqitch.plan

Note

You might be tempted to start creating migrations from the early stages of iterating on your API in order to be able to push changes to a remote deployment used for testing. You should avoid that. During the early stages you will have a lot of changes in your migration files and it will be a lot of work checking their correctness. Until you actually go to production, you should do full dumps of your dev database each time and use that for your remote test system.

If you look at the db/migrations/deploy/0000000001-initial.sql file, you'll see only statements that will create all your entities, you will not see any statement that will add the actual data in the tables that you currently have in your dev database. Usually this is exactly what you want in regard to the data, however there are still some places where the data in the tables is more like a configuration as opposed to user generated data so it also needs to be reflected in a migration. Since it's impossible to know which is which, this will be a manual step.

subzero-cli detected that only one view changed since your last migration and created the appropriate DDL statements.

Note

In the example above you've probably noticed the ATTENTION: bit and wondering what's that about. Internally, the cli uses apgdiff to auto generate migrations files, and while it does a good job most of the time, it's not 100% correct 100% of the time since generating DDL while making sure to preserve the current state is a complex task. For this reason, we recommend that when working on big features that involve lots of changes to different database entities, you generate a few smaller migrations instead of a big one at the end. This will make it easier to verify the correctness of the generated DDL statements. Once you are ready to push your big feature to production you can merge those migration files to a single one.