---
# System prepended metadata

title: 'Design: Database User Experience'

---

# Design: Database User Experience
**Author**: @worstell
<!-- Status of the document - draft, in-review, etc. - is conveyed via HackMD labels -->

## Declaring Databases

Databases are declared using discoverable file locations with a required hierarchy:

**Go**
```
db/
  ├── mysql/              # must be exactly "mysql" or "postgres"
  │   └── mydb/           # database name
  │       ├── schema/     # contains migration files
  │       └── queries/    # contains query files
  │       └── config.toml # additional configurations
```

**JVM**
```
src/main/resources/
  └── db/
      ├── mysql/              # must be exactly "mysql" or "postgres"
      │   └── mydb/           # database name
      │       ├── schema/     # contains migration files
      │       └── queries/    # contains query files
      │       └── config.toml # additional configurations
```

A `schema` subdirectory must be present in order for the DB to be extracted to the schema (and consequently provisioned).

The DB can be created manually or via CLI:
```
ftl mysql new <module-name>.<db-name>
ftl postgres new <module-name>.<db-name>
```

The following:

```
ftl mysql migration new example.mydb
```

Will create the `db/postgres/mydb` directory containing an empty `schema` directory and `config.toml` file with no contents. This database will be provisioned at the next deploy.

Users can optionally supply additional configurations in the `config.toml`.

```toml
timeout = 60000
max_connections = 5
... other configs
```

Like declaring the DB itself, users can add or update DB configs either manully or via the CLI. Something like this:

```
ftl database configure <module-name>.<db-name> key,value
```

Where the following would produce the example above:
```
ftl database configure example.mydb timeout,60000
ftl database configure example.mydb max_connections,5
```

### Accessing Databases

Any queries in `.sql` files found in the `queries.sql` subdirectory are generated into type-safe SQL verbs.

We'll also generate a handle for raw queries using the runtime-native driver. If no queries directory is present, or if the queries directory is empty, this handle is the only means to communicate with the DB.


**Generated handle**:

```go!
type Mydb struct {}
type MydbHandle = ftl.DatabaseHandle[Mydb]
```

**Example user code:**

```go!
//ftl:verb
func RawQuery(ctx context.Context, db MydbHandle) error {
    db.Get(ctx).Query("SELECT data FROM requests ORDER BY created_at;")
    ...
}
```