sqlc-dev / sqlc Goto Github PK
View Code? Open in Web Editor NEWGenerate type-safe code from SQL
Home Page: https://sqlc.dev
License: MIT License
Generate type-safe code from SQL
Home Page: https://sqlc.dev
License: MIT License
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"
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.
The tool is unable to properly generate some structs, with the columns of the tables have the following types:
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.
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.
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.
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
}
At the very least we can Close all of the prepared Stmt's, if that is something we are doing.
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.
The Go types emitted for PostgreSQL columns with integer types are current incorrect. We should change them to the following:
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
{
"packages": [{
"name": "db",
"out": "internal/db",
"queries": "./sql/query/",
"schema": "./sql/schema/"
}],
"emit_json_tags": true,
"emit_prepared_queries": false
}
DELETE FROM cloud_builds b WHERE b.appsid = $1;
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
.
1.14.0
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.
CREATE TABLE author (
id bigserial NOT NULL
);
-- name: GetRestrictedId :one
SELECT
NULLIF(id, $1) restricted_id
FROM
author;
{
"version": "1",
"packages": [
{
"engine": "postgresql",
"path": "go",
"name": "querytest",
"schema": "query.sql",
"queries": "query.sql"
}
]
}
https://play.sqlc.dev/p/321eb83b945edfab5a41d8c91feb596bcab9860ce1780639d1f9863207a20644
macOS
PostgreSQL
Go
Originally posted by @euller88 in #92 (comment)
...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!
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).
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 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
.
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
}
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)
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
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.
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.
create table rooms (
id varchar not null,
members varchar[] not null default '{}'
);
-- name: GetMembersByRoom :one
SELECT members
FROM rooms
WHERE id = $1;
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?
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;
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;
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
}
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.
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.
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;
The following query should fail to compile
INSERT INTO foo (bar, baz) VALUES ($1)
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 methodsrecords.go
: Contains generated types shared across other files{name}.db.go
: Contains query methods from the {name}.sql
filesql: Scan error on column index 5, name "xxx": unsupported Scan, storing driver.Value type <nil> into type *json.RawMessage
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.
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.
-- 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"
create table rooms (
building_id varchar not null
);
-- name: HasRooms :one
SELECT count(*) > 0
FROM rooms
WHERE building_id = $1;
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.
You mentioned it was possible to use libraries besides google/uuid but I don't see any docs for it.
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.
:one:
sql.Result
. This is a mix of :exec
and :execrow
.A corner case is UPDATE with RETURNING, which does not support LIMIT 1
.
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.
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
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
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
}
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.
Right now that means pgx. Not sure what other PostgreSQL drivers are out there.
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.
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;
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?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.