Efficient Database Migration with Dynamic Queries

In a recent project, we encountered a situation where we needed to compare two databases to ensure that all the information was successfully migrated during a server migration. To address this, I suggested using the dynamic queries technique to compare the databases programmatically.

Dynamic queries refer to the process of generating database queries on the fly or at runtime, rather than hard-coding them into an application. In other words, instead of having pre-defined SQL statements that are executed against a database, dynamic queries allow you to build SQL statements dynamically based on certain criteria or conditions. This can be achieved using input parameters, database catalogs, conditions, and other dynamic elements.

In our case, we needed to extract a specific number of rows from all tables in the database so we could compare them between the two databases and verify if everything was properly migrated.

This task could be daunting if done manually, especially when dealing with a large number of tables (e.g., 300, 100, etc.). However, using dynamic queries, we were able to automate the process and save time.  Here's an example of how we used dynamic queries with the PostgreSQL catalog to generate a new query:

SELECT 'SELECT COUNT(*) AS row_numbers, ''' || table_name || ''' AS table FROM ' || table_name || ' UNION ALL '
FROM information_schema.tables
WHERE table_schema='pg_catalog'
UNION ALL 
SELECT 'SELECT 0, ''dummy'''

The generated query looks like this:

SELECT COUNT(*) AS row_numbers, 'pg_stat_xact_user_functions' AS table FROM pg_stat_xact_user_functions UNION ALL 
SELECT COUNT(*) AS row_numbers, 'pg_stat_archiver' AS table FROM pg_stat_archiver UNION ALL 
SELECT COUNT(*) AS row_numbers, 'pg_stat_bgwriter' AS table FROM pg_stat_bgwriter UNION ALL 
SELECT COUNT(*) AS row_numbers, 'pg_stat_progress_analyze' AS table FROM pg_stat_progress_analyze UNION ALL 
SELECT 0, 'dummy'

And the result of the generated query would be:

row_numbers   table
0             pg_stat_xact_user_functions
1             pg_stat_archiver
1             pg_stat_bgwriter
0             pg_stat_progress_analyze
0             dummy

I hope you find this information useful for your future projects or as a new tool in your toolkit! This can be a powerful technique for generating database queries on the fly or just a time saver when you need to run some maintenance tasks or fix some problems.

Let me know if you have any questions, if you’ve faced a similar situation in the past, or if this is helpful to you! Happy coding!🤗

❗️❗️No programmer or tech lead was harmed in the making of this article.❗️❗️