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:

go get anetos.dev/anetos/drivers/sqlite

Steps

1. Configure the connection

# .env
DB_CONNECTION=postgres
DB_HOST=127.0.0.1
DB_DATABASE=blog
DB_USERNAME=blog
DB_PASSWORD=secret

For SQLite, DB_DATABASE is a file path (default database/app.db). Put TLS and other driver options in DB_URL, which replaces the individual settings. Every key is in the configuration reference.

2. Connect at startup

// DB_CONNECTION (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_CONNECTION 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_CONNECTION, 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_CONNECTION=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_CONNECTION=postgres
   DB_HOST=127.0.0.1
   DB_DATABASE=blog_test
   DB_USERNAME=blog
   DB_PASSWORD=secret

   Without DB_CONNECTION there, tests would use SQLite; anetostest stops them with a message when .env has another DB_CONNECTION.

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

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 SEARCH_* settings, features’ requirements), and stops with a clear message if it can’t: see Add full-text search.

Coming from Laravel? DB_CONNECTION, DB_HOST and friends mean what they mean in Laravel’s .env. There is no config/database.php: 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{Database: ":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

Next steps

• Migrations
• Define models and save data
• Query data