Migrations

DOCS • Concepts

---

💥 Manage your entire db structure declaratively with a diff-based migration approach!

As against the conventional script-based migration approach, Linked QL follows a diff-based migration 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 could 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 a whole lot of 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.)

Tip

For an existing DB, you can usa a command to automatically generate your DB structure: npx linkedql generate.

To Operate

Go ahead and 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 covered in the CLI area.

Notice that Linked QL automatically tells you the version of 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.)