# The data layer

Source: https://docs.anetos.dev/concepts/data-layer/

How the db package finds its connection, turns method calls into SQL, and
why it works the way it does.

```mermaid
flowchart LR
    C["db.Query[Post](ctx)"] --> M["model metadata<br/>(cached per type)"]
    C --> K{"ctx"}
    K -->|"transaction for this DB?"| T["*sql.Tx"]
    K -->|otherwise| P["*sql.DB pool"]
    M --> S["SQL for the dialect<br/>(placeholders, quoting, LIMIT)"]
    S --> T
    S --> P
    T --> R["rows → scan plan<br/>(cached per type + columns)"]
    P --> R
    R --> O["[]Post"]
```

## The connection travels in the context

`db.Connect` puts the `*db.DB` in every context the app creates, and
`db.Tx` returns a context that also carries a transaction. Every query
function takes a `context.Context` and looks both up:

- no database in the context: `db.ErrNoDB`;
- a transaction for that database: the query runs in it;
- otherwise: the connection pool.

This is why a handler can call `db.Find[Post](c, id)` with only its
request context, and why a function called inside `db.Tx` joins the
transaction without being told. The context already had to be passed for
cancellation and deadlines; the database rides along. For a second
database, `db.WithDB(ctx, other)` switches the target explicitly.

## Types in, SQL out

Models are plain structs. The first time the package sees a type, it reads
its fields and tags once (columns, key, timestamps, soft deletes) and
caches the result; the same happens for each combination of result type
and result columns when scanning. Per query, only values are copied: no
tag parsing and no type inspection.

Conditions are values built from typed columns (`PostCols.Views`,
generated by `anetos generate` from the struct), so `PostCols.Views.Eq("ten")`
is a compile error. The builder writes SQL for the
database's **dialect**: `$1` or `?` placeholders, `"quoted"` or
`` `quoted` `` identifiers, `RETURNING` or `LastInsertId`, `ON CONFLICT`
or `ON DUPLICATE KEY`. Values always travel as parameters, never inside
the SQL text.

Queries are immutable: every method returns a new query, so a base query
can be shared and extended without surprises.

## Drivers are separate modules

The dialects live in the core `db` package; the driver modules
(`drivers/sqlite`, `drivers/postgres`, `drivers/mysql`) only pair a
dialect with a `database/sql` driver and build connection strings. An app
downloads and compiles only the drivers it imports. The SQLite driver is
pure Go, so the single binary still cross-compiles without a C toolchain.

Every driver module runs the same conformance suite (`db/dbtest`) against
a real database in CI.

## One API, each database's own search

Full-text search (`q.Search(text)`) is one method over three different
engines: PostgreSQL's `tsvector` and GIN indexes, MySQL's `FULLTEXT`
indexes, SQLite's FTS5. A migration builds the database's own index
(`t.SearchIndex`), and the query builder writes that database's match and
ranking SQL. The engines don't agree on everything (which words they
stem, skip or weigh, and how they score), so the [Search](https://docs.anetos.dev/guides/search/index.md)
guide lists the differences instead of hiding them.

## The database is checked when the app boots

`db.Connect` pings the database when the app boots, then checks that it
can serve what the app asks of it: `DB_SEARCH_LANGUAGE` and `DB_SEARCH_RANKING`,
the capabilities features declared with `d.Require(feature, caps...)`
(`db.FullText`, `db.BM25`), and that the search indexes were built for the
current settings. A mismatch stops the app (and its commands) at once,
naming the setting, the database and the way out, rather than failing on
a user's first search or silently ranking differently. Commands that fix
the schema (`migrate…`, `search:reindex`) skip the last check, so they
can run.

## Times are UTC

Timestamps are written in UTC with microsecond precision, and times are
read back in UTC on every database, so a value round-trips exactly and
compares the same way everywhere. Every time argument is converted to UTC
before it is sent, and the drivers run their sessions in UTC (PostgreSQL
`timezone`, MySQL `time_zone`), so `CURRENT_TIMESTAMP` defaults and
`timestamp`/`DATETIME` columns without time zone agree with the app. On
SQLite, which stores times as text, times are written in the format of
its own `CURRENT_TIMESTAMP`, so text comparison and sorting match time
order.

The app can't drift into local time by a forgotten setting: when it
boots, it checks the session's time zone and stops if a connection string
set another one (unless `DB_ALLOW_LOCAL_TIMEZONE=true`). Calendar dates
are `anetos.Date` values, which have no zone and so can't change day on
the way to UTC. `APP_TIMEZONE` changes the zone the app works in (logs,
schedules, `anetos.Now`), never the one it stores in. See
[times and dates](https://docs.anetos.dev/guides/times-and-dates/index.md).

## What it deliberately doesn't do

- **No lazy loading.** Go can't intercept field access, and hidden queries
  are how N+1 bugs happen. Relations load only when asked, with `With`
  or `db.Load`, one query per relation whatever the number of rows
  ([Relations and eager loading](https://docs.anetos.dev/guides/relations/index.md)).
- **No dirty tracking.** `db.Update` writes every column; mass updates set
  exactly the columns you name. The [audit log](https://docs.anetos.dev/concepts/audit-log/index.md), which must
  know what changed, reads the row again in the write's transaction
  instead.
- **No magic zero values.** A NULL in a non-pointer field is an error, not
  a silent empty string.
- **No hidden SQL dialect.** Raw SQL is sent as written (apart from
  placeholders), so you can use each database's features.

## Guarantees

- Nested `db.Tx` calls use savepoints: an inner failure undoes only the
  inner work.
- `db.AfterCommit` callbacks run once, after the outermost commit, and
  never for rolled-back work.
- A `*db.DB` is safe for concurrent use. A transaction is one connection:
  run its queries one at a time.
- Writes to a table watched by `DB.Watch` (the audit log) run in a
  transaction with the watcher, which sees exactly what was written.

## Related

- [Connect to a database](https://docs.anetos.dev/guides/database/index.md), [Migrations](https://docs.anetos.dev/guides/migrations/index.md)
- [Define models and save data](https://docs.anetos.dev/guides/models/index.md)
- [Query data](https://docs.anetos.dev/guides/queries/index.md), [Search](https://docs.anetos.dev/guides/search/index.md)
- [Transactions](https://docs.anetos.dev/guides/transactions/index.md)
- [The audit log](https://docs.anetos.dev/concepts/audit-log/index.md)
- [Raw SQL](https://docs.anetos.dev/guides/raw-sql/index.md)
- [Performance](https://docs.anetos.dev/concepts/performance/index.md): what queries cost
- [Models reference](https://docs.anetos.dev/reference/models/index.md), [Query builder reference](https://docs.anetos.dev/reference/query-builder/index.md)
