You are viewing the documentation for v1.22.0, an older release of sqlc. View the latest documentation.

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: 2
sql:
  - engine: "mysql"
    queries: "query.sql"
    schema: "schema.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   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?parseTime=true")
	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.

If your tables have columns with date or time types, sqlc expects these values to scan into time.Time structs. If you’re using github.com/go-sql-driver/mysql, ensure that parseTime=true has been added to the connection string.