architecture

name	architecture
description	mssqlite system architecture: package dependency graph, request lifecycle from TDS packet to SQLite and back, key design decisions (name flattening, NOCASE collation, parameter passthrough, UDF strategy, session model, metadata inference) and current limitations. Use when deciding where a change belongs, extending T-SQL/protocol coverage, or debugging cross-layer behavior.

mssqlite Architecture

MSSQL compatible, SQLite backed, SQL Server. Two independent towers meet in the engine:

        wire                          language
  @mssqlite/bytes               @mssqlite/tsql (lex/parse → AST)
        │                             │
  @mssqlite/tds                 @mssqlite/transpile (AST → SQLite SQL)
        │                             │
        │                       @mssqlite/catalog (sys.* emulation)
        │                             │
        └──── @mssqlite/server ── @mssqlite/engine ── node:sqlite

@mssqlite/differential sits outside the runtime towers. It launches an ephemeral server plus pinned SQL Server 2025 container and drives both through one tedious capture path; no production package depends on it.

Request lifecycle

1. Socket bytes → messages — cleartext PRELOGIN first negotiates encryption. During a TDS 7.4 TLS handshake, server/tls-transport.ts bridges Node's TLSSocket records through PRELOGIN packet wrappers; after the final wrapped server record drains, both directions switch to raw TLS carrying ordinary TDS packets. After a MARS-enabled login, Tds.Smp.push first separates SYN/ACK/FIN/DATA frames by logical SID; DATA contains one TDS packet. Tds.Message.push then reassembles each session independently (pure incremental state) into { type, payload, ignore } messages; selected large packet types such as Bulk Load instead emit packet-sized fragments.
2. Dispatch by packet type (server/connection.ts) — prelogin, login7, SQL batch, RPC, transaction manager, bulk load, attention.
3. SQL batch — requests are serialized against the shared synchronous SQLite session and run through engine.executeBatchAsync(session, sql, control):
   • tsql.parse → Ast.Statement[]
   • per statement: directly renderable (SELECT/DML/DDL) → transpile → prepared SQLite statement with variables bound as native @x parameters; interpreted (DECLARE/SET/IF/WHILE/transactions/EXEC) → engine logic, scalar expressions evaluated via SELECT (expr).
   • an AbortSignal checkpoint yields before each statement and every interpreted control-flow iteration. Attention aborts that request, discards its accumulated items, closes the canceled response message, and emits a separate DONE_ATTN response. SQLite calls themselves remain synchronous and atomic because DatabaseSync has no interrupt API.
   • DDL additionally updates the catalog (catalog.createTable …).
4. Results → tokens — engine items (rows with TDS TypeInfo columns, count, message) render through Tds.Token.* encoders (server/respond.ts), split into packets, written back.
5. RPC — tedious sends parameterized queries as sp_executesql; parameters decode to JS values (Tds.Value.decode) and their TYPE_INFO is mapped to the corresponding T-SQL declaration before they bind as scoped variables (engine.executeSql). That declared type participates in implicit precedence; OUTPUT values return as RETURNVALUE tokens.
6. Bulk load — an INSERT BULK SQL batch prepares a catalog-validated engine plan. Subsequent type-7 fragments incrementally decode COLMETADATA, ROW/NULL/PLP values, and DONE; complete rows enter cached prepared INSERTs under one savepoint. EOM commits and returns DONE_COUNT. Invalid data, conversion/constraint errors, disconnect, IGNORE, or Attention roll back.
7. MARS response path — each logical response becomes ordinary TDS packets queued on its SID. SMP sequence/window credit limits transmission, and a round-robin drain emits one eligible packet at a time so a stalled reader cannot block another session. Attention drops only that SID's unsent output; FIN rolls back its bulk state and closes only the logical stream.

Key decisions

Multiple-database ownership and resolution

• One SQLite store and primary handle per SQL database — Server.databases owns database id, SQL name, stable internal attachment alias, backing filename, DatabaseSync, catalog, module/sequence registries, and rowversion state. Every primary handle attaches every other store, so operations in the selected database use its store as SQLite main while three-part names reach an attachment. In-memory servers use named shared-cache file: URIs; file-backed child databases use deterministic sibling files. The initial database owns the server manifest, and sys.databases lifecycle rows are mirrored into every database catalog.

• Encoded aliases isolate SQLite's namespace — every handle attaches every other store under a deterministic hex-encoded SQL database name such as mssqlite_73616c6573. Prefixing avoids main/temp collisions and lets the transpiler resolve a three-part name without mutable global context. ALTER DATABASE … MODIFY NAME detaches the old alias and reattaches the same store under the newly encoded alias. CREATE DATABASE bootstraps its independent catalog before attaching it everywhere; DROP DATABASE detaches it from all surviving handles, closes the owner, removes manifest rows, and deletes only an engine-owned child file. Partial CREATE attachment failures detach and remove the new store before surfacing the error. The bundled SQLite limit of ten attachments caps one server at eleven logical databases, including the four system databases.

• Object-position names resolve before transpilation — the engine localizes explicit references to the selected database onto SQLite main, preserves other three-part database names, validates their target, and rewrites their database part to a stable attachment alias. Schema flattening remains unchanged inside that SQLite schema: sales.dbo.orders becomes "mssqlite_73616c6573"."orders", and sales.app.orders becomes "mssqlite_73616c6573"."app.orders". Column qualifiers are not database names and are left untouched. Four-part linked-server names remain unsupported.

• Catalog and executable state are database-scoped — DDL writes physical SQL through the selected session handle and updates the target database's primary catalog handle. Procedure/function/trigger/sequence maps and rowversion counters live on that database state, allowing identical schema/object names in different databases. A three-part procedure call temporarily executes under its owner database and restores the caller context afterward.

Extension points

• New built-in function → transpile/functions.ts handler (native rendering, rewrite, subquery) or new mssqlite_* UDF in engine/udf.ts — keep both sides in sync.
• New statement kind → AST type (tsql/ast.ts), grammar (tsql/parse/statement.ts), then either a transpile rendering or an engine interpretation (engine/execute.ts switch).
• New token/protocol feature → tds/token/*, wire it in server/respond.ts or server/connection.ts.

Robustness invariants (enforced, regression-tested)

• Decoders never hang or throw uncaught on hostile bytes. Message.push rejects a packet whose length is below the header size (malformed framing → connection dropped, not an infinite loop); AllHeaders.decode rejects a zero-length inner header; Value.decode turns short/unknown-type reads into Result failures instead of exceptions. A deterministic fuzz sweep guards all top-level decoders (packages/tds/src/robustness.test.ts).
• No silent wire corruption from oversized values. A non-max value that would overflow its length prefix throws a clean error (mapped to an MSSQL error) rather than wrapping the prefix; column names are capped at 128 chars.
• Canceled requests are honored. A message whose EOM packet carries the IGNORE status bit (how tedious cancels mid-send) is dispatched as ignored but never executed. Bulk rows already staged under its savepoint are rolled back; IGNORE receives a regular completion because a pre-EOM cancel may not send Attention. An Attention for executing SQL/RPC work aborts at the next cooperative boundary, suppresses all accumulated rows/errors, sends the canceled request's final DONE and then a distinct DONE_ATTN message, and preserves any explicit user transaction for the client. Per-logical-session controllers isolate MARS cancellation; FIN and socket close abort without emitting late output.
• varchar/char/text use Windows-1252 (@mssqlite/bytes Cp1252), matching the advertised SQL_Latin1_General_CP1 collation — €, em dash and smart quotes round-trip instead of corrupting as ISO-8859-1. Undefined extension positions round-trip as their C1 controls.

Known limitations (v1)

Open implementation briefs are indexed in TODO.md. The compatibility audit is guarded by the opt-in pnpm test:differential corpus; declared path-and-value differences are checked for both unexpected drift and stale expectations. Its artifact also retains bounded client-side packet/token traces for both endpoints without recording LOGIN7 payload bytes.

• No login-only TDS 7.x encryption or TLS-first TDS 8.0. SSPI/FedAuth are not implemented; authenticated mode supports configured SQL logins.
• Cooperative cancellation cannot interrupt one synchronous SQLite statement; it takes effect before/after that call and within interpreted loops. This is a node:sqlite API limitation rather than a transaction policy.
• Cursor variables, positioned updates, and live KEYSET/DYNAMIC visibility are unsupported. Sequence DECIMAL/NUMERIC precision is capped at 18, cache options are metadata-only, same-row duplicate NEXT VALUE references are not coalesced, and an abnormal shutdown during an open user transaction can lose unflushed consumption state. Triggered DML does not yet support OUTPUT or UPDATE FROM, and MERGE does not yet fire DML triggers. MERGE OUTPUT may not reference source columns. Unlike SQL Server, table variable changes currently participate in the surrounding SQLite transaction and therefore roll back with it.
• Error classification currently covers mapped constraints, known conversion/ arithmetic numbers, RAISERROR, cursor, sequence, and rowversion ranges; additional SQL Server statement-vs-batch cases will be added as their operations land. Checked integer width inference for uncast columns currently follows SUM(int) and treats scalar columns of unknown declared width conservatively.
• DECIMAL/NUMERIC values use canonical fixed-scale strings and SQLite TEXT storage. The transpiler derives SQL Server precision/scale, routes casts, arithmetic, comparison, ordering, and aggregates through scaled-BigInt UDFs, and supplies decimal result hints; TDS encodes those strings directly.
• DATETIMEOFFSET values likewise use canonical TEXT rather than SQLite date functions. The local and UTC representations must both stay within years 0001-9999; offsets are limited to ±14:00. Raw lexical comparison is never sufficient because different local strings can denote the same instant.
• @@ROWCOUNT after SELECT reflects rows returned; unsupported globals raise error 137. ERROR_LINE() is always 1 (no statement positions in the AST).
• SET NOCOUNT is captured when each statement completes. Engine row/count items retain their cardinality for @@ROWCOUNT, while response rendering clears DONE_COUNT and writes a zero count when hidden. Procedure, trigger, and dynamic-SQL scopes restore the caller's setting; mid-batch toggles therefore affect only subsequent completions.