Database example

Start with the running sample from Try an example. Open the notes folder in your code editor.

Change the heading in runtimes/api/index.html, save the file, and refresh your browser after Fluffy finishes rebuilding. Your change should appear.

The sections below explain how to change what the app saves.

#Understand: three jobs around one database

Migrations change the schema; seeds provide local example data; runtime requests create the notes you actually write. Keeping those jobs separate makes a restart predictable.

The example uses Goose, pinned in go.mod. SQL files are embedded in the application binary, so the migration command uses the schema shipped in that image. The image has a normal API entrypoint and two explicit lifecycle modes: /app migrate and /app seed.

#Configure your database

Notes serves its page from the API. Database access happens in that API, never in the browser.

#Declare the database and migration task

The example's fluffy-chainsaw.yaml contains:

databaseServers:
  primary:
    minimumMajorVersion: 17
    databases:
      notes:
        migrations:
          artifact: api-image
          command: [/app, migrate]
          timeoutSeconds: 900

Fluffy runs that command with DATABASE_URL for the target database. A nonzero exit stops startup before workloads run. The workspace selects local PostgreSQL and the resource policy; the migration code does not hardcode a hostname or password.

#Run migrations and local seeds

The first file, runtimes/api/migrations/00001_create_notes.sql, creates the application table:

-- +goose Up
CREATE TABLE notes (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL CHECK (length(title) BETWEEN 1 AND 120),
    body text NOT NULL DEFAULT '' CHECK (length(body) <= 10000)
);

-- +goose Down
DROP TABLE notes;

The second, 00002_add_created_at.sql, evolves it:

-- +goose Up
ALTER TABLE notes ADD COLUMN created_at timestamptz NOT NULL DEFAULT now();

-- +goose Down
ALTER TABLE notes DROP COLUMN created_at;

On an empty database Goose applies both files. On another run it checks its version history and skips versions already applied. This is why the example does not need CREATE TABLE IF NOT EXISTS as a substitute for migration bookkeeping.

lifecycle.go opens the lifecycle database with database/sql and the pgx driver, then passes the embedded files to Goose. The relevant part is:

files, err := fs.Sub(migrations, "migrations")
if err != nil {
	return err
}
locker, err := lock.NewPostgresSessionLocker()
if err != nil {
	return err
}
provider, err := goose.NewProvider(
	goose.DialectPostgres, db, files,
	goose.WithSessionLocker(locker),
)
if err != nil {
	return err
}
_, err = provider.Up(ctx)
return err

This excerpt belongs inside the complete runDatabaseTask function in the example repository. Goose supplies version tracking and a PostgreSQL session lock. See its provider documentation for the library contract and SQL annotations for transaction behavior.

The local file declares a separate seed:

apiVersion: fluffy-chainsaw.dev/local/v1
databases:
  notes:
    seed:
      artifact: api-image
      command: [/app, seed]

That command inserts the welcome note only when the table is empty. It does not overwrite user-created notes on restart. The seed is local-only; a cloud deployment runs migrations without adding this welcome data.

#Connect your runtime to the database

The API's grant is independent of the migration command:

runtimes:
  api:
    artifact: api-image
    port: 8080
    ingress: {http: true}
    uses:
      databases:
        - {name: notes, actions: [read, write]}

The generated package supplies NewDatabasesNotesClient(connection). database.go calls GetConnection for connection coordinates, configures pgxpool, and refreshes Credential in BeforeConnect for each new physical connection. A single pool is shared by handlers and closed when the application exits.

This keeps connection policy in Fluffy and SQL in the application. The runtime does not reuse the migration's administrative DATABASE_URL. See the sidecar connection recipe and database access reference.

#Make and verify a request

The browser form sends the same request you can send with curl. Replace PORT with the printed API port:

curl -i http://127.0.0.1:PORT/notes \
  -H 'Content-Type: application/json' \
  -d '{"title":"Learn migrations","body":"Goose remembers which SQL files have run."}'

curl http://127.0.0.1:PORT/notes

Expect HTTP 201 and the saved note, then that note in the list. The handler uses parameterized SQL:

INSERT INTO notes (title, body) VALUES ($1, $2)
RETURNING id, created_at

Try a blank title. The handler returns HTTP 400 before attempting the insert. The database also constrains the stored title and body. Browser validation helps the person; it does not replace server or database validation.

#Add another schema change

Add the next numbered SQL file with a Goose Up section. Stop and restart the local run so Fluffy builds the new image and runs the migration lifecycle. Ordinary source reload does not rerun migrations. Keep applied migration files unchanged; put new changes in a new version.

A Down section describes a reverse schema operation. It is not a backup and may destroy data. This example's automatic lifecycle calls only Up; Fluffy does not automatically undo migrations when an application deployment fails.

#Reference: scope and expected behavior

This is a local learning application with shared notes and no sign-in. Add authentication and application authorization before using it for private data. Its configured project ID is a placeholder, not a deployment destination.

The example intentionally starts with the database boundary. File attachments, background work, and other capabilities can be added through the individual guides without making them prerequisites for this lesson. For cloud backup policy and recovery procedures, continue to Database recovery.