Welcome to the Septentrion quickstart documentation.
In this tutorial, you will learn how to write migrations in the expected format, how to visualize them and how to run them against a PostgreSQL database.
Prepare your database¶
If you already have a PostgreSQL database around, make sure to note the connection parameters. Otherwise, we’ll create one together with Docker:
$ docker run --detach --rm -p 5432:5432 --name septentrion_db postgres
If you need to stop the docker at some point, use
docker stop septentrion_db.
If you stop the Docker or reboot your machine, the Postgres database will be trashed and you will need to re-create one and add some data again.
You’ll also need
psycopg2, which is notoriously complex to install. Septentrion
will install the
psycopg2 python package, but will expect the system to already
have the prerequisites (on
$ sudo apt install libpq-dev python-dev postgresql-client
On a different OS, if you experiment difficulties, we’ll be grateful if you can tell us via an issue so that we improve this documentation.
Install and configure Septentrion¶
Within a virtualenv, install Septentrion with:
(venv) $ pip install septentrion[psycopg2_binary]
Next we will configure the connection to the PostgreSQL database. We can do this either
with command line flags, environment variables or a configuration file. In this
tutorial, we will use a configuration file,
[septentrion] migrations_root=migrations host=localhost username=postgres # port=5432
With the Docker setup described above, you should be good to go. If you need additional configuration parameters to connect to your database, have a look at advanced configuration options.
Migrations are SQL files that are executed in order. Migrations are grouped together in successive versions. All migrations in the same version are placed in a folder named after the version number.
Let’s create our migration folder, and a first version folder.
$ mkdir migrations $ mkdir migrations/1.0
Now we can add a migration file in
BEGIN; CREATE TABLE "author" ( "id" serial NOT NULL PRIMARY KEY, "name" varchar(100) NOT NULL, "birth_date" varchar(10) NOT NULL ); COMMIT;
Migrations are executed in alphabetical order, so an easy way to control the order is to prefix filenames with two digits.
ddl stands for Data Definition Language and corresponds to all the operations that change the structure of the
Congratulations, you have now written your first Septentrion migration. Let’s see how to run it!
First, we want to visualize what is going to happen, without making any change to our data yet.
$ septentrion --target-version 1.0 show-migrations Current version is None Target version is 1.0 Version 1.0 [ ] 00-author.ddl.sql
Great, we can now run it for real:
$ septentrion --target-version 1.0 migrate Applying migrations Version 1.0
You should run septentrion in your root directory, where your
migrations folder is.
--target-version flag is a required option (it might change in the future).
At this point, the
author table has been created in the database. We can check that
and simulate our application by creating a few rows in the table.
$ psql --host localhost --user postgres postgres postgres=# \d List of relations Schema | Name | Type | Owner --------+-------------------------------+----------+---------- public | author | table | postgres public | author_id_seq | sequence | postgres public | septentrion_migrations | table | postgres public | septentrion_migrations_id_seq | sequence | postgres public | sql_version | table | postgres (5 rows) postgres=# INSERT INTO author (name, birth_date) VALUES ('Victor Hugo', '1802-02-26'), ('George Gordon Byron', '1788-01-22'), ('JRR Tolkien', '1892-01-03'); INSERT 0 3 postgres=# SELECT * FROM author; id | name | birth_date ----+---------------------+--------------- 0 | Victor Hugo | 1802-02-26 1 | George Gordon Byron | 1788-01-22 2 | JRR Tolkien | 1892-01-03 (3 rows)
A more complex migration¶
2.0 of our application, we want to change
varchar to the
We create a new folder for the version.
$ mkdir migrations/2.0
We can add the migration file in
BEGIN; ALTER TABLE author ALTER COLUMN birth_date TYPE DATE USING to_date(birth_date, 'YYYY-MM-DD'); COMMIT;
We launch the migration.
$ septentrion --target-version 2.0 migrate Applying migrations Version 1.0 [X] Already applied Version 2.0 [X] Applied 00-change_birth_date_type.ddl.sql
Now we can check that our migration successfully changed the column type in the
$ psql --host localhost --user postgres postgres postgres=# SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'author'; column_name | data_type ---------------+------------------- id | integer name | character varying date_of_birth | date (3 rows)
Congratulations, you can now run migrations with Septentrion!