Build with Storine
Using an ORM
Point SQLAlchemy, Django, Prisma, TypeORM, Sequelize, Drizzle, GORM, Hibernate, Entity Framework Core, Eloquent or ActiveRecord at Storine through the SQL gateway, with a setup for each that the test suite runs.
Storine has two ways in:
| The Storine clients | An ORM through the SQL gateway | |
|---|---|---|
| Data model | Documents in collections | Tables, rows, joins, foreign keys |
| Speaks | Storine's own protocol | PostgreSQL, MySQL or SQL Server |
| Best for | New code, the fastest path, documents with nested fields, vector search, analytics | Existing apps and frameworks, teams that think in SQL, tools that need a SQL database |
| Read | Clients | This page |
The eight Storine clients are not ORMs: they read and write documents directly, with no schema to declare. If your framework expects a SQL database, use its ORM through storine-sql, the SQL gateway. It speaks the PostgreSQL, MySQL and SQL Server protocols, so the ORM uses its usual driver and dialect and does not know it is talking to Storine. Your tables are stored as Storine documents, with Storine's durability, replication and exact decimals.
Every setup on this page is taken from a suite in crates/storage-sql/tests/clients/ that runs against a real gateway.
1. Start the gateway#
# a database in a folder, like SQLite but served over the network
storine-sql --data ./db --user app:secret
# or in front of a running Storine server or cluster
storine-sql --storine storine://token@10.0.0.1:7878,10.0.0.2:7878,10.0.0.3:7878 --user app:secretIt listens on 127.0.0.1 by default: PostgreSQL on 5432, MySQL on 3306 and SQL Server on 1433. Each option is in the configuration reference.
- Users: with
--user name:password(repeatable) orSTORINE_SQL_USERS, clients must log in. Without any, every client is trusted, so keep the gateway on loopback. - Databases: the default database is
storine(--database). A client that names a database that does not exist is served the default one, andCREATE DATABASEmakes a separate one. Test databases (Django, Rails) and Prisma's shadow database rely on this, and it works. - TLS is on by default, with a certificate made for
localhostand kept inDIR/tls/server.crtwith--data. Drivers that prefer TLS use it. To verify the certificate, give the driver that file (sslmode=verify-full sslrootcert=./db/tls/server.crt). Clients may also connect without TLS unless you pass--require-tls.
2. Pick a protocol#
Use the PostgreSQL endpoint unless your app is written for MySQL or SQL Server. Every ORM below is tested on it, and it is the only one with pgvector's SQL for vector search.
Use the endpoint whose dialect your app already speaks. The gateway understands each dialect, including T-SQL on the SQL Server endpoint, and emulates the catalogs ORMs read to introspect a schema.
3. Connect your ORM#
Python: SQLAlchemy#
import sqlalchemy as sa
engine = sa.create_engine("postgresql+psycopg://app:secret@127.0.0.1:5432/shop") # psycopg 3
# "postgresql+psycopg2://...", "mysql+pymysql://app:secret@127.0.0.1:3306/shop", "mssql+pymssql://..." also work
Base.metadata.create_all(engine)Async works with create_async_engine("postgresql+asyncpg://app:secret@127.0.0.1:5432/shop"). Tested: create_all and drop_all, reflection with inspect, relationships with cascades, selectinload and joinedload, aggregates with outer joins, atomic UPDATE ... SET balance = balance - x, IntegrityError on a unique violation, paging. The pgvector adapter for SQLAlchemy works too.
Python: Django#
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"NAME": "shop",
"USER": "app",
"PASSWORD": "secret",
"HOST": "127.0.0.1",
"PORT": "5432",
}
}manage.py migrate runs Django's own apps' migrations, and the test runner creates and drops its test_shop database. select_for_update and transactions work. The MySQL backend is tested too, through PyMySQL.
Node.js: Prisma#
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}DATABASE_URL="postgresql://app:secret@127.0.0.1:5432/shop?schema=public" npx prisma db pushPrisma 6 is tested on all three endpoints:
| Provider | URL |
|---|---|
postgresql | postgresql://app:secret@127.0.0.1:5432/shop?schema=public |
mysql | mysql://app:secret@127.0.0.1:3306/shop |
sqlserver | sqlserver://127.0.0.1:1433;database=shop;user=app;password=secret;encrypt=true;trustServerCertificate=true |
prisma db push creates the schema, and introspecting it gives back exactly the same schema (prisma migrate diff finds no drift). Decimal fields stay exact.
Node.js: TypeORM#
const { DataSource } = require("typeorm");
const ds = new DataSource({
type: "postgres", // or "mysql", "mssql"
host: "127.0.0.1",
port: 5432,
username: "app",
password: "secret",
database: "shop",
entities: [Wallet, Move],
});
await ds.initialize();
await ds.synchronize();Tested: synchronize (a second run finds the schema up to date), repositories, relations, @VersionColumn optimistic locking, and transactions.
Node.js: Sequelize, Drizzle and Knex#
const { Sequelize } = require("sequelize");
const sequelize = new Sequelize("postgres://app:secret@127.0.0.1:5432/shop", { logging: false });
// or "mysql://app:secret@127.0.0.1:3306/shop"; SQL Server:
// new Sequelize("shop", "app", "secret", { dialect: "mssql", host: "127.0.0.1", port: 1433,
// dialectOptions: { options: { encrypt: false, trustServerCertificate: true } } })
await sequelize.sync();const { Pool } = require("pg");
const { drizzle } = require("drizzle-orm/node-postgres");
const db = drizzle(new Pool({ connectionString: "postgres://app:secret@127.0.0.1:5432/shop" }));const knex = require("knex")({ client: "pg", connection: { host: "127.0.0.1", port: 5432, user: "app", password: "secret", database: "shop" } });Sequelize's sync (including sync({ alter }) on SQL Server), associations, paging and transactions are tested; Drizzle on PostgreSQL and MySQL; Knex on PostgreSQL.
Go: GORM#
import (
"gorm.io/driver/postgres"
"gorm.io/gorm"
)
db, err := gorm.Open(postgres.Open("host=127.0.0.1 port=5432 user=app password=secret dbname=shop"), &gorm.Config{})
err = db.AutoMigrate(&Product{}, &Review{})MySQL: mysql.Open("app:secret@tcp(127.0.0.1:3306)/shop?parseTime=true"). SQL Server: sqlserver.Open("sqlserver://app:secret@127.0.0.1:1433?database=shop"), where upserts use MERGE. AutoMigrate, preloading associations, gorm.Expr updates, upserts with OnConflict and transactions are tested.
Java: Hibernate#
hibernate.connection.url=jdbc:postgresql://127.0.0.1:5432/shop
hibernate.connection.username=app
hibernate.connection.password=secret
hibernate.hbm2ddl.auto=updateMySQL: jdbc:mysql://127.0.0.1:3306/shop?allowPublicKeyRetrieval=true. SQL Server: jdbc:sqlserver://127.0.0.1:1433;databaseName=shop;encrypt=false, with SQLServerDialect. Hibernate 6 is tested with hbm2ddl create-drop and validate, and @Version optimistic locking.
.NET: Entity Framework Core#
options.UseNpgsql("Host=127.0.0.1;Port=5432;Username=app;Password=secret;Database=shop");
// MySQL (Pomelo): give the server version instead of auto-detecting it
options.UseMySql("Server=127.0.0.1;Port=3306;User ID=app;Password=secret;Database=shop;AllowPublicKeyRetrieval=true",
new MySqlServerVersion(new Version(8, 4, 3)));
// SQL Server
options.UseSqlServer("Server=tcp:127.0.0.1,1433;Database=shop;User ID=app;Password=secret;Encrypt=False");Tested on all three providers: creating the tables, relationships, concurrency tokens, ExecuteUpdate, and on SQL Server batched MERGE inserts. EnsureCreated skips a database that already has any table, as it does elsewhere.
PHP: Laravel Eloquent#
// config/database.php
'pgsql' => [
'driver' => 'pgsql',
'host' => '127.0.0.1',
'port' => 5432,
'database' => 'shop',
'username' => 'app',
'password' => 'secret',
],Eloquent models and the schema builder are tested on the pgsql and mysql drivers. Plain PDO and pgsql / mysqli work too, with native and emulated prepares.
Ruby: Rails ActiveRecord#
# config/database.yml
production:
adapter: postgresql
host: 127.0.0.1
port: 5432
database: shop
username: app
password: secretActiveRecord 8 is tested with the postgresql adapter and with trilogy for MySQL: migrations, associations, aggregates, lock_version optimistic locking, update_counters and transactions. Rails' test databases work, since CREATE DATABASE makes a separate database.
Bulk imports#
Before a large import, tell the store how many rows are coming. It sizes its index once, so the import doesn't pause each time the index doubles:
SELECT storine_reserve('orders', 1000000);Run it as raw SQL from any ORM (session.execute(text(...)), connection.cursor().execute(...), prisma.$queryRaw, db.Exec, DB::select, ActiveRecord::Base.connection.execute). Only a superuser may call it, and the gateway's own server token needs the admin role, since one call can take gigabytes of address space. It returns true. Run it from the import script with an admin login rather than from the app's everyday connection. One call covers up to 100,000,000 documents: each row counts as one, plus one per unique constraint. It is only a hint. It changes no data; on a range cluster every node is sized for its share. Most useful for imports of millions of rows; with fewer, the pauses are under a millisecond anyway.
To cover every import with no SQL at all, start the server with STORAGE_EXPECTED_DOCUMENTS set to about how many documents the database will hold (server settings). The ORM then needs nothing.
Migrations and schema changes#
ORMs create and change tables with ordinary DDL, which the gateway applies to its own catalog. Every way above of creating a schema is tested: create_all, migrate, db push, synchronize, AutoMigrate, hbm2ddl, Rails migrations and EF Core's table creation.
Warning
DDL is applied immediately, even inside a transaction, as on MySQL. On the PostgreSQL endpoint, a ROLLBACK undoes a transaction's data changes but not its CREATE, ALTER or DROP. So a migration that fails halfway leaves the steps it already ran: fix the cause and run it again, or undo those steps by hand.
Migration tools that take a lock first work: PostgreSQL advisory locks, MySQL GET_LOCK and SQL Server sp_getapplock are all implemented, and shared by every gateway in front of the same store.
Transactions, retries and money#
- No lost updates. Every written row carries the version it was read at, and a concurrent change makes the commit fail instead of being overwritten.
- Autocommit statements that lose such a race are re-run for you, so
UPDATE accounts SET balance = balance - 10 WHERE id = 1from many connections at once is always exact. - Explicit transactions that lose a race fail with SQLSTATE
40001(MySQL error 1213, SQL Server error 1205), the "serialization failure, retry" error. Wrap transactions in your ORM's retry helper, or retry on that error. - Optimistic locking works with every ORM's version column:
@Version,lock_version,@VersionColumn, concurrency tokens. SELECT ... FOR UPDATEtakes row locks, withNOWAITandSKIP LOCKED.- Decimals stay exact end to end: map money to
numeric(p, s)/decimal(p, s)and to your language's decimal type.
Isolation levels behave as on the database you chose; the details are in the gateway's transactions section.
Connection pools#
ORM pools work as usual. PgBouncer works in session and transaction pooling modes, and DISCARD ALL really resets a session. With transaction pooling, session state (SET, session advisory locks) does not carry between transactions, as with PostgreSQL.
What to watch for#
- One schema per database.
CREATE SCHEMAis accepted, but every table lives in the database's single namespace, reported aspublic(anddboon SQL Server). - Functions, procedures and triggers are written in PostgreSQL syntax (
LANGUAGE sqlor a PL/pgSQL subset) on every endpoint. T-SQL procedure bodies are not accepted. - Extensions: pgvector's SQL works; other PostgreSQL extensions do not.
- SQL Server: no MARS (one active result per connection) and logins by password, access token or Kerberos.
- Unsupported statements fail with SQLSTATE
0A000and a message naming the feature; nothing is silently ignored except session settings that do not apply, such asSET search_path.
The full list is in the gateway's known limitations.
Coming from SQLite#
There is no SQLite endpoint. SQLite is a library compiled into your program, not a server with a protocol the gateway could speak, so sqlite3 drivers and SQLite ORM dialects cannot connect to Storine.
What you can do instead:
- Keep the "one folder, no setup" feel: run
storine-sql --data ./dbbeside your app, and switch the ORM's dialect from SQLite to PostgreSQL. Most SQLite SQL carries over:INSERT OR REPLACE,ON CONFLICT ... DO NOTHINGandON CONFLICT ... DO UPDATEare understood. - Embed it in-process from Rust with
storage-core: see embedding the engine. That is the closest match to SQLite, without SQL. - Use a Storine client if you do not need SQL: documents need no schema or migrations.
Seeing ORM data from the rest of Storine#
Tables are stored as Storine documents, so backups, replication, encryption at rest and point-in-time recovery cover them like any other data. Read and write tables through SQL, though: the gateway keeps its own catalog of tables, indexes and constraints, and checks them on the writes it handles.