Data Diff is a free open-source command line tool to compare tables from different databases. It connects to SQL databases such as MySQL, Postgres, BigQuery, Snowflake and can generate a diff result when you give it a table name to compare. Along with table name, it takes a column name as a comparison parameter and then generates the result.
This tool is available as a service online and you can use that but that comes with limitations. But the command line tool is free and open source and you can even use it in production. Database tables having millions of rows can be compared with it and it can process them all with ease.
The diff result that it produces can be stored to a file in case you want to save it. With this, you can uncover data duplication and mismatches across same database tables across different DBMS. There is a very simple command you have to issue in order to use it which I will mention in the next section.
Free tool to Compare Tables across MySQL, Postgres, Databases: Data Diff
Data Diff tool can work on Linux, Mac, and Windows. All it needs is Python installed on your computer and then you can easily install and use it.
So, make sure that you have python and pip commands available in the terminal. Issue the command below to install it.
pip install data-diff
It also needs database drivers in order to communicate with the databases. You have to install the drivers separately for each database system that you are going to compare it with. In my case, I just want to compare a table across Postgres and MySQL, so I will issue these commands. But you check the other commands in its GitHub README.
pip install "data-diff[mysql]"
pip install "data-diff[postgres]"
Now, it is all ready to use. Run it in the syntax specified below and then hit enter. In order to obtain database connection string, you can follow this doc page.
data-diff DB_Connection1 TABLE1_NAME DB_Connection2 TABLE2_NAME [OPTIONS]
Finally, execute the command and then it will generate the diff result. You can compare any two tables from any database you have access to. It will process them all with ease and then show you the final results.
In this way, you can use this simple and powerful command line tool to compare tables of a database. Just give it connection strings along with table name and it will take care of rest. If you are a programmer then you can build GUI based on this and use it at work.
Final thoughts:
There are very few free database comparison tools out there which can be used in production. And Data Diff is one of them. I liked how it works and offers a lot of options to format the final results. If you are a database administrator or engineer then you will definitely find it useful.
