GuidesData
Configure a database¶
Hinoki's ORM runs on SQLite, PostgreSQL, MySQL and MariaDB. The database URL
selects the backend; models and queries stay the same wherever the backends'
SQL features overlap. With nothing configured, the database is local SQLite at
.hinoki/local.sqlite3.
Set up a database in four steps¶
-
Declare it.
hinoki new notes --database postgresqldoes this for you; otherwise add the field to your settings class. Name a TOML file only if you will use one (step 2): the file named bytoml=must exist.# notes/settings.py class ProjectSettings(settings.AppSettings, prefix="NOTES_", dotenv=".env"): database: settings.DatabaseSettings -
Populate it. With nothing set,
DatabaseSettingspoints at a local SQLite file,.hinoki/local.sqlite3. To use another database, pick one source:Source Use it for Example Nothing local development on SQLite – .envone URL on your machine ( hinoki new --database postgresqlwrites a.env.exampleto copy; keep.envout of git)NOTES_DATABASE__URL=postgresql://notes:secret@localhost:5432/notesEnvironment containers and CI export NOTES_DATABASE__URL_FILE=/run/secrets/database-urlsettings.tomlnon-secret fields ( engine,host,name,user), with the password from the environment; addtoml="settings.toml"to the classconnection fields The prefix (
NOTES_) comes from the settings class, and__separates the group from the field. Until you set one of them, a project created with--database postgresqlstill uses the local SQLite file, and so do its migrations. -
Check it.
hinoki settings, run from the project root, shows each value with its source; the URL and the password are redacted:$ hinoki settings | grep DATABASE__URL NOTES_DATABASE__URL .env ***hinoki migrations statusnames the database it will use, and fails with a clear error if the server does not answer:$ hinoki migrations status database: postgresql://***@127.0.0.1:5432/notes (from settings: ProjectSettings.database)statusconnects and records its migration ledger, so on SQLite it creates the database file. -
Create the tables. Hinoki never creates tables or runs migrations at startup (it only creates the local SQLite file): run
hinoki migrations applyonce you have models (migrations).
The rest of this page lists every field, the URL forms and each backend.
Configure the application¶
Declare a database field on the project settings. hinoki new adds it when
you choose a database.
# notes/settings.py
from hinoki import settings
class ProjectSettings(
settings.AppSettings, prefix="NOTES_", dotenv=".env", toml="settings.toml"
):
database: settings.DatabaseSettings
Each worker opens one bounded pool during startup and closes it during
shutdown. Importing models or routes never opens a connection, and no
modules= entry is needed. The file named by toml= must exist, even if it
is empty: otherwise the settings fail to load with
settings.toml: TOML file cannot be read or parsed.
Configure a server database with connection fields or with one URL. Setting both is an error when settings load.
Connection fields¶
Put non-secret fields in the TOML file named by toml=. Keep the settings in
their own file: hinoki.toml is the project manifest, and its tables are not
settings.
# settings.toml
[database]
engine = "postgresql"
name = "notes"
user = "notes"
host = "db.example.test"
port = 5432
options = ["sslmode=verify-full", "sslrootcert=/run/secrets/db-ca.pem"]
max_size = 10 # per worker
# settings.toml
[database]
engine = "mysql"
name = "notes"
user = "notes"
host = "db.example.test"
# settings.toml
[database]
engine = "mariadb"
name = "notes"
user = "notes"
host = "db.example.test"
# settings.toml
[database]
engine = "sqlite"
name = ".hinoki/local.sqlite3"
The password is a secret setting. It never comes from TOML:
export NOTES_DATABASE__PASSWORD_FILE=/run/secrets/db-password
| Field | Meaning | Default |
|---|---|---|
engine |
sqlite, postgresql, mysql or mariadb (settings.DatabaseEngine) |
– |
name |
database name, or the file path for SQLite | – |
user, password |
credentials; password is secret (*_PASSWORD or *_PASSWORD_FILE) |
– |
host, port |
server address; for PostgreSQL host may be a Unix-socket directory such as /var/run/postgresql |
localhost, backend port |
options |
key=value URL query pairs, such as sslmode |
() |
max_size |
connections per worker | 10 |
Hinoki builds DatabaseSettings.url from these fields and percent-encodes the
user, password and name. SQLite takes only name. options cannot carry
credentials or override user, host, port or the name. In an environment
variable, options uses the settings sequence syntax (commas between pairs);
a value that itself contains a comma belongs in the TOML array.
One URL¶
export NOTES_DATABASE__URL_FILE=/run/secrets/database-url
| Backend | URL |
|---|---|
| SQLite | sqlite://./.hinoki/local.sqlite3 |
| PostgreSQL | postgresql://notes:password@db.example.test/notes?sslmode=verify-full |
| MySQL | mysql://notes:password@db.example.test/notes |
| MariaDB | mariadb://notes:password@db.example.test/notes |
Percent-encode reserved characters in the user name and password of a
hand-written URL. Prefer NOTES_DATABASE__URL_FILE or a secret manager over a
URL in shell history or a process argument. Hinoki redacts the URL in settings
output and connection errors.
Credentials
settings.Secret values, url and password included, are never read from
TOML. SQLite has no database credentials: protect the file and its backups
with operating-system permissions.
Backends¶
| Backend | Security defaults | Limits |
|---|---|---|
| SQLite | no network, no TLS | one writer at a time; bounded connections and a five-second busy timeout; a relative path resolves from the server's working directory (start it from the project root; migrations anchor it beside hinoki.toml) |
| PostgreSQL | sslmode=prefer; use verify-full for remote servers; sslrootcert adds a PEM CA to the system roots. require, verify-ca and verify-full all verify the certificate |
native UUID, timestamp, JSON and byte types |
| MySQL | TLS required, certificate and host verified against the system trust store; require_ssl=false only for an isolated disposable local server |
InnoDB, native JSON; no RETURNING |
| MariaDB | TLS required by default | separate backend, tested on MariaDB 11.4 with its own conformance job. JSON is a text alias: typed dict/list fields decode by declaration; low-level Database.fetch() returns JSON values as bytes |
SQL Server and Oracle are not supported. Any other scheme, such as oracle://
or mssql://, fails when the pool is created with
DatabaseError: unsupported database URL scheme; the message does not repeat
the URL.
PostgreSQL TLS¶
PostgreSQL URLs accept sslmode=disable, prefer, require, verify-ca or
verify-full, and sslrootcert=/path/to/ca.pem. prefer tries TLS first and
falls back to plaintext only if the server declines TLS; a certificate or
handshake error fails closed. All TLS connections verify the server
certificate chain and hostname: require, verify-ca and verify-full all
require TLS, and Hinoki's verify-ca also verifies the hostname. Trust uses
Mozilla WebPKI roots plus any PEM certificates in sslrootcert (at most
1 MiB), so sslmode=require needs a trusted certificate too. sslmode=allow
is unsupported. Mount the CA file where each worker can read it.
Hinoki does not create the PostgreSQL role or database: create them once with
your usual tools before the first hinoki migrations apply.
SQLite storage¶
SQLite uses bundled SQLite 3.35 or newer. UUID and timestamp columns use text
storage; JSON uses JSON text. contains matching is ASCII case-insensitive.
In-memory databases are not supported, because each pooled connection would
have an independent database.
Use the database from a script¶
A script, a one-off task or a test fixture opens its own pool with
Database.connect and makes it current with use_database, so model queries
find it. Run it from the project root so a relative SQLite path and .env
match the server's:
# scripts/unpaid.py
import asyncio
from hinoki.db import Database
from hinoki.orm import use_database
from notes.apps.billing.models import Invoice
from notes.settings import ProjectSettings
async def main() -> None:
settings = ProjectSettings.load()
database = await Database.connect(
settings.database.url.reveal(), max_size=settings.database.max_size
)
try:
with use_database(database):
print(await Invoice.filter(paid=False).acount())
finally:
database.close()
asyncio.run(main())
Database.close() is a plain call, not awaited. The script does not create
tables: run hinoki migrations apply against the same database first.
Replace the database module¶
A DatabaseModule in modules=, or installed with app.install(...), replaces
the one Hinoki builds from the settings, for example to use a URL chosen at
runtime:
# notes/main.py (excerpt)
from hinoki.db import DatabaseModule
app.install(DatabaseModule(url, max_size=4))
The migration commands use this module's URL too, and print
(from application: DatabaseModule). A database module of your own whose URL
Hinoki cannot read stops them; pass --database or HINOKI_DATABASE_URL.
Migrations use the application's database¶
hinoki migrations reads the database of the project's application (its
DatabaseModule, or its database setting),
so the variables above configure both the server and the migration commands.
--database or HINOKI_DATABASE_URL override it for one run; with none of
them set, the local SQLite default is anchored beside hinoki.toml:
export NOTES_DATABASE__URL=postgresql://notes:secret@db.internal/notes
hinoki migrations status # database: postgresql://***@db.internal/notes (from settings: ...)
hinoki migrations apply
Hinoki never runs migrations at startup. An application that declares no
database setting and installs no DatabaseModule has no database at run
time, although its migrations still fall back to the local SQLite file:
declare the field before you add models. See which database
for the full order, and migrations for planning, review and
per-backend behaviour.
See also¶
- Settings: source precedence, secrets and validation
- Application modules: resource ownership and the
Databaseinstance - Database API reference: raw SQL, placeholders and backend limits