3.2 · Guides
Configure a database¶
Hinoki's ORM supports PostgreSQL, SQLite, MySQL and MariaDB. SQLite is the
default for local development. The backend is selected by the database URL; an
application uses the same Model and query APIs across these backends where
their SQL features overlap.
Configure the application¶
Declare a database: DatabaseSettings field on your app settings. Hinoki then
opens one bounded pool per worker during startup and closes it during shutdown;
importing models or routes does not open a connection, and no modules= entry
is needed.
from hinoki import Hinoki, settings
class BlogSettings(
settings.AppSettings, prefix="BLOG_", dotenv=".env", toml="hinoki.toml"
):
database: settings.DatabaseSettings
app = Hinoki(settings=BlogSettings)
With nothing configured, the database is local SQLite at
.hinoki/local.sqlite3. Configure a server database either with separate
connection fields or with one URL; setting both is an error when settings load.
Connection fields¶
engine selects the backend with the settings.DatabaseEngine values
sqlite, postgresql, mysql and mariadb. name is the database name, or
the file path for SQLite. options are key=value URL query pairs such as
PostgreSQL's sslmode. host defaults to localhost; the password is a secret
setting.
# hinoki.toml, or the file named by the settings class's toml=
[database]
engine = "postgresql"
name = "blog"
user = "blog"
host = "db.example.test"
port = 5432
options = ["sslmode=verify-full", "sslrootcert=/run/secrets/db-ca.pem"]
max_size = 10 # per worker; the default is 10
export BLOG_DATABASE__PASSWORD='…' # or BLOG_DATABASE__PASSWORD_FILE
Hinoki builds the connection URL from these fields, percent-encoding the user,
password and name, and keeps it in DatabaseSettings.url. SQLite takes only
name.
For PostgreSQL, host may be a Unix-socket directory such as
/var/run/postgresql; it is encoded in the generated URL. Other engines require
a host name or address. Environment values for options use the settings
sequence syntax, with commas separating key=value pairs. If an option value
itself contains a comma, put it in the TOML array or percent-encode it in a URL.
Options cannot specify credentials or override user, host, port or database
name; use their dedicated fields instead. The password always belongs in the
secret password setting or in a credential-bearing URL supplied as a secret.
One URL¶
export BLOG_DATABASE__URL='postgresql://blog:password@db.example.test/blog'
export BLOG_DATABASE__URL='postgresql://blog:password@db.example.test/blog?sslmode=verify-full&sslrootcert=%2Frun%2Fsecrets%2Fdb-ca.pem'
export BLOG_DATABASE__URL='mysql://blog:password@db.example.test/blog'
export BLOG_DATABASE__URL='sqlite://./.hinoki/local.sqlite3'
A DatabaseModule listed in modules=, or installed with app.install(...),
replaces the one Hinoki would create from the settings, for example to pass a
URL chosen at runtime: DatabaseModule(url, max_size=4).
Use a secret manager or a BLOG_DATABASE__URL_FILE / BLOG_DATABASE__PASSWORD_FILE
variable for deployment credentials. Hinoki redacts the URL in settings output and connection errors.
Avoid placing a credential-bearing URL in shell history or a process argument.
Database authentication uses the username and password; in a hand-written URL,
percent-encode reserved characters in either component. SQLite files are protected by the
operating-system account and filesystem permissions, not by database credentials.
For non-secret pool settings, TOML works as well. Keep url in an environment
variable or secret file; settings.Secret values are not read from TOML.
[database]
max_size = 12
See settings for source precedence and validation, and
application modules for resource ownership and access to the
provided Database instance.
Backend URLs and behavior¶
| Backend | URL example | Use and constraints |
|---|---|---|
| SQLite | sqlite://./.hinoki/local.sqlite3 |
Supported; recommended local default. No network authentication or TLS. SQLite allows one writer at a time; Hinoki uses bounded connections and a five-second busy timeout. Relative paths are resolved from the application working directory. |
| PostgreSQL | postgresql://user:password@host/database |
Supported. Uses PostgreSQL username/password authentication and native UUID, timestamp, JSON and byte types. sslmode defaults to prefer; use sslmode=verify-full for remote databases. sslrootcert adds a PEM CA certificate to the system roots. Hinoki accepts disable, prefer, require, verify-ca and verify-full; the last three all require TLS and certificate verification in Hinoki. |
| MySQL | mysql://user:password@host/database |
Supported. Uses username/password authentication and requires TLS by default, with certificate and host verification. The system trust store must trust the server certificate. require_ssl=false disables encryption and is only for an isolated disposable local server. MySQL uses InnoDB and MySQL-native JSON; RETURNING is unavailable. |
| MariaDB | mariadb://user:password@host/database |
Supported and tested separately on MariaDB 11.4. Uses the MySQL wire driver and SQL dialect. MariaDB's JSON alias is text; typed ORM dict/list fields are decoded by their model declaration. Low-level Database.fetch() returns MariaDB JSON values as bytes because server metadata cannot safely distinguish JSON from arbitrary text. TLS is required by default. |
SQL Server and Oracle are not supported by this release. MySQL and MariaDB are separate URL/backend identities and each has its own real-server conformance job; MySQL's URL scheme does not imply MariaDB compatibility.
Any other URL scheme, such as oracle:// or mssql://, fails when the pool is
created with DatabaseError: unsupported database URL scheme; the error does not
repeat the URL, so credentials in it are not logged.
Create and apply migrations¶
Migrations use the same selected URL. With no explicit URL, the CLI uses the local SQLite database. For another backend, export the URL without passing it as a process argument:
cd examples
export HINOKI_DATABASE_URL="$BLOG_DATABASE__URL"
uv run --project .. hinoki migrations plan --app blog
uv run --project .. hinoki migrations status
uv run --project .. hinoki migrations sql --app blog 0001-initial
uv run --project .. hinoki migrations sql --app blog 0002-accounts
uv run --project .. hinoki migrations sql --app blog 0003-index-blog-posts-author-id
uv run --project .. hinoki migrations apply --app blog
Review generated SQL and state before applying them. Use a disposable database first. Hinoki does not run migrations automatically at app startup.
On a fresh local checkout, hinoki migrations ... apply uses the default
SQLite URL when HINOKI_DATABASE_URL is unset. Opening that URL creates
.hinoki/local.sqlite3 if it does not exist; applying creates the migration
ledger and runs the pending SQL files. As with Django's separate
makemigrations and migrate steps, first run plan to generate migration
files from model changes, then run apply to create the tables. status also
opens the selected database and creates its ledger table if needed.
Migration behavior differs by backend:
- SQLite rebuilds a table when a safe direct alteration is unavailable and checks foreign keys before committing the migration. Keep the database file and its backups on storage with permissions limited to the application user.
- PostgreSQL applies migrations in a transaction.
- MySQL implicitly commits DDL. If a migration fails, earlier statements may already have changed the schema; Hinoki reports that partial application is possible. Inspect schema and migration status before retrying, and retain a recoverable backup.
- MariaDB uses the MySQL wire protocol, but is a separate backend and is
tested against its own service. Its JSON type is a text alias:
Modelfields annotateddictorlistare decoded by the typed ORM, while the low-levelDatabase.fetch()API returns JSON column values as bytes.
Hinoki creates MySQL and MariaDB foreign keys as table-level constraints for
consistent behavior across the two dialects. MySQL ignores inline REFERENCES
clauses; MariaDB behavior differs by server version. For schemas created by an
older Hinoki version, inspect SHOW CREATE TABLE before relying on referential
integrity or on_delete. A migration ledger entry alone does not establish
that MySQL created the constraint. Write a reviewed repair migration where a
constraint is missing; Hinoki does not silently rewrite applied migrations.
See the migration guide for destructive-change review and planning details, and the database API reference for types, raw SQL placeholders and backend limits.