SQL Storage

The SqlStorage interface encapsulates methods that modify the SQLite database embedded within a Durable Object. The SqlStorage interface is accessible via the sql property of DurableObjectStorage class.

For example, using sql.exec(), a user can create a table, then insert rows into the table.

import { DurableObject } from "cloudflare:workers";

export class MyDurableObject extends DurableObject {
  sql: SqlStorage;
  constructor(ctx: DurableObjectState, env: Env) {
    super(ctx, env);
    this.sql = ctx.storage.sql;

    this.sql.exec(`CREATE TABLE IF NOT EXISTS artist(
      artistid    INTEGER PRIMARY KEY,
      artistname  TEXT
    );INSERT INTO artist (artistid, artistname) VALUES
      (123, 'Alice'),
      (456, 'Bob'),
      (789, 'Charlie');`
    );
  }
}

SQLite in Durable Objects Beta

SQL API methods accessed with ctx.storage.sql are only allowed on Durable Object classes with SQLite storage backend and will return an error if called on Durable Object classes with a key-value storage backend.

Writing to indexes or virtual tables

When writing data, every index counts as an additional row. However, indexes may be beneficial for read-heavy use cases. Refer to Index for SQLite Durable Objects.

Writing data to SQLite virtual tables ↗ also counts towards rows written.

Specifically for Durable Object classes with SQLite storage backend, KV operations which were previously asynchronous (for example, get, put, delete, deleteAll, list) are synchronous, even though they return promises. These methods will have completed their operations before they return the promise.

Methods

exec

exec(query: string , ...bindings: any[] ): SqlStorageCursor

Parameters

• query: string
  • The SQL query string to be executed. query can contain ? placeholders for parameter bindings. Multiple SQL statements, separated with a semicolon, can be executed in the query. With multiple SQL statements, any parameter bindings are applied to the last SQL statement in the query, and the returned cursor is only for the last SQL statement.
• ...bindings: any[] Optional
  • Optional variable number of arguments that correspond to the ? placeholders in query.

Returns

A cursor (SqlStorageCursor) to iterate over query row results as objects. SqlStorageCursor is a JavaScript Iterable ↗, which supports iteration using for (let row of cursor). SqlStorageCursor is also a JavaScript Iterator ↗, which supports iteration using cursor.next().

SqlStorageCursor supports the following methods:

• next()
  • Returns an object representing the next value of the cursor. The returned object has done and value properties adhering to the JavaScript Iterator ↗. done is set to false when a next value is present, and value is set to the next row object in the query result. done is set to true when the entire cursor is consumed, and no value is set.
• toArray()
  • Iterates through remaining cursor value(s) and returns an array of returned row objects.
• one()
  • Returns a row object if query result has exactly one row. If query result has zero rows or more than one row, one() throws an exception.
• raw(): Iterator
  • Returns an Iterator over the same query results, with each row as an array of column values (with no column names) rather than an object.
  • Returned Iterator supports next(), toArray(), and one() methods above.
  • Returned cursor and raw() iterator iterate over the same query results and can be combined. For example:

let cursor = this.sql.exec("SELECT * FROM artist ORDER BY artistname ASC;");
let rawResult = cursor.raw().next();

if (!rawResult.done) {
  console.log(rawResult.value); // prints [ 123, 'Alice' ]
} else {
  // query returned zero results
}

console.log(cursor.toArray()); // prints [{ artistid: 456, artistname: 'Bob' },{ artistid: 789, artistname: 'Charlie' }]

SqlStorageCursor had the following properties:

• columnNames: string[]
  • The column names of the query in the order they appear in each row array returned by the raw iterator.
• rowsRead: number
• The number of rows read so far as part of this SQL query. This may increase as you iterate the cursor. The final value is used for SQL billing.
• rowsWritten: number
• The number of rows written so far as part of this SQL query. This may increase as you iterate the cursor. The final value is used for SQL billing.

Note that sql.exec() cannot execute transaction-related statements like BEGIN TRANSACTION or SAVEPOINT. Instead, use the ctx.storage.transaction() or ctx.storage.transactionSync() APIs to start a transaction, and then execute SQL queries in your callback.

Examples

SQL API examples below use the following SQL schema:

import { DurableObject } from "cloudflare:workers";

export class MyDurableObject extends DurableObject {
  sql: SqlStorage
  constructor(ctx: DurableObjectState, env: Env) {
    super(ctx, env);
    this.sql = ctx.storage.sql;

    this.sql.exec(`CREATE TABLE IF NOT EXISTS artist(
      artistid    INTEGER PRIMARY KEY,
      artistname  TEXT
    );INSERT INTO artist (artistid, artistname) VALUES
      (123, 'Alice'),
      (456, 'Bob'),
      (789, 'Charlie');`
    );
  }
}

Iterate over query results as row objects:

  let cursor = this.sql.exec("SELECT * FROM artist;");

  for (let row of cursor) {
    // Iterate over row object and do something
  }

Convert query results to an array of row objects:

  // Return array of row objects: [{"artistid":123,"artistname":"Alice"},{"artistid":456,"artistname":"Bob"},{"artistid":789,"artistname":"Charlie"}]
  let resultsArray1 = this.sql.exec("SELECT * FROM artist;").toArray();
  // OR
  let resultsArray2 = Array.from(this.sql.exec("SELECT * FROM artist;"));
  // OR
  let resultsArray3 = [...this.sql.exec("SELECT * FROM artist;")]; // JavaScript spread syntax

Convert query results to an array of row values arrays:

  // Returns [[123,"Alice"],[456,"Bob"],[789,"Charlie"]]
  let cursor = this.sql.exec("SELECT * FROM artist;");
  let resultsArray = cursor.raw().toArray();

  // Returns ["artistid","artistname"]
  let columnNameArray = this.sql.exec("SELECT * FROM artist;").columnNames.toArray();

Get first row object of query results:

  // Returns {"artistid":123,"artistname":"Alice"}
  let firstRow = this.sql.exec("SELECT * FROM artist ORDER BY artistname DESC;").toArray()[0];

Check if query results have exactly one row:

  // returns error
  this.sql.exec("SELECT * FROM artist ORDER BY artistname ASC;").one();

  // returns { artistid: 123, artistname: 'Alice' }
  let oneRow = this.sql.exec("SELECT * FROM artist WHERE artistname = ?;", "Alice").one()

Returned cursor behavior:

  let cursor = this.sql.exec("SELECT * FROM artist ORDER BY artistname ASC;");
  let result = cursor.next();
  if (!result.done) {
    console.log(result.value); // prints { artistid: 123, artistname: 'Alice' }
  } else {
    // query returned zero results
  }

  let remainingRows = cursor.toArray();
  console.log(remainingRows); // prints [{ artistid: 456, artistname: 'Bob' },{ artistid: 789, artistname: 'Charlie' }]

Returned cursor and raw() iterator iterate over the same query results:

  let cursor = this.sql.exec("SELECT * FROM artist ORDER BY artistname ASC;");
  let result = cursor.raw().next();

  if (!result.done) {
    console.log(result.value); // prints [ 123, 'Alice' ]
  } else {
    // query returned zero results
  }

  console.log(cursor.toArray()); // prints [{ artistid: 456, artistname: 'Bob' },{ artistid: 789, artistname: 'Charlie' }]

sql.exec().rowsRead():

  let cursor = this.sql.exec("SELECT * FROM artist;");
  cursor.next()
  console.log(cursor.rowsRead); // prints 1

  cursor.toArray(); // consumes remaining cursor
  console.log(cursor.rowsRead); // prints 3

databaseSize

databaseSize: number

Returns

The current SQLite database size in bytes.

let size = ctx.storage.sql.databaseSize;

Point in time recovery

For Durable Objects classes with SQL storage, the following point-in-time-recovery (PITR) API methods are available to restore a Durable Object's embedded SQLite database to any point in time in the past 30 days. These methods apply to the entire SQLite database contents, including both the object's stored SQL data and stored key-value data using the key-value put() API. The PITR API is not supported in local development because a durable log of data changes is not stored locally.

The PITR API represents points in times using 'bookmarks'. A bookmark is a mostly alphanumeric string like 0000007b-0000b26e-00001538-0c3e87bb37b3db5cc52eedb93cd3b96b. Bookmarks are designed to be lexically comparable: a bookmark representing an earlier point in time compares less than one representing a later point, using regular string comparison.

getCurrentBookmark

ctx.storage.getCurrentBookmark(): Promise<string>

• Returns a bookmark representing the current point in time in the object's history.

getBookmarkForTime

ctx.storage.getBookmarkForTime(timestamp: number | Date ): Promise<string>

• Returns a bookmark representing approximately the given point in time, which must be within the last 30 days. If the timestamp is represented as a number, it is converted to a date as if using new Date(timestamp).

onNextSessionRestoreBookmark

ctx.storage.onNextSessionRestoreBookmark(bookmark: string ): Promise<string>

• Configures the Durable Object so that the next time it restarts, it should restore its storage to exactly match what the storage contained at the given bookmark. After calling this, the application should typically invoke ctx.abort() to restart the Durable Object, thus completing the point-in-time recovery.

This method returns a special bookmark representing the point in time immediately before the recovery takes place (even though that point in time is still technically in the future). Thus, after the recovery completes, it can be undone by performing a second recovery to this bookmark.

let now = new Date();
// restore to 2 days ago
let bookmark = ctx.storage.getBookmarkForTime(now - 2);
ctx.storage.onNextSessionRestoreBookmark(bookmark);

TypeScript and query results

You can use TypeScript type parameters ↗ to provide a type for your results, allowing you to benefit from type hints and checks when iterating over the results of a query.

Warning

Providing a type parameter does not validate that the query result matches your type definition. In TypeScript, properties (fields) that do not exist in your result type will be silently dropped.

Your type must conform to the shape of a TypeScript Record ↗ type representing the name (string) of the column and the type of the column. The column type must be a valid SqlStorageValue: one of ArrayBuffer | string | number | null.

For example,

type User = {
  id: string;
  name: string;
  email_address: string;
  version: number;
}

This type can then be passed as the type parameter to a sql.exec call:

// The type parameter is passed between the "pointy brackets" before the function argument:
const result = this.ctx.storage.sql.exec<User>("SELECT id, name, email_address, version FROM users WHERE id = ?", [user_id]).one()
// result will now have a type of "User"

// Alternatively, if you are iterating over results using a cursor
let cursor = this.sql.exec<User>("SELECT id, name, email_address, version FROM users WHERE id = ?", [user_id])
for (let row of cursor) {
  // Each row object will be of type User
}

// Or, if you are using raw() to convert results into an array, define an array type:
type UserRow = [
  id: string,
  name: string,
  email_address: string,
  version: number,
];

// ... and then pass it as the type argument to the raw() method:
let cursor = sql.exec("SELECT id, name, email_address, version FROM users WHERE id = ?", [user_id]).raw<UserRow>();

for (let row of cursor) {
  // row is of type User
}

You can represent the shape of any result type you wish, including more complex types. If you are performing a JOIN across multiple tables, you can compose a type that reflects the results of your queries.