Getting started with SQLite
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: 2
sql:
- engine: "sqlite"
schema: "schema.sql"
queries: "query.sql"
gen:
go:
package: "tutorial"
out: "tutorial"
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 INTEGER 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 = ? LIMIT 1;
-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;
-- name: CreateAuthor :one
INSERT INTO authors (
name, bio
) VALUES (
?, ?
)
RETURNING *;
-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = ?;
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 = ?,
bio = ?
WHERE id = ?;
Otherwise, to return the updated record back to the user, add this to the query.sql
file:
-- name: UpdateAuthor :one
UPDATE authors
set name = ?,
bio = ?
WHERE id = ?
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"
_ "embed"
_ "github.com/mattn/go-sqlite3"
)
//go:embed schema.sql
var ddl string
func run() error {
ctx := context.Background()
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
return err
}
// create tables
if _, err := db.ExecContext(ctx, ddl); 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 SQLite driver.
go mod tidy
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
.