Getting started with PostgreSQL
This tutorial assumes that the latest version of sqlc is installed and ready to use.
Create a new directory called sqlc-tutorial
and open it up.
Initialize a new Go module named tutorial.sql.dev/app
go mod init tutorial.sqlc.dev/app
sqlc looks for either a sqlc.yaml
or sqlc.json
file in the current
directory. In our new directory, create a file named sqlc.yaml
with the
following contents:
version: 1
packages:
- path: "tutorial"
name: "tutorial"
engine: "postgresql"
schema: "schema.sql"
queries: "query.sql"
sqlc needs to know your database schema and queries. In the same directory,
create a file named schema.sql
with the following contents:
CREATE TABLE authors (
id BIGSERIAL PRIMARY KEY,
name text NOT NULL,
bio text
);
Next, create a query.sql
file with the following four queries:
-- 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;
For SQL UPDATE if you do not want to return the updated record to the user, add this to the query.sql
file:
-- name: UpdateAuthor :exec
UPDATE authors
set name = $2,
bio = $3
WHERE id = $1;
Otherwise, to return the updated record back to the user, add this to the query.sql
file:
-- name: UpdateAuthor :one
UPDATE authors
set name = $2,
bio = $3
WHERE id = $1
RETURNING *;
You are now ready to generate code. Run the generate
command. You shouldn’t see any errors or output.
sqlc generate
You should now have a tutorial
package containing three files.
├── go.mod
├── query.sql
├── schema.sql
├── sqlc.yaml
└── tutorial
├── db.go
├── models.go
└── query.sql.go
You can use your newly generated queries in app.go
.
package main
import (
"context"
"database/sql"
"log"
"reflect"
"tutorial.sqlc.dev/app/tutorial"
_ "github.com/lib/pq"
)
func run() error {
ctx := context.Background()
db, err := sql.Open("postgres", "user=pqgotest dbname=pqgotest sslmode=verify-full")
if err != nil {
return err
}
queries := tutorial.New(db)
// list all authors
authors, err := queries.ListAuthors(ctx)
if err != nil {
return err
}
log.Println(authors)
// create an author
insertedAuthor, err := queries.CreateAuthor(ctx, tutorial.CreateAuthorParams{
Name: "Brian Kernighan",
Bio: sql.NullString{String: "Co-author of The C Programming Language and The Go Programming Language", Valid: true},
})
if err != nil {
return err
}
log.Println(insertedAuthor)
// get the author we just inserted
fetchedAuthor, err := queries.GetAuthor(ctx, insertedAuthor.ID)
if err != nil {
return err
}
// prints true
log.Println(reflect.DeepEqual(insertedAuthor, fetchedAuthor))
return nil
}
func main() {
if err := run(); err != nil {
log.Fatal(err)
}
}
Before the code will compile, you’ll need to add the Go PostgreSQL driver.
go get github.com/lib/pq
go build ./...
To make that possible, sqlc generates readable, idiomatic Go code that you
otherwise would have had to write yourself. Take a look in tutorial/query.sql.go
.