plainsql.yaml

plainsql.yaml

projects:
  blog:
    database:
      dsn: ${DATABASE_URL}
    queries:
      - dir: queries
        mode: read
        include:
          - "**/*_read.sql"
      - dir: queries
        mode: readwrite
        include:
          - "users.sql"
          - "posts.sql"
    generate:
      go:
        package: db
        dir: gen/db

All paths are relative to plainsql.yaml. In this example, the blog project reads SQL files from queries, checks them against the database at DATABASE_URL, and writes a Go package to gen/db.

Apply migrations to the database before running plainsql generate.

Settings

Setting	Meaning / default
projects	One or more named projects, each with its own database, queries, and output.
database.dialect	postgres. This is the only supported value.
database.dsn	Required. The connection string. ${NAME} is replaced with the environment variable NAME. An unset variable is an error.
queries[].dir	Required. A directory with SQL files. A project can have more than one queries entry.
queries[].mode	readwrite by default, or read. Applies to every query in the selected files.
queries[].include	Optional glob patterns, relative to dir. Without this key, PlainSQL selects every .sql file under dir. An empty list selects no files.
generate.go.package	Required. The Go package name.
generate.go.dir	Required. The output directory. Generation replaces everything in it.
generate.go.result_structs	values by default, or pointers.
generate.go.models	Optional. Custom names for shared models, keyed by schema.table.
generate.go.types	Optional. Custom Go type mappings, keyed by Postgres type.

Generation replaces everything in the output directory, so keep handwritten code elsewhere.

Each SQL file can belong to only one queries entry. See query modes for how files are selected.

Choose a shared model name

By default, the model for the users table is User. To use a different name, add models under generate.go:

models:
  public.users: BlogUser

Queries that select a complete users record now return BlogUser, including those with a record annotation:

-- plainsql: query GetUser returns one
-- plainsql: record u
SELECT u.* FROM users AS u WHERE u.id = $1;

Return structs as pointers

By default, generated methods return structs as values. If your application passes structs around as pointers, set result_structs under generate.go:

generate:
  go:
    package: db
    dir: gen/db
    result_structs: pointers

For shared models and query-specific row structs, the return types change as follows:

Result	values (default)	pointers
One user	(User, error)	(*User, error)
A list of complete users	([]User, error)	([]*User, error)
A list of partial user rows	([]ListUsersRow, error)	([]*ListUsersRow, error)

Parameter structs, single-value results, and struct fields do not change.

In both modes, returns one with no matching row returns an error that matches pgx.ErrNoRows, and returns many with no rows returns an empty slice.