kyleconroy/sqlc

Generate type-safe code from SQL

sqlc: A SQL Compiler

sqlc generates type-safe code from SQL. Here's how it works:

1. You write queries in SQL.
2. You run sqlc to generate code with type-safe interfaces to those queries.
3. You write application code that calls the generated code.

Check out an interactive example to see it in action, and the introductory blog post for the motivation behind sqlc.

Overview

• Documentation
• Installation
• Playground
• Website
• Downloads
• Community

Supported languages

• sqlc-gen-go
• sqlc-gen-kotlin
• sqlc-gen-python
• sqlc-gen-typescript

Additional languages can be added via plugins.

Acknowledgments

sqlc was inspired by PugSQL and HugSQL.

sqlc's Issues

NULLIF returns bool instead of correct type

Version

1.14.0

What happened?

Actually, NULLIF also smells bad. When using it in a query, the Go struct is generated with a field with a bool type, instead of a NullInt64 type.

Database schema

CREATE TABLE author (
  id bigserial NOT NULL
);

SQL queries

-- name: GetRestrictedId :one
SELECT
  NULLIF(id, $1) restricted_id
FROM
  author;

Configuration

{
  "version": "1",
  "packages": [
    {
      "engine": "postgresql",
      "path": "go",
      "name": "querytest",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/321eb83b945edfab5a41d8c91feb596bcab9860ce1780639d1f9863207a20644

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

Originally posted by @euller88 in #92 (comment)

Generated Params JSON tags not in Snake Case

When the Params are Generated by sqlc, the tags are in camel case rather than snake case (as in the models.go when we have the emit_json_tags set to true.

Here is the sqlc.json:

{
  "packages": [
    {
      "name": "sqlc",
      "emit_json_tags": true,
      "emit_prepared_queries": false,
      "path": "./internal/sqlc",
      "queries": "./dbconfig/query/",
      "schema": "./dbconfig/migrations"
    }
  ],
  "overrides": null,
  "rename": null
}

The Query:

-- name: CreateAgreement :one
INSERT INTO insys_agreements.agreements
    (id, user_id, user_email, first_name, last_name, location_id, created_at, agreement_content_id)
VALUES ($1, $2, $3, $4, $5, $6,now(), $7)
RETURNING id, user_id, user_email, first_name, last_name, location_id, created_at, agreement_content_id;

The Params generated by sqlc:

type CreateAgreementParams struct {
    ID                 uuid.UUID      `json:"id"`
    UserID             uuid.UUID      `json:"userID"`
    UserEmail          sql.NullString `json:"userEmail"`
    FirstName          sql.NullString `json:"firstName"`
    LastName           sql.NullString `json:"lastName"`
    LocationID         uuid.UUID      `json:"locationID"`
    AgreementContentID uuid.UUID      `json:"agreementContentID"`
}

And the Model generated by sqlc:

type InsysAgreementsAgreement struct {
    ID                 uuid.UUID      `json:"id"`
    UserID             uuid.UUID      `json:"user_id"`
    UserEmail          sql.NullString `json:"user_email"`
    FirstName          sql.NullString `json:"first_name"`
    LastName           sql.NullString `json:"last_name"`
    LocationID         uuid.UUID      `json:"location_id"`
    CreatedAt          time.Time      `json:"created_at"`
    AgreementContentID uuid.UUID      `json:"agreement_content_id"`
}

I would expect the json tags on the Model to match the Params, but they aren't.

Generate multiple packages in one pass

{
  "packages": [{
    "name": "db",
    "out": "internal/db",
    "queries": "./sql/query/",
    "schema": "./sql/schema/"
  }],
  "emit_json_tags": true,
  "emit_prepared_queries": false
}

Support alternative PostgreSQL drivers

Right now that means pgx. Not sure what other PostgreSQL drivers are out there.

Generate better field names for stored procedures

In the following example Dollar2 should be ProjectIDs or CatProjectIDs

const deleteToken = `-- name: DeleteToken :exec
DELETE FROM chef_authn_tokens cat
WHERE cat.id=$1
AND projects_match(cat.project_ids, $2::TEXT[])
`

type DeleteTokenParams struct {
        ID      string
        Dollar2 []string
}

Replaces #7

Customize parameter names

Certain queries end up with generic (read: terrible) names in parameter structs. For example

CREATE TABLE foo (bar text not null);

-- name: ListBar :many
SELECT bar FROM foo WHERE $1:bool;

There is no way to generate a good name for lone parameter in this query. Maybe another special comment? -- param could work.

-- name: ListBar :many
-- param: 1 IsTrue
SELECT bar FROM foo WHERE $1:bool;

Omitting `:one` or `:many` causes a panic

Validation of the magic comment is not quite right. If you omit the directive of how the query should be handled, sqlc panics:

panic: runtime error: index out of range

goroutine 1 [running]:
github.com/kyleconroy/sqlc/internal/dinosql.parseMetadata(0xc0004c02c0, 0x52, 0x900b60, 0xc00000e760, 0x0, 0x52, 0xc0004c02c0, 0x52)
	/home/runner/work/sqlc/sqlc/internal/dinosql/parser.go:294 +0x1e7
github.com/kyleconroy/sqlc/internal/dinosql.parseQuery(0xc00017a030, 0x900b60, 0xc0004cffc0, 0xc0004c02c0, 0x145, 0x0, 0x0, 0x0)
	/home/runner/work/sqlc/sqlc/internal/dinosql/parser.go:322 +0x2cb
github.com/kyleconroy/sqlc/internal/dinosql.ParseQueries(0xc00017a030, 0xc0000deea0, 0x1, 0x4, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
	/home/runner/work/sqlc/sqlc/internal/dinosql/parser.go:210 +0x6e6
github.com/kyleconroy/sqlc/internal/cmd.glob..func4(0xe0db40, 0xe3e5a8, 0x0, 0x0)
	/home/runner/work/sqlc/sqlc/internal/cmd/cmd.go:142 +0x9b7
github.com/spf13/cobra.(*Command).execute(0xe0db40, 0xe3e5a8, 0x0, 0x0, 0xe0db40, 0xe3e5a8)
	/home/runner/go/pkg/mod/github.com/spf13/[email protected]/command.go:830 +0x2ae
github.com/spf13/cobra.(*Command).ExecuteC(0xc000160280, 0xc0000bbf20, 0x1, 0x1)
	/home/runner/go/pkg/mod/github.com/spf13/[email protected]/command.go:914 +0x2fc
github.com/spf13/cobra.(*Command).Execute(...)
	/home/runner/go/pkg/mod/github.com/spf13/[email protected]/command.go:864
github.com/kyleconroy/sqlc/internal/cmd.Do(0xc0000a0010, 0x1, 0x1, 0x8fac60, 0xc00009a000, 0x8fac80, 0xc00009a008, 0x8fac80, 0xc00009a010, 0xc000080058)
	/home/runner/work/sqlc/sqlc/internal/cmd/cmd.go:33 +0x24a
main.main()
	/home/runner/work/sqlc/sqlc/cmd/sqlc/main.go:10 +0xab

Test case:

-- name: ListFlags
SELECT * FROM flag;

Disallow configuration file from having unknown properties

I accidentally used the top-level key "package" instead of "packages" and sqlc happily printed out nothing and took no action. It should fail on unrecognized options in the schema file with an appropriate error message.

JOIN support

The following query should work

CREATE TABLE foo (
  id SERIAL PRIMARY KEY
);

CREATE TABLE bar (
  id SERIAL PRIMARY KEY,
  foo SERIAL references foo(id)
);

SELECT *
FROM foo, bar
WHERE bar.foo = foo.id;

SELECT *
FROM foo
JOIN bar ON bar.foo = foo.id;

SELECT *
FROM foo f
JOIN bar b ON b.foo = f.id;

consider stripping comments

I have a long comment above a query that's explaining what it's doing. Unfortunately this gets included in the Queries file and sent to Postgres every time, which seems unnecessary. Stripping comments might be a good idea, or permitting comments to be included if they have a metadata tag.

Generate correct integer types

The Go types emitted for PostgreSQL columns with integer types are current incorrect. We should change them to the following:

• smallint -> int16
• integer -> int32
• bigint -> int64

https://www.postgresql.org/docs/current/datatype-numeric.html
https://www.postgresql.org/docs/current/datatype.html#DATATYPE-TABLE
https://golang.org/pkg/builtin/#int

Support boolean operations

create table rooms (
    building_id varchar not null
);

-- name: HasRooms :one  
SELECT count(*) > 0
FROM rooms
WHERE building_id = $1;

Return type is incorrect for identical tables

CREATE TABLE bar (id text not null);
CREATE TABLE foo (id text not null);

-- name: ListBar :many
SELECT * FROM bar;

This will generate the following code:

func (q *Queries) ListBar(ctx context.Context) ([]Foo, error) 

Scanning fails for bytea into []byte

When scanning and from bytea into []byte, the generated code is unable to properly read or write the data. This is due to the fact that the generated code for slices in general(wrapping the slices in the pq.Array() function), does not work for bytea when reading, and probably (not tested) is not writing the correct data.
I already know how to solve this, and I'm working on a PR.

String arrays

create table rooms (
            id varchar not null,
            members varchar[] not null default '{}'
);

-- name: GetMembersByRoom :one  
SELECT members
FROM rooms
WHERE id = $1;

Support alias names

DELETE FROM cloud_builds b WHERE b.appsid = $1;

Computed columns do not generate a field

If I have a query like:

-- name: ListUsageQuotas :many
SELECT *, bytes_downloaded / 1024 as mb_downloaded FROM usages;

I expect to have a MbDownloaded field on the ListUsagesTestRow struct. But there is no extra field there.

This seems related to #103, which helped me discover a workaround by wrapping it in parens and adding an explicit type:

-- name: ListUsagesTest :many
SELECT *,(bytes_downloaded / 1024)::float AS mb_downloaded FROM usages;

The use of the "IN" keyword, A.K.A. filtering a column using multiple values

Does this package can generate code that supports the "IN" operator?

Like in this example, in which this definition:

-- name: ListAuthorsByIDs :many
SELECT * FROM authors
WHERE id IN (/*mysterious syntax*/);

Would generate this code:

func (q *Queries) ListAuthorsByIDs(ctx context.Context, ids ...int64) ([]Author, error) {
	rows, err := q.db.QueryContext(ctx, listAuthors, ...ids)
	 if err != nil {
		return nil, err
	}
	// rest of the function
}

Document the UUID switching stuff

You mentioned it was possible to use libraries besides google/uuid but I don't see any docs for it.

Query templates

A sqlc user asked how it would be possible to share a set of select expressions across queries. There isn't a way to do this in the SQL standard, so I came up with the following idea. A given query would be marked as a template. This query must be a certain shape, specifically a select from a single table with no additional clauses. You could then refer to that template from subsequent queries to that same table.

Here's an example using the setup from the README

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

-- template: named_author
SELECT id, lower(name) as fullname, coalese(bio, 'none') as bio_default, 1 as one
FROM authors;

-- name: GetAuthor :one
SELECT sqlc.named_author.* FROM authors
WHERE id = $1 LIMIT 1;

-- name: CreateAuthor :one
INSERT INTO authors (
  name, bio
) VALUES (
  $1, $2
)
RETURNING sqlc.named_author.*;

package db

import (
	"context"
	"database/sql"
)

type NamedAuthor struct {
	ID   int64
	Fullname string
	BioDefault  string
	One int64
}

const createAuthor = `-- name: CreateAuthor :one
INSERT INTO authors (
  name, bio
) VALUES (
  $1, $2
)
RETURNING id, lower(name) as fullname, coalese(bio, 'none') as bio_default, 1 as one
`

type CreateAuthorParams struct {
	Name string
	Bio  sql.NullString
}

func (q *Queries) CreateAuthor(ctx context.Context, arg CreateAuthorParams) (NamedAuthor, error) {
	row := q.db.QueryRowContext(ctx, createAuthor, arg.Name, arg.Bio)
	var i NamedAuthor
	err := row.Scan(&i.ID, &i.Fullname, &i.BioDefault, &i.One)
	return i, err
}

const getAuthor = `-- name: GetAuthor :one
SELECT id, lower(name) as fullname, coalese(bio, 'none') as bio_default, 1 as one
FROM authors
WHERE id = $1 LIMIT 1
`

func (q *Queries) GetAuthor(ctx context.Context, id int64) (NamedAuthor, error) {
	row := q.db.QueryRowContext(ctx, getAuthor, id)
	var i NamedAuthor
	err := row.Scan(&i.ID, &i.Fullname, &i.BioDefault, &i.One)
	return i, err
}

Prepare with no queries does not compile

If I don't have any queries, Prepare generates this:

func Prepare(ctx context.Context, db dbtx) (*Queries, error) {
	q := Queries{db: db}
	var err error
	return &q, nil
}

I understand this is invalid. But I'm just trying to test this out and it's not a great experience.

Cleaner methods with prepared queries

A method using prepared queries looks like this today.

const deleteVenue = `-- name: DeleteVenue :exec
DELETE FROM venue
WHERE slug = $1 AND slug = $1
`

func (q *Queries) DeleteVenue(ctx context.Context, slug string) error {
    var err error
    switch {
    case q.deleteVenue != nil && q.tx != nil:
        _, err = q.tx.StmtContext(ctx, q.deleteVenue).ExecContext(ctx, slug)
    case q.deleteVenue != nil:
        _, err = q.deleteVenue.ExecContext(ctx, slug)
    default:
        _, err = q.db.ExecContext(ctx, deleteVenue, slug)
    }
    return err
}

The arguments to ExecContext are repeated three times. Instead, we should have helper methods to make the body of methods shorter.

const deleteVenue = `-- name: DeleteVenue :exec
DELETE FROM venue
WHERE slug = $1 AND slug = $1
`

func (q *Queries) DeleteVenue(ctx context.Context, slug string) error {
    _, err := q.exec(ctx, q.deleteVenueStmt, deleteVenue, slug)
    return err
}

LOWER(x) = $n does not generate query parameter

If I have a table like the authors table in the readme, and I have a query like

-- name: LowerAuthor :one 
SELECT * FROM authors WHERE bio = $1 AND LOWER(name) = $2

I expect this to generate LowerAuthor(ctx context.Context, params LowerAuthorParams) with two parameters, Bio and Name. Instead, I get just one parameter, Bio (which gets placed in the function string).

dynamic limit, ordering

how to deal with dynamic limit and ordering ? in my memories those arguments can t be prepared; thus a regular variable, like a condition statement uses, will trigger error.

delete generated Go files

I dropped a table and removed the corresponding db/query/<table>.go file. However, src/internal/db/<table>.sql.go file was not deleted. I expected it to be deleted.

Update query builder

A common-ish pattern is to allow three or four fields to be updated, and then issue an UPDATE query that only modifies the ones the user actually wants to set. Generally people do this by manually building a string - keeping track of $1, $2, etc. and then executing it.

I'm wondering if sqlc could help automate this process or at least make it less error prone.

make it less pg dependant

hi,

while i really like the project, it is tied to pgsql engine.

I would like the package to be as agnostic as possible with very little ties to the underlying engine as much as possible.

in my current understanding of the package it does parses the sql create, alter statements to performs some type coalescence.
Those types metadatas are being use to produce compatible func signatures, for as much as i understood so far.
As a consequence the package uses a full blown parser of pgsql statements.

Have you considered to extract those type metadata from the golang struct ?
Instead of building the catalog using the create/alter statements, a catalog can be built from the queries statements, using name resolution their types can be retrieved from the golang metadata obtained earlier.

the sql query parser could be as agnostic as possible, trying to understand sufficiently to extract (table|alias).columns names, but blind enough to not bother if it encounters a function name (or anything similar specific to a db engine) it does not know about.

the code generator has some ties to pg too, but it does not look like a big rethink.

CASE expressions aren't being parsed/read

Queries using conditional expressions like NULLIF and COALESCE generate proper Go code. Meanwhile, the CASE conditional expression does not. The generated go code doesn't generate an additional field in the result struct, making the Scan fail each time is called. I will try to investigate this issue later on the week and try to provide a fix. The following query can be used to reproduce the observed behavior:

SELECT
  id,
  CASE 
    WHEN id = $1 THEN true
    ELSE false
  END is_one
FROM
  author;

Should sqlc generate methods for constraint failures?

Say we have the following table for authors

CREATE TABLE authors (
  id serial primary key,
  name text not null unique
);

-- name: CreateAuthor :one
INSERT INTO authors (name) VALUES ($1) RETURNING *;

sqlc will generate the following method

func (q *Queries) CreateAuthor(context.Context, name string) (Author, error) {
    // ...
}

Attempting to create an author with an existing name will return a pq.Error with code 23505 and constraint authors_name_key. Instead of having to hard-code this constraint name, what if we generated code with all the constraints as constants?

package constraints

type UniqueConstraint string
const (
  AuthorsNameKey UniqueConstraint = "authors_name_key"
)

func IsUniqueConstraint(err error, uc UniqueConstraint) bool {
    if pqerr, ok := err.(*pq.Error); ok {
        return pqerr.Code == "23505" && pqerr.Constraint = string(uc)
    }
    return false
}

Add support for `pq` and `sqlx`

I tbelieve pq and sqlx are very commonly used. Allowing to choose the dialect/library is a great addition and can keep the implementation flexible/doesn’t contradict its generated code use.

add Close method

At the very least we can Close all of the prepared Stmt's, if that is something we are doing.

Infer :one, :many, :exec from query

User php_questions on Reddit asked why the query notations were required.

Why not? If a query contains "limit 1" then you know it will only return a single row.

Why not just return a slice of results in almost all other queries? you can still have 0 results in that slice or 1 result in this slice.

There are some cases where we can infer it directory from the query.

• If a SELECT includes a LIMIT 1, choose :one:
  • Otherwise, SELECT will always return a slice
• If a CREATE / UPDATE / INSERT has no RETURING, return a sql.Result. This is a mix of :exec and :execrow.

A corner case is UPDATE with RETURNING, which does not support LIMIT 1.

json.RawMessage does not support NULL

sql: Scan error on column index 5, name "xxx": unsupported Scan, storing driver.Value type <nil> into type *json.RawMessage

Not recognizing some types

The tool is unable to properly generate some structs, with the columns of the tables have the following types:

• character of any size, should map to string or array of rune (string is my favorite option);
• real, should map to float32;
• double precision, should map to float64
• numeric and decimal are tricky, so I don't think they should be mapped to any kind of float.

I will try to send a PR this weekend. In the meantime, I will investigate some other types that may be missed by the tool.

Unhelpful error when specifying :one but not including a RETURNING clause

-- name: DeleteFoo :one
DELETE FROM foo
WHERE id = $1 and type = $2;

Observed:

sqlc generate
# package query
error generating code: template: table:33:77: executing "table" at <.Ret.Type>: error calling Type: no type for GoQueryValue:

Expected:
"Query 'DeleteFoo' specifies parameter ":one" without containing a RETURNING clause"

Using schemas doesn't generate structs in the models.go file

I'm gonna investigate this issue later, but this repo shows exactly the what the title says. I will try to make a PR to solve it, but I can't guarantee when.

Quality of life improvements

• sqlc.json: "name" in the config should be optional. if unspecified should just take the package basename
• sqlc.json: "schema" should be able to be a file or directory
• automatically create the output directory

a *Queries, with Close() functionality

In tests, I would like to open a handle to a database, make some queries, and then close it. Right now this requires holding handles to both the *sql.DB and the *db.Queries structs, and adding two layers of error checks. This is cumbersome in exactly the place where I want to use this - in tests.

Further, it would be nice to enforce that callers are not using the raw db handle.

Could we add .BeginTx() and Close() error to the *Queries struct?

Or introduce a concept of a ManagedDB that has both of those functions, plus either a handle Queries or exports its functions?

readme: broken links

listing broken links i encountered

https://github.com/kyleconroy/sqlc/blob/master/examples/slice_arg.md

https://github.com/kyleconroy/sqlc/blob/master/internal/sqlc/testdata/ondeck

that s it

Allow passing a single filename for "query" or "schema"

The sqlc json file requires that the schema and queries options must be directories, but in some cases I'd like to be specific and pass a particular file, e.g.:

			"schema": "./postgres/flags.sql"

A comma in the query name causes unhelpful generation failure

I occasionally forget the comment syntax and put a comma after the query name which results in unexpected and unintuitive errors:

Input:

-- name: CreateFoo, :one
INSERT INTO ...

Observed Error:
error generating code: source error: 92:33: expected 'IDENT', found '=' (and 3 more errors)

This is because the generated code now has values like this: q.createFoo,Stmt

Expected: sqlc validates and gives me a helpful error message that the parameter to name: must be a valid Go identifier

Generate multiple Go files

In a larger code base, stuffing all generated code into a single Go file will not scale. Instead, split up code across a few files.

• queries.go: Contains the Queries struct and non-query methods
• records.go: Contains generated types shared across other files
• {name}.db.go: Contains query methods from the {name}.sql file

Change the name

After a few months, I'm not happy with the name dinosql. It sounds too much like a toy. So far, the best new name I've come up with is sqlc.

Use new types from Go 1.13

The release of Go 1.13 includes two new types in the database/sql package: NullTime and NullInt32. We should use those types instead of reaching for lib/pq.

Not emiting JSON tags

I think this is probably due to misuse, but none of the structs were generated with JSON tags. Right now I can't investigate the cause, but I'm leaving this issue as a reminder, if I found something unusual, I will try sending a PR. BTW, this is my config file:

{
  "packages": [
    {
      "name": "models",
      "path": "./models",
      "schema": "./schema",
      "queries": "./query",
      "emit_json_tags": true,
      "emit_prepared_queries": false
    }
  ]
}

If the need arises, I will also post some of my script files.

overrides not behaving correctly

I expect this stanza:

{
  "packages": [
    {
      "name": "db",
      "emit_json_tags": false,
      "emit_prepared_queries": true,
      "path": "src/internal/db",
      "queries": "./db/query/",
      "schema": "./db/migrations/"
    }
  ],
  "overrides": [{
      "go_type": "uuid.UUID",
      "package": "github.com/gofrs/uuid",
      "postgres_type": "uuid"
  }]
}

to override the uuid settings in the models.go file, but instead I end up with two UUID imports - the Google one and the other one.

Wish: add //go:generate comments to generated files

...for compatibility with go generate which will make sqlc easier to integrate into existing Go builds.

sqlc looks really really good - thank you for this project!

Migrations

There's a CREATE TABLE statement at the top of a sqlc file for a given table. How do you suggest we handle migrations? Should we pass the sqlc file to Postgres, or store the sql migration commands in a separate file(s), and keep the two in sync?

Fail to generate code for tables in SCHEMAs other than public

This code:

CREATE SCHEMA sc01;

CREATE TABLE sc01.cidade (
  id serial NOT NULL,
  nome character varying(250) NOT NULL,
  uf character varying(2) NOT NULL,
  CONSTRAINT pk_cidade PRIMARY KEY (id)
);

-- name: ListCidade :many
SELECT * FROM sc01.cidade;

-- name: GetCidade :one
SELECT * FROM sc01.cidade WHERE id = $1;

-- name: CreateCidade :one
INSERT INTO sc01.cidade (nome, uf) VALUES ($1, $2) RETURNING id;

-- name: UpdateCidade :exec
UPDATE sc01.cidade SET nome = $2, uf = $3 WHERE id = $1;

-- name: DeleteCidade :exec
DELETE FROM sc01.cidade WHERE id = $1;

Will fail with this error:

query.sql:5:33: column "id" does not exist
query.sql:8:26: column "nome" does not exist
query.sql:11:49: column "id" does not exist
query.sql:14:31: column "id" does not exist

I'm not sure what is the cause, but I will try to investigate more. Not sure on how much time it will take.

Error on INSERT parameter mismatch

The following query should fail to compile

INSERT INTO foo (bar, baz) VALUES ($1)