Microsoft SQL Server
이 콘텐츠는 아직 번역되지 않았습니다.
The SQL Server backend provides all three persistence components against a
single SQL Server database, via the
mssql driver (tedious under the
hood):
MsSqlJournal— the event journal forPersistentActors.MsSqlSnapshotStore— snapshots to bound recovery.MsSqlDurableStateStore— key-value durable state forDurableStateActors.
Like Postgres, it is shared across cluster nodes — any node can read or
write any persistenceId — and mssql/tedious is pure JavaScript, so it
needs no native build step and runs on all three supported runtimes.
Install
Section titled “Install”mssql is an optional peer dependency — install it alongside actor-ts:
bun add mssqlThe framework imports it only when a SQL Server store opens — at startup, or on first use when the boot-time open is turned off — so it stays out of your bundle until you opt in.
Register all three stores through the plugins slot of the system’s options
and select them in its configuration. Pass a shared pool so all three
components reuse one connection pool:
import sql from 'mssql';import { ActorSystem, ActorSystemOptions,} from 'actor-ts';import { MsSqlDurableStateStoreOptions, MsSqlJournalOptions, MsSqlSnapshotStoreOptions, RegisterMsSqlPluginsOptions, registerMsSqlPlugins,} from 'actor-ts/persistence';
const pool = await new sql.ConnectionPool({ server: 'db.example.com', port: 1433, user: 'actor_ts', password: process.env.MSSQL_PASSWORD, database: 'app', options: { encrypt: true },}).connect();
const msSqlSnapshotStoreOptions = MsSqlSnapshotStoreOptions.create() .withKeepN(3);const registerOptions = RegisterMsSqlPluginsOptions.create() // One pool shared by journal + snapshot + durable-state (recommended). .withPool(pool) .withJournal(MsSqlJournalOptions.create() /* .withEventsTable(...).withTagsTable(...) */) .withSnapshotStore(msSqlSnapshotStoreOptions) .withDurableStateStore(MsSqlDurableStateStoreOptions.create() /* .withTable(...) */);const systemOptions = ActorSystemOptions.create() // Select the SQL Server plugins for all three stores. .withConfig({ 'actor-ts': { persistence: { journal: { plugin: 'actor-ts.persistence.journal.mssql' }, 'snapshot-store': { plugin: 'actor-ts.persistence.snapshot-store.mssql' }, 'durable-state': { plugin: 'actor-ts.persistence.durable-state.mssql' }, }, }, }) .withPersistence({ plugins: [(persistence) => registerMsSqlPlugins(persistence, registerOptions)] });const system = ActorSystem.create('my-app', systemOptions);The plug-in runs inside ActorSystem.create, so all three stores are
registered before create returns, and the ones the configuration selects
open as part of the startup: a database the node cannot reach fails the
startup rather than the first persisted message — see opening the stores
at startup.
That holds with autoCreateTables off too, since the open then sends a
SELECT 1 where it would have run the DDL, and while the boot-time open is
on, the default: with the boot-time open turned off, the failure moves to the
store’s first operation. It holds for these stores, not for every axis: an
object-storage snapshot or durable-state store selected beside them, or a store
of your own without start(), has nothing to open ahead of time and fails its
first operation instead.
registerMsSqlPlugins also returns the durable-state store, for a caller
that would rather wire it by hand; a plug-in keeps it by assigning the
result, (persistence) => { msSql = registerMsSqlPlugins(persistence, registerOptions); }.
Since #872 that is a convenience rather than the only route, and a store
wired by hand is not a selected one, so it opens on first use rather than
with the startup.
A shared pool is caller-owned: no store ends it, so close it yourself at
shutdown. Without one, each component builds its own from its
poolConfig / url when it opens, and closes it on close():
const poolConfig = { server: 'db.example.com', database: 'app', user: 'actor_ts', password: process.env.MSSQL_PASSWORD, options: { encrypt: true },};const msSqlJournalOptions = MsSqlJournalOptions.create() .withPoolConfig(poolConfig);Configure it from HOCON
Section titled “Configure it from HOCON”The plugin id is the config section, so the block named after the id is the
block that plugin reads (#872). Everything except the live objects can come
from application.conf, which means registerMsSqlPlugins(persistence) needs no options at all:
import { ActorSystem, ActorSystemOptions } from 'actor-ts';import { registerMsSqlPlugins } from 'actor-ts/persistence';
const systemOptions = ActorSystemOptions.create().withPersistence({ plugins: [(persistence) => registerMsSqlPlugins(persistence)],});const system = ActorSystem.create('my-app', systemOptions);actor-ts.persistence { journal { plugin = "actor-ts.persistence.journal.mssql" } snapshot-store { plugin = "actor-ts.persistence.snapshot-store.mssql" } durable-state { plugin = "actor-ts.persistence.durable-state.mssql" }
journal.mssql { url = ${?MSSQL_URL}, events-table = "events" } snapshot-store.mssql { url = ${?MSSQL_URL}, keep-n = 5 } durable-state.mssql { url = ${?MSSQL_URL}, table = "durable_state" }}Options passed to registerMsSqlPlugins still win, field by field, and a field you leave
unset falls through to the block rather than blanking it — so a shared
pool in code and the table names in HOCON is a normal mix. A pre-built
pool and a serializer have no leaf at all: they are live objects, and
a config file cannot express one. Neither does poolConfig — the driver’s
whole option surface, which stays code-only. tags-table is read but ships as a comment
rather than a key, because its default is derived from events-table, and
absence is what keeps the two in step.
What you do tune on the pool has typed leaves (#1490), each a comment in the reference configuration so that leaving it out keeps node-mssql’s own default:
actor-ts.persistence.journal.mssql { url = ${?MSSQL_URL} # Server=...;Database=...;Encrypt=true max-pool-size = 20 # pool.max (default 10) idle-timeout = 1m # pool.idleTimeoutMillis (default 30s) connect-timeout = 5s # connectionTimeout (default 15s) request-timeout = 2m # requestTimeout (default 15s) ca-file = "/etc/ssl/mssql-ca.pem" # read when the pool opens, verified against by tedious}Each reaches the driver only when set — laid over the url as node-mssql’s own
parser reads it, so server, database and credentials survive. The store checks
them when it is built: a size or timeout that is not a positive whole number, or
a connect or request timeout longer than a timer can hold, is refused with an
OptionsError — and so is a leaf beside the same setting in poolConfig or in
the connection string (max-pool-size beside Max Pool Size, connect-timeout
beside Connect Timeout), which names both instead of letting one quietly win.
ca-file is a path the framework reads, never the certificate itself.
A url that switches TLS verification off is refused. A connection string
whose TrustServerCertificate is anything node-mssql reads as true — true,
yes, 1, every value but false, no, 0 or an empty one — makes the
reader throw a ConfigError naming the leaf, in any letter case of the key and
whether the url is written out or substituted from ${?MSSQL_URL}. The
refusal is strict about the file: the block’s url is refused even when
registerMsSqlPlugins is given a url or a pool of its own. Only HOCON is
inspected — the same string passed to withUrl(...) is your code’s decision.
A connection string has no key for a CA, so a server behind a private CA is
named beside it, with ca-file; that is refused together with an Encrypt or
a TrustServerCertificate that would leave the CA unused. Encrypt=false on
its own is plaintext rather than unverified TLS and stays allowed. The
reasoning is on TLS everywhere.
durable-state.plugin now selects this backend’s durable-state store. It
used to be built eagerly and handed back from registerMsSqlPlugins, so it could only be
wired by threading that instance through application code. The returned handle
still works and still yields the same instance — it is simply built when it is
first asked for rather than at registration, which for a selected store is the
startup.
When to use it
Section titled “When to use it”- SQL Server is your system of record. The enterprise default in a lot of stacks, and Akka.NET’s primary backend — this is the closest analogue.
- Cluster-shared persistence. Sharded entities that move between nodes, or cross-node projections, need a journal every node can read.
- You want a relational backend without a native driver. tedious is pure
JavaScript, unlike
better-sqlite3.
For single-node development, SqliteJournal
is simpler (one file, no server).
Configuration
Section titled “Configuration”type MsSqlConnection = { url?: string; // Server=…;Database=… (an mssql:// URL is refused) poolConfig?: Record<string, unknown>; // mssql config object; takes precedence over url pool?: MsSqlPoolLike; // pre-built / shared pool maxPoolSize?: number; // pool.max, default 10 (max-pool-size) idleTimeoutMs?: number; // pool.idleTimeoutMillis, default 30 000 (idle-timeout) connectTimeoutMs?: number; // connectionTimeout, default 15 000 (connect-timeout) requestTimeoutMs?: number; // requestTimeout, default 15 000 (request-timeout) caFile?: string; // PEM file tedious verifies against (ca-file)};
interface MsSqlJournalOptions extends MsSqlConnection { eventsTable?: string; // default 'events' tagsTable?: string; // default '<eventsTable>_tags' autoCreateTables?: boolean; // default true}interface MsSqlSnapshotStoreOptions extends MsSqlConnection { snapshotsTable?: string; // default 'snapshots' keepN?: number; // keep newest N per pid; default 3, <=0 keeps all autoCreateTables?: boolean;}interface MsSqlDurableStateStoreOptions extends MsSqlConnection { table?: string; // default 'durable_state' autoCreateTables?: boolean;}Options are validated when the store is constructed. Table names come from
config (not user input) and are checked against a safe-identifier pattern;
everything else — persistenceIds, tags, payloads — is passed as a named bind
parameter (@p1, @p2, …), never string-concatenated.
Schema
Section titled “Schema”With autoCreateTables (the default), the backend runs guarded CREATE TABLE
statements when the store opens — at startup, or on first use when the
boot-time open is turned off. T-SQL has no CREATE TABLE IF NOT EXISTS, so each is
wrapped in an IF OBJECT_ID(…) IS NULL check:
CREATE TABLE events ( persistence_id NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL, sequence_nr BIGINT NOT NULL, payload NVARCHAR(MAX) NOT NULL, -- JSON tags NVARCHAR(MAX) NULL, -- CSV (also mirrored into events_tags) timestamp BIGINT NOT NULL, CONSTRAINT PK_events PRIMARY KEY (persistence_id, sequence_nr));CREATE TABLE events_tags ( -- indexed tag lookups for projections persistence_id NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL, sequence_nr BIGINT NOT NULL, tag NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL, timestamp BIGINT NOT NULL, CONSTRAINT PK_events_tags PRIMARY KEY NONCLUSTERED (tag, timestamp, persistence_id, sequence_nr));CREATE TABLE events_meta ( -- compaction high-water mark persistence_id NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL, deleted_to BIGINT NOT NULL, CONSTRAINT PK_events_meta PRIMARY KEY (persistence_id));CREATE TABLE snapshots ( persistence_id NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL, sequence_nr BIGINT NOT NULL, payload NVARCHAR(MAX) NOT NULL, timestamp BIGINT NOT NULL, CONSTRAINT PK_snapshots PRIMARY KEY (persistence_id, sequence_nr));CREATE TABLE durable_state ( persistence_id NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL, revision BIGINT NOT NULL, payload NVARCHAR(MAX) NOT NULL, timestamp BIGINT NOT NULL, CONSTRAINT PK_durable_state PRIMARY KEY (persistence_id));The tags table’s primary key is nonclustered on purpose. NVARCHAR(n)
counts 2n bytes toward an index key, so (tag, timestamp, persistence_id, sequence_nr) needs 1036 bytes — past SQL Server’s 900-byte clustered limit,
but inside the 1700-byte nonclustered one. That is why SQL Server 2016 or
later is required. The collation is metadata, not width, so it costs
nothing against that budget.
Pre-provision these (granting only INSERT/SELECT/UPDATE/DELETE) and set
autoCreateTables: false if the database role cannot run DDL. With it off,
the store sends a SELECT 1 where it would have run the DDL, so it still
reaches the database when it opens.
Why persistence_id and tag are collated
Section titled “Why persistence_id and tag are collated”Every persistence_id and tag column carries an explicit COLLATE Latin1_General_100_BIN2. Without it the column inherits the database
default — SQL_Latin1_General_CP1_CI_AS on a stock instance, which is
case-insensitive.
A persistence_id is not merely a value: it is the name of an event
stream and half of the events primary key. Under a folding collation
Alice and alice are therefore one stream here and two on
Postgres, SQLite, MongoDB and DynamoDB. An actor started as Alice
recovers alice’s events, folds them into its own state and appends into
alice’s stream — a cross-entity read whose only symptom is a run of
JournalConcurrencyErrors that reads like a storage fault. The same
column keys durable-state load, revision CAS and delete, so there a
collision overwrites and deletes another entity’s state.
Validating the id cannot fix this: Alice and alice are both perfectly
legal ids. What differs is the comparison, which is a property of the
column.
BIN2 compares by Unicode code point, the way every other backend already
does. The Latin1_General half of the name only selects a code page for
non-Unicode types; on NVARCHAR it is inert, so this is a full-Unicode
ordinal comparison and not a Western-European restriction. Version 100
has shipped since SQL Server 2008, well below the 2016 floor the tags
table’s nonclustered key already imposes.
Migrating an existing schema
Section titled “Migrating an existing schema”The DDL is guarded with IF OBJECT_ID(…) IS NULL and autoCreateTables
defaults to true, so an existing database does not pick this up: the
framework never revisits a table it finds. New deployments get the
collation silently; an older database keeps the instance default until you
run the migration below — but it no longer does so quietly. Each store
reads its own tables out of sys.columns when it opens and warns if a
persistence_id or tag column comes back on a collation that is not
binary, naming the table, the column and the collation it found. A
selected store opens as part of the startup, so the warning is in the
startup log; with the boot-time open turned off the store connects lazily
instead and warns on first use, at the first persistence operation. That read needs no DDL rights and runs whether or not
autoCreateTables is set, because a schema the operator manages — the case
this page tells you to pre-provision — is the one most likely to predate
the pin.
SQL Server refuses to re-collate a column that an index or constraint depends on (error 5074), so each table is drop, alter, recreate. Run it once per database, as one transaction, with no node writing:
SET XACT_ABORT ON;BEGIN TRANSACTION;
ALTER TABLE events DROP CONSTRAINT PK_events;DROP INDEX idx_events_pid ON events;ALTER TABLE events ALTER COLUMN persistence_id NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL;ALTER TABLE events ADD CONSTRAINT PK_events PRIMARY KEY (persistence_id, sequence_nr);CREATE INDEX idx_events_pid ON events (persistence_id);
ALTER TABLE events_tags DROP CONSTRAINT PK_events_tags;DROP INDEX idx_events_tags_pid_seq ON events_tags;ALTER TABLE events_tags ALTER COLUMN persistence_id NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL;ALTER TABLE events_tags ALTER COLUMN tag NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL;ALTER TABLE events_tags ADD CONSTRAINT PK_events_tags PRIMARY KEY NONCLUSTERED (tag, timestamp, persistence_id, sequence_nr);CREATE INDEX idx_events_tags_pid_seq ON events_tags (persistence_id, sequence_nr);
ALTER TABLE events_meta DROP CONSTRAINT PK_events_meta;ALTER TABLE events_meta ALTER COLUMN persistence_id NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL;ALTER TABLE events_meta ADD CONSTRAINT PK_events_meta PRIMARY KEY (persistence_id);
ALTER TABLE snapshots DROP CONSTRAINT PK_snapshots;ALTER TABLE snapshots ALTER COLUMN persistence_id NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL;ALTER TABLE snapshots ADD CONSTRAINT PK_snapshots PRIMARY KEY (persistence_id, sequence_nr);
ALTER TABLE durable_state DROP CONSTRAINT PK_durable_state;ALTER TABLE durable_state ALTER COLUMN persistence_id NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL;ALTER TABLE durable_state ADD CONSTRAINT PK_durable_state PRIMARY KEY (persistence_id);
COMMIT;ALTER COLUMN replaces the whole definition, so NOT NULL has to be
repeated — omit it and the column silently becomes nullable. Substitute
your own table names if you overrode eventsTable, tagsTable,
snapshotsTable or table.
The transaction is not ceremony. Tag queries join events_tags to
events on persistence_id, and between the block that re-collates
events and the block that re-collates events_tags the two columns
carry different collations — SQL Server then refuses the join outright
with error 468, Cannot resolve the collation conflict: a hard failure,
not a wrong answer, so nothing downstream can paper over it. DDL is
transactional on SQL Server, which is what closes that window: a reader
blocks on the schema lock until COMMIT instead of seeing the mixed
state, and with XACT_ABORT on, a statement that fails — an index named
differently from the default, say — rolls the whole batch back rather
than leaving half the tables converted. Neither replaces stopping the
writers; they make the window invisible to whatever you forgot to stop.
The same conflict is permanent, not transient, for a tagsTable pointed
at a new table beside a legacy events: the new table is created with
the BIN2 pin, the old one keeps the instance default, and every tag
query fails with 468 until events is re-collated too.
The startup warning reports per column, which is what tells that state
apart from an untouched schema: it names only the columns still on a
folding collation, and says the schema is half migrated and that a tag
query across the two can fail outright with 468. A tagsTable beside a
legacy events reads the same way, every time the store opens, until
events is converted.
Check for damage before migrating, while the folding collation is still
what GROUP BY uses:
SELECT MIN(persistence_id) AS sample, COUNT(DISTINCT persistence_id COLLATE Latin1_General_100_BIN2) AS spellings FROM events GROUP BY persistence_idHAVING COUNT(DISTINCT persistence_id COLLATE Latin1_General_100_BIN2) > 1;Any row means two ids have already been sharing a stream. The migration
splits them apart again, and each half is then missing the other’s events —
so those actors fail recovery loudly with JournalIntegrityError (a hole
in the sequence) instead of quietly serving a merged history. That is the
better failure, but it is still a failure: decide what those streams ought
to be before you migrate, not after.
Concurrency model
Section titled “Concurrency model”- Journal append reads the current head and inserts inside one
transaction; the caller’s
expectedSeqis checked against that head. A racing writer that slips through trips the primary key, and SQL Server error 2627 (or 2601 for a unique index) is translated toJournalConcurrencyErroras a backstop. - Durable-state CAS uses the
revisioncolumn: a create (expectedRevision === 0) is a plainINSERT, so a collision arrives as error 2627; an update isUPDATE … WHERE revision = expected, where zero affected rows means the stored revision diverged. Either way the current revision is read back for theDurableStateConcurrencyError. - Upserts use
MERGE … WITH (HOLDLOCK). Without the hint, two concurrent merges can both take theNOT MATCHEDbranch and one fails on the primary key.
Pitfalls
Section titled “Pitfalls”Where to next
Section titled “Where to next”- PostgreSQL — the other relational backend, with a richer upsert vocabulary.
- MariaDB — the MySQL-family sibling.
- Durable state — the state-oriented alternative to event sourcing.
- Snapshots — bound the recovery scan.
