Budget 2 project, i want to be able to develop the database model over time. This results in a series of changes to the model – migrations to be more specific. In order to work with this in a more structured way, where it is possible to revisit earlier versions etc., i want to execute the migrations through scripts loaded into psql. For every “up” file, there’s a “down” file.
Now a first i thought, that a down file would be a simple “negative” version of the up file. “CREATE TABLE > DROP TABLE” and so forth. But besides developing test data for every version and trying to figure out how to develop the api code in the same way, postgres also has quite a few triggers running, that creates additional tables – sequences and constraints – that one need to be aware of, when writing the “downfile”.
Manipulating sequences
One of these is the sequence for a table with an ID column of datatype SERIAL. This datatype ensures, that whenever a new row is inserted, the value of the ID will be incremented by 1. I found when up and down migrating with tables being created and dropped again, that leftover sequences wouldn’t be deleted, and this creating a new sequence, when the tables would be recreated in the next up-migration proces. This results in sequences with a name like “envelope_id_seq1” making it hard to write code that relates to this sequence, as i will not know what version of a sequence I will be at after x amount of up or down migrations.
This opens up a new area of knowledge i need to conquer in order to work with databases: roles and privileges. As the tables used for the application can be created by my superuser and afterwards grant all the needed privileges to the app-user (select, insert, update and delete), it is more complicated with the tables automatically created when the database is set up. I’ve tried to grant all privileges to my app-user, but for now i’m hitting a wall as the user does not have the right permissions.
So – for now i’m left at questions about how to grant privileges for tables, that are created while setting up the database. For instance: is it possible to have privileges over all tables in a schema, but not be able to do anything, as the schema itself is something i need to have the right privileges over? I will investigate into this here: PostgreSQL: Roles and privileges
Up and down migration
In order to run up and down migration files in an automated fashion, i am storing the database version number in a separate file. Then i execute a script that looks into the file of current version, and looks through the up or down files (depending on which kind of migration i want to do) and executes either files with a higher or lower version number.
Looking through up and down files is for now implemented as a list of the files in a file each – “upfiles” and “downfiles”. This of course can be done using some sort of regex statement, but for now i’ve decided to go with the more simple approach.
Each “upfile” with a version number larger than that written in the “current_version” file is run with the command “psql -d <database_name> -U <username> -a -f <filename of up/down file>”. The -d flag is for defining the name of the database. -U is for the name of the user logging in. -a is for printing every line run by psql to the console and -f is for running a sql file of command into the database.
In short: up.sh asks current_version for the <current_version>. It then asks upfiles for available upfiles. up.sh then filters out up files with a lower or equal version number and runs them at the database using the psql command. Finally, the current version of the last and highest version is then written back to current_version for future reference.
Resetting tables
type something here…
Working with testdata
type something here…
Development of a developer 