This approach didn't use an ORM and run the tests concurrently against the same database.<p>I follow those steps on my pipeline:<p>Every time I commit changes the CI/CD pipeline follow those steps, on this order:<p>- I use sqitch for the database migration (my DB is postgresql).<p>- Run the migration script `sqitch deploy`. It runs only the items that hasn't been migrated yet.<p>- Run the `revert all` feature of sqitch to check if the revert action works well too.<p>- I run `sqitch deploy` again to test if the migration works well from scratch.<p>- After the schema migration has been applied, I run integration tests with Typescript and a test runner, which includes a mix of application tests and database tests too.<p>- If everything goes well, then it runs the migration script to the staging environment, and eventually it runs on the production database after a series of other steps on the pipeline.<p>I test my database queries from Typescript in this way:<p>-in practice I'm not strict on separating the tests from the database queries and the application code, instead, I test the layers as they are being developed, starting from simple inserts on the database, where I test my application CRUD functions that is being developed, plus to the fixtures generators (the code that generate synthetic data for my tests) and the deletion and test cleanup capabilities.<p>-having those boilerplate code, then I start testing the complex queries, and if a query is large enough (and assuming there are no performance penalties using CTE for those cases), I write my largue queries on small chunks on a cte, like this (replace SELECT 1 by your queries):<p><pre><code> export const sql_start = `
WITH dummy_start AS (
SELECT 1
)
export const step_2 = `${sql_start},
step_2 AS (
SELECT 1
)
`;
export const step_3 = `${step_2},
step_3 AS (
SELECT 1
)
`;
export const final_sql_query_to_use_in_app = ` ${step_3},
final_sql_query_to_use_in_app AS(
SELECT 1
)
SELECT \* FROM final_sql_query_to_use_in_app</code></pre>
`;<p>Then on my tests I can quickly pick any step of the CTE to test it<p><pre><code> import {step_2, step_3, final_sql_query_to_use_in_app} from './my-query';
test('my test', async t => {
//
// here goes the code that load the fixtures (testing data) to the database
//
//this is one test, repeat for each step of your sql query
const sql = `${step_3}
SELECT * FROM step_3 WHERE .....
`;
const {rows: myResult} = await db.query(sql, [myParam]);
t.is(myResult.length, 3);
//
// here goes the code that cleanup the testing data created for this test
//
});
</code></pre>
and on my application, I just use the final query:<p><pre><code> import {final_sql_query_to_use_in_app} from './my-query';
db.query(final_sql_query_to_use_in_app)
</code></pre>
The tests start with an empty database (sqitch deploy just ran on it), then each test creates its own data fixtures (this is the more time consuming part of the test process) with UUIDs as synthetic data so I don't have conflicts between each test data, which makes it possible to run the tests concurrenlty, which is important to detect bugs
on the queries too. Also, I include a cleanup process after each tests so after finishing the tests the database is empty of data again.<p>For sql queries that are critical pieces, I was be able to develop thounsands of automated tests with this approach and in addition to combinatorial approaches. In cases where a column of a view are basically a operation of states, if you write the logic in sql directly, you can test the combination of states from a spreadsheet (each colum is an state), and combining the states you can fill the expectations directly on the spreadsheet and give it to the test suites to run the scenarios and expectations by consuming the csv version of your spreadsheets.<p>If you are interested on more details just ping me, I'll be happy to share more about my approach.