I have a use case where I need to run the teardown script again after run and post_sync scripts have completed.

I think we need to provide access to the teardown and setup steps independently as well.

Something like:

1. teardown

   # Rolls back all the db objects
   $ sync-db --teardown

2. setup

   # Creates all the db objects
   $ sync-db --setup

3. setup

   # Rolls back and Creates all the db objects
   $ sync-db

Current Behavior

For sync-db CLI, the only way to provide database connections is through the connections.sync-db.json. There's no other way.

If you need to resolve connections in any other way (eg: retrieve it from a different service or network), you'll need to use the Programmatic API and write your own code to do so - which is the only workaround.

Proposal

Need an ability to define and use a custom connection resolver which resolves the database connections for the CLI.

What is a resolver?
A resolver is a javascript file resolver.js that exposes a method to resolve connections.

How would you define a resolver?
Create a connections.resolver.js that exports a function named resolve which return a Promise that resolves to a lis

// connections.resolver.js

/**
 * Resolve a list of database connections.
 *
 * @returns {Promise<ConnectionConfig[]>}
 */
export function resolve() {
  // Do something here to resolve your database connections.
  const connections = [ ... ];

  return connections;
}

Or, this should be supported too.

// connections.resolver.js

exports.resolve = function() {
  // Do something here to resolve your database connections.
  const connections = [ ... ];

  return connections;
};

Use Cases

How database credentials are managed, is user-specific and might differ from project to project. For example - some might use it via environment variables, or a .env file and in case there are multiple databases, they might have a json file similar to connections.sync-db.json file.

In addition, you might need to fetch these creds from external services like Vault or AWS Secret Manager which would definitely be the case if you have a fully cloud native infrastructure.

All of these use cases are possible using a custom resolver file, where user themselves provide us how to resolve their database connections - it could be from other files, configurations or from across the network or services.

Other Requirements

1. This should be an opt-in feature. A resolver file is used only if connections.resolver.js exists in the CWD.
2. Using a resolver would be mutually exclusive to using the connections.sync-db.json; it should not use that file if resolver exists.
3. Validate the resolver definition and database connections (against the schema) before proceeding further in the synchronization process.

Reference

See how connections are resolved now here 👉 https://github.com/leapfrogtechnology/sync-db/blob/master/src/config.ts#L32-L56.

No one is willing to wait 182 days for a CI job to finish.

Setup docker and docker-compose configuration for the node example demonstrated here - https://github.com/leapfrogtechnology/sync-db/tree/master/examples/node-app-mssql.

The example projects should be simply runnable just a simple command.

$ cd examples/node-app-mssql
$ docker-compose up

Add new example project based on postgres.

Requirements

1. Add the sample project under examples/node-app-pg
2. Make it similar to the MSSQL example here but use postgres insteaad of MSSQL.
3. Requires all the functionalities, docs, migrations like what the mssql example has.
4. Add the new example link under the main README here

Add sample tables for the node-app-mssql example.

What needs to be done?

• Put migrations (use knex) for creating sample tables under examples/node-app-mssql/src/migrations
• Add instructions for running migrations in the README
• Update the codebase such that output displays the list of tables you've created like this:
  

Feature Discussion

Batch jobs with preparation scripts

Problem background

One common use case is to run one-time batch job or data operation - which is a series of SQL scripts to be run on N databases.

Consider these examples:

• ETL job running a series of data transformation logic (procedure calls) on source tables to prepare destination tables with processed data
• data analysis - series of data processing (procedure calls) to analyse the data from tables and write analysed outputs to destination tables.
• or any kind of batch data operations job

In these kinds of jobs, the logical components are not needed in the DB once the job is complete so they could be dropped.

Proposed Implementation

Example of the config of how we can achieve this.

sql:
  prep:
    # These are the preparation scripts or the dependencies required by the batch script
    - schema/transformation.sql
    - view/transformation/view1.sql
    - view/transformation/view1.sql
    - function/transformation/some_func.sql
    - procedure/transformation/proc1.sql
    - procedure/transformation/proc2.sql

hooks:
  script:
   # The actual batch script or a series of these
    - scripts/run_transform.sql

Steps

• drop (if exists) the objects under sql.prep in reverse order
• create the objects under sql.prep in the normal order
• run the sql scripts hooks.script
• drop (if exists) the objects under sql.prep in reverse order

This isn't a database synchronization operation but a general batch job running mechanism.

TLDR - no db objects exists in the db once the batch job (script) is executed.

@mesaugat @SafalPandey Let me know what do you think?

Issue

When the folder names where the database objects are in do not match one of procedure, schema, view or function, the logs explicitly say Rolling back <object name> and Executed: <xx> ms However the objects aren't removed from the database.

Expected behavior

I think it should raise an error instead. We need to make it clear that the directory name did not match the expected convention and raise an error if rollback script is not executed.

Log trail

  sync-db:db1 Synchronize database +0ms
  sync-db:db1 Running rollback on a database: db1 +0ms
  sync-db:db1 Rolling back: etl.noop +0ms
  sync-db:db1 Executed:   +48ms
  sync-db:db1 Rolling back: etl +0ms
  sync-db:db1 Executed:   +2ms
  sync-db:db1 Finished running rollback +273ms
  sync-db:db1 Running setup +0ms
  sync-db:db1 Running schemas/etl.sql +0ms
  sync-db:db1 Running procedures/etl/noop.sql +0ms
  sync-db Error caught:  { TransactionError: Transaction has been aborted.
...

Add an example with showing the programmatic usage in Node / JavaScript.

• yml configuration i used with sync-db version ---> 1.0.0-experimental.20200628
  

Scenario:

• while trying just to synchronize some tables, didn't want any migration to run
• so i simply removed migration field in yml config and ran synchronize command but it errored out as shown below:
  

So it looks like migration field is a compulsary in yml right now and had to use --skip-migration option as way around.

cc @kabirbaidhya

Instead of verbose error only[object Object] is displayed in the case of a corrupt migration file.

It only displays errors while running in a debug mode.

cc @SafalPandey

Issue

• Migrations not logged properly

Replication

• Run migrate-list command for given set of migration

sync-db has been fairly stable and is being used in production for quite a while. With the recent bug fixes it seems more and more likely that we should get it out of beta and take it to v1.0.0.

Word on the street: If we want to make @dependabot happy, we can fix the security warnings and release version 1.0.0 to the world.

Node 10 has already dropped active support and it's security support is ending in a few months time.

• Update package.json engine version to use Node 12 LTS and above
• Remove Node 10 from .travis.yml and include Node 12, 14
• Upgrade packages that don't support Node 12

Resolve the basePath relative to the sync-db.yml path instead of current working directory.

Issue recreated here: https://github.com/silwalanish/sync-db-bug-reports/tree/main/base-path-resolution

JS/TS knex migration support was recently added. #88

Include examples of JS/TS migrations with sync-db.

CI - Integrate code coverage reporting.

• Report code coverage
• Add code coverage badge in the README.

Database objects not deleted from db when removed from sync-db.yml file.

CLI Usage

$ sync-db --generate-connections

Requirements

1. Introduce a CLI argument / flag --generate-connections that if invoked with - should generate a connections.sync-db.json file.
2. The file should be generated in the path same as sync-db.yml.
3. If successful it should print out a message with the generated file path.
4. In case of failure it should display the error message of why it failed.
5. Use these environment variables to get the credentials:
   • DB_HOST - Database Host
   • DB_PASSWORD - Password
   • DB_NAME - Database name
   • DB_USERNAME - Database username
   • DB_PORT - Port
   • DB_CLIENT - Database client eg: mssql, mysql
   • DB_ENCRYPTION - Boolean value to set whether or not to use encrypted connection. Should be false by default unless explicitly set as true.

Proof of concept / demo - the feature explained above should work in the same way as this demo bash script below.

echo '{
  "connections": [
    {
      "client": "'$DB_CLIENT'",
      "host": "'"$DB_HOST"'",
      "port": '"$DB_PORT"',
      "user": "'"$DB_USERNAME"'",
      "password": "'"$DB_PASSWORD"'",
      "database": "'"$DB_NAME"'",
      "options": {
        "encrypt": '"${DB_ENCRYPTION:=false}"'
      }
    }
  ]
}' > 'connections.sync-db.json'

• --print-sql will be helpful in debugging and will also help while doing a dry run

• Make sure every example is easy to run and test (see #112)
• Bump sync-db to latest version >= beta.10
• Remove security vulnerabilities and update dependencies + remove unwanted dependencies
• Update example documentation

Examples:

• https://github.com/leapfrogtechnology/sync-db/tree/master/examples/node-app-mssql-ts
• https://github.com/leapfrogtechnology/sync-db/tree/master/examples/node-app-pg-ts
• https://github.com/leapfrogtechnology/sync-db/tree/master/examples/node-app-mssql
• https://github.com/leapfrogtechnology/sync-db/tree/master/examples/node-app-pg
• https://github.com/leapfrogtechnology/sync-db/tree/master/examples/node-mssql-programmatic-use