Migrations

DOCS • Concepts

---

As against the conventional script-based migrations approach, Linked QL follows a diff-based approach that lets you manage your entire DB structure declaratively out of a single schema.json (or schema.yml) file!

How It Works

A schema.json file has the general form:

[
    {
        // Database name
        "name": "database_1",
        // List of tables
        "tables": []
    },
    {
        // Database name
        "name": "database_2",
        // List of tables
        "tables": []
    }
]

More details in the schema.json spec.

If you had the above somewhere in your application, say at ./database/schema.json, Linked QL would help keep it in sync both ways with your database:

• you add or remove a database object or table object or column object... and it is automatically reflected in your DB structure at the click of a command: linkedql commit
• other people make changes to the same DB from their codebase... and it is automatically reflected in your local copy at your next git pull, or at the click of a command: linkedql refresh

You gain it in operational simplicity! No need to maintain a growing list of migration files or manually write your DDL commands and their corresponding rollback commands.

To Setup

First, make a directory within your application for database concerns. Linked QL will look in ./database/ by default, but you are able to point Linked QL to your custom location when running Linked QL commands.

Next, have a driver.js file in that directory that has a default export function that returns a Linked QL instance. This will be imported and used by Linked QL to interact with your database. This could look something like:

import pg from 'pg';
import { SQLClient } from '@linked-db/linked-ql/sql';

export default async function() {
    const pgClient = new pg.Client({
        host: 'localhost',
        port: 5432,
    });
    await pgClient.connect();
    const client = new SQLClient(pgClient, { dialect: 'postgres' });
    return client;
}

Now, have your DB structure defined in a schema.json (or schema.yml) file in that directory. (Use the schema.json spec as a guide.)

To Operate

Simply edit your schema file in-place: add new objects, drop existsing ones, or modify existsing objects in-place to their smallest detail/attribute.

On having made changes:

• Use linkedql commit to walk through your staged local changes and interactively perform a commit against your database.
• Use linkedql rollback to walk through the latest savepoint at each database and interactively perform a rollback operation.
• Use linkedql rollforward to walk through the latest savepoint at each database and interactively perform a roll forward operation.

Details of these commands and others are in the CLI area.

Notice that Linked QL automatically adds a version number to each database schema in your local schema file after running any of the above commands:

[
    {
        // Database name
        "name": "database_1",
        // Version tag automatically managed by Linked QL
        "version": 2,
        // List of tables
        "tables": []
    },
    {
        // Database name
        "name": "database_2",
        // Version tag automatically managed by Linked QL
        "version": 7,
        // List of tables
        "tables": []
    }
]

To Deploy

If you have your production database separate from the default database that you directly commit to:

• Use linkedql replicate to replicate all changes in your development database in your production database. (See linkedql replicate for details.)