blzshared-rds-sync

This package includes an implementation for database change management of blazedpath solutions.

Requirements

To get started, you must meet the following requirements:

Configure blazedpath registry

Because this is a private scoped project, distributed through a private npm enterprise registry, you must set the proper registry config to resolve the package.

In order to request this package from a private registry, you need to configure @blazedpath as a scoped registry.

The following are some of the ways to associate @blazedpath scope with a private registry:

You can associate the scope with a registry at login using your credentials (user/pass) - per-user config file. (~/.npmrc)

npm login --registry=https://repository.core.blazedpath.com/repository/shared/ --scope=@blazedpath

And you’ll end up with a line in your ~/.npmrc file that looks like this:

@blazedpath:registry=https://repository.core.blazedpath.com/repository/shared/
//repository.core.blazedpath.com/repository/shared/:_authToken=NpmToken.abcdef-xxxxx-xxxx-xxxx-xyz

You can also associate the scope with a registry by using npmrc configuration file - per-project config file. (/path/to/my/project/.npmrc) This is the recommended way to avoid any conflict between projects.

You have to create a .npmrc file at your project's root with something like this:

@blazedpath:registry=https://repository.core.blazedpath.com/repository/shared/
//repository.core.blazedpath.com/repository/shared/:_authToken=${NPM_SHARED_TOKEN}

As you see to make this more secure when pushing it up to a server for sharing, you can set the auth token as an environment variable so you will also need to add NPM_SHARED_TOKEN to your environment variables on your development machine.

Note: Run the npm login detailed in the above steps and complete the login procedure by providing a username, password and an email address, in order to generate the proper authentication token - this effectively creates the ~/.npmrc file for you. Now, open ~/.npmrc file at your home and copy the related config to your project's root .npmrc file.

Please note that if you change your npm password or execute npm logout on a machine that is logged with that token then you'll need to update your NPM_TOKEN with a new one.

Install required dependency

This project requires the electron-rebuild library in order to rebuild the oracledb add-on.

npm install electron-rebuild

Installation

npm install @blazedpath/blzshared-rds-sync

Introduction

This is a data synchronization module to keep in sync the solution model with database schema that supports the following features:

• Use of plain SQL scripts
• Multiple blazedpath compliant databases support
• Destructive changes indicator to guaranteed preservation of data

Usage

To manage schema changes, what you need to do, would be the following:

1. Have a Relational Database System configured in your blazedpath solution
2. Verify if the proper Connection Data are setted using the checkConnection() method
3. Get snapshots diff and synchronize data models using the operation methods

API

boolean checkConnection(ConnectionData connection)

Allows to verify a Relational Database connection configuration. Tests connection and authentication. If it returns an error, then something is not correct, otherwise the server is ready to accept operations.

The connection is a Relational Database connection.

DbRules getDbRules(ConnectionData connection)

Gets information about SQL data types supported from a specific blazedpath compliant database provider for mapping to, or from, the actual database data type.

The connection is a Relational Database connection.

See DbRules for more details.

RdsObjects getConnectionObjects(ConnectionData connection)

Provides information about tables, views and procedures of a specific database to use for introspection actions.

The connection is a Relational Database connection.

See RdsObjects and Database Introspection for more details.

Snapshot getConnectionSnapshot(ConnectionData connection, Options options)

Gets a snapshot of the whole database schema information of a specific database at a given time.

The connection is a Relational Database connection.

The options is an object which can be passed to adjust the snapshot operation.

See Options and Snapshot for more details.

Snapshot getSolutionSnapshot(ConnectionData connection, ModelTables modTables, DbRules dbRules)

Gets a snapshot of the whole data model schema information of a specific blazedpath solution at a given time.

The connection is a Relational Database connection.

The modTables is an object that holds the data model table definitions of a Relational Database System in a blazedpath solution.

The dbRules is a DbRules object with SQL data types information supported by a database provider.

See ModelTables, DbRules and Snapshot for more details.

Differences getSnapshotsDifferences(Snapshot connectionSnapshot, Snapshot solutionSnapshot, DbRules dbRules, Config config)

Compares and gets the differences if two snapshots are considered not in sync.

The connectionSnapshot is a snapshot of a database schema.

The solutionSnapshot is a snapshot of a solution model.

The dbRules is a DbRules object with SQL data types information supported by a database provider.

The config is an object which can be passed to adjust the schema comparison.

See Snapshot, DbRules, Config and Differences for more details.

ScriptsStructure[] getScriptsStructure(ConnectionData connection, Differences differences)

Gets plain SQL scripts from snapshots differences to apply as schema changes to the target database.

The connection is a Relational Database connection.

The differences between two snapshots.

See Differences and ScriptsStructure for more details.

void applyScriptsStructure(ConnectionData connection, string[] scripts)

Executes the schema update SQL scripts against the database.

The connection is a Relational Database connection.

The scriptsStructure are the sentences from each ScriptsStructure to apply in a database.

Relational Databases

In order to start operating in a database, you must have configured a Relational Database System in your blazedpath solution with proper connection data setted.

Supported Providers

This synchronization module supports any database provider that blazedpath does. For now, that means:

• MySql
• MySqlx
• Oracle
• Postgres

ConnectionData

The connection data properties are provider specific configured by the blazedpath solution and look like this one for a MySqlx provider:

Example:

let connectionData = {
    'providerName': 'MySqlx',
    'host': 'localhost',
    'user': 'john',
    'password': '1234',
    'database': 'world'
}

See Database Providers for more details.

Model Tables

Data models of blazedpath solutions are backed by json metadata elements representing data base tables like this one:

Example:

let modelTable = {
    'autoincrementalPrimaryKey': 'false',
    'columns': [
        ...    
    ],
    'foreignKeys': [
        ...    
    ],
    'indexes': [
        ...    
    ],
    'isView': false,
    'primaryKeyName': 'PRIMARY',
    'sequenceName': '',
    'tableName': 'city',
    'uniqueKeys': []
}

See Database Tables for more details.

Database Synchronization

The data synchronization flow is:

1. Validate the database connection by authenticating and executing a provider specific validation operation
2. Create and retrieve a schema snapshot of the target database
3. Create and retrieve a schema snapshot of the blazedpath solution data model
4. Perform schema comparison between related snapshots
5. If there is any difference create schema update SQL scripts.
6. Synchronize blazedpath solution data model to the database by executing schema update SQL scripts

Note: Preservation of data in general is not guaranteed because schema changes such as the deletion of a database column can destroy data.

See Database Synchronization for more details.

Object Types

DbRules

DbRules object consists of the following properties:

• supportSequences (boolean) Determine if the database support sequences
• dbTypesToTypes (DbTypeToType[]) Array of mappings between the database and data model data type
• typesToDbTypes (TypeToDbType[]) Array of mappings between the data model and database data type

See DbTypeToType and TypeToDbType for more details.

DbTypeToType

DbTypeToType object consists of the following properties:

• dbType (RegExp) Regular expression that describes a database type
• type (string) The name of a data model type

TypeToDbType

TypeToDbType object consists of the following properties:

• type (RegExp) Regular expression that describes a data model type
• dbType (string) The name of a database type

RdsObjects

RdsObjects object consists of the following properties:

• tables (string[]) Array of database table names
• views (string[]) Array of database view names
• procedures (string[]) Array of database procedure names

Config

Config object consists of the following properties:

• includeDropTables (boolean) If true drop table sentences are considered within schema comparison (defaults to false).
• includeDropSequences (boolean) If true drop sequence sentences are considered within schema comparison (defaults to false).

Options

Snapshot Options object consists of the following properties:

• tables (string[]) Optional. Array of database table/view names to filter
• procedures (string[]) Optional. Array of database procedure names to filter
• excludeTable (boolean) If true table objects are discarded from the snapshot (defaults to false).
• excludeView (boolean) If true view objects are discarded from the snapshot (defaults to false).
• excludeProcedure (boolean) If true procedure objects are discarded from the snapshot (defaults to false).

Snapshot

Snapshot object consists of the following properties:

• tables (RdsTable[]|RdsView[]) Array of table/view definitions
• sequences (RdsSequence[]) Array of sequence definitions
• procedures (RdsProcedure[]) Array of procedure definitions

See RdsTable, RdsView, RdsSequence and RdsProcedure for more details.

RdsTable

• tableName (string) The name of the table
• columns (RdsColumn[]) Array of column definitions that conform the table
• primaryKey (RdsPrimaryKey) The primary key definition
• foreignKeys (RdsForeignKey[]) Array of foreign key definitions
• uniqueKeys (RdsIndex[]) Array of unique key definitions
• indexes (RdsIndex[]) Array of index definitions
• autoincrementalPrimaryKey (boolean) Determine if the primaryKey has auto-incremented values

See RdsColumn, RdsPrimaryKey, RdsForeignKey and RdsIndex for more details.

RdsColumn

• columnName (string) The name of the column
• dbType (string) The data type of the column
• nullable (boolean) Determine if the column support null values

RdsView

• tableName (string) The name of the view
• isView (boolean) Determine if the table is a view. True in this case
• columns (RdsColumnView[]) Array of column definitions that conform the view.

See RdsColumnView for more details.

RdsColumnView

• columnName (string) The name of the column
• dbType (string) The data type of the column

RdsPrimaryKey

• primaryKeyName (string) The name of the primary key
• columns (string[]) Array of columns that conform the key

RdsForeignKey

• tableName (string) The name of the table
• foreignKeyName (string) The name of the foreign key
• relatedTableName (string) The name of the related table
• columns (string[]) Array of column names that conform the key
• relatedColumns (string[]) Array of related column names that conform the key

RdsIndex

• name (string) The name of the index
• columns (string[]) Array of columns that conform the index

RdsSequence

• sequenceName (string) The name of the sequence

RdsProcedure

• procedureName (string) The procedure name
• parameters (RdsProcedureParameter[]) Bind parameters

See RdsProcedureParameter for more details.

RdsProcedureParameter

• direction (string) The direction of the bind
• name (string) The name of the parameter
• type (string) The data type to be bound

Differences

Differences object consists of the following properties:

• tablesToAdd (RdsTable[]) Tables to add in the database
• tablesToRemove (RdsTable[]) Tables to remove from the database
• tablesToModify (RdsTableToModify[]) Tables to modify in the database
• sequencesToAdd (RdsSequence[]) Sequences to add in the database
• sequencesToRemove (RdsSequence[]) Sequences to remove from the database

See RdsTable, RdsTableToModify and RdsSequence for more details.

RdsTableToModify

• tableName (string) The name of the table
• columnsToAdd (RdsColumn[]) Columns to add in the table
• columnsToRemove (RdsColumn[]) Columns to remove from the table
• columnsToModify (RdsColumn[]) Columns to modify in the table
• primaryKeyToAdd (RdsPrimaryKey) Primary key to add in the table
• primaryKeyToRemove (RdsPrimaryKey) Primary key to remove from table
• foreignKeysToAdd (RdsForeignKey[]) Foreign keys to add in the table
• foreignKeysToRemove (RdsForeignKey[]) Foreign keys to remove from the table
• uniqueKeysToAdd (RdsIndex[]) Unique keys to add in the table
• uniqueKeysToRemove (RdsIndex[]) Unique keys to remove from the table
• indexesToAdd (RdsIndex[]) Indexes to add in the table
• indexesToRemove (RdsIndex[]) Indexes to remove from the table

See RdsColumn, RdsPrimaryKey,RdsForeignKey and RdsIndex for more details.

ScriptsStructure

ScriptsStructure object consists of the following properties:

• description (string) The script description
• sentences (string[]) SQL statements of the schema update script