The goal of this article is to demonstrate the tools that can significantly ease the process of database development in PHP projects utilizing the PostgreSQL DBMS. schema-keeper Information from this article will primarily benefit developers who wish to maximize the capabilities of PostgreSQL but encounter difficulties in maintaining business logic that is offloaded to the database.

The article will not describe the advantages or disadvantages of storing business logic in the database. It is assumed that the reader has already made this choice.

The following questions will be addressed:

What format to use for storing database structure dumps in a version control system (hereafter referred to as VCS)

1. How to monitor changes in the database structure after saving the dump
2. How to transfer changes in the database structure to other environments without conflicts and large migration files
3. How to establish a process for multiple developers to work on the project in parallel
4. How to safely deploy a larger number of changes in the database structure to the production environment
5. SchemaKeeper
   

   is designed to work with stored procedures written in the language PL/pgSQL . Testing with other languages has not been conducted, so usage may not be as effective or could be impossible.What format to use for storing database structure dumps in VCS

provides a function

Library schema-keeper saveDump , which saves the structure of all objects from the database as separate text files. A directory is created that contains the database structure divided into grouped files that can be easily added to VCS.Let's consider the conversion of objects from the database into files through several examples:

Object type

Relative path to the file
Scheme
Title
Table

accounts
public
./public/tables/accounts.txt
Stored procedure

auth(hash bigint)
public
./public/functions/auth(int8).sql
booking

Representation
tariffs
./booking/views/tariffs.txt
The content of the files is a textual representation of the structure of a specific database object. For example, the content of the file for stored procedures will be the full definition of the stored procedure, starting with the block

CREATE OR REPLACE FUNCTION As seen from the table above, the file path contains information about the type, schema, and name of the object. This approach facilitates navigation through the dump and code review of changes in the database..

.sql

Extension .sql For files with stored procedure source code, it was chosen so that the IDE automatically provides tools for interacting with the database when the file is opened.

How to transfer changes in the database structure to other environments without conflicts and large migration files

By saving a dump of the current database structure in VCS, we gain the ability to check whether changes have been made to the database structure since the dump was created. In the library, schema-keeper there is a function to identify changes in the database structure, verifyDump, which returns information about differences without side effects.

An alternative way to check is to call the function again, specifying the same directory and verifying in VCS for any changes. Since all objects from the database are saved in separate files, VCS will only show the changed objects. , which saves the structure of all objects from the database as separate text files. A directory is created that contains the database structure divided into grouped files that can be easily added to VCS.deployDump
The main downside of this method is the need to overwrite files to see changes.

How to establish a process for multiple developers to work on the project in parallel

Thanks to the function The source code of stored procedures can be edited just like regular application source code. You can add/delete lines in the stored procedure code and immediately submit changes to the version control system, or create/delete stored procedures by creating/deleting the corresponding files in the dump directory. For example, to create a new stored procedure in the schema,

it is enough to create a new file with the extension public public/functions .sql in the directory , place the source code of the stored procedure into it, including the block, and then call the function. As seen from the table above, the file path contains information about the type, schema, and name of the object. This approach facilitates navigation through the dump and code review of changes in the database.Changes and deletions of stored procedures occur in the same way. Thus, the code simultaneously enters both VCS and the database. The source code of stored procedures can be edited just like regular application source code. You can add/delete lines in the stored procedure code and immediately submit changes to the version control system, or create/delete stored procedures by creating/deleting the corresponding files in the dump directory.If there is an error in the source code of any stored procedure, or a mismatch between the file name and the stored procedure name, then

it will not execute, displaying the error message. Misalignment of stored procedures between the dump and the current database is impossible when using The source code of stored procedures can be edited just like regular application source code. You can add/delete lines in the stored procedure code and immediately submit changes to the version control system, or create/delete stored procedures by creating/deleting the corresponding files in the dump directory. When creating a new stored procedure, there is no need to manually enter the correct file name. It is sufficient for the file to have the extension The source code of stored procedures can be edited just like regular application source code. You can add/delete lines in the stored procedure code and immediately submit changes to the version control system, or create/delete stored procedures by creating/deleting the corresponding files in the dump directory..

. After calling .sqlthe error message will contain the correct name that can be used to rename the file. The source code of stored procedures can be edited just like regular application source code. You can add/delete lines in the stored procedure code and immediately submit changes to the version control system, or create/delete stored procedures by creating/deleting the corresponding files in the dump directory. allows changing the function parameters or return type without additional actions, whereas in the classical approach you would have had to

The source code of stored procedures can be edited just like regular application source code. You can add/delete lines in the stored procedure code and immediately submit changes to the version control system, or create/delete stored procedures by creating/deleting the corresponding files in the dump directory. first execute
DROP FUNCTION , and only then., and only then As seen from the table above, the file path contains information about the type, schema, and name of the object. This approach facilitates navigation through the dump and code review of changes in the database..

Unfortunately, there are situations where The source code of stored procedures can be edited just like regular application source code. You can add/delete lines in the stored procedure code and immediately submit changes to the version control system, or create/delete stored procedures by creating/deleting the corresponding files in the dump directory. it cannot automatically apply changes. For example, if a trigger function is deleted that is used by at least one trigger. Such situations are resolved manually using migration files.

If the process of transferring changes in stored procedures is handled by schema-keeper, then to transfer the other changes in the structure, migration files must be used. For example, a good library for working with migrations is doctrine/migrations.

Migrations should be applied before launching The source code of stored procedures can be edited just like regular application source code. You can add/delete lines in the stored procedure code and immediately submit changes to the version control system, or create/delete stored procedures by creating/deleting the corresponding files in the dump directory.. This allows all changes to the structure to be made and resolves problematic situations so that changes in stored procedures can later be transferred without issues.

More details on working with migrations will be described in the following sections.

How to safely deploy a larger number of changes in the database structure to the production environment

It is necessary to create a script for the complete initialization of the database, which will be run by the developer on their workstation, aligning the structure of the local database with the dump saved in VCS. It's easiest to break down the initialization of the local database into 3 steps:

1. Import the file with the basic structure, which might be called base.sql
2. Applying migrations
3. Call The source code of stored procedures can be edited just like regular application source code. You can add/delete lines in the stored procedure code and immediately submit changes to the version control system, or create/delete stored procedures by creating/deleting the corresponding files in the dump directory.

base.sql — this is the starting point upon which migrations are applied and executed The source code of stored procedures can be edited just like regular application source code. You can add/delete lines in the stored procedure code and immediately submit changes to the version control system, or create/delete stored procedures by creating/deleting the corresponding files in the dump directory., which means base.sql + migrations + deployDump = current database structure. Such a file can be generated using the utility pg_dump. It is used base.sql exclusively for initializing the database from scratch.

Let’s name the complete database initialization script refresh.sh. The workflow might look as follows:

1. The developer runs in their environment refresh.sh and receives the current database structure
2. The developer starts working on the assigned task, modifying the local database to meet the needs of the new functionality (ALTER TABLE ... ADD COLUMN etc.)
3. After completing the task, the developer calls the function , which saves the structure of all objects from the database as separate text files. A directory is created that contains the database structure divided into grouped files that can be easily added to VCS., to commit the changes made to the database to VCS
4. The developer reruns refresh.sh, then verifyDump, which now shows the list of changes to be included in the migration
5. The developer transfers all structural changes to the migration file, runs refresh.sh and verifyDumpagain, and if the migration is correctly composed, verifyDump will show no differences between the local database and the saved dump.

The process described above is compatible with gitflow principles. Each branch in the VCS will contain its own version of the dump, and merging branches will result in merging dumps. In most cases, no additional actions are required after merging, but if changes were made in different branches, for example, in the same table, conflicts may arise.

Let's consider a conflicting situation: there is a branch develop, from which two branches have been created: feature1 and feature2, which do not have conflicts with develop, but have conflicts with each other. The task is to merge both branches into develop. In such a case, it is recommended to first merge one of the branches into develop, and then merge develop into the remaining branch, resolving conflicts in the remaining branch, after which the last branch can be merged into develop. During the conflict resolution stage, it may be necessary to correct the migration file in the last branch to match the final dump that includes the results of the merges.

SchemaKeeper

Thanks to the presence of a current database structure dump in the VCS, it becomes possible to check the production database for exact compliance with the required structure. This ensures that all the changes the developers intended have been successfully transferred to the production database.

Since DDL in PostgreSQL is transactional, it is recommended to follow this deployment order so that in case of unforeseen errors, one can execute rolls-back smoothly. ROLLBACK:

1. Begin transaction
2. Perform all migrations in the transaction
3. In this transaction, also execute The source code of stored procedures can be edited just like regular application source code. You can add/delete lines in the stored procedure code and immediately submit changes to the version control system, or create/delete stored procedures by creating/deleting the corresponding files in the dump directory.
4. Without closing the transaction, execute verifyDump. If there are no errors, execute COMMIT. If there are errors, execute ROLLBACK

These steps can be easily integrated into existing application deployment approaches, including zero-downtime deployments.

Conclusion

By using the methods described above, one can maximize performance from "PHP + PostgreSQL" projects, sacrificing relatively small amounts of development convenience compared to the implementation of all business logic in the main application code. Moreover, data processing in . Testing with other languages has not been conducted, so usage may not be as effective or could be impossible. often appears more transparent and requires less code than the same functionality written in PHP.

Source: habr.com