Nested Parenthetical Madness

    @leapfrogtechnology/sync-db
    TypeScript icon, indicating that this package has built-in type declarations

    1.0.1 • Public • Published

    sync-db

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

    Version Travis PRs Welcome LICENSE

    Installation

    Using npm:

    $ npm install @leapfrogtechnology/sync-db

    You can install it globally as well.

    $ npm install -g @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 mssql
    

    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 (-v|--version|version)
    @leapfrogtechnology/sync-db/1.0.1 linux-x64 node-v17.2.0
    $ 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

    USAGE
      $ sync-db
    

    See code: src/commands/index.ts

    sync-db help [COMMAND]

    display help for sync-db

    USAGE
      $ sync-db help [COMMAND]
    
    ARGUMENTS
      COMMAND  command to show help for
    
    OPTIONS
      --all  see all commands in CLI
    

    See code: @oclif/plugin-help

    sync-db make NAME

    Make migration files from the template.

    USAGE
      $ sync-db make NAME
    
    ARGUMENTS
      NAME  Object or filename to generate.
    
    OPTIONS
      -c, --config=config        Custom configuration file.
      -t, --type=TYPE            [default: migration] Type of file to generate.
      --create                   Generate create table stub.
      --object-name=object-name  Name of table/view/routine to migrate.
    

    See code: src/commands/make.ts

    sync-db make-publish

    Publish migration templates files.

    USAGE
      $ sync-db make-publish
    
    OPTIONS
      -c, --config=config  Custom configuration file.
    

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

    sync-db migrate-latest

    Run the migrations up to the latest changes.

    USAGE
      $ sync-db migrate-latest
    
    OPTIONS
      -c, --config=config         Custom configuration file.
      --connection-resolver=PATH  Path to the connection resolver.
      --dry-run                   Dry run migration.
      --only=CONNECTION_ID        Filter only a single connection.
    

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

    sync-db migrate-list

    List all the migrations.

    USAGE
      $ sync-db migrate-list
    
    OPTIONS
      -c, --config=config         Custom configuration file.
      --connection-resolver=PATH  Path to the connection resolver.
      --only=CONNECTION_ID        Filter only a single connection.
    

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

    sync-db migrate-rollback

    Rollback migrations up to the last run batch.

    USAGE
      $ sync-db migrate-rollback
    
    OPTIONS
      -c, --config=config         Custom configuration file.
      --connection-resolver=PATH  Path to the connection resolver.
      --dry-run                   Dry run rollback.
      --only=CONNECTION_ID        Filter only a single connection.
    

    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
    
    OPTIONS
      -c, --config=config         Custom configuration file.
      --connection-resolver=PATH  Path to the connection resolver.
      --dry-run                   Dry run prune.
      --only=CONNECTION_ID        Filter only a single connection.
    

    See code: src/commands/prune.ts

    sync-db synchronize

    Synchronize all the configured database connections.

    USAGE
      $ sync-db synchronize
    
    OPTIONS
      -c, --config=config         Custom configuration file.
      -f, --force                 Force synchronization.
      --connection-resolver=PATH  Path to the connection resolver.
      --dry-run                   Dry run synchronization.
      --only=CONNECTION_ID        Filter only a single connection.
      --skip-migration            Skip running migrations.
    

    See code: src/commands/synchronize.ts

    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 * as 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 };
    
      // 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
      - 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.
      • 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 JavaScript Sample
    2. Node MSSQL TypeScript Sample
    3. Node MSSQL Programmatic Usage Sample
    4. Node PostgreSQL JavaScript Sample
    5. Node PostgreSQL TypeScript Sample

    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.

    FOSSA Status

    Install

    npm i @leapfrogtechnology/sync-db

    DownloadsWeekly Downloads

    104

    Version

    1.0.1

    License

    MIT

    Unpacked Size

    144 kB

    Total Files

    123

    Last publish

    Collaborators

    • mesaugat