Skip to main content

Databases

FTL has support for Postgresql and MySQL databases, including support for automatic provisioning and migrations.

Your database is automatically declared by following a specific directory structure for your SQL files. No additional configuration is needed - just create the directory structure and FTL will handle the rest. See the Creating a New Database section for CLI shortcuts.

Creating a New Database​

To create a new database with the required directory structure, you can use the ftl postgres new or ftl mysql new command. The format of the command is:

ftl <engine> new <module>.<datasource>

Where:

  • <engine> is either mysql or postgres
  • <module>.<datasource> is the qualified name of the datasource (module name can be omitted if in a single module directory)

For example:

ftl mysql new mymodule.mydb    # Create a MySQL database named "mydb" in module "mymodule"
ftl postgres new mydb          # Create a PostgreSQL database named "mydb" in the current module

This command will:

  1. Create the appropriate directory structure
  2. Create an initial migration file in the schema directory

SQL File Structure​

In order to be discoverable by FTL, the SQL files in your project must follow a specific directory structure. FTL supports two database engines, declared via the directory hierarchy as either mysql or postgres:

For Go projects, SQL files must be located in:

db/
  ├── mysql/           # must be exactly "mysql" or "postgres"
  │   └── mydb/        # database name
  │       ├── schema/  # contains migration files
  │       └── queries/ # contains query files

The presence of a schema directory under your database name automatically declares the database in FTL.

Schema Directory​

The schema directory contains all your database migration .sql files. These files are used to create and modify your database schema.

Queries Directory​

The queries directory contains .sql files with any SQL queries you would like generated as FTL verbs for use in your module. These queries must be annotated with SQLC annotation syntax. FTL will automatically lift these queries into the module schema and provide a type-safe client to execute each query.

Find more information in the Using Generated Query Clients section below.

Provisioning​

FTL includes support for automatically provisioning databases. The actual backing implementation is extensible, and presently we include support for both local development provisioning using docker, and cloud formations based provisioning for AWS deployments. When using ftl dev a docker container will automatically be spun up for each datasource that has been defined, and FTL will automatically handle configuration. The same applies when deploying to an AWS cluster with cloud formations provisioning setup.

Migrations​

FTL includes support for automatically running migrations on databases. This is provided by dbmate.

To create additional migrations you can use the ftl postgres new migration or ftl mysql new migration command. The format of the command is ftl <engine> new migration <module>.<datasource> <migration-name>.

The module name can be omitted if the current working directory only contains a single module.

E.g. to create a new migration called init for the testdb datasource in the mysql module you would run ftl mysql new migration mysql.testdb init.

When the modules are provisioned FTL will automatically run these migrations for you.

Connecting with your DB​

There are two supported ways to interact with your database in FTL: using the generated database handle to perform raw queries, or using generated query clients.

Using the Generated Database Handle​

Once you've declared a database, FTL automatically generates a database handle that provides direct access to the underlying connection. You can use this to execute raw SQL queries (where MydbHandle is the generated handle type for the mydb datasource):

//ftl:verb export
func Query(ctx context.Context, db MydbHandle) ([]string, error) {
	rows, err := db.QueryContext(ctx, "SELECT data FROM requests")
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []string
	for rows.Next() {
		var i string
		if err := rows.Scan(&i); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

Using Generated Query Clients​

For better type safety and maintainability, FTL can automatically generate type-safe query clients from SQL files in your queries directory. Your SQL files must be annotated with SQLC annotation syntax to specify the type of query and its parameters. For example:

-- name: GetUser :one
SELECT id, name, email
FROM users
WHERE id = $1;

-- name: ListUsers :many
SELECT id, name, email
FROM users
ORDER BY name;

-- name: CreateUser :exec
INSERT INTO users (name, email)
VALUES ($1, $2);

These queries will be automatically converted into FTL verbs with corresponding generated clients that you can inject into your verbs just like any other verb client. For example:

//ftl:verb export
func GetEmail(ctx context.Context, id int, query GetUserClient) (string, error) {
	result, err := query(ctx, id)
	if err != nil {
		return "", err
	}
	return result.Email, nil
}