Managing database structure without pain

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

The project is written in Django as a backend — PostgreSQL. In the beginning it was decided, at least partially, to abandon the use of Django ORM in favor of "raw" SQL and stored procedures. In other words, almost all business logic is put to the database level. I must say that to cook ORM I know, but in this case it was necessary to perform multi-stage calculations associated with many samples, and this is best done on the DB server and not to carry intermediate data to the application.

Faced with the need to maintain the database structure manually, without the niceties of Django Migrations, I found that manually writing SQL incremental patches is possible, but hard to keep track of dependencies of database objects. For example, when a function that is used somewhere else, add another argument, a simple CREATE OR REPLACE is not enough — it must first DROP and then CREATE. In this case you need to remove its dependent functions, and then create anew (and if these functions someone else depends, and then you have to recreate them).

Under the cut is a brief description of the features in the form of a tutorial. Meet — Sqlibrist.

I must say that my problem has learned to solve. For example, a relatively long time there Sqitch. It allows to describe a database structure in a declarative SQL. Each table, view or function is stored in a separate file that describes a simple DSL based on. The utility is written in Perl, and I'm not familiar with development on Perl and its ecosystem of packages, I had to try very hard to compile this utility. Maybe because of the long history of development, the Sqitch has a lot of dependencies for such a simple program. I liked the intricate description of the dependencies and versioning structure. I admit that I just didn't want to adapt and deal with the tool that I felt uncomfortable.

Creating Sqlibrist, I was inspired and Sqitch, and Django Migrations, and some VCS. And wanted it to be simple and intuitive to use. The objects of the database structure are stored in separate files. Each contains the SQL statement to create and (optional) delete the object. Dependencies between objects are explicitly described in the form of directives on the embedded DSL (in it, by the way, only three key words: REQ, UP, DOWN). Like a version control system, Sqlibrist keeps snapshots of database structure and SQL patch to upgrade to it from a previous snapshot.

Intelligence Sqlibrist is limited, it does not parses the SQL and generates an ALTER TABLE — that's your job. It only monitors changes to files and creates patches with your instructions, and also keeps track of applied migrations.
It all sounds kind of abstract, let's move on to practice.

the

Installation

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

First the header file:

the

Ubuntu

the

$ 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

the

Fedora/CentOS

the

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

the

$ sudo dnf install mariadb-devel # for MariaDB

or

the

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

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

Is installed using pip in a virtualenv or or in system libraries:

the

$ pip install sqlibrist

or

the

$ sudo pip install sqlibrist

After the installation command is available sqlibrist.

the

Database online store

the

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

The command init have created the directory structure for our project:

the

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

In sqlibrist.yaml project configuration to connect to the database:

the

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

To verify that the settings are correct:

the

$ test_connection sqlibrist
Connection OK

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

the

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

Done.

By the way, in the terminology Sqlibrist, migration is the structure of the database and patches for applications that migrate or roll back to the previous one.

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

the

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

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

Similarly, create two more files:

shop_schema/schema/tables/product.sql:

the

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

shop_schema/schema/tables/order.sql:

the

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

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

Another file:

shop_schema/schema/tables/order_product.sql:

the

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

Going to create our first migration:

the

$ 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:

the

up.sql
down.sql
schema.json

In up.sql contains a patch for application migration, down.sql in this case empty, and schema.json the current database structure.

Before applying the patch you can (and desirable) to read the text of the patch and make sure it does what you need. If you are not satisfied, delete the directory 0001-initial and create a migration again. Can edit up.sql and down.sql, if you know what you're doing, but don't touch schema.json.

We now apply our first migration:

the

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

Three tables are created. Now we need a view that displays a customer's orders with order amount:

shop_schema/schema/views/user_orders.sql:

the

--tables REQ/user
--Tables REQ/order
REQ --tables/product
REQ --tables/order_product

--UP
CREATE VIEW AS SELECT user_orders
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;

After the Directive --DOWN are instructions for removal of user_orders when you recreate.

General rule: contains the data objects, such as tables, we update manually, so their descriptions do not contain --DOWN, and the functions, types, indices, you can safely delete and create, so that you can trust the automatics.

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

the

--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:

the

$ 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 want to add another field to the form user_orders. Here what problems may arise:
the

the
• we can remove and re-create a new kind of user_orders, but the database will not allow to do this, because the get_user_orders depends on it;
the
• you can cheat and get out a CREATE OR REPLACE VIEW user_orders... but the field type and the result type of the function will differ. And in this case the database will not allow us to do so without recreating the function.

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

the

REQ --tables/user
--Tables REQ/order
REQ --tables/product
REQ --tables/order_product

--UP
CREATE VIEW AS SELECT user_orders
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:

the

$ 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

Let's create a migration:

the

$ 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 first removed dependent object get_user_orders, then himself. Then the view is created with the new structure, after recovering function. This scheme will work for dependencies of arbitrary depth (but not circular dependencies — Sqlibrist will be asked to fix it).

Apply the migration:

the

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

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

the
1. Change the CREATE TABLE statement;
the
2. will Generate a new migration by the same team makemigration;
the
3. will Add in up.required sql ALTER TABLE.

Add new field type text to table product:

shop_schema/schema/tables/product.sql:

the

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

This paragraph 1. Now let's create a migration:

the

$ 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

Please note that despite the fact that we changed the definition of the product table, tables/product is not present in the migration log, BUT all its dependent objects re-created. This is paragraph 2.

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

Paste the following:
the

ALTER TABLE product
ADD COLUMN type TEXT;

Our up.sql will look like this:

the

 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 AS SELECT user_orders
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:

the

$ sqlibrist migrate

Applying migration 0004-new product field... done

At this point let alone our online store.

Even Sqlibrist able to integrate into a Django project, I use it in this context.

The project website is here, bug reports are welcome.

Article based on information from habrahabr.ru