Query modes

A query mode says whether a set of SQL files can write data. Files in read mode may only read. PlainSQL puts their methods in a Read interface. If a function takes db.Read, it cannot call a write method, and the compiler reports the mistake.

ReadTx and ReadTxValue also start a read-only Postgres transaction, so the database itself rejects writes at runtime. A direct query call does not start a transaction.

Use read for queries that only read data and readwrite for queries that can write data or lock rows.

In plainsql.yaml, each queries entry selects SQL files and assigns them a mode.

Select files by pattern

queries:
  - dir: queries
    mode: read
    include:
      - "reports/**/*.sql"
      - "users/get.sql"
      - "users/list.sql"

  - dir: queries
    mode: readwrite
    include:
      - "commands/**/*.sql"
      - "users/create.sql"
      - "users/lock.sql"

Both entries point at the same queries directory. The include patterns decide which files get which mode.

Pattern	Selected files
users/get.sql	One specific file.
users/*.sql	SQL files directly inside users.
reports/**/*.sql	SQL files inside reports and its subdirectories.
**/*_read.sql	SQL files with the _read.sql suffix at any depth.

Patterns are relative to dir and use / as the separator. An absolute pattern, or one that contains .., is an error.

No include key and an empty list mean different things. Without the key, PlainSQL selects every .sql file under dir, including subdirectories. With include: [], it selects none. PlainSQL ignores files that are not selected and does not follow symbolic links.

Within one entry, two patterns may match the same file. The file is selected once. If two entries select the same file, PlainSQL reports an error, even when both have the same mode. The order of the entries does not matter.

Assign a mode to a directory

If your read and write queries are already in separate directories, you do not need patterns:

queries:
  - dir: queries/reports
    mode: read
  - dir: queries/commands
    mode: readwrite

readwrite is the default mode. A file name like posts_read.sql does not make a file read-only. Only the configuration does.

Generated interfaces

Suppose a project has four queries. GetUser and ListUsers are in a read entry, and CreateUser and LockUser are in a readwrite entry. PlainSQL generates these interfaces:

type Read interface {
    GetUser(ctx context.Context, id int64) (User, error)
    ListUsers(ctx context.Context) ([]User, error)
}

type ReadWrite interface {
    Read

    CreateUser(ctx context.Context, params CreateUserParams) (User, error)
    LockUser(ctx context.Context, id int64) (User, error)
}

ReadWrite embeds Read, so it has every method. The generated *Queries type implements both. All queries entries in a project go into the same generated package.