I want to share a tool that was born in the development of a single web project and really helps me not to get lost in the sea of ​​tables, stored procedures, indexes and other database inhabitants.

The project itself is written in Django, PostgreSQL is used as a backend. At the very beginning of the work, it was decided to at least partially abandon the use of Django ORM in favor of “raw” SQL and stored procedures. In other words, almost all business logic is moved to the database level. I can say right away that I can prepare ORM, but in this case it was necessary to perform multi-stage calculations related to a multitude of samples, and this is better done on the database server and not drag intermediate data into the application.
')
Faced with the need to maintain the database structure manually, without Django Migrations, I found out that manually writing incremental SQL patches is possible, but it is difficult to keep track of the dependencies of database objects. For example, when a function that is used somewhere else, you add another argument, a simple CREATE OR REPLACE is not enough - you need to first DROP, and then CREATE. In this case, you must first remove the functions dependent on it, and then create it again (and if someone else depends on these functions, then you need to recreate them).

Under the cut a brief description of the possibilities in the form of a tutorial. Meet - Sqlibrist.

I must say that my problem has already been learned to be solved. For example, Sqitch has been around for a relatively long time. It allows you to describe the database structure in a declarative form in SQL. Each table, view, or function is stored in a separate file, and a simple DSL describes the dependencies. The utility is written in Perl, and I, who are not familiar with Perl development and the ecosystem of its packages, had to really try to compile this utility. Perhaps due to the long history of development, Sqitch has a lot of dependencies, as for such a simple program. I also did not like the confusing description of dependencies and the work with versions of the structure. I admit that I just did not want to adapt and understand the tool, which seemed uncomfortable to me.

When creating Sqlibrist, I was inspired by Sqitch, Django Migrations, and some VCS. He also wanted it to be simple and straightforward to use. Objects of the database structure are stored in separate files. Each contains a SQL statement for creating and (optionally) deleting this object. Dependencies between objects are described explicitly in the form of directives on the built-in DSL (in it, by the way, only three keywords: REQ, UP, DOWN). Like the version control system, Sqlibrist stores snapshots of the database structure and SQL patch for updating to it from the previous snapshot.

Sqlibrist's intelligence is limited, it does not parse SQL and does not generate ALTER TABLE - this is your work. It only tracks changes in files and creates patches with your instructions, and also keeps records of applied migrations.
It all sounds like something abstract, let's move on to practice.

Installation

My primary OS is Linux both on the server and on the desktop, so the installation instructions are for it only. Maybe someone will help me with Windows and Mac.

First header files:

Ubuntu

$ sudo apt-get install python-pip python-dev libyaml-dev $ sudo apt-get install libmysqlclient-dev # for MySQL $ sudo apt-get install libpq-dev # PostgreSQL 

Fedora / CentOS

 $ sudo dnf install python-devel python-pip libyaml-devel $ sudo dnf install postgresql-devel # PostgreSQL 

 $ sudo dnf install mariadb-devel # for MariaDB 

or

 $ sudo dnf install mysql++-devel # for MySQL 

Sqlibrist is written in Python and has two dependencies: PyYAML and one of psycopg2 and mysql-python.

It is installed using pip either in virtualenv or in the system libraries:

 $ pip install sqlibrist 

or

 $ sudo pip install sqlibrist 

After installation, the sqlibrist command becomes available.

Online Store Database

Let's play with Sqlibrist on the example of a primitive online store.

 $ mkdir shop_schema $ cd shop_schema $ sqlibrist init Creating directories... Done. 

The init team created the directory structure of our project:

 shop_schema sqlibrist.yaml migrations schema constraints functions indexes tables triggers types views 

In sqlibrist.yaml project configuration for connecting to the database:

 --- default: engine: pg user: <username> name: <database_name> password: <password> # host: 127.0.0.1 # port: 5432 

To verify that the settings are correct:

 $ sqlibrist test_connection Connection OK 

Next, we initialize the table where Sqlibrist will store information about the migrations applied. This part is identical to Django Migrations / South.

 $ sqlibrist initdb Creating db... Creating schema and migrations log table... Done. 

By the way, in the terminology of Sqlibrist, migration is a snapshot of the base structure and patches for applying this migration or reverting to the previous one.

Next, create the file shop_schema / schema / tables / user.sql :

 --UP CREATE TABLE "user" ( id SERIAL PRIMARY KEY, name TEXT, password TEXT); 

The first line --UP means that the following SQL statements create a database object. This is enough to create a table.

Similarly, create two more files:

shop_schema / schema / tables / product.sql :

 --UP CREATE TABLE product ( id SERIAL PRIMARY KEY, name TEXT, price MONEY); 

shop_schema / schema / tables / order.sql :

 --REQ tables/user --UP CREATE TABLE "order" ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES "user"(id), date DATE); 

Note the line --REQ tables / user . It means that the current object depends on the object in the tables / user.sql file (the extension is not written to the REQ). This ensures that when the patch is generated, the user table will be created before the order table. All --REQ should go at the beginning of the file.

Another file:

shop_schema / schema / tables / order_product.sql :

 --REQ tables/order --UP CREATE TABLE order_product ( id SERIAL PRIMARY KEY, order_id INTEGER REFERENCES "order"(id), product_id INTEGER REFERENCES product(id), quantity INTEGER); 

Create the first migration:

 $ sqlibrist makemigration -n 'initial' Creating: tables/user tables/product tables/order tables/order_product Creating new migration 0001-initial 

Migration files are created in shop_schema / migrations / 0001-initial :

 up.sql down.sql schema.json 

Up.sql contains a patch for applying migration, down.sql in this case is empty, and schema.json has a snapshot of the current database structure.

Before applying the patch, you can (and this is desirable) familiarize yourself with the text of the patch and make sure that it is doing what it needs. If it does not suit you, delete the entire directory 0001-initial and re-create the migration. You can edit up.sql and down.sql if you know what you are doing, but do not touch schema.json .

Now apply our first migration:

 $ sqlibrist migrate Applying migration 0001-initial... done 

Three tables are created. Now we need a view that displays the user's orders with the order amounts:

shop_schema / schema / views / user_orders.sql :

 --REQ tables/user --REQ tables/order --REQ tables/product --REQ tables/order_product --UP CREATE VIEW user_orders AS SELECT u.id as user_id, o.id as order_id, o.date, SUM(p.price*op.quantity) AS total FROM "user" u INNER JOIN "order" o ON u.id=o.user_id INNER JOIN order_product op ON o.id=op.order_id INNER JOIN product p ON p.id=op.product_id GROUP BY o.id, u.id; --DOWN DROP VIEW user_orders; 

Following the --DOWN directive, there are instructions for removing user_orders when it is being re-created.

The general rule: we update data containing objects, for example, tables manually, so their descriptions do not contain --DOWN , and functions, types, indexes can be safely removed and created, so this can be entrusted to automation.

We also need a function that returns user_orders for a given user:

 --REQ views/user_orders --UP CREATE FUNCTION get_user_orders(_user_id INTEGER) RETURNS SETOF user_orders LANGUAGE SQL AS $$ SELECT * FROM user_orders WHERE user_id=_user_id; $$; --DOWN DROP FUNCTION get_user_orders(INTEGER); 

Create and apply the following migration:

 $ sqlibrist makemigration -n 'user_orders view and function' Creating: views/user_orders functions/get_user_orders Creating new migration 0002-user_orders view and function $ sqlibrist migrate Applying migration 0002-user_orders view and function... done 

Thus, we have 4 tables, one view and one function.

Suppose we need to add another field to the user_orders view. Here are some problems that may arise:

• we can delete and recreate a new user_orders view, but the database will not allow this, because the get_user_orders function depends on this view;
• you can cheat and get out CREATE OR REPLACE VIEW user_orders ..., but the type of the field of the form and the type of the result of the function will be different. And in this case the database will not allow us to do this without re-creating the function.

Sqlibrist is designed to solve such problems. Add the SUM field (op.quantity) as order_total to the user_orders view:

 --REQ tables/user --REQ tables/order --REQ tables/product --REQ tables/order_product --UP CREATE VIEW user_orders AS SELECT u.id as user_id, o.id as order_id, o.date, SUM(p.price*op.quantity) AS total, SUM(op.quantity) as order_total FROM "user" u INNER JOIN "order" o ON u.id=o.user_id INNER JOIN order_product op ON o.id=op.order_id INNER JOIN product p ON p.id=op.product_id GROUP BY o.id, u.id; --DOWN DROP VIEW user_orders; 

You can see what has changed:

 $ sqlibrist -V diff Changed items: views/user_orders --- +++ @@ -2,7 +2,8 @@ u.id as user_id, o.id as order_id, o.date, - SUM(p.price*op.quantity) AS total + SUM(p.price*op.quantity) AS total, + SUM(op.quantity) as total_quantity FROM "user" u INNER JOIN "order" o ON u.id=o.user_id 

Create a migration:

 $ sqlibrist makemigration Updating: dropping: functions/get_user_orders views/user_orders creating: views/user_orders functions/get_user_orders Creating new migration 0003-auto 

You see that the dependent object is first removed — the get_user_orders function, then the view itself. Next, the view is created with a new structure, then the function is restored. Such a scheme will work for dependencies of arbitrary depth (but not circular dependencies — Sqlibrist will ask you to fix it).

Apply this migration:

 $ sqlibrist migrate Applying migration 0003-auto... done 

Finally, let's make a change to the table. Since files with table definitions do not contain --DROP , we will work with our hands:

1. Change the CREATE TABLE statement;
2. We will generate a new migration using the same makemigration command;
3. Add the required ALTER TABLE to up.sql.

Add a new “type” text field to the product table:

shop_schema / schema / tables / product.sql :

 --UP CREATE TABLE product ( id SERIAL PRIMARY KEY, name TEXT, "type" TEXT, price MONEY); 

This is item 1. Now create the migration:

 $ sqlibrist makemigration -n 'new product field' Updating: dropping: functions/get_user_orders views/user_orders creating: views/user_orders functions/get_user_orders Creating new migration 0004-new product field 

Note that although we have changed the definition of the product table, tables / product is not present in the migration log, BUT all objects dependent on it are re-created. This is point 2.

Now point 3: open shop_schema / migrations / 0004-new product field / up.sql in the editor and find line 12 with the text - ==== Add your instruction here ==== . This is the logical middle of the migration. At this point, all dependent objects are deleted and we can insert our ALTER TABLE.

Insert the following:

 ALTER TABLE product ADD COLUMN "type" TEXT; 

Our up.sql will look like this:

 -- begin -- DROP FUNCTION get_user_orders(INTEGER); -- end -- -- begin -- DROP VIEW user_orders; -- end -- -- begin -- -- ==== Add your instruction here ==== ALTER TABLE product ADD COLUMN "type" TEXT; -- end -- -- begin -- CREATE VIEW user_orders AS SELECT u.id as user_id, o.id as order_id, o.date, SUM(p.price*op.quantity) AS total, SUM(op.quantity) as total_quantity FROM "user" u INNER JOIN "order" o ON u.id=o.user_id INNER JOIN order_product op ON o.id=op.order_id INNER JOIN product p ON p.id=op.product_id GROUP BY o.id, u.id; -- end -- -- begin -- CREATE FUNCTION get_user_orders(_user_id INTEGER) RETURNS SETOF user_orders LANGUAGE SQL AS $$ SELECT * FROM user_orders WHERE user_id=_user_id; $$; -- end -- 

You can apply this patch:

 $ sqlibrist migrate Applying migration 0004-new product field... done 

At this point, we leave alone our online store.

Sqlibrist is also able to integrate into the Django project, I use it in this context.

Project site - here , bug reports are welcome.