Connect to a database
Open your app’s database from configuration and use it from handlers, jobs and background tasks.
Before you start
Pick a driver module. Each is a separate Go module, so your binary only contains the drivers you use:
| Database | Module | DB_DRIVER |
|---|---|---|
| SQLite (pure Go, no C compiler) | anetos.dev/anetos/drivers/sqlite | sqlite |
| PostgreSQL | anetos.dev/anetos/drivers/postgres | postgres |
| MySQL, MariaDB | anetos.dev/anetos/drivers/mysql | mysql |
go get anetos.dev/anetos/drivers/sqliteSteps
1. Configure the connection
# .env
DB_DRIVER=postgres
DB_HOST=127.0.0.1
DB_NAME=blog
DB_USER=blog
DB_PASSWORD=secretFor SQLite, DB_NAME is a file path (default database/app.db).
A database on another machine is reached over TLS that checks its
certificate (DB_TLS=verify, the default for any host but localhost
or a loopback address); DB_TLS_CA names a PEM file of your provider’s
certificate authority when the system doesn’t know it, and
DB_TLS=none turns TLS off for a private network you trust. Put other
driver options in DB_URL, which replaces the individual settings
(TLS included), in the driver’s format:
DB_URL=postgres://blog:[email protected]:5432/blog?sslmode=verify-full
DB_URL=blog:secret@tcp(db.example.com:3306)/blog?tls=true # MySQLsystemd’s EnvironmentFile and Docker’s --env-file read the value as
it is, quotes included, so leave it unquoted there; in a file a shell
reads (set -a; . ./production.env), quote a MySQL URL
(DB_URL='blog:secret@tcp(…)/blog'): its parentheses are the shell’s.
Every key is in the
configuration reference.
2. Connect at startup
// DB_DRIVER (default sqlite) picks one of the drivers passed here.
if _, err := db.Connect(context.Background(), app, sqlite.Driver()); err != nil {
return nil, err
}(Copied from examples/database, region connect.)
Pass every driver the app may use: DB_DRIVER picks one, so you can
develop on SQLite and deploy on PostgreSQL with the same binary.
db.Connect pings the database when the app boots, so a wrong password
stops the app (or a command such as migrate) at startup instead of on
the first request, while ./app help works without a database. It also:
- adds the connection to every context the app creates (HTTP requests,
app.Gotasks, components, shutdown hooks); - provides it as a
*db.DBservice (anetos.Resolve[*db.DB](app)); - closes it in a shutdown hook, after the components stop.
3. Query with the request context
Any function with the request’s context can query, with no database field to pass around:
// illustrative
func (h *Posts) Show(c *web.Ctx, in PostID) (Post, error) {
return db.Find[Post](c, in.ID) // c is a context.Context
}Code outside a request, such as a CLI command, gets the same values from
app.Context(ctx).
4. Add more connections (optional)
Read another set of keys with a prefix and put that database in the context when you need it:
// illustrative
cfg, err := db.LoadConfig(app.Source(), "ANALYTICS_") // ANALYTICS_DB_DRIVER, ANALYTICS_DB_HOST, …
analytics, err := db.Open(postgres.Driver(), cfg, db.WithLogger(app.Logger()))
app.OnShutdown("analytics-db", func(context.Context) error { return analytics.Close() })
events, err := db.Query[Event](db.WithDB(ctx, analytics)).Get()5. Switch a project to PostgreSQL or MySQL
A project made with anetos new uses SQLite unless you passed
--db postgres or --db mysql. To switch later:
Add the driver:
go get anetos.dev/anetos/drivers/postgres, and passpostgres.Driver()todb.Connectinmain.go(keepsqlite.Driver()too if you still want SQLite anywhere).Set
DB_DRIVER=postgresand the otherDB_*settings in.env(and.env.example), as in step 1.Create a test database and write
.env.testingwith all its settings: tests don’t read.env, so nothing is inherited from it.# .env.testing DB_DRIVER=postgres DB_HOST=127.0.0.1 DB_NAME=blog_test DB_USER=blog DB_PASSWORD=secretWithout
DB_DRIVERthere (or without the file), tests would use SQLite;anetosteststops them with a message when.envhas anotherDB_DRIVER.go run . migrate, thengo test ./....The deploy files still say SQLite: in
deploy/production.env.example, putDB_DRIVERandDB_URLin place of SQLite’s settings; in theDockerfile, dropDB_NAMEandDB_MIGRATE_ON_START(runmigrateon each deploy instead); indeploy/<name>.service, dropDB_NAME; the README’s “nothing to set up” is SQLite’s too. A project made with--db postgresshows the server versions of these files.go tool anetos agent:update: the coding agents’ files name the database too.
Migrations written with the schema builder run on every database; raw SQL in migrations may need changes.
How it works
A *db.DB wraps a database/sql pool and the database’s dialect. Queries
look it up in their context with db.From, and use a transaction instead
if the context carries one (see Transactions). The
data layer concept explains why.
In development, every query is logged at debug level with its duration.
Queries slower than DB_SLOW_QUERY (default 500ms) are logged as warnings
in every environment. In development and tests, a request (or job, …)
that runs the same query five times or more is logged as a warning: see
Find N+1 queries.
When the app starts, it also checks that the database can serve what the
app asks of it (the DB_SEARCH_* settings, features’ requirements), and
stops with a clear message if it can’t: see
Add full-text search.
Note: There is no database config file: extra connections use prefixed keys.
Testing it
anetostest.New(t, setup) boots the app with an in-memory SQLite
database (or, with DB_* set, your test database inside a transaction
rolled back at the end) and runs the migrations; pass app.Context() to
code that queries. See Test your app.
To test code without the app, open a database and put it in a context:
// illustrative
d, err := db.Open(sqlite.Driver(), db.Config{Name: ":memory:"})
if err != nil {
t.Fatal(err)
}
t.Cleanup(func() { d.Close() })
ctx := db.WithDB(t.Context(), d)An in-memory database has a single connection, so a query that waits for
a second one (on the outer context inside db.Tx, or inside an All()
loop) blocks. A file in t.TempDir() behaves like production instead.
Common problems
| Symptom | Cause | Fix |
|---|---|---|
db: DB_DRIVER is "postgres", but the drivers passed to Connect are [sqlite] | The driver isn’t passed to Connect | Import the driver module and pass its Driver() |
db: no database in context | The context didn’t come from the app | Use the request’s c, a context from app.Context, or db.WithDB |
connect to postgres: … connection refused at startup | Wrong host or port, or the server isn’t running | Check DB_HOST/DB_PORT; the error comes from the ping in Connect |
… certificate signed by unknown authority, server does not support SSL, TLS requested but server does not support TLS | A remote DB_HOST gets verified TLS by default | Set DB_TLS_CA to your provider’s CA file, or DB_TLS=none on a private network (a Compose service, say) |
database is locked on SQLite | A write took longer than the 5s busy timeout | Keep transactions short; SQLite allows one writer at a time |
Tests fail on rows they didn’t make (redirect to /register, want /dashboard: the user exists) | .env.testing names the app’s own database, which has your rows | Give the tests a database of their own |