SQLite-backed Durable Object Storage·续1

SQLite-backed Durable Object Storage·续1

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

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>

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.

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`

ctx.storage.kv.get(key string): Any, undefined
Retrieves the value associated with the given key. The type of the returned value will be whatever was previously written for the key, or undefined if the key does not exist.

`put`

ctx.storage.kv.put(key string, value any): void
Stores the value and associates it with the given key. The value can be any type supported by the structured clone algorithm ↗︎, which is true of most types.

For the size of keys and values refer to SQLite-backed Durable Object limits

`delete`

ctx.storage.kv.delete(key string): boolean
Deletes the key and associated value. Returns true if the key existed or false if it did not.

`list`

ctx.storage.kv.list(options Object optional): Iterable<string, any>
Returns all keys and values associated with the current Durable Object in ascending sorted order based on the keys' UTF-8 encodings.
The type of each returned value in the Iterable ↗︎ will be whatever was previously written for the corresponding key.
Be aware of how much data may be stored in your Durable Object before calling this version of list without options because all the data will be loaded into the Durable Object's memory, potentially hitting its limit. If that is a concern, pass options to list as documented below.

Supported options

start string
Key at which the list results should start, inclusive.
startAfter string
Key after which the list results should start, exclusive. Cannot be used simultaneously with start.
end string
Key at which the list results should end, exclusive.
prefix string
Restricts results to only include key-value pairs whose keys begin with the prefix.
reverse boolean
If true, return results in descending order instead of the default ascending order.
Enabling reverse does not change the meaning of start, startKey, or endKey. start still defines the smallest key in lexicographic order that can be returned (inclusive), effectively serving as the endpoint for a reverse-order list. end still defines the largest key in lexicographic order that the list should consider (exclusive), effectively serving as the starting point for a reverse-order list.
limit number
Maximum number of key-value pairs to return.

Asynchronous KV API

get

ctx.storage.get(key string, options Object optional): Promise<any>
Retrieves the value associated with the given key. The type of the returned value will be whatever was previously written for the key, or undefined if the key does not exist.
ctx.storage.get(keys Array<string>, options Object optional): Promise<Map<string, any>>
Retrieves the values associated with each of the provided keys. The type of each returned value in the Map ↗︎ will be whatever was previously written for the corresponding key. Results in the Map will be sorted in increasing order of their UTF-8 encodings, with any requested keys that do not exist being omitted. Supports up to 128 keys at a time.

Supported options

allowConcurrency: boolean
By default, the system will pause delivery of I/O events to the Object while a storage operation is in progress, in order to avoid unexpected race conditions. Pass allowConcurrency: true to opt out of this behavior and allow concurrent events to be delivered.
noCache: boolean
If true, then the key/value will not be inserted into the in-memory cache. If the key is already in the cache, the cached value will be returned, but its last-used time will not be updated. Use this when you expect this key will not be used again in the near future. This flag is only a hint. This flag will never change the semantics of your code, but it may affect performance.

put

put(key string, value any, options Object optional): Promise
Stores the value and associates it with the given key. The value can be any type supported by the structured clone algorithm ↗︎, which is true of most types.

The size of keys and values have different limits depending on the Durable Object storage backend you are using. Refer to either: - SQLite-backed Durable Object limits - KV-backed Durable Object limits.

On a KV-backed Durable Object, if the serialized value exceeds the 128 KiB (131072 bytes) value-size limit, put() throws a RangeError (for example, Values cannot be larger than 131072 bytes.) before the write is applied.

put(entries Object, options Object optional): Promise
Takes an Object and stores each of its keys and values to storage.
Each value can be any type supported by the structured clone algorithm ↗︎, which is true of most types.
Supports up to 128 key-value pairs at a time. The size of keys and values have different limits depending on the flavor of Durable Object you are using. Refer to either:
- SQLite-backed Durable Object limits
- KV-backed Durable Object limits

delete

delete(key string, options Object optional): Promise<boolean>
Deletes the key and associated value. Returns true if the key existed or false if it did not.
delete(keys Array<string>, options Object optional): Promise<number>
Deletes the provided keys and their associated values. Supports up to 128 keys at a time. Returns a count of the number of key-value pairs deleted.

Supported options

put(), delete() and deleteAll() support the following options:
allowUnconfirmed boolean
By default, the system will pause outgoing network messages from the Durable Object until all previous writes have been confirmed flushed to disk. If the write fails, the system will reset the Object, discard all outgoing messages, and respond to any clients with errors instead.
This way, Durable Objects can continue executing in parallel with a write operation, without having to worry about prematurely confirming writes, because it is impossible for any external party to observe the Object's actions unless the write actually succeeds.
After any write, subsequent network messages may be slightly delayed. Some applications may consider it acceptable to communicate on the basis of unconfirmed writes. Some programs may prefer to allow network traffic immediately. In this case, set allowUnconfirmed to true to opt out of the default behavior.
If you want to allow some outgoing network messages to proceed immediately but not others, you can use the allowUnconfirmed option to avoid blocking the messages that you want to proceed and then separately call the sync() method, which returns a promise that only resolves once all previous writes have successfully been persisted to disk.
noCache boolean
If true, then the key/value will be discarded from memory as soon as it has completed writing to disk.
Use noCache if the key will not be used again in the near future. noCache will never change the semantics of your code, but it may affect performance.
If you use get() to retrieve the key before the write has completed, the copy from the write buffer will be returned, thus ensuring consistency with the latest call to put().