DEV Community

loading...

How to compare two databases, identify the diferences and generate the necessary SQL to update the structure of one of them

Lawrence Lagerlof
Avid Isaac Asimov reader. Backend and frontend developer.
Updated on ・1 min read

Tool: DBDiff (https://github.com/DBDiff/DBDiff)

Note: It works on Linux or Windows

What do you need

  • PHP, GIT and Composer on PATH
  • Credentials to access the source and target databases

Quick start

  • Download and enter the DBDiff directory
$ git clone https://github.com/DBDiff/DBDiff.git
$ cd DBDiff
$ composer update
Enter fullscreen mode Exit fullscreen mode
  • Create a .dbdiff file in DBDiff directory with the connection settings for 2 servers. Add to "tablesToIgnore" a list of tables and views that should be ignored on comparisson.

.dbdiff file:

server1:
    user: root
    password:
    port: 3306
    host: localhost
server2:
    user: AzureDiamond
    password: hunter2
    port: 3306
    host: 192.168.1.1
template: templates/db-migrate.tmpl
type: schema
include: up
nocomments: true
tablesToIgnore:
- view1
- view2
- table5
Enter fullscreen mode Exit fullscreen mode
  • Create a db-migrate.tmpl file in templates directory with the content:
-- UP

{{ $up }}

-- END UP

Enter fullscreen mode Exit fullscreen mode
  • Execute the command below to generate the database diff file migration.sql
$ php dbdiff server1.yourdatabase:server2.yourdatabase
Enter fullscreen mode Exit fullscreen mode
  • Run the commands that are inside the migration.sql using a database client of your preference on server2.yourdatabase to upgrade your database. I recommend review the commands before running them.

Discussion (1)

Forem Open with the Forem app