Answer.

Background
Manual verification of business logic in SQL (at the level of stored procedures, functions, triggers) leads to errors that emerge only in production. For a long time, testing SQL scripts was informal and non-standardized. However, the development of CI/CD technologies requires automated tests for SQL code as well.

Problem
Most developers limit themselves to application-level tests. The lack of verification of SQL procedures and functions leads to defects that are not covered by any test suite — for example, when changing the logic of UDFs or regressions in reports.

Solution
In the workflows of modern teams, unit and integration tests are organized directly for SQL code. For unit testing, frameworks such as tSQLt (SQL Server), utPLSQL (Oracle), and pgTAP (PostgreSQL) are used, and for integration testing — separate environments for setting up temporary databases, applying migrations, and validating business scenarios.

Example of a unit test in pgTAP:

-- Checking salary separation
SELECT plan(2);
SELECT is( (SELECT calc_salary(1)), 1000, 'Salary for user 1 correct' );
SELECT isnt( (SELECT calc_salary(2)), 0, 'User 2 salary is not zero' );
SELECT finish();

Code for an integration test for CI/CD:

psql -U user -d testdb < migrations.sql
psql -U user -d testdb < test_data.sql
psql -U user -d testdb -c "SELECT * FROM my_procedure_test();"

Key features:

• Tests should be isolated, with an independent data set (setup/teardown).
• Automatic test execution in the CI/CD pipeline.
• Verification not only of data correctness but also of errors/boundary situations/output conditions.

Trick questions.

Is it possible to rely solely on automated tests at the application level if the procedures are large? No, because UI/API tests do not guarantee the correctness of the SQL logic itself (for example, incorrect conditions within stored functions or violations during data updates). Unit tests must cover all root branching of executions in the SQL code itself.

Is it sufficient to manually run test scripts since there are few changes in the database? Not sufficient, even in small projects, bugs arise after changes to the schema or logic. Automating testing in the CI process reduces human factor and prevents regressions.

Can only "critical" procedures be tested, while skipping the rest? The best approach is to cover as many functions as possible, especially if the code will be changed by several teams in the future: non-obvious calculations and edge cases often manifest in non-standard branches.

Common mistakes and anti-patterns

• Lack of setup/teardown → tests interfere with each other
• Coverage only of "positive" scenarios, absence of negative tests
• Complex/unmaintainable test data structure

Real-life example

Negative case

When improving the discount calculation procedure, only a couple of cases were tested manually, missing the main logic branches. In production, clients started receiving incorrect discounts, and it took several days to investigate.

Pros:
Time savings at the start.

Cons:
Losses on manual corrections, inconvenience during revisions and refactoring.

Positive case

Developed unit tests with pgTAP for all key UDFs and procedures; integration tests are run through CI on every branch merge. Errors and regressions are identified before deployment.

Pros:
Stability of functions, ability to quickly revise business logic, minimal bugs in production.

Cons:
Requires an investment of time for starting and maintaining the testing database.