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 a technique called dynamic queries 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." Chat GPT (thanks)
For our specific case, we needed to extract the 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 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 to generate database queries on the fly or just a time saver when you need to run some maintenance tasks or fix some problem
Let me know if you have any questions, if you face 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.❗️❗️
Top comments (0)