Tutorial¶

Basic description of the tool¶

Usage¶

Usage: aiosqlembic [OPTIONS] [asyncpg|aiosqlite] DSN COMMAND [ARGS]...

  Main entry point for the application

Options:
  --version                       Prints aiosqlembic version and exits
  --debug / --no-debug            Sets the logging level to DEBUG or let it at
                                  INFO
  -m, --migration-directory DIRECTORY
                                  Where migrations files will be
  --help                          Show this message and exit.

Commands:
  create
  down
  status
  up

There is no config file, everything is done on the CLI.

The 4 commands are:

up / down, which will respectlively upgrade and downgrade from one database revision to the next / previous
status which aims at giving you a list of revision files and if yes / no they are applied
create which creates a revision file

Create a revision file with the create command¶

Usage¶

Usage: aiosqlembic create [OPTIONS]

  Create a revision file

Options:
  -n, --name TEXT     [required]
  -a, --auto BOOLEAN
  --help              Show this message and exit.

Example¶

aiosqlembic --debug -m ./migrations aiosqlite ./foo.db create -n "revision 1"

This creates a new revision file, the required --name switch (or -n as above) should be a general description of what the revision does.

It will be snaked case to built the filename.

It also contains the timestamp of the creation:, for instance 20200218145048_revision_1.sql

In production you should probably use a mix of timestamp and fix numbers, see goose terrific post about it. (Note that currently the regexp pattern used in aiosqlembic will discard fix numbers, it’s on the TODO)

If you use the –auto flag which is set to False by default, the program will attempt to generate the sql migrations, up and down automatically making a difference between all your revisions files applied on a theorical database and the current one (currently commnented but working, TODO)

You end up with your revision file which looks like that:

-- name: up#
SELECT 'upgrade sql query here';

-- name: down#
SELECT 'downgrade sql query here';

You’ll have to manually edit it (unless -auto switch was used) and even if you used the automatic generation it’s strongly advised to read it. So for instance let’s say on postgresql you want to add a new table, in a new schema, with some uuids, your revision file could look like the following:

-- name: up#
CREATE SCHEMA app;
SET search_path TO app;
CREATE EXTENSION "uuid-ossp"
WITH SCHEMA app
VERSION '1.1';
COMMENT ON EXTENSION "uuid-ossp" IS E'generate universally unique identifiers (UUIDs)';
CREATE TABLE app.users (
    time_created timestamp DEFAULT now(),
    id uuid NOT NULL DEFAULT app.uuid_generate_v4(),
    email character varying,
    password character varying,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_email_key UNIQUE (email)

);

-- name: down#
DROP TABLE IF EXISTS app.users CASCADE;
DROP EXTENSION IF EXISTS "uuid-ossp" CASCADE;
DROP SCHEMA IF EXISTS app CASCADE;

The revision file follows the aiosql syntax, so for that revision file there are 2 methods that will be triggered by the up and down command and thinking very deeply about it, we named them the same…

See a list of revisions and if the’re live or not with status¶

Usage¶

Usage: aiosqlembic status [OPTIONS]

  Prints the status of revisions files and if those are applied or not

Options:
  --help  Show this message and exit.

Example¶

aiosqlembic -m ./migrations aiosqlite ./foo.db status

Shows the list of revisions files you have and if they are pending or not.

INFO:main.py.cli:94 Logging set to INFO
Connected
++++++++++ time ++++++++++    ++++ applied at +++    ++++++++++ revision file ++++++++++
2020-02-18T15:16:07.897040    2020-02-18T15:13:59    ./migrations/20200210145858_this_is_test1.sql
2020-02-18T15:16:07.897730    2020-02-18T15:13:59    ./migrations/20200211131532_this_is_test2.sql
2020-02-18T15:16:07.898189    pending                ./migrations/20200218145048_revision_3.sql

Upgrade your database with the up command¶

Usage¶

Usage: aiosqlembic up [OPTIONS]

  Upgrade database to latest revision

Options:
  --help  Show this message and exit.

Example¶

aiosqlembic -m ./migrations aiosqlite ./foo.db up

INFO:main.py.cli:94 Logging set to INFO
Connected
Applied revisions up: [20200218145048]