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=trueUsers 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 forlocalhost,127.0.0.1and::1is created once inDIR/tls/(server.crt,server.keywith mode 0600) and reused. Clients usingsslmode=require/ssl-mode=REQUIREDconnect as is. For full verification, pin it:sslmode=verify-full sslrootcert=DIR/tls/server.crt. --tls-cert chain.pem --tls-key key.pemserves your own certificate. The key must be Ed25519 (openssl genpkey -algorithm ed25519 -out key.pem, then have your CA sign it).--require-tlsrefuses clients that stay in plaintext;--no-tlsturns TLS off.- SQL Server: every encryption mode works.
Encrypt=Optional/encrypt=falseencrypts the login only (like SQL Server),Encrypt=Mandatory/encrypt=truethe whole connection (TLS inside the TDS 7.x PRELOGIN exchange), andEncrypt=Strict/encrypt=strictis TDS 8.0 (TLS first, ALPNtds/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-jwksis the issuer's key set, the document at itsjwks_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 periodiccurl.- A token is accepted when its signature verifies (RS256, RS384, RS512 or EdDSA;
noneand HMAC are refused),issis the issuer,audincludes an--oidc-audience(repeatable), andexp/nbfhold (60 s clock-skew allowance). - The session user (
SUSER_NAME(),CURRENT_USER) is the token'spreferred_username, elseupn,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=ActiveDirectoryPasswordand 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 aslocalhost) have none, soMSSQLSvc/localhost:1433becomesMSSQLSvc/localhos:1433and the KDC reportsKDC_ERR_S_PRINCIPAL_UNKNOWN. Connect by a DNS name, or setServerSPNto 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-realmkeeps 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):
| Language | Drivers | ORMs / query builders |
|---|---|---|
| Python | psycopg2, psycopg 3, asyncpg, PyMySQL | SQLAlchemy 2 (sync and asyncio, PostgreSQL and MySQL), Django 6 (migrations, test databases, select_for_update) |
| Node.js | pg, mysql2 | Sequelize, Knex, TypeORM (schema sync), Drizzle, Prisma 6 (db push with an exact introspection round trip) |
| Go | pgx, lib/pq, go-sql-driver/mysql | GORM (PostgreSQL and MySQL) |
| Java | pgJDBC, MySQL Connector/J | Hibernate 6 (both protocols, optimistic locking) |
| .NET | Npgsql, MySqlConnector | Entity Framework Core (Npgsql and Pomelo providers) |
| PHP | PDO (pgsql, mysql; native and emulated prepares), pgsql, mysqli | Laravel Eloquent and schema builder |
| Ruby | pg, trilogy | ActiveRecord 8 (migrations, locking, update_counters) |
| CLI | psql, mysql | |
| Poolers | PgBouncer 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):
| Language | Drivers | ORMs / query builders |
|---|---|---|
| Python | pymssql (FreeTDS) | SQLAlchemy 2 (mssql+pymssql: create_all, reflection) |
| Node.js | tedious, mssql | Sequelize (sync({ alter })), TypeORM (schema sync), Prisma 6 (db push, exact introspection round trip) |
| Go | go-mssqldb | GORM (sqlserver driver, MERGE upserts) |
| Java | mssql-jdbc | Hibernate 6 (SQLServerDialect, hbm2ddl create and validate) |
| .NET | Microsoft.Data.SqlClient | Entity 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, MySQLenum, pgvectorvector(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, correlatedLATERAL), CTEs (includingWITH 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, aggregatesOVER (PARTITION BY .. ORDER BY ..)), set-returning functions inFROMand in select lists,LIMIT/OFFSET/FETCH,FOR UPDATE [NOWAIT | SKIP LOCKED]. - Writes:
INSERT ... RETURNING, multi-rowVALUES,INSERT ... SELECT,ON CONFLICT DO NOTHING / DO UPDATEand MySQLON 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 withCASCADE/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 DATABASEandUSE. - 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 andTABLEvariables),IF ... ELSE,EXEC/EXEC (sql),sp_executesql,IDENTITY,NEXT VALUE FOR,ALTER COLUMN,ADD [CONSTRAINT] DEFAULT ... FOR,SEToptions (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 (datetimevsdatetime2,varcharvsnvarchar) 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
numericand arrays),COPY ... FROM STDIN/TO STDOUTin text, CSV and binary formats (psql\copy, psycopgcopy(), asyncpgcopy_records_to_table), MySQL text protocol andCOM_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 includingNVARCHAR(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 SQLSTATE22000, 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+ - *;sumandavg. - Indexes:
USING hnsworUSING ivfflaton onevector(n)column creates a vector index in the store (up to 4 per table, 4,096 dimensions; index parameters andSET hnsw.ef_searchare accepted and ignored). A query of the formORDER BY col <=> constant LIMIT kon one table, ascending, withoutGROUP BY/DISTINCT, reads only the nearest candidates (re-ranked exactly); whenWHEREleaves fewer thankof 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
pgvectoradapters 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'snearestqueries.
Connection pools and application locks#
PgBouncer works in session and transaction pooling modes (
tests/clients/pgbouncer).DISCARD ALL(PgBouncer'sserver_reset_query),RESET ALL/RESET nameandCLOSE ALLreally 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_sharedand_xact_variants, with onebigintkey or twointkeys. Blocking calls honourSET 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 inEXEC @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.- PostgreSQL:
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 = 1from many connections at once is always exact. - Explicit transactions report SQLSTATE
40001(MySQL error1213, SQL Server error1205), which drivers and ORMs retry.SELECT ... FOR UPDATEtakes row locks (honoringlock_timeout/NOWAIT/SKIP LOCKED). - Isolation levels:
READ COMMITTED(PostgreSQL's default) and MySQL's defaultREPEATABLE-READbehave like those engines.- PostgreSQL
REPEATABLE READ/SERIALIZABLEand MySQLSERIALIZABLEvalidate every row the transaction read. A stale write, write skew, or a looked-up key that appeared concurrently fails the commit with40001. This protects the naive finance pattern (read the balance, compute in the app, write the result) instead of losing a concurrent deposit. UnderREAD COMMITTED, useFOR UPDATEor an atomicbalance = balance + xfor 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 parentDELETEracing a childINSERTcannot leave an orphan. numeric/decimalvalues 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=ActiveDirectoryPasswordand similar) are refused with an explanation. One schema (dbo), no MARS (one active result per connection, as withMultipleActiveResultSets=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
ROLLBACKundoes the transaction's data changes but not itsCREATE / ALTER / DROP. A migration that fails halfway leaves the steps it already ran. - One schema per database: PostgreSQL
CREATE SCHEMAis accepted, but every table lives in the database's single namespace (reported aspublic). 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 STDINoutside a transaction commits every 5,000 rows (insideBEGIN...COMMITit is all-or-nothing, up to the store's 256 MiB per-transaction limit).COPYto or from server-side files is not available; stream throughSTDIN/STDOUT.- Views are read-only (no
INSERTinto a view). Materialized views hold their rows untilREFRESH MATERIALIZED VIEW. - Functions, procedures and triggers take
LANGUAGE sqlor a PL/pgSQL subset. They run with the caller's rights (SECURITY DEFINERis refused); a function called inside a larger query may only read; foreign-key cascades do not fire triggers. LISTEN/NOTIFYdeliver 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
0A000and 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 DIRbeside the app and switch its dialect to PostgreSQL, which accepts SQLite'sINSERT OR REPLACEandON 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