data-sync

Data Sync is an automated tool for comparing database schemas and data. It can compare two databases, whether they are local or remote, and automatically produce a migration file of the differences.

If you find this extension useful for your projects, please consider supporting me by Github, Patreon, KO-FI or Paypal. It's a great way to help me maintain and improve this tool in the future. Your support is truly appreciated!

Installation

Get it from Visual Studio Marketplace or Open VSX Registry.

Features

Connects to a source and target database to perform the comparison.
Can compare changes to both the schema and data, and generate valid SQL to update the target database to match the source.
Allows you to ignore certain tables or fields during the comparison.
Currently only works with PostgreSQL, but we plan to expand to other databases based on user demand in the future.

Using the extension

Step 1: Configuration

On the first time, you need create the configuration. You can quickly create your configuration from Data Sync Explorer or Command Palette.

// A Boolean variable indicating whether to include verbose log in migrate data.
"verbose": boolean,

// List of migrate pattern, multiple pattern can be defined.
"patterns": {
    // Your defined pattern
    "patternName": {
        // Your source database connection section
        "source": {
            "user": string,
            "password": string,
            "host": string,
            "database": string,
            "port": number,

            // database connection string
            "connectionString": string,

            // passed directly to node.TLSSocket, supports all tls.connect options
            "ssl": object,

            // custom type parsers
            "types": object,

            // number of milliseconds before a statement in query will time out, default is no timeout
            "statement_timeout": number,

            // number of milliseconds before a query call will timeout, default is no timeout
            "query_timeout": number,

            // the name of the application that created this Client instance
            "application_name": string,

            // number of milliseconds to wait for connection, default is no timeout
            "connectionTimeoutMillis": number,

            // number of milliseconds a client must sit idle in the pool
            "idleTimeoutMillis": number,

            // maximum number of clients the pool should contain by default this is set to 10.
            "max": object,

            // default behavior is the pool will keep clients open & connected
            "allowExitOnIdle": boolean,

            // Number of milliseconds before terminating any session with an open idle transaction, default is no timeout
            "idle_in_transaction_session_timeout": number
        },

        // Your target database connection section
        "target": {
            "user": string,
            "password": string,
            "host": string,
            "database": string,
            "port": number
            // And other property you can defined (same as source)
        },

        // You diff options
        "diff": {
            // Auto format diff data (UNDER DEVELOPMENT)
            "format": true,

            // The table you want to diff changes.
            "tables": [
                {
                    // The database table schema
                    "schema": string,

                    // The database table name
                    "name": string,

                    // Exclude one or more columns
                    "excludes": Array<string>,

                    // Custom where query (.e.g customer_id = 'JHQMFFE8sJ')
                    "where": string,

                    // Custom order by column (.e.g iam_role_code, iam_policy_code)
                    "orderBy": string,

                    // Define columns you want get to compare, default all column
                    "columns": Array<string>,

                    // Define primary keys of table, to check table row is unique
                    "primaryKeys": Array<string>
                }
            ]
        },

        // Your migrate options
        "migrate": {
            // No generate the insert record.
            "noInsert": false,

            // No generate the update record.
            "noUpdate": false,

            // No generate the delete record.
            "noDelete": false,

            // Action on no row affected when migrate data
            "noRowAffected": "ignore" | "log" | "warn" | "throw",

            // Action on multiple row affected when migrate data
            "multipleRowAffected": "ignore" | "log" | "warn" | "throw"
        }
    }
}