Quickstart¶
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
Note
If you need to stop the docker at some point, use docker stop septentrion_db
.
Note
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 Ubuntu
):
$ sudo apt install libpq-dev python-dev postgresql-client
Note
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.ini
.
[septentrion]
migrations_root=migrations
host=localhost
username=postgres
# port=5432
Note
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.
Write migrations¶
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 migrations/1.0/
named 00-author.ddl.sql
:
BEGIN;
CREATE TABLE "author" (
"id" serial NOT NULL PRIMARY KEY,
"name" varchar(100) NOT NULL,
"birth_date" varchar(10) NOT NULL
);
COMMIT;
Note
Migrations are executed in alphabetical order, so an easy way to control the order is to prefix filenames with two digits.
Note
ddl
stands for Data Definition Language and corresponds to all the operations that change the structure of the
database (CREATE
, DROP
, …).
Congratulations, you have now written your first Septentrion migration. Let’s see how to run it!
Run migrations¶
First, we want to visualize what is going to happen, without making any change to our data yet.
$ septentrion 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 migrate
Applying migrations
Version 1.0
Note
You should run septentrion in your root directory, where your migrations
folder is.
If something is not working as it should be, you probably want to check the troubleshooting guide or the advanced options.
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¶
For version 2.0
of our application, we want to change birth_date
from
varchar
to the date
type.
We create a new folder for the version.
$ mkdir migrations/2.0
We can add the migration file in migrations/2.0/
named
00-change_birth_date_type.ddl.sql
:
BEGIN;
ALTER TABLE author
ALTER COLUMN birth_date
TYPE DATE USING to_date(birth_date, 'YYYY-MM-DD');
COMMIT;
We launch the migration.
$ septentrion 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
author
table.
$ 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!
Going further¶
To continue with practical steps, head to the How-to… section.
If you want to better understand some design decisions, head to the Discussions sections.