跳转到主要内容

SQLite-backed Durable Object Storage

import { Render, Type, MetaInfo, GlossaryTooltip, TypeScriptExample, Details, Tabs, TabItem } from "~/components";

:::note This page documents the storage API for the newer SQLite-backed Durable Objects.

For the legacy KV-backed Durable Object storage API, refer to KV-backed Durable Object Storage (Legacy). :::

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.

```ts 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;
}

}

</TypeScriptExample>

<TabItem label="Python" icon="seti:python">

```python
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');
    """)

<Render file="d1-do-supported-sqlite-extensions" product="d1" params={{ product: "Durable Objects support" }}/>

exec

exec(query: , ...bindings: ):

Parameters

  • query:
    • 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:
    • 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().

:::caution[Consume cursors synchronously] Although a cursor object can technically be held across an await, it does not provide a stable snapshot of the query results. A cursor resumed after an await may observe rows inserted, updated, or deleted after the cursor was created, including writes from a later implicit transaction that has not yet committed. If that later transaction rolls back, the cursor may have already returned data from a state that never became durable.

For predictable behavior, fully consume cursors synchronously before the next await, for example with .toArray() or Array.from(cursor). Treat cursors that cross await boundaries as having no snapshot isolation guarantees.

// ✅ Safe: cursor is fully consumed before any await
const rows = this.ctx.storage.sql.exec("SELECT * FROM users").toArray();
const result = await fetch("https://example.com", { method: "POST", body: JSON.stringify(rows) });

// ⚠️ No snapshot isolation: cursor may reflect changes made after it was created
const cursor = this.ctx.storage.sql.exec("SELECT * FROM users");
await fetch("https://example.com/notify");
// Rows returned here may include writes that occurred during the await,
// including uncommitted data from a later implicit transaction.
const rows = cursor.toArray();

:::

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():
    • 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() and toArray() 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' }]

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:

  • columnNames:
    • The column names of the query in the order they appear in each row array returned by the raw iterator.
  • rowsRead:
    • 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:
    • 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.
  • Any numeric value in a column is affected by JavaScript's 52-bit precision for numbers. If you store a very large number (in int64), then retrieve the same value, the returned value may be less precise than your original number.

:::note[SQL transactions] 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

databaseSize

databaseSize:

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():

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

getBookmarkForTime

ctx.storage.getBookmarkForTime(timestamp: ):

  • 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: ):

  • 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.

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

Asynchronous KV API

Alarms

Other

Storage properties

sql

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

这篇文档对你有帮助吗？
想留言或继续讨论？请到 Cyberforums 赛博论坛参与。 参与讨论 / 回复