Automatic Schema Versioning

DOCS • Concepts

---

⚡️ CREATE, ALTER, DROP schemas on top of a powerful versioning system!

Linked QL comes to your database with a powerful database versioning system—combined with a nifty rollback (and rollforward) mechanism—that addresses a notoriously complex and error-prone exercise in SQL: schema evolution! Whereas the industry practice is to manually manage past states using migration files, Linked QL gives you the concept of Automatic Schema Savepoints and Rollbacks!

Here, you alter your schema and get back a reference to a "savepoint" automatically created for you:

• using a RETURNING clause at DDL execution time:

  // Alter schema and obtain savepoint
  const savepoint = await client.query(
      `CREATE TABLE public.users (
          id int,
          name varchar
      )
      RETURNING SAVEPOINT`,
      { desc: 'Create users table' }
  );

• or using the database.savepoint() API at any time:

  const savepoint = await client.database('public').savepoint();

Either way, you get a piece of a magic wand tool right in your hands!

Included are a couple important details about the referenced point in time:

console.log(savepoint.versionTag()); // 1
console.log(savepoint.commitDesc()); // Create users table
console.log(savepoint.commitDate()); // 2024-07-17T22:40:56.786Z

More details in the Savepoint API.

Next are a pair of methods that let's you roll back—and again roll forward—your db to this point in time!

A rollback operates on the schema snapshot captured in the savepoint and reverses all changes:

• this to preview:

  // SQL
  console.log(savepoint.reverseSQL());
  // "DROP TABLE public.users CASCADE"

• this to execute:

  // Execute rollback (drops "users" table)
  await savepoint.rollback({
      desc: 'Users table unnecessary'
  });

A rollforward operates on the same snapshot and recommits the exact original changes:

• this to preview:

  // SQL
  console.log(savepoint.reverseSQL());
  // "CREATE TABLE public.users (...)"

• this to execute:

  // Execute recommit (recreates "users" table)
  await savepoint.recommit({
      desc: 'Users table necessary again'
  });

And you can roll all the way back—or forward—to a point in time:

// Rollback to a point
let savepoint;
while((savepoint = await client.database('public').savepoint()) && savepoint.versionTag() > 3) {
    await savepoint.rollback({
        desc: 'These changes are no more necessary'
    });
}

// Rollforward to a point
let savepoint;
while((savepoint = await client.database('public').savepoint({ lookAhead: true })) && savepoint.versionTag() <= 5) {
    await savepoint.recommit({
        desc: 'These changes are necessary again'
    });
}

But you also get more than a programmatic interface over your database histories.

On the Linked QL CLI

The Linked QL Command Line Interface provides a great way to interact with your database histories right within your terminal.

To display a table of the most recent savepoint at each database, run the linkedql savepoints command:

npx linkedql savepoints

To perform a rollback (or rollforward) on a database-by-database basis, run the linkedql rollback (or linkedql rollforward) command:

npx linkedql rollback

In schema.json

The concept of automatic schema versioning is an integral part of Linked QL Migrations. Here, you declaratively model your database structure in a schema.json file and Linked QL helps you manage versioning information alongside each database object therein. Version numbers like the below will appear after your first commit operation:

[
    {
        // 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": []
    }
]

On the Database API

Your database's version information is directly available on your Database instance. Simply call the database.version() method:

const database = client.database('public');
console.log(await database.version()); // 2

const database = await client.renameDatabase('public', 'private');
console.log(await database.version()); // 3

const database = await client.alterDatabase('private', (schema) => schema.name('public'));
console.log(await database.version()); // 4

Disabling Auto-Savepoints

You can configure Linked QL to not auto-create savepoints on the database. Simply set the Linked DB's config.auto_savepoints parameter to 0.

This configuration takes effect at the database level and thus will apply to all clients connected to the database.

Important

Note that on re-enabling auto-savepoints, exisiting histories will be cleared.