Query from edge runtimes

Query Postgres over HTTPS and WebSockets from Workers, Vercel Edge, Deno Deploy and other runtimes without TCP: tagged queries, batched transactions and interactive sessions.

Some runtimes can't open a TCP connection to Postgres: Cloudflare Workers, Vercel Edge Functions, Deno Deploy and some serverless platforms. The serverless driver in stitchdb-js/sql sends queries over HTTPS instead. A single query is one request. Several statements can run in one transaction in one request. For transactions where you read, decide and then write, open a WebSocket session. Queries go to your project's server and run through a connection pool there, so many short-lived functions don't each open a connection.

#Turn it on and make a key

In Database › Serverless SQL (project owners and admins) choose Turn on serverless SQL, then Create key. Studio shows the key once, as a connection URL:

STITCHDB_SQL_URL=https://sdb_sql_...@<ref>.p.stitchdb.com

You can have up to 10 keys. Use one per app or environment so you can revoke one without touching the others. Revoking a key takes effect at once.

#Query

import { createSql } from 'stitchdb-js/sql'   // or https://stitchdb.com/sdk/stitchdb-sql.mjs

const sql = createSql(env.STITCHDB_SQL_URL)

// Values become $1, $2, ... parameters; they are never put into the SQL text.
const todos = await sql`select id, title from todos where owner = ${userId} and done = ${false} limit 20`

// Or with numbered parameters:
const rows = await sql.query('select * from todos where id = $1', [id])
const result = await sql.query('update todos set done = true where id = $1', [id], { fullResults: true })
result.rowCount  // 1

Parts of a query compose. A sql`...` fragment inside another keeps its parameters. Use sql.identifier() for table and column names that are not fixed:

const filter = onlyOpen ? sql`and done = ${false}` : sql``
const rows = await sql`select * from ${sql.identifier(['public', table])} where owner = ${userId} ${filter}`

Parameters: Date is sent as an ISO time, bigint as text and Uint8Array as bytea. Plain objects are sent as JSON. JavaScript arrays become Postgres arrays; to store an array in a jsonb column, pass JSON.stringify(list).

#Transactions in one request

sql.transaction() runs the queries in order in one transaction and resolves to each one's rows. If any query fails, nothing is kept:

const [, [order]] = await sql.transaction([
  sql`update stock set left = left - 1 where sku = ${sku}`,
  sql`insert into orders (sku, buyer) values (${sku}, ${userId}) returning *`,
], { isolation: 'serializable' })   // also readOnly: true, deferrable: true

#Interactive transactions over a WebSocket

sql.connect() opens a session. Inside db.transaction() you can read, decide and write. The transaction is committed when your function returns and rolled back when it throws. Queries sent at the same time run in order on the server.

const db = await sql.connect()
await db.transaction(async (tx) => {
  const [acct] = await tx`select balance from accounts where id = ${id} for update`
  if (Number(acct.balance) < amount) throw new Error('Not enough money')   // rolled back
  await tx`update accounts set balance = balance - ${amount} where id = ${id}`
})
const rows = await db.sql`select * from accounts where id = ${id}`   // outside a transaction: each query commits
db.close()

You can also call db.begin(), db.commit() and db.rollback(), or send begin and commit as SQL. An open transaction holds one database connection. A transaction idle for 30 seconds is rolled back, and db.onnotice tells you. A session idle for 5 minutes is closed.

#Over HTTP

Any HTTP client works, with the key as a bearer token:

curl https://<ref>.p.stitchdb.com/sql/v1/query \
  -H "Authorization: Bearer $STITCHDB_SQL_KEY" -H "Content-Type: application/json" \
  -d '{"query":"select * from todos where id = $1","params":[1]}'
# → {"command":"SELECT","rowCount":1,"fields":[{"name":"id","dataTypeID":23},...],"rows":[{"id":1,...}]}

# several statements in one transaction: send this body instead
-d '{"transaction":[{"query":"..."},{"query":"...","params":[...]}],"isolation":"serializable"}'
# → {"results":[...]}

Errors come back with status 400 and the Postgres fields: message, code (the SQLSTATE, such as 23505 for a duplicate key), detail, hint, position and constraint. In a transaction, index says which statement failed. The driver throws them as SqlError. Other statuses: 401 for a missing or revoked key, 413 for a result over 8 MB, 503 when every connection is busy or the server is building too many large results at once (retry after a moment), and 504 when the statement timeout cancels a query.

The WebSocket is at wss://<ref>.p.stitchdb.com/sql/v1/ws. Every message is JSON with an id that the answer repeats. Send { type: 'auth', key } first, then { type: 'query', query, params }, { type: 'begin' }, { type: 'commit' } or { type: 'rollback' }. Answers are { id, result } or { id, error }.

#Good to know

• One statement per query. Send several statements with transaction, or run migrations with the CLI.
• Session settings (SET, prepared statements, LISTEN, temporary tables) don't carry over to the next request: the connection is reset after them. Use SET LOCAL inside a transaction.
• The statement timeout is 30 seconds by default. Change it in Studio to 5, 10, 15, 30 or 60 seconds.
• Results are limited to 8 MB per query. Use limit, or page with where id > $1.
• Connections and WebSocket sessions at once depend on your plan. They are separate from the connection pooler limits.