SQL Schema and Migrations

Use this guide when you need to create or change a TinyCloud SQL schema. The SDK’s migration helper applies versioned SQL during install or startup instead of running ad hoc DDL in a hot path.

Apply migrations

await tc.sql.db("main").migrations.apply({
  namespace: "com.example.notes",
  migrations: [
    {
      id: "001_initial_note_index",
      sql: [
        "CREATE TABLE IF NOT EXISTS note_index (id TEXT PRIMARY KEY, title TEXT NOT NULL, updated_at TEXT NOT NULL)",
        "CREATE INDEX IF NOT EXISTS idx_note_index_updated_at ON note_index(updated_at)",
      ],
    },
  ],
});

Use stable migration ids and run them before serving user traffic.

Request the schema capability

Schema changes need the tinycloud.sql/schema action for the same database path used at runtime. If you use the tc.sql shortcut, the database name is default.

await tc.signIn();

const sql = "CREATE TABLE IF NOT EXISTS notes (id TEXT PRIMARY KEY, body TEXT NOT NULL)";

const direct = await tc.sql.execute(sql);
if (!direct.ok) throw new Error(direct.error.message);

const scoped = await tc.sql.db("default").execute(sql);
if (!scoped.ok) throw new Error(scoped.error.message);

Those two forms target the same database.

What to avoid

do not run cold CREATE TABLE or ALTER TABLE in a request path
do not copy app-specific schema bootstrap helpers between apps
do not treat mirrored indexes as canonical data without saying so

Source note

The migration helper and schema permission behavior are implemented in the JS SDK and SQL service tests. This guide stays on the operational path and leaves exact SQL semantics to the code.

​Apply migrations

​Request the schema capability

​What to avoid

​Source note

Apply migrations

Request the schema capability

What to avoid

Source note