Getting started with MySQL
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: "mysql"
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 BIGINT NOT NULL AUTO_INCREMENT 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 :execresult
INSERT INTO authors (
name, bio
) VALUES (
?, ?
);
-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = ?;
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/go-sql-driver/mysql"
)
func run() error {
ctx := context.Background()
db, err := sql.Open("mysql", "user:password@/dbname")
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
result, 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
}
insertedAuthorID, err := result.LastInsertId()
if err != nil {
return err
}
log.Println(insertedAuthorID)
// get the author we just inserted
fetchedAuthor, err := queries.GetAuthor(ctx, insertedAuthorID)
if err != nil {
return err
}
// prints true
log.Println(reflect.DeepEqual(insertedAuthorID, fetchedAuthor.ID))
return nil
}
func main() {
if err := run(); err != nil {
log.Fatal(err)
}
}
Before the code will compile, you’ll need to add the Go MySQL driver.
go get github.com/go-sql-driver/mysql
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
.