datatypes.md

Datatypes

Arrays

PostgreSQL arrays are materialized as Go slices. Currently, the pgx/v5 sql package only supports multidimensional arrays.

CREATE TABLE places (
  name text   not null,
  tags text[]
);

package db

type Place struct {
	Name string
	Tags []string
}

Dates and Time

All PostgreSQL time and date types are returned as time.Time structs. For null time or date values, the NullTime type from database/sql is used. The pgx/v5 sql package uses the appropriate pgx types.

CREATE TABLE authors (
  id         SERIAL    PRIMARY KEY,
  created_at timestamp NOT NULL DEFAULT NOW(),
  updated_at timestamp
);

package db

import (
	"database/sql"
	"time"
)

type Author struct {
	ID        int
	CreatedAt time.Time
	UpdatedAt sql.NullTime
}

Enums

PostgreSQL enums are mapped to an aliased string type.

CREATE TYPE status AS ENUM (
  'open',
  'closed'
);

CREATE TABLE stores (
  name   text    PRIMARY KEY,
  status status  NOT NULL
);

package db

type Status string

const (
	StatusOpen   Status = "open"
	StatusClosed Status = "closed"
)

type Store struct {
	Name   string
	Status Status
}

Null

For structs, null values are represented using the appropriate type from the database/sql or pgx package.

CREATE TABLE authors (
  id   SERIAL PRIMARY KEY,
  name text   NOT NULL,
  bio  text
);

package db

import (
	"database/sql"
)

type Author struct {
	ID   int
	Name string
	Bio  sql.NullString
}

UUIDs

The Go standard library does not come with a uuid package. For UUID support, sqlc uses the excellent github.com/google/uuid package. The pgx/v5 sql package uses pgtype.UUID.

CREATE TABLE records (
  id   uuid PRIMARY KEY
);

package db

import (
	"github.com/google/uuid"
)

type Author struct {
	ID uuid.UUID
}

For MySQL, there is no native uuid data type. When using UUID_TO_BIN to store a UUID(), the underlying field type is BINARY(16) which by default sqlc would interpret this to sql.NullString. To have sqlc automatically convert these fields to a uuid.UUID type, use an overide on the column storing the uuid.

{
  "overrides": [
    {
      "column": "*.uuid",
      "go_type": "github.com/google/uuid.UUID"
    }
  ]
}

JSON

By default, sqlc will generate the []byte, pgtype.JSON or json.RawMessage for JSON column type. But if you use the pgx/v5 sql package then you can specify a some struct instead of default type. The pgx implementation will marshall/unmarshall the struct automatically.

package dto

type BookData struct {
	Genres    []string `json:"genres"`
	Title     string   `json:"title"`
	Published bool     `json:"published"`
}

CREATE TABLE books (
  data jsonb
);

{
  "overrides": [
    {
      "column": "books.data",
      "go_type": {
        "import":"example/db",
        "package": "dto",
        "type":"BookData"
      }
    }
  ]
}

package db

import (
	"example.com/db/dto"
)

type Book struct {
    Data *dto.BookData
}