@leapfrogtechnology/sync-db

sync-db

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

Setup

Installation

$ yarn add @leapfrogtechnology/sync-db

Global Installation

$ yarn global add @leapfrogtechnology/sync-db

Install node database driver(s) of the database(s) that are to be synced, for example this is how you would install the mssql driver:

$ yarn add mssql

Note: If sync-db is installed globally, install database driver(s) globally too.

Configure Database Connections

Create connections-sync-db.json in your project folder and configure your database connection(s) to be synced.

{
  "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"
    }
  ]
}

Configure Path To SQL Database Objects

1. Copy the SQL files in your project in following folder 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.

basePath: /path/to/sql

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

Note: Default basePath is src/sql.

1. Specify the base path of the folder for SQL files in basePath key.

2. List database objects in the order they need to be synced under the sql key.

Usage

Add sync-db script in your package.json file.

Add sync-db script in package.json

1. Add script in your package.json

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

1. Run

$ yarn sync-db

Usage with npx

1. Install npx globally on your machine

npm install -g npx

1. Run

npx sync-db

Sample Projects

1. Node MSSQL Sample (JavaScript)

License

Licensed under The MIT License.

Changelog

v1.0.0-alpha.2 (2019-08-09)

Full Changelog

Changes

• Remove password attribute from connections object before logging #6 (SafalPandey)
• Set published source for sync-db #4 (shradayshakya)