@leapfrogtechnology/sync-db

_See code: [src/commands/migrate-latest.ts](https://github.com/leapfrogtechnology/sync-db/blob/v2.0.1/src/commands/migrate-latest.ts)_

## `sync-db migrate-list`

List all the migrations.

_See code: [src/commands/migrate-rollback.ts](https://github.com/leapfrogtechnology/sync-db/blob/v2.0.1/src/commands/migrate-rollback.ts)_

## `sync-db prune`

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

_See code: [src/commands/synchronize.ts](https://github.com/leapfrogtechnology/sync-db/blob/v2.0.1/src/commands/synchronize.ts)_

<!-- commandsstop -->

Refer to the [examples](#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.

```ts
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);
})();

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 };

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

Configuration

1. Sync Configuration
2. Database Connections

1. Sync Configuration

# 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.

• migration (array) - Migrations specific configurations.

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

1. Database Connections

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

Caveat

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