Database and migrations

Goat uses PostgreSQL running in Docker, so you do not need to install or configure a database server directly on the host.

The local environment stores PostgreSQL data in the directory:

.cache/postgres

This ensures that data does not disappear when the container is stopped or restarted.

This approach preserves the convenience of local development while maintaining a reproducible environment across different computers and operating systems.

Starting the database

To start only the database, run:

go run ./scripts run:script --path=herd/db/run.goat

Once started, PostgreSQL is available on port:

5433

Connection parameters are loaded from the local .env file.

Goat uses values with the prefix:

GOAT_DB_MAIN_*

These define, among other things, the data required to connect to the database, such as the database name, user, password, and host.

Do not store real production secrets in the repository.

Local data persistence

The PostgreSQL container can be stopped and restarted without data loss because the actual database files are located outside its temporary filesystem.

In the local environment, data is stored in:

.cache/postgres

This means that:

  • restarting the container does not delete the database,
  • restarting the development environment preserves the data,
  • migrations can be run against the existing local state,
  • you can work on the project without having to recreate the database from scratch every time.

If you need a clean environment, use the dedicated cleanup script instead of manually deleting PostgreSQL files.

Migrations

Run migrations with:

goat run:script --path=herd/db/migrate.goat

The script prepares the environment required to run migrations.

A typical process includes:

starting PostgreSQL
        ↓
generating the application
        ↓
building the CLI
        ↓
running db:migrate

This ensures that migrations do not depend on the developer manually performing subsequent steps.

Initial database schema

The basic application schema is generated from the model defined in:

herd/_model.goat

The generated SQL is located in:

app/static/data/sql/migrations/0001_schema.sql

The file contains the initial database structure resulting from the application model.

It may include, among other things:

  • tables,
  • columns,
  • data types,
  • keys,
  • relationships,
  • indexes,
  • constraints derived from the model.

This makes it possible to maintain consistency between the application's domain model and the underlying PostgreSQL schema.

Model and migrations

Changing:

herd/_model.goat

may affect the structure of the generated schema.

For example:

adding a field to an entity
        ↓
goat re
        ↓
changing the generated model
        ↓
changing the SQL

However, this does not mean that every model change is automatically a safe migration for an existing database.

This is particularly important when the database already contains data or is used by other users.

Examples of changes requiring additional attention:

  • removing a column,
  • changing a data type,
  • adding a NOT NULL field,
  • changing a relationship,
  • removing a table,
  • changing keys or constraints,
  • restructuring data already stored in the database.

The generator can describe the target structure, but safely transitioning from the current data state to the new schema may require a manually prepared migration.

Verifying migrations

Before applying schema changes to a database containing important data, review the generated or prepared SQL.

In particular, verify:

  • that the migration does not delete data,
  • that changing a column type is possible for existing values,
  • that new fields have appropriate default values,
  • that new constraints are satisfied by existing records,
  • that the migration does not cause costly locking of a large table,
  • that it is possible to safely roll back the change.

Treat operations such as the following with particular caution:

DROP TABLE
DROP COLUMN
ALTER COLUMN

because they may lead to irreversible data loss.

Cleaning the local database

To clean the local database, run:

goat run:script --path=herd/db/clean.goat

The script removes the data and structure of the local database, allowing you to start from a clean state.

This can be useful, among other things, when:

  • you want to retest project initialization,
  • you changed the model in a way that is incompatible with the local database,
  • you are testing migrations from an empty schema,
  • you want to restore fixture data,
  • local data no longer matches the current version of the application.

Warning about destructive operations

herd/db/clean.goat performs destructive operations.

It may permanently remove:

  • tables,
  • data,
  • the local database state.

Therefore, use it only when you are certain that the configuration points to the correct environment.

Before running it, it is worth checking the values of GOAT_DB_MAIN_* in .env, especially if the project can connect to more than one database.

Do not treat clean.goat as a tool for managing a production environment.

Typical workflow when changing the model

In a development environment, changing the data structure may look as follows:

# change the model
vim herd/_model.goat

# generate changes
goat re

# review the generated SQL and code
git diff

# run migrations
goat run:script --path=herd/db/migrate.goat

# run tests
goat run:script --path=herd/test.goat

If the local database is in a state incompatible with the current model and preserving the data is not required, you can clean it:

goat run:script --path=herd/db/clean.goat
goat run:script --path=herd/db/migrate.goat

Migrations in shared environments

An approach that is convenient during local development should not be automatically transferred to test, staging, or production environments.

For a database used by others, changing the model alone is not enough.

Before deploying a schema change:

  1. check the difference between the current and target schema,
  2. review the SQL executed by the migration,
  3. assess the impact of the migration on existing data,
  4. create a current backup,
  5. test the migration on a copy of real data,
  6. estimate the execution time and possible locks,
  7. prepare a way to roll back or fix a failed migration,
  8. only then apply the change to the target environment.

Testing migrations on data with a structure and size similar to production is particularly important. A migration that works correctly on an empty local database may behave completely differently on a large table containing millions of records.

The generator does not replace a data migration strategy

Goat can generate the structure resulting from the model, but it should not be treated as an automatic answer to every problem related to changing data.

There is a significant difference between:

the target schema

and:

a safe way to transition
from the current schema
to the target schema

For example, adding a required column may require several stages:

adding an optional column
        ↓
populating existing data
        ↓
deploying code that uses the new field
        ↓
adding a NOT NULL constraint

Similarly, changing a data type or relationship structure may require a data migration performed in stages.

The generator helps maintain the application structure, but responsibility for data safety remains with the migration process.

Best practice

Treat herd/_model.goat as a description of the application model, not as a guarantee of a safe migration of existing data.

For local changes, you can quickly regenerate the application and rebuild the database.

For changes affecting shared or production environments, always analyze the consequences for the existing schema and data.

The most important principle is:

The generator can describe the target database structure, but safely transitioning between successive data versions requires a deliberately designed migration.