Design: Database User Experience

Author: @worstell

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;")
    ...
}