Skip to content
Unreleased: these pages follow the main branch. The latest release is v0.5.
Connect to a database

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:

DatabaseModuleDB_DRIVER
SQLite (pure Go, no C compiler)anetos.dev/anetos/drivers/sqlitesqlite
PostgreSQLanetos.dev/anetos/drivers/postgrespostgres
MySQL, MariaDBanetos.dev/anetos/drivers/mysqlmysql
go get anetos.dev/anetos/drivers/sqlite

Steps

1. Configure the connection

# .env
DB_DRIVER=postgres
DB_HOST=127.0.0.1
DB_NAME=blog
DB_USER=blog
DB_PASSWORD=secret

For 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   # MySQL

systemd’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.Go tasks, components, shutdown hooks);
  • provides it as a *db.DB service (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:

  1. Add the driver: go get anetos.dev/anetos/drivers/postgres, and pass postgres.Driver() to db.Connect in main.go (keep sqlite.Driver() too if you still want SQLite anywhere).

  2. Set DB_DRIVER=postgres and the other DB_* settings in .env (and .env.example), as in step 1.

  3. Create a test database and write .env.testing with 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=secret

    Without DB_DRIVER there (or without the file), tests would use SQLite; anetostest stops them with a message when .env has another DB_DRIVER.

  4. go run . migrate, then go test ./....

  5. The deploy files still say SQLite: in deploy/production.env.example, put DB_DRIVER and DB_URL in place of SQLite’s settings; in the Dockerfile, drop DB_NAME and DB_MIGRATE_ON_START (run migrate on each deploy instead); in deploy/<name>.service, drop DB_NAME; the README’s “nothing to set up” is SQLite’s too. A project made with --db postgres shows the server versions of these files.

  6. 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

SymptomCauseFix
db: DB_DRIVER is "postgres", but the drivers passed to Connect are [sqlite]The driver isn’t passed to ConnectImport the driver module and pass its Driver()
db: no database in contextThe context didn’t come from the appUse the request’s c, a context from app.Context, or db.WithDB
connect to postgres: … connection refused at startupWrong host or port, or the server isn’t runningCheck 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 TLSA remote DB_HOST gets verified TLS by defaultSet 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 SQLiteA write took longer than the 5s busy timeoutKeep 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 rowsGive the tests a database of their own

Next steps