Overriding types

Note

Type overrides and field renaming are only fully-supported for Go.

In many cases it’s useful to tell sqlc explicitly what Go type you want it to use for a query input or output. For instance, by default when you use pgx/v5, sqlc will map a PostgreSQL UUID type to UUID from github.com/jackc/pgx/pgtype. But you may want sqlc to use UUID from github.com/google/uuid instead.

To tell sqlc to use a different Go type, add an entry to the overrides list in your configuration.

sqlc offers two kinds of Go type overrides:

• db_type overrides, which override the Go type for a specific database type.

• column overrides, which override the Go type for a column or columns by name.

Here’s an example including one of each kind:

version: "2"
sql:
- schema: "postgresql/schema.sql"
  queries: "postgresql/query.sql"
  engine: "postgresql"
  gen:
    go: 
      package: "authors"
      out: "db"
      sql_package: "pgx/v5"
      overrides:
        - db_type: "uuid"
          nullable: true
          go_type:
            import: "github.com/google/uuid"
            type: "UUID"
        - column: "users.birthday"
          go_type: "time.Time"

Tip

A single db_type override configuration applies to either nullable or non-nullable columns, but not both. If you want the same Go type to override regardless of nullability, you’ll need to configure two overrides: one with nullable: true and one without.

The overrides list

Each element in the overrides list has the following keys:

• db_type:

  • A database type to override. Find the full list of supported types in postgresql_type.go or mysql_type.go. Note that for Postgres you must use pg_catalog-prefixed names where available. db_type and column are mutually exclusive.

• column:

  • A column name to override. The value should be of the form table.column but you can also specify schema.table.column or catalog.schema.table.column. column and db_type are mutually exclusive.

• go_type:

  • The fully-qualified name of a Go type to use in generated code. This is usually a string but can also be a map for more complex configurations.

• go_struct_tag:

  • A reflect-style struct tag to use in generated code, e.g. a:"b" x:"y,z". If you want json or db tags for all fields, configure emit_json_tags or emit_db_tags instead.

• unsigned:

  • If true, sqlc will apply this override when a numeric column is unsigned. Note that this only applies to db_type overrides and has no effect on column overrides. Defaults to false.

• nullable:

  • If true, sqlc will apply this override when a column is nullable. Otherwise sqlc will apply this override when a column is non-nullable. Note that this only applies to db_type overrides and has no effect on column overrides. Defaults to false.

Tip

A single db_type override configuration applies to either nullable or non-nullable columns, but not both. If you want the same Go type to override regardless of nullability, you’ll need to configure two overrides: one with nullable: true and one without.

Note

When generating code, column override configurations take precedence over db_type configurations.

The go_type map

Some overrides may require more detailed configuration. If necessary, go_type can be a map with the following keys:

• import:

  • The import path for the package where the type is defined.

• package:

  • The package name where the type is defined. This should only be necessary when your import path doesn’t end with the desired package name.

• type:

  • The type name itself, without any package prefix.

• pointer:

  • If true, generated code will use a pointer to the type rather than the type itself.

• slice:

  • If true, generated code will use a slice of the type rather than the type itself.

An example:

version: "2"
sql:
- schema: "postgresql/schema.sql"
  queries: "postgresql/query.sql"
  engine: "postgresql"
  gen:
    go:
      package: "authors"
      out: "db"
      sql_package: "pgx/v5"
      overrides:
        - db_type: "uuid"
          go_type:
            import: "a/b/v2"
            package: "b"
            type: "MyType"
            pointer: true

Choosing the right db_type value

Overrides match with an exact string comparison against the type name sqlc infers for a column. That name is schema.name when the catalog includes a schema, otherwise just name (see sdk.DataType).

This is why the same logical type can appear under several aliases, and why a value that works in SQL (timestamptz) sometimes needs a catalog-qualified form in config (pg_catalog.timestamptz).

How to discover the type sqlc sees

1. Prefer a column override when you only care about one field ? it bypasses db_type naming entirely (table.column, or schema.table.column).

2. For a database-wide override, generate once and inspect the Go field type, or check the switch cases in the engine type mappers linked below.

3. When an override seems ignored, try the pg_catalog.-prefixed form (Postgres) or the lowercase SQL type name (MySQL/SQLite). Configure both nullable and non-nullable overrides if needed.

PostgreSQL

Common db_type strings (all aliases in a row match the same mapper branch). Prefer the bold form when more than one is listed ? that is usually what the catalog emits:

SQL / concept	Accepted db_type values
smallint	pg_catalog.int2, smallint, int2
integer	pg_catalog.int4, integer, int, int4
bigint	pg_catalog.int8, bigint, int8
smallserial	pg_catalog.serial2, smallserial, serial2
serial	pg_catalog.serial4, serial, serial4
bigserial	pg_catalog.serial8, bigserial, serial8
real	pg_catalog.float4, real, float4
double precision	pg_catalog.float8, float, double precision, float8
numeric / decimal	pg_catalog.numeric, numeric, money
boolean	pg_catalog.bool, boolean, bool
text / varchar / char	text, pg_catalog.varchar, pg_catalog.bpchar, string, citext, name
bytea	pg_catalog.bytea, bytea, blob
date	date
time	pg_catalog.time
timetz	pg_catalog.timetz
timestamp	pg_catalog.timestamp, timestamp
timestamptz	pg_catalog.timestamptz, timestamptz
interval	pg_catalog.interval, interval
uuid	uuid
json	pg_catalog.json, json
jsonb	pg_catalog.jsonb, jsonb
inet / cidr	inet, cidr
macaddr	macaddr, macaddr8
ranges	int4range, int8range, numrange, tsrange, tstzrange, daterange (and *multirange variants)
geometric	point, line, lseg, box, path, polygon, circle
other	bit, varbit, pg_catalog.bit, pg_catalog.varbit, hstore, ltree, vector, void, any

Full branch list: postgresql_type.go.

MySQL

SQL / concept	Accepted db_type values
string	varchar, text, char, tinytext, mediumtext, longtext
tinyint	tinyint (often used for booleans)
smallint	smallint
int	int, integer, mediumint
bigint	bigint, bigint unsigned, bigint signed
year	year
binary	blob, binary, varbinary, tinyblob, mediumblob, longblob
floating	double, double precision, real, float
decimal	decimal, dec, fixed
enum	enum
date/time	date, timestamp, datetime, time
boolean	boolean, bool
json	json
other	any

Use unsigned: true on the override when matching unsigned numeric columns. Full branch list: mysql_type.go.

SQLite

SQL / concept	Accepted db_type values
integer	int, integer, tinyint, smallint, mediumint, bigint, unsignedbigint, int2, int8
blob	blob
real	real, double, doubleprecision, float
boolean	boolean, bool
date/time	date, datetime, timestamp
json	json, jsonb
other	any

Full branch list: sqlite_type.go.

Tips when an override does not apply

• Postgres timestamps: try pg_catalog.timestamptz / pg_catalog.timestamp, not only the short name.

• Nullability: a non-nullable override never applies to a nullable column (and the reverse). Duplicate the entry with nullable: true.

• Arrays / slices: element db_type still uses the base type name; see Datatypes.

• Expressions / functions: the inferred type may differ from the underlying column (for example aggregates). Prefer a column override or cast in SQL when that happens.

Global overrides

To override types in all packages that sqlc generates, add an override configuration to the top-level overrides section of your sqlc config:

version: "2"
overrides:
  go:
    overrides:
      - db_type: "pg_catalog.timestamptz"
        nullable: true
        engine: "postgresql"
        go_type:
          import: "gopkg.in/guregu/null.v4"
          package: "null"
          type: "Time"
sql:
- schema: "service1/schema.sql"
  queries: "service1/query.sql"
  engine: "postgresql"
  gen:
    go: 
      package: "service1"
      out: "service1"
- schema: "service2/schema.sql"
  queries: "service2/query.sql"
  engine: "postgresql"
  gen:
    go:
      package: "service2"
      out: "service2"

Using this configuration, whenever there is a nullable timestamp with time zone column in a Postgres table, sqlc will generate Go code using null.Time.

Note that the mapping for global type overrides has a field called engine that is absent in per-package type overrides. This field is only used when there are multiple sql sections using different engines. If you’re only generating code for a single database engine you can omit it.

Version 1 configuration

If you are using the older version 1 of the sqlc configuration format, override configurations themselves are unchanged but are nested differently.

Per-package configurations are nested under the overrides key within an item in the packages list:

version: "1"
packages:
  - name: "db"
    path: "internal/db"
    queries: "./sql/query/"
    schema: "./sql/schema/"
    engine: "postgresql"
    overrides: [...]

And global configurations are nested under the top-level overrides key:

version: "1"
packages: [...]
overrides:
  - db_type: "uuid"
    go_type: "github.com/gofrs/uuid.UUID"