Storine v0.3.0

Tools

SQL gateway

Point applications written for PostgreSQL, MySQL or SQL Server at Storine by changing their connection string.

storine-sql lets applications that already talk to PostgreSQL, MySQL or Microsoft SQL Server use Storine by changing only their connection string. It speaks the PostgreSQL v3, MySQL and TDS (SQL Server) wire protocols, understands the three SQL dialects (including T-SQL), and emulates the catalogs that drivers and ORMs introspect (pg_catalog, information_schema, SHOW ..., sys.*, the sp_* metadata procedures). Your tables are stored as Storine documents, so you keep Storine's durability, replication and exact decimals.

storine-sql --data ./db                      # embedded database: PostgreSQL on 127.0.0.1:5432, MySQL on :3306, SQL Server on :1433
storine-sql --storine storine://h1,h2,h3     # or in front of a running Storine server / cluster
            [--pg 0.0.0.0:5432] [--mysql 0.0.0.0:3306] [--mssql 0.0.0.0:1433]
            [--no-pg] [--no-mysql] [--no-mssql]
            [--user name:password]...        # without --user every client is trusted (keep it on localhost)
            [--database shop]                # name of the default database (default: storine)
            [--tls-cert chain.pem --tls-key key.pem | --no-tls] [--require-tls]
            [--max-connections 1024]         # open client connections; a client silent for 30 s before logging in is closed
            [--oidc-issuer URL --oidc-audience AUD --oidc-jwks keys.json]   # SQL Server access-token logins (below)
            [--kerberos-keytab FILE]         # SQL Server Kerberos logins (below)

Then point the application at it, exactly as you would at the database it was written for (a setup for each tested ORM is in Using an ORM):

postgresql://app:secret@127.0.0.1:5432/shop
mysql://app:secret@127.0.0.1:3306/shop
Server=tcp:127.0.0.1,1433;Database=shop;User ID=app;Password=secret;TrustServerCertificate=true

Users can also come from STORINE_SQL_USERS=name:password,name2:password2. PostgreSQL clients authenticate with SCRAM-SHA-256, MySQL clients with mysql_native_password (both standard; no plaintext passwords on the wire). SQL Server clients use SQL Server authentication, whose password travels inside the (always TLS-encrypted, when TLS is on) login packet, as with SQL Server itself; they can also log in with an OpenID Connect access token or Kerberos (below). Set STORINE_SQL_LOG=1 to log every statement.

TLS#

All three endpoints speak TLS 1.3 and TLS 1.2, implemented in-house like the rest of Storine (no OpenSSL): TLS_CHACHA20_POLY1305_SHA256 (TLS 1.3) or TLS_ECDHE_ECDSA_WITH_CHACHA20_POLY1305_SHA256 (TLS 1.2, with extended master secret), always with x25519 key exchange and an Ed25519 certificate. It is on by default:

  • With --data DIR, a self-signed certificate for localhost, 127.0.0.1 and ::1 is created once in DIR/tls/ (server.crt, server.key with mode 0600) and reused. Clients using sslmode=require / ssl-mode=REQUIRED connect as is. For full verification, pin it: sslmode=verify-full sslrootcert=DIR/tls/server.crt.
  • --tls-cert chain.pem --tls-key key.pem serves your own certificate. The key must be Ed25519 (openssl genpkey -algorithm ed25519 -out key.pem, then have your CA sign it).
  • --require-tls refuses clients that stay in plaintext; --no-tls turns TLS off.
  • SQL Server: every encryption mode works. Encrypt=Optional / encrypt=false encrypts the login only (like SQL Server), Encrypt=Mandatory / encrypt=true the whole connection (TLS inside the TDS 7.x PRELOGIN exchange), and Encrypt=Strict / encrypt=strict is TDS 8.0 (TLS first, ALPN tds/8.0). Drivers verify the certificate unless told to trust it (TrustServerCertificate=true), exactly as with a self-signed SQL Server certificate.

Verified with OpenSSL (libpq, psycopg, asyncpg, PyMySQL, PHP, Ruby, tedious, Prisma, openssl s_client, including HelloRetryRequest, KeyUpdate and TLS 1.2), Java's JSSE (pgJDBC, Connector/J, mssql-jdbc), .NET (Npgsql, MySqlConnector, Microsoft.Data.SqlClient), Go's crypto/tls (pgx, go-sql-driver, go-mssqldb; TLS 1.3 and 1.2) and Node (pg, mysql2). Clients restricted to AES cipher suites or to RSA/ECDSA certificates (some FIPS configurations) cannot connect.

SQL Server: access-token (OpenID Connect) logins#

Besides user name and password, SQL Server clients can log in with an access token from any OpenID Connect provider (Keycloak, Dex, Authentik, Zitadel, Auth0, Okta, Entra ID, ...) — what the drivers call an access token (SqlConnection.AccessToken, mssql-jdbc's accessToken, go-mssqldb's NewAccessTokenConnector, tedious' azure-active-directory-access-token; the names are historical, nothing is tied to a cloud). Tell storine-sql which issuer to trust:

storine-sql --data /var/lib/storine-sql \
  --oidc-issuer https://id.example.com/realms/main \
  --oidc-audience storine \
  --oidc-jwks /etc/storine/jwks.json
  • --oidc-jwks is the issuer's key set, the document at its jwks_uri (for Keycloak, <issuer>/protocol/openid-connect/certs): curl -o /etc/storine/jwks.json <jwks_uri>. The file is re-read whenever it changes, so key rotation is a periodic curl.
  • A token is accepted when its signature verifies (RS256, RS384, RS512 or EdDSA; none and HMAC are refused), iss is the issuer, aud includes an --oidc-audience (repeatable), and exp / nbf hold (60 s clock-skew allowance).
  • The session user (SUSER_NAME(), CURRENT_USER) is the token's preferred_username, else upn, email, sub; --oidc-user-claim NAME (repeatable) picks other claims.
  • A rejected token fails the login with the reason (token has expired, token audience ...). Workflows where the driver asks the server where to sign in (Authentication=ActiveDirectoryPassword and similar) are not supported; acquire the token in the application and pass it.
  • Use TLS (Encrypt=true, the default for these drivers), since the token is a bearer credential.

    SQL Server: Kerberos (integrated) logins#

Integrated Security=true / Trusted_Connection=yes (SqlClient, ODBC), integratedSecurity=true;authenticationScheme=JavaKerberos (mssql-jdbc) and authenticator=krb5 (go-mssqldb) log in with the user's Kerberos ticket. The ticket can come from any realm: MIT Kerberos, Heimdal, FreeIPA, Samba AD or Active Directory, on Linux, macOS or Windows clients. Give storine-sql the service's keytab:

# MIT / FreeIPA: create the service principal and export its keys
kadmin -q "addprinc -randkey MSSQLSvc/db.example.com:1433"
kadmin -q "ktadd -k /etc/storine/storine-sql.keytab MSSQLSvc/db.example.com:1433"
# (Active Directory: setspn -S MSSQLSvc/db.example.com:1433 svc-storine, then ktpass or adcli)

storine-sql --data /var/lib/storine-sql --mssql 0.0.0.0:1433 \
  --kerberos-keytab /etc/storine/storine-sql.keytab
  • Clients ask for the service principal MSSQLSvc/<host>:<port>, so the host name in the connection string must be one the keytab has a key for (a keytab may hold several).
  • go-mssqldb (up to at least v1.8.2) looks the SPN's host up and drops the last character of the answer, expecting the trailing dot DNS answers carry. Names resolved from /etc/hosts (such as localhost) have none, so MSSQLSvc/localhost:1433 becomes MSSQLSvc/localhos:1433 and the KDC reports KDC_ERR_S_PRINCIPAL_UNKNOWN. Connect by a DNS name, or set ServerSPN to a name served by DNS (or one that does not resolve at all, which go-mssqldb then leaves unchanged).
  • Accepted: AP-REQs wrapped in SPNEGO or plain GSS-API Kerberos tokens, with the AES encryption types (aes256/aes128-cts-hmac-sha1-96, aes256-cts-hmac-sha384-192, aes128-cts-hmac-sha256-128). RC4 and DES are refused, as is NTLM.
  • Checked: the ticket decrypts with the keytab and has not expired; the authenticator matches the ticket's client and is within 5 minutes of the server clock; replays are rejected. With mutual authentication (the default for most drivers) the server returns an AP-REP proving it holds the key.
  • The session user is the client principal without the realm (ann@EXAMPLE.COM → ann) when the realm is the service's own; users from other realms keep it (ann@OTHER.ORG). --kerberos-keep-realm keeps it always.
  • The keytab is re-read when it changes (key rotation). Keep it readable only by the server (mode 0600).

Tested clients#

Every suite below runs against a real storine-sql (tests/clients/run_all.sh):

LanguageDriversORMs / query builders
Pythonpsycopg2, psycopg 3, asyncpg, PyMySQLSQLAlchemy 2 (sync and asyncio, PostgreSQL and MySQL), Django 6 (migrations, test databases, select_for_update)
Node.jspg, mysql2Sequelize, Knex, TypeORM (schema sync), Drizzle, Prisma 6 (db push with an exact introspection round trip)
Gopgx, lib/pq, go-sql-driver/mysqlGORM (PostgreSQL and MySQL)
JavapgJDBC, MySQL Connector/JHibernate 6 (both protocols, optimistic locking)
.NETNpgsql, MySqlConnectorEntity Framework Core (Npgsql and Pomelo providers)
PHPPDO (pgsql, mysql; native and emulated prepares), pgsql, mysqliLaravel Eloquent and schema builder
Rubypg, trilogyActiveRecord 8 (migrations, locking, update_counters)
CLIpsql, mysql
PoolersPgBouncer 1.22 (session and transaction pooling, SCRAM on both legs, max_prepared_statements) with psycopg2, psycopg 3, asyncpg and SQLAlchemy

SQL Server (TDS 7.1 - 7.4 and 8.0):

LanguageDriversORMs / query builders
Pythonpymssql (FreeTDS)SQLAlchemy 2 (mssql+pymssql: create_all, reflection)
Node.jstedious, mssqlSequelize (sync({ alter })), TypeORM (schema sync), Prisma 6 (db push, exact introspection round trip)
Gogo-mssqldbGORM (sqlserver driver, MERGE upserts)
Javamssql-jdbcHibernate 6 (SQLServerDialect, hbm2ddl create and validate)
.NETMicrosoft.Data.SqlClientEntity Framework Core (SqlServer provider: batched MERGE inserts, ExecuteUpdate, concurrency tokens)

Each suite also checks money handling under concurrency: parallel balance = balance + x updates, transfers between rows in transactions, and stale-version rejection all come out exact.

SQL support#

  • Types: smallint/int/bigint, numeric(p,s) / decimal(p,s) (exact, never binary floating point), real/double, boolean, text/varchar(n)/char(n), bytea/blob, date, time(p), timestamp(p) / datetime(p) / timestamptz (precision kept and rounded like PostgreSQL), interval, uuid, json/jsonb, arrays, MySQL enum, pgvector vector(n) (see below).
  • Queries: joins (inner, left, right, full, cross, USING, NATURAL, comma joins planned as hash joins), subqueries (scalar, IN, EXISTS, ANY/ALL, derived tables, correlated LATERAL), CTEs (including WITH RECURSIVE), UNION/INTERSECT/EXCEPT, GROUP BY/HAVING, DISTINCT [ON], window functions (row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first/last/nth_value, aggregates OVER (PARTITION BY .. ORDER BY ..)), set-returning functions in FROM and in select lists, LIMIT/OFFSET/FETCH, FOR UPDATE [NOWAIT | SKIP LOCKED].
  • Writes: INSERT ... RETURNING, multi-row VALUES, INSERT ... SELECT, ON CONFLICT DO NOTHING / DO UPDATE and MySQL ON DUPLICATE KEY UPDATE, REPLACE, INSERT IGNORE, UPDATE ... FROM, DELETE ... USING, TRUNCATE.
  • Schema: CREATE / ALTER / DROP TABLE (add, drop, rename and retype columns, defaults, NOT NULL, CHECK, UNIQUE, primary and foreign keys with CASCADE / SET NULL / SET DEFAULT / RESTRICT), indexes, SERIAL / IDENTITY / AUTO_INCREMENT, sequences (CREATE SEQUENCE, nextval, setval), views (CREATE [OR REPLACE] VIEW, listed in the catalogs), CREATE / DROP DATABASE and USE.
  • Functions: the common string, numeric, date/time, JSON (->, ->>, #>, @>, jsonb_build_object, json_agg, JSON_EXTRACT, ...), array, regex, conditional and aggregate functions of both dialects, plus the catalog functions ORMs call (format_type, pg_get_indexdef, pg_get_constraintdef, col_description, current_schemas, ...).
  • T-SQL (SQL Server endpoint): TOP, OFFSET ... FETCH, [bracketed] names, OUTPUT INSERTED.* / DELETED.* [INTO @t], MERGE, UPDATE e ... FROM t AS e / DELETE e FROM ..., DECLARE (scalars and TABLE variables), IF ... ELSE, EXEC / EXEC (sql), sp_executesql, IDENTITY, NEXT VALUE FOR, ALTER COLUMN, ADD [CONSTRAINT] DEFAULT ... FOR, SET options (NOCOUNT, IMPLICIT_TRANSACTIONS, ...), string +, and the usual built-ins (ISNULL, LEN, CHARINDEX, DATEADD, DATEDIFF, DATEPART, EOMONTH, CONVERT, TRY_CAST, IIF, NEWID, SCOPE_IDENTITY, OBJECT_ID, SERVERPROPERTY, ...). Catalogs: sys.* (objects, tables, columns, types, indexes, keys, foreign keys, defaults, identity columns, sequences, databases, ...), INFORMATION_SCHEMA, and the procedures drivers call (sp_tables, sp_columns, sp_pkeys, sp_fkeys, sp_statistics, sp_helpindex, sp_rename). Declared types (datetime vs datetime2, varchar vs nvarchar) and constraint names are kept, so migration tools see their schema unchanged.
  • Protocol features: PostgreSQL simple and extended query protocol (named and unnamed statements and portals, parameter type inference, text and binary formats including numeric and arrays), COPY ... FROM STDIN / TO STDOUT in text, CSV and binary formats (psql \copy, psycopg copy(), asyncpg copy_records_to_table), MySQL text protocol and COM_STMT_* binary prepared statements, multi-statement queries, LAST_INSERT_ID(), ROW_COUNT(), FOUND_ROWS(), server status flags; TDS SQL batches, RPC (sp_executesql, sp_prepare / sp_execute / sp_prepexec / sp_unprepare) with typed parameters including NVARCHAR(MAX) / VARBINARY(MAX), transaction-manager requests, multiple result sets.

Vector search (pgvector)#

The PostgreSQL endpoint accepts pgvector's SQL, so code written for PostgreSQL + pgvector runs unchanged (CREATE EXTENSION vector is accepted and does nothing):

CREATE TABLE items (id bigserial PRIMARY KEY, body text, embedding vector(384));
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
SELECT id, body, embedding <=> $1 AS distance
FROM items WHERE body <> '' ORDER BY distance LIMIT 10;
  • Type: vector(n) (up to 16,000 dimensions; text [1,2,3] and pgvector's binary format), casts from text and numeric arrays. Dimension mismatches fail with SQLSTATE 22000, as in pgvector.
  • Operators and functions: <-> (L2), <=> (cosine), <#> (negative inner product), <+> (L1); l2_distance, cosine_distance, inner_product, l1_distance, vector_dims, vector_norm, l2_normalize, subvector; element-wise + - *; sum and avg.
  • Indexes: USING hnsw or USING ivfflat on one vector(n) column creates a vector index in the store (up to 4 per table, 4,096 dimensions; index parameters and SET hnsw.ef_search are accepted and ignored). A query of the form ORDER BY col <=> constant LIMIT k on one table, ascending, without GROUP BY / DISTINCT, reads only the nearest candidates (re-ranked exactly); when WHERE leaves fewer than k of them, it rescans the whole table, so filtering never shortens the result. Every other query, and <-> / <#> / <+> ordering, computes distances row by row (exact, no index).
  • Clients: the pgvector adapters for psycopg 3, psycopg2 and SQLAlchemy are tested (tests/clients/test_python.py). On the MySQL and SQL Server endpoints, store embeddings in JSON columns or use the document API's nearest queries.

Connection pools and application locks#

  • PgBouncer works in session and transaction pooling modes (tests/clients/pgbouncer). DISCARD ALL (PgBouncer's server_reset_query), RESET ALL / RESET name and CLOSE ALL really reset the session: prepared statements (SQL and protocol level), settings and advisory locks of one client never reach the next. As with PostgreSQL itself, transaction pooling does not keep session state (SET, session advisory locks) between transactions.

  • Advisory and named locks for migration tools and job schedulers:

    • PostgreSQL: pg_advisory_lock, pg_try_advisory_lock, pg_advisory_unlock, pg_advisory_unlock_all, the _shared and _xact_ variants, with one bigint key or two int keys. Blocking calls honour SET lock_timeout.
    • MySQL: GET_LOCK(name, seconds), RELEASE_LOCK, RELEASE_ALL_LOCKS, IS_FREE_LOCK, IS_USED_LOCK.
    • SQL Server: sp_getapplock / sp_releaseapplock (session or transaction owner, shared or exclusive, @LockTimeout), with the return status in EXEC @r = ....

    Locks are stored in the database, so every gateway in front of the same store sees them. They are released by unlock calls, at the end of the transaction or session, or by DISCARD ALL; a gateway that stops without releasing them loses them after 60 seconds. There is no deadlock detection between advisory locks: use timeouts or try-locks if two tasks take several of them in different orders.

Bulk imports#

SELECT storine_reserve('table', rows) sizes the store's index for rows more rows before an import, so the import doesn't pause while the index doubles. Only superusers may call it (SQLSTATE 42501 otherwise), and the gateway's own token must have the server's admin role, since one call can take gigabytes of address space. It returns true. Each row counts as one document plus one per unique constraint, and one call covers at most 100,000,000 documents (SQLSTATE 22003 beyond, or for a negative count). It changes no data; on a range cluster every node is sized for its share.

Transactions and money safety#

Every statement runs in a transaction: an explicit one (BEGIN ... COMMIT) or an implicit one per statement. Writes are buffered and committed as one atomic batch, and every written row carries the version it had when it was read, so a concurrent change makes the commit fail instead of being overwritten. No lost updates.

  • Autocommit statements that lose such a race are re-run transparently, so UPDATE acct SET balance = balance - 10 WHERE id = 1 from many connections at once is always exact.
  • Explicit transactions report SQLSTATE 40001 (MySQL error 1213, SQL Server error 1205), which drivers and ORMs retry. SELECT ... FOR UPDATE takes row locks (honoring lock_timeout / NOWAIT / SKIP LOCKED).
  • Isolation levels:
    • READ COMMITTED (PostgreSQL's default) and MySQL's default REPEATABLE-READ behave like those engines.
    • PostgreSQL REPEATABLE READ / SERIALIZABLE and MySQL SERIALIZABLE validate every row the transaction read. A stale write, write skew, or a looked-up key that appeared concurrently fails the commit with 40001. This protects the naive finance pattern (read the balance, compute in the app, write the result) instead of losing a concurrent deposit. Under READ COMMITTED, use FOR UPDATE or an atomic balance = balance + x for that pattern, as you would on PostgreSQL.
  • Constraints (primary keys, UNIQUE, foreign keys) are enforced by preconditions inside the same atomic batch, so they hold under concurrency. A parent DELETE racing a child INSERT cannot leave an orphan.
  • numeric / decimal values are exact decimals end to end, including both binary wire formats.

Databases#

CREATE DATABASE makes a separate namespace: its own tables, sequences, constraints and locks. Clients select it in the connection string, with USE, or with MySQL COM_INIT_DB. DROP DATABASE removes it. Names that were never created are served by the default database (--database), so clients connecting with any name keep working. This is what Django's and Rails' test databases and Prisma's shadow databases rely on.

Known limitations#

  • TLS: TLS 1.3 and 1.2, ChaCha20-Poly1305 only, Ed25519 certificates only (see above). No client-certificate authentication.
  • SQL Server: logins by password, OpenID Connect access token or Kerberos; NTLM and the driver-driven sign-in workflows (Authentication=ActiveDirectoryPassword and similar) are refused with an explanation. One schema (dbo), no MARS (one active result per connection, as with MultipleActiveResultSets=false). Functions, procedures and triggers are written in PostgreSQL syntax on every endpoint (below), so T-SQL procedure bodies are not accepted. A statement error leaves the transaction open (XACT_ABORT OFF), as SQL Server does.
  • DDL is applied immediately, even inside a transaction (MySQL behavior). On the PostgreSQL endpoint, a ROLLBACK undoes the transaction's data changes but not its CREATE / ALTER / DROP. A migration that fails halfway leaves the steps it already ran.
  • One schema per database: PostgreSQL CREATE SCHEMA is accepted, but every table lives in the database's single namespace (reported as public). Cross-database references (otherdb.table) are resolved in the current database.
  • Phantoms under SERIALIZABLE: rows read and keys looked up are validated. A new row matching a range predicate that the transaction scanned is not detected (predicate locks).
  • COPY ... FROM STDIN outside a transaction commits every 5,000 rows (inside BEGIN ... COMMIT it is all-or-nothing, up to the store's 256 MiB per-transaction limit). COPY to or from server-side files is not available; stream through STDIN / STDOUT.
  • Views are read-only (no INSERT into a view). Materialized views hold their rows until REFRESH MATERIALIZED VIEW.
  • Functions, procedures and triggers take LANGUAGE sql or a PL/pgSQL subset. They run with the caller's rights (SECURITY DEFINER is refused); a function called inside a larger query may only read; foreign-key cascades do not fire triggers.
  • LISTEN / NOTIFY deliver at once within one gateway. Between gateways in front of the same cluster, delivery is polled every 100 ms and best effort: a gateway that was down misses what was sent meanwhile, as a disconnected PostgreSQL client does.
  • Not supported: PostgreSQL extensions other than pgvector's SQL. Unsupported statements fail with SQLSTATE 0A000 and a message naming the feature; nothing else is silently ignored except session settings that do not apply (e.g. SET search_path, SET NAMES).
  • An SQLite endpoint is not available (SQLite is an in-process library, not a server). Run storine-sql --data DIR beside the app and switch its dialect to PostgreSQL, which accepts SQLite's INSERT OR REPLACE and ON CONFLICT; or embed the engine from Rust (Using an ORM).

Development#

cargo test -p storage-sql                       # executor and unit tests (embedded store)
cargo build --release -p storage-sql
target/release/storine-sql --data /tmp/db --pg 127.0.0.1:55432 --mysql 127.0.0.1:53306 --mssql 127.0.0.1:51433 &
crates/storage-sql/tests/clients/run_all.sh     # every real-client suite whose toolchain is installed