Introducing sqlc - Compile SQL queries to type-safe Go
source link: https://conroy.org/introducing-sqlc
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Ask any Go developer and they will tell you how painful it is to write programs that talk to SQL databases. Go feels like it isn't even half as productive compared to working with toolkits like SQLAlchemy , Diesel , Hibernate or ActiveRecord . The existing tools in the Go ecosystem force application developers to hand-write mapping functions or litter their code with unsafe empty interfaces.
Introducing sqlc
I've been feeling this pain for years, so six months ago I started developing a solution. Today, I'm excited to announce the result of that work. sqlc is a new tool that makes working with SQL in Go a joy.
It dramatically improves the developer experience of working with relational databases without sacrificing type-safety or runtime performance. It does not use struct tags, hand-written mapper functions, unnecessary reflection or add any new dependencies to your code. In fact, it even provides correctness and safety guarantees that no other SQL toolkit in the Go ecosystem can match.
sqlc accomplishes all of this by taking a fundamentally different approach: compiling SQL into fully type-safe, idiomatic Go code. sqlc can take this SQL:
CREATE TABLE authors ( id BIGSERIAL PRIMARY KEY, name text NOT NULL, bio text ); -- name: GetAuthor :one SELECT * FROM authors WHERE id = $1 LIMIT 1;
and generate the following Go code automatically.
package db import ( "context" "database/sql" ) type Author struct { ID int64 Name string Bio sql.NullString } const getAuthor = `-- name: GetAuthor :one SELECT id, name, bio FROM authors WHERE id = $1 LIMIT 1 ` type Queries struct { db *sql.DB } func (q *Queries) GetAuthor(ctx context.Context, id int64) (Author, error) { row := q.db.QueryRowContext(ctx, getAuthor, id) var i Author err := row.Scan(&i.ID, &i.Name, &i.Bio) return i, err }
For years, software engineers have been generating SQL queries from annotated objects in programming languages. SQL is already a structured, typed language; we should be generating correct, type-safe code in every programming language from the source of truth: SQL itself.
Problems using SQL from Go, today
Working with relational databases in any programming language is challenging . Go in particular is especially painful; even with the existing packages and tools, writing and maintaining queries in a Go application is a chore.
Low-level standard library
Go’s standard library offers the
database/sql
package for interacting
with relational databases, but most applications outgrow the database/sql
package and reach for a full-fledged ORM or a higher-level library abstraction.
Why?
Using the database/sql
package is straightforward. Write a query, pass in
the necessary arguments, and scan the results back into fields. Programmers are
responsible for explicitly specifying the mapping between a SQL field and its
value in the program for both inputs and outputs.
Once an application has more than a few queries, maintaining these mappings is cumbersome and severely impacts programmer productivity. Worse, it's trivial to make mistakes that are not caught until runtime. If you switch the order of parameters in your query, the parameter mapping must be updated. If a column is added to a table, all queries must be updated to return that value. If the type in SQL does not match the type in your code, failures will not occur until query execution time.
Higher-level libraries
The Go community has produced higher-level libraries ( github.com/jmoiron/sqlx ) and ORMs ( github.com/jinzhu/gorm ) to solve these issues. However, higher-level libraries still require manual mapping via query text and struct tags that, if incorrect, will only fail at runtime.
ORMs do away with much of the manual mapping but require you to write your queries now in a pseudo-sql DSL that basically reinvents SQL in a set of Go function calls.
Invalid SQL
With either approach, it is still trivial to make errors that the compiler can't check. As a Go programmer, have you ever:
- Mixed up the order of the arguments when invoking the query so they didn't match up with the SQL text
- Updated the name of a column in one query both not another
- Mistyped the name of a column in a query
- Changed the number of arguments in a query but forgot to pass the additional values
- Changed the type of a column but forgot to change the type in your code?
All of these errors are impossible with sqlc. Wait, what? How?
How to use sqlc in 3 steps
sqlc sqlc
Seriously, it's that easy. You don't have to write any boilerplate SQL querying code ever again. sqlc generates fully-type-safe idiomatic Go code from your queries. sqlc also prevents entire classes of common errors in SQL code.
During code generation, sqlc parses all of your queries and DDL statements
(e.g. CREATE TABLE
) so that it knows the names and types of every column in
your tables and every expression in your queries. If any of them do not match,
sqlc will fail to compile your queries
, catching would-be runtime errors
before they happen.
Likewise, the methods that sqlc generates for you have a strict arity and correct Go type definitions that match your columns. So if you change a query's arguments or a column's type but don't update your code, it will fail to compile.
A guided tour of sqlc
That all sounds great, but what does it look like in practice?
Download and install
To start, download the latest version of sqlc
and add it to your $PATH
. You’ll also want to have Go 1.13 installed on your system. Create a
new directory for the example project.
$ mkdir sqlc-example $ cd sqlc-example $ go mod init github.com/kyleconroy/sqlc-example
Configuration file
sqlc uses a configuration file
at the root of your project to store settings. Create sqlc.json
with the
following contents:
{ "version": "1", "packages": [{ "schema": "schema.sql", "queries": "query.sql", "name": "main", "path": "." }] }
sqlc will generate a Go package for each entry in the packages list. Each entry has four required properties:
-
schema
- Path to a SQL file that defines database tables (can also be a directory of SQL files)
-
queries
- Path to a SQL file with application queries (can also be a directory of SQL files)
-
name
-
The package name to use for the generated code. Defaults to
path
basename
-
The package name to use for the generated code. Defaults to
-
path
- Output directory for generated code
Write DDL
Let’s build an application for tracking authors. It’s a small application with
only a single table. Define the authors
table in schema.sql
.
-- schema.sql CREATE TABLE authors ( id BIGSERIAL PRIMARY KEY, name text NOT NULL, bio text );
Write queries
Your application needs a few queries to create, insert, update and delete author
records. Queries are annotated with a small comment that includes a Go
method name and the database/sql
function to use.
-- query.sql -- name: GetAuthor :one SELECT * FROM authors WHERE id = $1 LIMIT 1; -- name: ListAuthors :many SELECT * FROM authors ORDER BY name; -- name: CreateAuthor :one INSERT INTO authors ( name, bio ) VALUES ( $1, $2 ) RETURNING *; -- name: DeleteAuthor :exec DELETE FROM authors WHERE id = $1;
Generate code
With just the information stored in these two SQL files, sqlc can now generate
database access methods for your application. Make sure that you’re in the sqlc-example
directory and run the following command:
$ sqlc generate
This will generate three files: db.go
, models.go
, and query.sql.go
.
db.go
defines a shared interface for using a *sql.DB
or *sql.Tx
to
execute queries, as well as the Queries
struct which contains the database
access methods.
db.go
// db.go package main import ( "context" "database/sql" ) type dbtx interface { ExecContext(context.Context, string, ...interface{}) (sql.Result, error) PrepareContext(context.Context, string) (*sql.Stmt, error) QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error) QueryRowContext(context.Context, string, ...interface{}) *sql.Row } func New(db dbtx) *Queries { return &Queries{db: db} } type Queries struct { db dbtx } func (q *Queries) WithTx(tx *sql.Tx) *Queries { return &Queries{ db: tx, } }
models.go
models.go
contains the structs associated with the database tables. In this
example it contains a single struct, Author
.
// models.go package main import "database/sql" type Author struct { ID int64 Name string Bio sql.NullString }
query.sql.go
query.sql.go
contains the data access methods we defined in query.sql
. It’s
a bit verbose, but this is code you would have had to write yourself!
// query.sql.go package main import ( "context" "database/sql" ) const createAuthor = `-- name: CreateAuthor :one INSERT INTO authors ( name, bio ) VALUES ( $1, $2 ) RETURNING id, name, bio ` type CreateAuthorParams struct { Name string Bio sql.NullString } func (q *Queries) CreateAuthor(ctx context.Context, arg CreateAuthorParams) (Author, error) { row := q.db.QueryRowContext(ctx, createAuthor, arg.Name, arg.Bio) var i Author err := row.Scan(&i.ID, &i.Name, &i.Bio) return i, err } const deleteAuthor = `-- name: DeleteAuthor :exec DELETE FROM authors WHERE id = $1 ` func (q *Queries) DeleteAuthor(ctx context.Context, id int64) error { _, err := q.db.ExecContext(ctx, deleteAuthor, id) return err } const getAuthor = `-- name: GetAuthor :one SELECT id, name, bio FROM authors WHERE id = $1 LIMIT 1 ` func (q *Queries) GetAuthor(ctx context.Context, id int64) (Author, error) { row := q.db.QueryRowContext(ctx, getAuthor, id) var i Author err := row.Scan(&i.ID, &i.Name, &i.Bio) return i, err } const listAuthors = `-- name: ListAuthors :many SELECT id, name, bio FROM authors ORDER BY name ` func (q *Queries) ListAuthors(ctx context.Context) ([]Author, error) { rows, err := q.db.QueryContext(ctx, listAuthors) if err != nil { return nil, err } defer rows.Close() var items []Author for rows.Next() { var i Author if err := rows.Scan(&i.ID, &i.Name, &i.Bio); err != nil { return nil, err } items = append(items, i) } if err := rows.Close(); err != nil { return nil, err } if err := rows.Err(); err != nil { return nil, err } return items, nil }
Star expansion
Were you surprised to see SELECT *
/ RETURNING *
in query.sql
? sqlc
replaces *
references with the correct columns when generating code. Take a
second look at the createAuthor
, listAuthor
and getAuthor
SQL queries in
the example above.
Write your application code
It’s now easy to create, delete and fetch author
records. Paste the following
into main.go
. It should build ( go build
) without any errors.
package main import ( "context" "database/sql" "fmt" ) func run(ctx context.Context, db *sql.DB) error { q := &Queries{db: db} insertedAuthor, err := q.CreateAuthor(ctx, CreateAuthorParams{ Name: "Brian Kernighan", Bio: sql.NullString{ String: "Co-author of The C Programming Language", Valid: true, }, }) if err != nil { return err } authors, err := q.ListAuthors(ctx) if err != nil { return err } fmt.Println(authors) err = q.DeleteAuthor(ctx, insertedAuthor.ID) if err != nil { return err } return nil } func main() { // TODO: Open a connection to your PostgreSQL database run(context.Background(), nil) }
All the above code can be found on in the sqlc-example repository. A larger, more complicated example application can be found in the ondeck package in the sqlc repository.
Packed with power
Don’t let the previous example’s simplicity fool you. sqlc has support for complex queries and advanced usage patterns:
- Transactions and prepared statements
- Aggregates , case statements, and common table expressions
-
RETURNING
values from INSERT, UPDATE, and DELETE statements - PostgreSQL types like arrays , enums , timestamps , and UUIDs
- Go type overrides for individual columns or PostgreSQL values
- Generated structs with JSON tags
How it works
You might be wondering how this all works. It’s not magic, but it’s close: sqlc parses queries using the same parser as your PostgreSQL database.
A first pass uses DDL statements to build an in-memory representation of your database. Next, sqlc parses each query and uses the in-memory representation to determine input parameters and output columns.
This is only possible thanks to the amazing work by Lukas Fittl on pg_query_go . If you need help diagnosing PostgreSQL performance issues, his service pganalyze may be exactly what you need.
What’s next
While it’s still early days, sqlc is ready for production. It’s used for all database access in my own projects:
- https://equinox.io - Go application packaging & distribution
- https://upcoming.fm - Spotify playlists for your favorite music venues
- https://chaincontrol.org - SMS notifications for winter road closures in Tahoe
More importantly, it’s seen adoption in larger companies. Both ngrok and Weave use sqlc to power portions of their stack.
sqlc is a young project in active development. It currently only supports
PostgreSQL and Go. However, it's designed to support additional language
backends in the future. If you'd like sqlc support for your language of choice,
create an issue
or send me an email at [email protected]
.
Lastly, I like to thank the authors of
hugsql
,
pugsql
, and
protoc
, which served as
inspiration for sqlc
. Without these tools, sqlc would not exist.
Recommend
-
16
:rotating_light: sqlc is new and under rapid development. The code it generates is correct and safe for production use, but there is currently no guarantee of stability or backward...
-
21
简介 在 Go 语言中编写数据库操作代码真的非常痛苦! database/sql 标准库提供的都是比较底层的接口。我们需要编写大量重复的代码。大量的模板代码不仅写起来烦,而且还容易出错。有时候字段类型修改了一下,可...
-
2
Thoughts on Compile-Time Function Evaluation and Type Systems For some time now (since the 1.26 release, to be precise), Rust has a very powerful machinery for CTFE, or compi...
-
0
How We Went All In on sqlc/pgx for Postgres + GoAfter a few months of research and experimentation with running a heavily DB-dependent Go app, we’ve arrived at the conclusion that
-
2
Creating an API using Go and sqlc ...
-
1
Declarative migrations for sqlcsqlc is a tool that generates type-safe idiomatic Go code from SQL queries. It's like a transpiler, where you provide the required queries alo...
-
2
Introducing Content Collections: Type-Safe Markdown in Astro 2.0 January 25, 2023 by ...
-
2
TL;DR In this tutorial, you'll learn how to produce type-safe GraphQL queries using TypeScript using some great tools, techniques, and solutions. Intro When you're building a company obsessed with making f...
-
3
Blog Express Hibernate Queries as Type-Safe Java Streams Express Hibernate Queries as Type-Saf...
-
1
Design safe collection API with compile-time reference stability in Rust Feb 8, 2024 Reference Stability is an important con...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK