# 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`.

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**:

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

**Example user code:**

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