Automatic Schema Versioning

DOCS • Concepts

---

⚡️ Evolve schemas—CREATE, ALTER, DROP—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:

// 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 done another way:

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'
    });
}

See Linked QL Migrations to see how versioning works in migrations.

Disabling Auto-Savepoints

You can configure Linked DB to not create auto-savepoints on a database. Simply set the auto_savepoints config to 0:

const linkedDB = await client.linkedDB();
await linkedDB.config('auto_savepoints', 0);

Important

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