@leapfrogtechnology/sync-db
Advanced tools
Command line utility to synchronize and version control relational database objects across databases
Command line utility to synchronize and version control relational database objects across databases.
Using npm:
$ npm install @leapfrogtechnology/sync-db
You can install it globally as well.
$ npm install -g @leapfrogtechnology/sync-db
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.
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.1.0 linux-x64 node-v19.3.0
$ sync-db --help [COMMAND]
USAGE
$ sync-db COMMAND
...
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-dbsync-db help [COMMAND]sync-db make NAMEsync-db make-publishsync-db migrate-latestsync-db migrate-listsync-db migrate-rollbacksync-db prunesync-db synchronizesync-dbUSAGE
$ 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 NAMEMake 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-publishPublish 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-latestRun 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-listList 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-rollbackRollback 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 pruneDrop 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 synchronizeSynchronize 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.
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);
})();
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
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.
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.
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);
});
Check the CHANGELOG for release history.
Feel free to send pull requests.
# 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
# Generate build.
$ yarn build
# Run tests
$ yarn test
# Invoke the CLI locally (development mode).
$ bin/run-dev.sh
Publish a new version.
Create a PR updating version in package.json to master.
Licensed under The MIT License.
v1.1.0 (2022-12-20)
Implemented enhancements:
Changes
FAQs
Command line utility to synchronize and version control relational database objects across databases
The npm package @leapfrogtechnology/sync-db receives a total of 159 weekly downloads. As such, @leapfrogtechnology/sync-db popularity was classified as not popular.
We found that @leapfrogtechnology/sync-db demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 0 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Critics call the Node.js EOL CVE a misuse of the system, sparking debate over CVE standards and the growing noise in vulnerability databases.
Security News
cURL and Go security teams are publicly rejecting CVSS as flawed for assessing vulnerabilities and are calling for more accurate, context-aware approaches.
Security News
Bun 1.2 enhances its JavaScript runtime with 90% Node.js compatibility, built-in S3 and Postgres support, HTML Imports, and faster, cloud-first performance.