Skip to Content
Reference@streamsy/storage

SqlStorage

Reference for the SqlStorage namespace in @streamsy/storage. It keeps streams in a SQLite, Postgres, or MySQL database through an Effect SqlClient that you provide.

import { SqlStorage } from "@streamsy/storage";
import type { CommitBoundary, Storage, StorageFault, Streamsy } from "@streamsy/core";
import type { StreamsReader, StreamsWriter } from "@streamsy/core/protocol";
import type { Effect, Layer, Scope } from "effect";
import type { Reactivity } from "effect/reactivity";
import type { SqlClient } from "effect/sql";

SqlStorage keeps streams in a SQL database through a SqlClient that you provide. The root entry point imports no SQL driver, so add the driver that you use. See SQLite on Node and Deno.

The BunStorage and DurableObjectStorage Layers build the client for you, and run SqlStorage over it. The Storage contract page describes the services that all three provide.

For concurrent projection handlers, see Binding concurrency for pool sizing, transaction context and shared output locks.

Layers

SqlStorage.layer

declare function layer<const Keys extends ReadonlyArray<Streamsy.Any>>(
  group: Streamsy.Group<Keys>,
  options?: SqlStorage.Options,
): Layer.Layer<
  | Storage
  | CommitBoundary
  | OwnerRecords
  | CommitHooks
  | ProjectionMarks
  | Streamsy.Host<Streamsy.Group<Keys>>,
  StorageFault,
  SqlClient.SqlClient | Reactivity.Reactivity
>;

// OwnerRecords, CommitHooks, and ProjectionMarks have no exported name in a
// public entry point. See "Types with no exported name" at the end of this page.

layer returns a Layer that provides the stream services of group over the database of the SqlClient. The Layer also provides Storage, CommitBoundary, and the internal services OwnerRecords, CommitHooks, and ProjectionMarks. With them, the projection checkpoint store and the pending marks of a durable attached projection commit in the same transaction as the streams.

The SqlClient and the Reactivity that the Layer requires must be the pair that the client was made with. clientLayer provides that pair.

import { Layer } from "effect";
import { SqlStorage } from "@streamsy/storage";
import { shop } from "./streams.ts";

// makeClient is the make call of a driver, such as PgClient.make({ url }).
const storage = SqlStorage.layer(shop).pipe(Layer.provide(SqlStorage.clientLayer(makeClient)));

Building the Layer migrates the database to the current Streamsy format. On SQLite and Postgres, the migration runs in one transaction. On Postgres, it first takes an advisory lock, so several instances migrate one after the other. MySQL commits each DDL statement by itself, so the migration takes a named lock with GET_LOCK on one connection instead, and waits at most 30 s for it. Schema version 3 adds stopped, failed_at, failure_reason, failure_message and nullable wake_at to the streamsy_projection_pending table introduced in version 2. Opening a version 2 database upgrades it automatically, preserving pending work and backoff; existing marks start with stopped: false and no recorded failure or wake. MySQL checks each column in information_schema.columns under its named lock, so a partially applied upgrade can safely resume. A release that supports only schema version 2 cannot reopen the upgraded database.

The build fails with a StorageFault in these cases:

Caseoperation
The client is not SQLite, Postgres, or MySQLstorage.dialect
The database holds an older Streamsy formatmigration.open
The database holds a newer Streamsy schema versionmigration.open
The MySQL migration lock is not free within 30 smigration.lock

When the format is older or newer, the build does not change the database. Use a new database for the older format.

layer throws a RangeError when transactionRetryAttempts is not a positive safe integer, or when transactionRetryDelayMs is not a positive finite number. Building the Layer dies with a RangeError when repairIntervalMs is not a positive number. The rules for readLimit and longPollTimeoutMs are those of Protocol.layer.

The store follows these rules:

  • The store has chain forks, store atomic scope, push wakes, and an indexed expiry index. See StorageCapabilities
  • On Postgres and MySQL, each mutation transaction first locks the rows of the streams that it writes with SELECT ... FOR UPDATE, in id order, so writers on several connections commit one after the other
  • A create fails with a StorageFault with operation mutate.stream-id when the stream id has more than 512 code points. An append fails with operation mutate.producer-id when its producer id has more than 255 code points. Every dialect applies these limits, which come from the key columns of the MySQL schema
  • On MySQL, an owner record key over 512 code points is stored as its first 447 code points, #, and the SHA-256 hex of the full key, so two long keys that share their first 512 code points stay apart. A key of at most 512 code points is stored as it is. SQLite and Postgres store every key as it is
  • To run your SQL and a mutation in one transaction, use CommitBoundary.withTransaction. Inside a transaction that you open with sql.withTransaction, a mutation dies, and so does CommitBoundary.withTransaction. See Commit with your own tables

SqlStorage.layerProtocol

declare function layerProtocol(
  options?: SqlStorage.Options,
): Layer.Layer<
  | CommitBoundary
  | CommitHooks
  | OwnerRecords
  | ProjectionMarks
  | Storage
  | StreamsReader
  | StreamsWriter,
  StorageFault,
  Reactivity.Reactivity | SqlClient.SqlClient
>;

// OwnerRecords, CommitHooks, and ProjectionMarks have no exported name in a
// public entry point. See "Types with no exported name" at the end of this page.

layerProtocol returns a Layer with the generic protocol reader and writer over the SQL store, and no stream classes. StreamsReader and StreamsWriter read and write any stream id. The build, the options, and the store follow the rules of layer.

SqlStorage.clientLayer

declare const clientLayer: <A extends SqlClient.SqlClient, E>(
  makeClient: Effect.Effect<A, E, Scope.Scope | Reactivity.Reactivity>,
) => Layer.Layer<SqlClient.SqlClient | Reactivity.Reactivity, E>;

clientLayer builds a new Reactivity, runs makeClient with it, and provides the client as SqlClient together with that Reactivity. The client and the storage then use the same Reactivity, so a commit wakes the readers in the same process. Pass the make call of a driver, such as PgClient.make or SqliteClient.make. The Layer fails with the error of makeClient.

Options

SqlStorage.Options

interface Options {
  readonly repairIntervalMs?: number;
  readonly transactionRetryAttempts?: number;
  readonly transactionRetryDelayMs?: number;
  readonly readLimit?: number;
  readonly longPollTimeoutMs?: number;
}

Options holds the options of layer and layerProtocol. The Bun and Durable Object options add a client to these fields.

OptionMeaning
repairIntervalMsThe interval of the repair pass, in ms. Each open changes subscription reads again at this interval. The default is 1000
transactionRetryAttemptsThe total attempts of a transaction that the store opens itself, including the first. The default is 16
transactionRetryDelayMsThe wait between two of those attempts, in ms. The default is 25
readLimitThe largest number of messages in a catch-up page. See Protocol.ProtocolOptions
longPollTimeoutMsThe longest wait of a long-poll read. The default is 30 s. See Protocol.ProtocolOptions

A commit in the same process wakes its readers at once. The repair pass finds the other commits: a commit from another process, and a commit that was interrupted before it woke its readers. The readers see such a commit within repairIntervalMs.

The store retries a transaction that it opened when the transaction fails with a retryable SQL error, such as a busy SQLite file, or with a retryable StorageFault. The migration uses the same retry policy. A mutation inside CommitBoundary.withTransaction joins the open transaction, and the store does not retry it alone. When the COMMIT statement itself fails, the store does not retry, because the commit may have applied. The transaction dies with the SQL error.

Constants

SqlStorage.DEFAULT_REPAIR_INTERVAL_MS

declare const DEFAULT_REPAIR_INTERVAL_MS: 1000;

DEFAULT_REPAIR_INTERVAL_MS is the default of repairIntervalMs: 1000 ms.

SqlStorage.DEFAULT_TRANSACTION_RETRY_ATTEMPTS

declare const DEFAULT_TRANSACTION_RETRY_ATTEMPTS: 16;

DEFAULT_TRANSACTION_RETRY_ATTEMPTS is the default of transactionRetryAttempts: 16 attempts.

SqlStorage.DEFAULT_TRANSACTION_RETRY_DELAY_MS

declare const DEFAULT_TRANSACTION_RETRY_DELAY_MS: 25;

DEFAULT_TRANSACTION_RETRY_DELAY_MS is the default of transactionRetryDelayMs: 25 ms.

Types with no exported name

The Layer signatures name three services from @streamsy/core/internal. They connect a storage host to @streamsy/projection, and no public entry point exports them.

interface OwnerRecords extends Context.ServiceClass.Shape<
  "@streamsy/core/internal/OwnerRecords",
  {
    readonly read: (key: string) => Effect.Effect<Option.Option<string>, StorageFault>;
    readonly write: (key: string, encoded: string) => Effect.Effect<void, StorageFault>;
    readonly remove: (key: string) => Effect.Effect<void, StorageFault>;
  }
> {}

interface CommitHooks extends Context.ServiceClass.Shape<
  "@streamsy/core/internal/CommitHooks",
  {
    readonly beforeCommit: (
      hook: (ids: ReadonlySet<StreamId>) => Effect.Effect<void, SqlError.SqlError>,
    ) => Effect.Effect<void, never, Scope.Scope>;
  }
> {}

interface ProjectionMarks extends Context.ServiceClass.Shape<
  "@streamsy/core/internal/ProjectionMarks",
  {
    readonly mark: (
      projection: string,
      params: ReadonlyArray<string>,
    ) => Effect.Effect<void, SqlError.SqlError>;
    readonly nextDue: (projection: string) => Effect.Effect<Option.Option<number>, StorageFault>;
    readonly summary: (
      projection: string,
      now: number,
    ) => Effect.Effect<ProjectionMarkSummary, StorageFault>;
    readonly due: (
      projection: string,
      now: number,
      limit: number,
    ) => Effect.Effect<ReadonlyArray<PendingMark>, StorageFault>;
    readonly read: (
      projection: string,
      params: ReadonlyArray<string>,
    ) => Effect.Effect<ReadonlyMap<string, PendingMark>, StorageFault>;
    readonly clear: (
      projection: string,
      params: string,
      seq: number,
    ) => Effect.Effect<void, StorageFault>;
    readonly sleep: (
      projection: string,
      params: string,
      seq: number,
      wakeAt: number,
    ) => Effect.Effect<void, StorageFault>;
    readonly remove: (projection: string, params: string) => Effect.Effect<void, StorageFault>;
    readonly fail: (
      projection: string,
      params: string,
      options: {
        readonly attempts: number;
        readonly failure: MarkFailure;
        readonly dueAt: Option.Option<number>;
      },
    ) => Effect.Effect<void, StorageFault>;
    readonly retry: (projection: string, params?: string) => Effect.Effect<number, StorageFault>;
    readonly resume: (projection: string) => Effect.Effect<number, StorageFault>;
    readonly failed: (
      projection: string,
      limit: number,
    ) => Effect.Effect<ReadonlyArray<PendingMark>, StorageFault>;
  }
> {}

// Disjoint counts of one projection's marks at the supplied time.
interface ProjectionMarkSummary {
  readonly fresh: number;
  readonly sleeping: number;
  readonly retryDue: number;
  readonly backingOff: number;
  readonly stopped: number;
  readonly nextDueAt: Option.Option<number>;
}

// One pending mark: a binding with input work, a retry, or a recorded wake.
interface PendingMark {
  readonly params: string;
  readonly seq: number;
  readonly attempts: number;
  readonly dueAt: number;
  readonly wakeAt: Option.Option<number>;
  readonly stopped: boolean;
  readonly failure: Option.Option<MarkFailure>;
}

interface MarkFailure {
  readonly at: number;
  readonly reason: Option.Option<string>;
  readonly message: string;
}

@streamsy/projection uses each service for one job:

  • OwnerRecords holds the encoded records of the projection checkpoint store, in the streamsy_owner_records table
  • CommitHooks runs each hook that beforeCommit registers inside every later owned transaction that writes at least one stream id. The hook runs after the body and before COMMIT, and receives the ids that the transaction created, appended to, or deleted. Its statements join the transaction. Closing the scope of beforeCommit removes the hook
  • ProjectionMarks holds the pending marks of durable attached projections in the streamsy_projection_pending table, keyed by projection id and params. See AttachOptions

The store treats a hook failure as a failure of the body. The transaction rolls back, and a retryable SqlError from a hook replays the whole transaction, the hook included.

ProjectionMarks.mark runs inside the commit hook and fails with SqlError; the other mark operations fail with StorageFault.

  • summary(projection, now) returns five disjoint counts: fresh, sleeping, retry due, backing off and stopped. Fresh rows have no wake and no failed attempts; sleeping rows have a wake and no failure or attempts. Its earliest due time includes sleeping marks and excludes stopped marks, as do nextDue and due.
  • mark raises the sequence, preserving attempts, stops and failure. A sleeping row becomes due now and clears its wake; other rows keep their due time, so writes never shorten backoff. fail(projection, params, { attempts, failure, dueAt }) preserves the sequence, inserts an absent mark, and records the failure with its message cut to 1,000 characters. Some(dueAt) backs off; None stops. Both clear the wake index.
  • clear deletes on a matching sequence; a moved sequence keeps the row but resets attempts, due time, wake, stop and failure.
  • sleep(projection, params, seq, wakeAt) resets failure state and keeps a matching row with dueAt and wakeAt both set to the deadline. A moved sequence becomes fresh and due now; an absent row is inserted asleep with sequence 1. Past wake times are stored unchanged. The SQL store uses the existing (projection, due_at) index for both retries and sleeps.
  • retry(projection, params?) makes failed or stopped marks due now and clears their stop, preserving attempts, sequence and failure. It returns the number changed; a repeat before the marks run returns zero. Sleeping rows are left alone. It can select one binding by canonical params.
  • resume(projection) makes only stopped marks due now and clears their stop, preserving attempts, sequence and failure. It returns the number changed. Sleeping rows are left alone.
  • failed(projection, limit) lists at most limit marks with failed attempts, stopped first, then by due time and params. The old marks-level deferred count is summary.retryDue + summary.backingOff + summary.stopped.

On this page