@leapfrogtechnology/sync-db

sync-db

Command line utility to synchronize and version control relational database objects across databases.

Setup

Installation

You can install it using npm or yarn.

$ npm install @leapfrogtechnology/sync-db
# OR
$ yarn add @leapfrogtechnology/sync-db

You can install it globally too.

$ npm install -g @leapfrogtechnology/sync-db
# OR
$ yarn global add @leapfrogtechnology/sync-db

Verify the installation.

$ sync-db --version

Additionally, you'll need to install the database driver specific to your project.

For instance - if you're using MSSQL, you can do:

$ yarn add mssql

This utility uses Knex under the hood so these are the supported drivers.

Configuring Connections

You'll need a connections-sync-db.json file in your project folder as shown below with all the databases connections.

{
  "connections": [
    {
      "id": "db1",
      "host": "localhost",
      "port": 1433,
      "user": "db1user",
      "database": "db1",
      "password": "password",
      "client": "mssql"
    },
    {
      "id": "db2",
      "host": "localhost",
      "port": 1433,
      "user": "db2user",
      "database": "db2",
      "password": "password",
      "client": "mssql"
    }
  ]
}

Directory Structure

1. The SQL codebase containing all your database objects need to follow the following directory structure.

 └─ sql
    ├─ schema
    │  ├─ schema1.sql
    │  ├─ schema2.sql
    │  ├─ schema3.sql
    │  └─ ...
    │
    ├─ function
    │  ├─ schema1
    │  │  ├─ function1.sql
    │  │  ├─ function2.sql
    │  │  └─ ...
    │  ├─ schema2
    │  │  ├─ function3.sql
    │  │  ├─ function4.sql
    │  │  └─ ...
    │  ├─ function5.sql
    │  └─ ...
    │
    ├─ procedure
    │  ├─ schema1
    │  │  ├─ procedure1.sql
    │  │  ├─ procedure2.sql
    │  │  └─ ...
    │  ├─ schema2
    │  │  ├─ procedure3.sql
    │  │  ├─ procedure4.sql
    │  │  └─ ...
    │  ├─ procedure5.sql
    │  └─ ...
    │
    └─...

Note: When procedures and functions aren't placed inside a schema folder, they are associated with the default schema.

1. Create sync-db.yml file in your project folder.

# Base path for the SQL source files.
# If omitted, "src/sql" will be the default base path.
basePath: /path/to/sql

sql:
  - schema/<schema_name>.sql
  - function/<schema_name>/<function_name>.sql
  - procedure/<schema_name>/<procedure_name>.sql

Usage

You can use sync-db as a CLI tool as well as within your scripts.

CLI

When installed globally, you can just invoke the CLI directly.

$ sync-db

For local installation you can trigger it with npx.

$ npx sync-db

Using npm script

You can also add a script into your project's package.json file like this:

{
  "scripts": {
    "sync-db": "sync-db"
  }
}

This allows you to trigger sync-db like this:

$ yarn sync-db

Or,

$ npm run sync-db

Programmatic usage

Use sync-db's synchronize function within your ts scripts. You can use synchronize as follows:

import * as Knex from 'knex';

import { loadConfig } from '@leapfrogtechnology/sync-db/lib/config';
import { synchronize } from '@leapfrogtechnology/sync-db/lib/migrator';

(async () => {
  // Load sync-db.yml
  const config = await loadConfig();

  const db = Knex({
    client: 'mssql',
    connection: {
      host: 'host',
      port: 'dbPort',
      user: 'userName',
      password: 'password',
      database: 'dbName'
    }
  });

  // Rollback and create all db objects using config.
  await synchronize(config, db, { force: false });

  // Perform other db operations
  // ...
})();

Caveat

Setup and Teardown steps aren't always run within a single transaction. You need to specifically pass a trx object to make sure this happens.

await db.transaction(async trx => {
  // Rollback and create all db objects using config.
  await synchronize(config, (trx as any), { force: false });

  // Perform other db operations
  // ...
});

Sample Projects

1. Node MSSQL Sample (JavaScript)

Changelog

Check the CHANGELOG for release history.

Contributing

Feel free to send pull requests.

License

Licensed under The MIT License.

Changelog

v1.0.0-alpha.3 (2019-08-26)

Full Changelog

Implemented enhancements:

• Enable passing connection instance as well as connection config for synchronization #10 (SafalPandey)

Changes

• Improvements on the README Documentation #7 (kabirbaidhya)