What are the database development options?

Question

PostgreSQL

I am developing the base in the following way. There are two folders:
entity (base entities)

and migrations (change history)

In entity I store SQL queries for creating each entity separately (for example, in one file there can be a table and all functions related to it or a view with functions, etc. ).
In migrations I store changes in the database structure in chronological order.
As a result, when you need to make changes, I do the following:
1) I make a new file in migrations
2) I run this file on a test base to check for errors
3) I make all changes from this file in the entity
Disadvantages:
1) I have to make one in two places and the same changes.
2) Not very convenient debugging (you have to constantly create a test base on which to check everything, including the operation of a new version of the program that accesses the base).
Are there options for developing a database that are more convenient than mine?
It should be noted that there are a lot of production databases, and on each one it will be necessary to update the database structure to the latest version.

Answer 1

I recommend using pyrseas . In it, dbtoyaml parses the database schema with support for all the original PostgreSQL features into a yaml file that you put in the version control system. Unlike a SQL dump, the yaml schema is easy to work with and merge across branches. I am using PyCharm with yaml syntax highlighting for this. Also, yamltodb can compare the yaml file with the database and generate an SQL migration. I have been using this thing for a year at work, all other solutions were not close by.

Answer 2

It is not for me to judge the approach itself, so I will focus only on suggestions for optimizing your processes.
What can be done is to automate the process of generating migrations between versions. For example via apgdiff .
Like the idea is this. You have everything organized just the way you like it. Therefore, we can simply write a simple script that uploads a new schema, and then generates a migration based on the differences between the previous version. This will allow you to reduce the amount of routine.

Answer 3

They came up with a cool idea, there is a similar migration mechanism in yii2 www.yiiframework.com/doc-2.0/guide-db-migrations.html
It is automated at the code level, there is an up - to roll the migration, and a down to roll back.
Thus, you can move arbitrarily forward and backward through migrations, but there is one thing: it is not always possible to roll back without loss.