sync-db

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

Installation

Using npm:

$ npm install @leapfrogtechnology/sync-db

Drivers Installation

You'll need to install the database driver specific to your project separately.

For instance - if your project uses MSSQL, you will need to do:

$ yarn add tedious

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

Usage

You can use sync-db both as a CLI utility and programmatically.

$ npm install -g @leapfrogtechnology/sync-db
$ sync-db COMMAND
running command...
$ sync-db (--version|-v)
@leapfrogtechnology/sync-db/2.3.1 darwin-arm64 node-v22.23.3
$ sync-db --help [COMMAND]
USAGE
  $ sync-db COMMAND
...

Commands

When installed globally, you can invoke the CLI directly.

The CLI exposes a single command sync-db that runs synchronize operation based on your configuration.

• sync-db help [COMMAND]
• sync-db make NAME
• sync-db make-publish
• sync-db migrate-latest
• sync-db migrate-list
• sync-db migrate-rollback
• sync-db prune
• sync-db synchronize
• sync-db version

sync-db help [COMMAND]

Display help for sync-db.

USAGE
  $ sync-db help [COMMAND...] [-n]

ARGUMENTS
  [COMMAND...]  Command to show help for.

FLAGS
  -n, --nested-commands  Include all nested commands in the output.

DESCRIPTION
  Display help for sync-db.

See code: @oclif/plugin-help

sync-db make NAME

Make migration files from the template.

USAGE
  $ sync-db make NAME [--object-name <value>] [--create] [-c <value>] [-t TYPE]

ARGUMENTS
  NAME  Object or filename to generate.

FLAGS
  -c, --config=<value>       Custom configuration file.
  -t, --type=TYPE            [default: migration] Type of file to generate.
      --create               Generate create table stub.
      --object-name=<value>  Name of table/view/routine to migrate.

DESCRIPTION
  Make migration files from the template.

See code: src/commands/make.ts

sync-db make-publish

Publish migration templates files.

USAGE
  $ sync-db make-publish [-c <value>]

FLAGS
  -c, --config=<value>  Custom configuration file.

DESCRIPTION
  Publish migration templates files.

See code: src/commands/make-publish.ts

sync-db migrate-latest

Run the migrations up to the latest changes.

USAGE
  $ sync-db migrate-latest [--dry-run] [--only CONNECTION_ID(s)] [--connection-resolver PATH] [-c <value>]

FLAGS
  -c, --config=<value>            Custom configuration file.
      --connection-resolver=PATH  Path to the connection resolver.
      --dry-run                   Dry run migration.
      --only=CONNECTION_ID(s)     Filter provided connection(s). Comma separated ids eg: id1,id2

DESCRIPTION
  Run the migrations up to the latest changes.

See code: src/commands/migrate-latest.ts

sync-db migrate-list

List all the migrations.

USAGE
  $ sync-db migrate-list [--only CONNECTION_ID(s)] [--connection-resolver PATH] [-c <value>]

FLAGS
  -c, --config=<value>            Custom configuration file.
      --connection-resolver=PATH  Path to the connection resolver.
      --only=CONNECTION_ID(s)     Filter provided connection(s). Comma separated ids eg: id1,id2

DESCRIPTION
  List all the migrations.

See code: src/commands/migrate-list.ts

sync-db migrate-rollback

Rollback migrations up to the last run batch.

USAGE
  $ sync-db migrate-rollback [--dry-run] [--only CONNECTION_ID(s)] [--connection-resolver PATH] [-c <value>]

FLAGS
  -c, --config=<value>            Custom configuration file.
      --connection-resolver=PATH  Path to the connection resolver.
      --dry-run                   Dry run rollback.
      --only=CONNECTION_ID(s)     Filter provided connection(s). Comma separated ids eg: id1,id2

DESCRIPTION
  Rollback migrations up to the last run batch.

See code: src/commands/migrate-rollback.ts

sync-db prune

Drop all the synchronized db objects except the ones created via migrations.

USAGE
  $ sync-db prune [--dry-run] [--only CONNECTION_ID(s)] [--connection-resolver PATH] [-c <value>]

FLAGS
  -c, --config=<value>            Custom configuration file.
      --connection-resolver=PATH  Path to the connection resolver.
      --dry-run                   Dry run prune.
      --only=CONNECTION_ID(s)     Filter provided connection(s). Comma separated ids eg: id1,id2

DESCRIPTION
  Drop all the synchronized db objects except the ones created via migrations.

See code: src/commands/prune.ts

sync-db synchronize

Synchronize all the configured database connections.

USAGE
  $ sync-db synchronize [-f] [--dry-run] [--skip-migration] [--sync-files FILE_PATH(s)] [--only CONNECTION_ID(s)]
    [--connection-resolver PATH] [-c <value>]

FLAGS
  -c, --config=<value>            Custom configuration file.
  -f, --force                     Force synchronization.
      --connection-resolver=PATH  Path to the connection resolver.
      --dry-run                   Dry run synchronization.
      --only=CONNECTION_ID(s)     Filter provided connection(s). Comma separated ids eg: id1,id2
      --skip-migration            Skip running migrations.
      --sync-files=FILE_PATH(s)   Comma separated relative file paths to sync. When provided, skips teardown and only
                                  syncs specified files.

DESCRIPTION
  Synchronize all the configured database connections.

See code: src/commands/synchronize.ts

sync-db version

USAGE
  $ sync-db version [--json] [--verbose]

FLAGS
  --verbose  Show additional information about the CLI.

GLOBAL FLAGS
  --json  Format output as json.

FLAG DESCRIPTIONS
  --verbose  Show additional information about the CLI.

    Additionally shows the architecture, node version, operating system, and versions of plugins that the CLI is using.

See code: @oclif/plugin-version

Refer to the examples section below for full example with CLI usage.

Programmatic Usage

You may use programmatic API as shown below in case you need better flexibility based on your needs.

import { synchronize, loadConfig, resolveConnections } from '@leapfrogtechnology/sync-db';

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

  // Invoke the command.
  await synchronize(config, connections);
})();

You can also pass your own database connection (eg: Knex connection) instead of resolving connections.sync-db.json file.

import { knex } from 'knex';
import { synchronize, loadConfig } from '@leapfrogtechnology/sync-db';

(async () => {
  const config = await loadConfig(); // Load sync-db.yml
  const connection = knex({
    // Your Knex connection instance.
    client: 'mssql',
    connection: {
      host: 'host',
      user: 'userName',
      password: 'password',
      database: 'dbName'
    }
  });
  const options = {
    force: false,
    'skip-migration': false,
    'sync-files': ['functions/dbo/fn_calculate.sql', 'views/dbo/vw_summary.sql'] // Optional: partial sync
  };

  // Invoke the command.
  await synchronize(config, connection, options);
})();

Configuration

1. Sync Configuration
2. Database Connections

1. Sync Configuration

sync-db expects the configuration file sync-db.yml to be present in your working directory. This holds all your configurations.

sync-db.yml

# Base path for the SQL source files.
basePath: /path/to/sql

sql:
  - schema/<schema_name>.sql
  - function/<schema_name>/<function_name>.sql.drop # While synchronizing this will only be dropped, not synced.
  - procedure/<schema_name>/<procedure_name>.sql

Configuration Options

• basePath (string) - Base directory to hold all your SQL & migrations codebase (default: "src").

• sql (array) - A series of SQL file paths that are to be run in ordered sequence (top to bottom), based on dependency. It should be noted that the source files needs to follow this convention of directory hierarchy. File paths listed here are relative to ${basePath}/sql value.

• migration (array) - Migrations specific configurations.

  • sourceType (string) - Type of migration file. Value defaults to sql. - example: javascript, typescript.
    • TypeScript migrations are compiled on the fly with ts-node. With TypeScript 6+ your project needs a tsconfig.json (e.g. module: nodenext, an explicit rootDir) - see the TypeScript samples, which include a working tsconfig.json.
  • tableName (string) - Custom name for table to store migrations meta data.

• connectionResolver (string) - Connection resolver file name optional if connections are resolved using connections.sync-db.json.

2. Database Connections

Database connections are configured in connections.sync-db.json file in your project root directory as shown below.

Since it contains all your database credentials, it is recommended that you DO NOT COMMIT it to VCS.

connections.sync-db.json

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

Note: The connections key expects an array, so you can also provide multiple databases and sync-db ensures your configured db objects are synced across all these databases.

Connection using connection-resolver.js

File consists a resolve function which returns an array of connections to the databases. Add the resolver file name to connectionResolver field in sync-db.yml.

Caveat

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

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

Examples

1. Node MSSQL TypeScript Sample - includes programmatic usage
2. Node PostgreSQL TypeScript Sample - includes programmatic usage

Changelog

Check the CHANGELOG for release history.

Contributing

Feel free to send pull requests.

Development

Setting up

# Clone the repository.
$ git clone https://github.com/leapfrogtechnology/sync-db.git

# Go to the project directory.
$ cd sync-db

# Install dependencies. (Notice that we use yarn for this.)
$ yarn

Building / Testing

# Generate build.
$ yarn build

# Run tests
$ yarn test

# Invoke the CLI locally (development mode).
$ bin/run-dev.sh

Release

Publish a new version.

Create a PR updating version in package.json to master.

License

Licensed under The MIT License.