INTEGRITY Cloudflare Docs

← Durable Objects / durable-objects / api

SQLite-backed Durable Object Storage

The Durable Object Storage API allows Durable Objects to access transactional and strongly consistent storage. A Durable Object's attached storage is private to its unique instance and cannot be accessed by other objects.

The Durable Object Storage API comes with several methods, including SQL, point-in-time recovery (PITR), key-value (KV), and alarm APIs. Available API methods depend on the storage backend for a Durable Objects class, either SQLite or KV.

Methods 1 SQLite-backed Durable Object class KV-backed Durable Object class
SQL API ✅ ❌
PITR API ✅ ❌
Synchronous KV API ✅ 2, 3 ❌
Asynchronous KV API ✅ 3 ✅
Alarms API ✅ ✅

Footnotes

1 Each method is implicitly wrapped inside a transaction, such that its results are atomic and isolated from all other storage operations, even when accessing multiple key-value pairs.

2 KV API methods like get(), put(), delete(), or list() store data in a hidden SQLite table __cf_kv. Note that you will be able to view this table when listing all tables, but you will not be able to access its content through the SQL API.

3 SQLite-backed Durable Objects also use synchronous KV API methods using ctx.storage.kv, whereas KV-backed Durable Objects only provide asynchronous KV API methods.

Access storage

Durable Objects gain access to Storage API via the DurableObjectStorage interface and accessed by the DurableObjectState::storage property. This is frequently accessed via this.ctx.storage with the ctx parameter passed to the Durable Object constructor.

The following code snippet shows you how to store and retrieve data using the Durable Object Storage API.

export class Counter extends DurableObject {
	constructor(ctx, env) {
		super(ctx, env);
	}

	async increment() {
		let value = (await this.ctx.storage.get("value")) || 0;
		value += 1;
		await this.ctx.storage.put("value", value);
		return value;
	}
}
export class Counter extends DurableObject {
  constructor(ctx: DurableObjectState, env: Env) {
    super(ctx, env);
  }

    async increment(): Promise<number> {
      let value: number = (await this.ctx.storage.get('value')) || 0;
      value += 1;
      await this.ctx.storage.put('value', value);
      return value;
    }

}
from workers import DurableObject

class Counter(DurableObject):
  def __init__(self, ctx, env):
    super().__init__(ctx, env)

  async def increment(self):
    value = (await self.ctx.storage.get('value')) or 0
    value += 1
    await self.ctx.storage.put('value', value)
    return value

JavaScript is a single-threaded and event-driven programming language. This means that JavaScript runtimes, by default, allow requests to interleave with each other which can lead to concurrency bugs. The Durable Objects runtime uses a combination of input gates and output gates to avoid this type of concurrency bug when performing storage operations. Learn more in our blog post ↗.

SQL API

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 and insert rows.

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');
    `);
  }
}
from workers import DurableObject

class MyDurableObject(DurableObject):
  def __init__(self, ctx, env):
    super().__init__(ctx, env)
    self.sql = ctx.storage.sql

    self.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');
    """)

Durable Objects support a subset of SQLite extensions for added functionality, including:

Refer to the source code ↗ for the full list of supported functions.

exec

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

Parameters

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:

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' }]
cursor = self.sql.exec("SELECT * FROM artist ORDER BY artistname ASC;")
raw_result = cursor.raw().next()

if not raw_result.done:
  print(raw_result.value)  # prints [ 123, 'Alice' ]
else:
  # query returned zero results
  pass

print(cursor.toArray())  # prints [{ artistid: 456, artistname: 'Bob' },{ artistid: 789, artistname: 'Charlie' }]

SqlStorageCursor has the following properties:

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;
size = ctx.storage.sql.databaseSize

PITR (Point In Time Recovery) API

For SQLite-backed Durable Objects, 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 time 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>

getBookmarkForTime

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

onNextSessionRestoreBookmark

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

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.

const DAY_MS = 24*60*60*1000;
// restore to 2 days ago
let bookmark = ctx.storage.getBookmarkForTime(Date.now() - 2 * DAYS_MS);
ctx.storage.onNextSessionRestoreBookmark(bookmark);
from datetime import datetime, timedelta

now = datetime.now()
# restore to 2 days ago
bookmark = ctx.storage.getBookmarkForTime(now - timedelta(days=2))
ctx.storage.onNextSessionRestoreBookmark(bookmark)

Synchronous KV API

get

put

delete

list

Supported options

Asynchronous KV API

get

Supported options

put

delete

Supported options

list

Supported options

Alarms

getAlarm

Supported options

setAlarm

deleteAlarm

Supported options

Other

deleteAll

transactionSync

transaction

sync

Storage properties

sql

sql is a readonly property of type DurableObjectStorage encapsulating the SQL API.