Повна версія
Go: ефективна робота з SQL
Підписуйтеся наTelegram-канал «DOU #tech», щоб не пропустити нові технічні статті.
Привіт! Ця стаття — про генерацію коду на основі SQL-запитів. Ми розглянемо бібліотеку sqlc, яка й відповідає за генерацію коду, а також goose для роботи з міграціями. Обидва інструменти я з задоволенням використовую в повсякденній роботі, вони підтримують MySQL та PostgreSQL, але в статті буду розглядати роботу з PostgreSQL, щоб краще показати, на що здатний sqlc.
Стаття має класичну структуру: від створення проєкту з docker-compose до посилання на код репозиторію.
ІлюстраціяАнастасії Сігетій
Передісторія
Я так часто пишу в статтях про Go, як раніше працював з PHP, що мені стає соромно перед аудиторією, яка читає кожну мою статтю. Якщо ви подумали про маму, то я теж про неї подумав.
Але хтось читає мою статтю вперше і для новеньких треба попередньо пояснювати, чому мій Go-код так схожий на PHP.
Якщо ж ви пишите на PHP та хочете змінити стек, то рекомендую прочитатити вже класичну статтю «PHP: Піти не можна лишитися. Піти», яка покращить вам настрій (навіть якщо ви читали її раніше), а вже потім з гарним настроєм прочитати мою «Як перекваліфікуватись з PHP на Go». Якщо що, то за популяризацію Go в Україні я вже отримав грошовий бонус від Google, або за щось інше, можливо, про це напишу в наступних статтях.
В PHP я звик до каркасу Symfony та бібліотеки Doctrine, яка відповідає за роботу з БД, ці ж інструменти використовував у домашніх проєктах і шукав щось схоже на Doctrine. Але коли почав вивчати Go, то популярною ORM-ою в Go був github.com/go-gorm/gorm. Я подивився на приклади запитів в GORM і вирішив, що писати та читати голий SQL буде комфорніше.
Писати SQL-запити мені цікаво, а ось вручну писати в Go модель, метод та тест на цей SQL-запит вже рутинно, а рутина втомлює. Зараз sqlc робить цю рутинну роботу за мене. Щоб побачити приклад, як працює sqlc , перейдіть в пісочницю play.sqlc.dev.
Опис задачі
Уявімо, що ми почали працювати над ще одним проєктом анонімного пошуку роботи (поки всі домени yeews.com, yaaws.com, yaaws.co, yaaws.io та yaaws.ai доступні для купівлі), такі проєкти зараз популярні (yaaws is yet another anonym work search).
Якщо будете робити подібний проєкт, то робіть одразу з українською локалізацією (а то вже запустили цього року один сервіс, переповнений рекламою, а потім в LinkedIn «відгуки» про нього збирають).
І нам треба зробити стандартний функціонал взаємодії з БД:
- Створення та редагування компанії.
- Створення та редагування вакансії.
- Деревовидні відгуки про компанії.
Почнемо з налаштування проєкту, далі міграції, далі автогенерація коду взаємодії з БД.
Налаштування проєкту
Я створю Go-проєкт, загорну в Docker, підключу Postgres та перевірю, що контейнери робочі.
go mod init gitlab.com/go-yp/go-sqlc-example
go: creating new go.mod: module gitlab.com/go-yp/go-sqlc-example
Тепер підготую docker-compose.yml , який буде мати опис контейнеру з Go та опис контейнеру з Postgres:
version: "2"
services:
app:
container_name: "yaaws_app"
image: golang:1.17.1-alpine
volumes:
- .:/go/src/gitlab.com/go-yp/go-sqlc-example
- ./docker/golang/.ash_history:/root/.ash_history:ro
- go-modules:/go/pkg/mod # Put modules cache into a separate volume
command: "sleep 365d"
working_dir: "/go/src/gitlab.com/go-yp/go-sqlc-example"
links:
- postgres
depends_on:
- postgres
postgres:
container_name: "yaaws_postgres"
image: postgres:14.0
environment:
POSTGRES_DB: "yaaws"
POSTGRES_USER: "yaroslav"
POSTGRES_PASSWORD: "AnySecretPassword!!"
ports:
- "5432:5432"
volumes:
go-modules: # Define the volume
docker-compose up -d
docker exec yaaws_app go version
# result
go version go1.17.1 linux/amd64
docker exec yaaws_postgres psql -U yaroslav -d yaaws -c "SELECT VERSION();"
# result
PostgreSQL 14.0 (Debian 14.0-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
docker-compose down
Як бачимо, команди успішно виконались в контейнерах та вивели очікувані результати.
Якщо ви помітили, що налаштування проєкту взято зі статті про ClickHouse, то я також це помітив.
Налаштування міграцій
Для міграцій в Go вистачає бібліотек на GitHub, я ж вибрав github.com/pressly/goose. Вона використовується в одному з проєктів, де працюю, має зрозумілу документації, до goose вже звик.
Встановлення:
go get -u github.com/pressly/goose/v3/cmd/goose
Створення міграції:
mkdir -p ./storage/schema
goose -dir ./storage/schema -table schema_migrations postgres $(POSTGRES_URI) create $(MIGRATION_NAME) go
goose -dir ./storage/schema -table schema_migrations postgres $(POSTGRES_URI) create $(MIGRATION_NAME) sql
tree ./storage/schema -h
./storage/schema
├── [ 364] 20211001220519_yaaws_init.go
└── [ 173] 20211001220531_yaaws_init.sql
0 directories, 2 files
cat ./storage/schema/20211001220519_yaaws_init.go
package migrations
import (
"database/sql"
"github.com/pressly/goose"
)
func init() {
goose.AddMigration(upYaawsInit, downYaawsInit)
}
func upYaawsInit(tx *sql.Tx) error {
// This code is executed when the migration is applied.
return nil
}
func downYaawsInit(tx *sql.Tx) error {
// This code is executed when the migration is rolled back.
return nil
}
cat ./storage/schema/20211001220531_yaaws_init.sql
-- +goose Up
-- +goose StatementBegin
SELECT 'up SQL query';
-- +goose StatementEnd
-- +goose Down
-- +goose StatementBegin
SELECT 'down SQL query';
-- +goose StatementEnd
Як бачите, у вас є вибір. Я вибрав *.sql , бо так буде менше коду.
Щоб спростити роботу з міграціями, налаштував Makefile.dev та Makefile :
# Makefile.dev
USER=yaroslav
PORT=5432
HOST=localhost
PASSWORD=AnySecretPassword!!
DATABASE=yaaws
OPTIONS=sslmode=disable
POSTGRES_URI=postgresql://$(USER):$(PASSWORD)@$(HOST):$(PORT)/$(DATABASE)?$(OPTIONS)
# Makefile
stage := $(or $(YAAWS_STAGE), dev)
include Makefile.$(stage)
goose-install:
go get -u github.com/pressly/goose/v3/cmd/goose
MIGRATION_NAME=$(or $(MIGRATION), yaaws_init)
migrate-create:
mkdir -p ./storage/schema
goose -dir ./storage/schema -table schema_migrations postgres $(POSTGRES_URI) create $(MIGRATION_NAME) sql
migrate-up:
goose -dir ./storage/schema -table schema_migrations postgres $(POSTGRES_URI) up
migrate-redo:
goose -dir ./storage/schema -table schema_migrations postgres $(POSTGRES_URI) redo
migrate-down:
goose -dir ./storage/schema -table schema_migrations postgres $(POSTGRES_URI) down
migrate-reset:
goose -dir ./storage/schema -table schema_migrations postgres $(POSTGRES_URI) reset
migrate-status:
goose -dir ./storage/schema -table schema_migrations postgres $(POSTGRES_URI) status
MIGRATION=users make migrate-create
2021/10/02 00:10:08 Created new file: storage/schema/20211002001008_users.sql
MIGRATION=companies make migrate-create
2021/10/02 00:10:15 Created new file: storage/schema/20211002001015_companies.sql
Опишемо таблиці для компаній, вакансій, відгуків та користувачів:
-- +goose Up
-- +goose StatementBegin
CREATE TABLE users
(
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
created TIMESTAMP WITH TIME ZONE NOT NULL
);
-- +goose StatementEnd
-- +goose Down
-- +goose StatementBegin
DROP TABLE IF EXISTS users;
-- +goose StatementEnd
-- +goose Up
-- +goose StatementBegin
CREATE TABLE companies
(
id SERIAL PRIMARY KEY,
alias VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
created_by INT NOT NULL,
created TIMESTAMP WITH TIME ZONE NOT NULL,
FOREIGN KEY (created_by)
REFERENCES users (id)
);
CREATE TABLE vacancies
(
id SERIAL PRIMARY KEY,
company_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
created_by INT NOT NULL,
created TIMESTAMP WITH TIME ZONE NOT NULL,
FOREIGN KEY (company_id)
REFERENCES companies (id),
FOREIGN KEY (created_by)
REFERENCES users (id)
);
CREATE TABLE reviews
(
id SERIAL PRIMARY KEY,
parent_id INT NULL,
company_id INT NOT NULL,
content TEXT NOT NULL,
created_by INT NOT NULL,
created TIMESTAMP WITH TIME ZONE NOT NULL,
FOREIGN KEY (parent_id)
REFERENCES reviews (id),
FOREIGN KEY (company_id)
REFERENCES companies (id),
FOREIGN KEY (created_by)
REFERENCES users (id)
);
-- +goose StatementEnd
-- +goose Down
-- +goose StatementBegin
DROP TABLE IF EXISTS reviews;
DROP TABLE IF EXISTS vacancies;
DROP TABLE IF EXISTS companies;
-- +goose StatementEnd
Тепер запустимо міграції:
make migrate-up
goose -dir ./storage/schema -table schema_migrations postgres ... up
2021/10/02 01:22:44 OK 20211002001008_users.sql
2021/10/02 01:22:44 OK 20211002001015_companies.sql
2021/10/02 01:22:44 goose: no migrations to run. current version: 20211002001015
make migrate-status
goose -dir ./storage/schema -table schema_migrations postgres ... status
2021/10/02 01:22:55 Applied At Migration
2021/10/02 01:22:55 =======================================
2021/10/02 01:22:55 Fri Oct 1 22:22:44 2021 -- 20211002001008_users.sql
2021/10/02 01:22:55 Fri Oct 1 22:22:44 2021 -- 20211002001015_companies.sql
Міграції налаштовані успішно.
Налаштування кодогенерації sqlc
Інструмент sqlc генерує Go-код на основі SQL-запитів, отже логічно почати саме з запитів. Їх розмістив в теці ./storage/query/ :
tree ./storage/query/ -h
./storage/query/
└── [ 435] users.sql
cat ./storage/query/users.sql
-- name: UserNew :exec
INSERT INTO users (username, created)
VALUES (@username, @created);
-- name: UserNewAndGet :one
INSERT INTO users (username, created)
VALUES (@username, @created)
RETURNING id, username, created;
-- name: UserGetByID :one
SELECT id, username, created
FROM users
WHERE id = @id;
-- name: UsersCount :one
SELECT COUNT(*) FROM users;
-- name: Users :many
SELECT *
FROM users
LIMIT @sql_limit OFFSET @sql_offset;
Біля кожного запиту є анотація з двома параметрами: назва та команда. Анотація потрібна для кодогенерації.
Команда :one для отримання з БД одного рядка, :many отримання рядків, :exec виконання дії.
Весь список команд є в meta.go та описаний в документації:
const (
CmdExec = ":exec"
CmdExecResult = ":execresult"
CmdExecRows = ":execrows"
CmdMany = ":many"
CmdOne = ":one"
)
Першою дією ми описали запити, а другою маємо описати файл sqlc.yaml :
version: "1"
packages:
- path: "./storage/dbs/"
engine: "postgresql"
schema: "./storage/schema/"
queries: "./storage/query/"
- queries — це тека, де файли з запитами, поки один users.sql ;
- schema — це тека зі схемою БД, в нашому випадку в ./storage/schema зберігаються міграції, згенеровані goose ;
- path — це тека, в яку буде зберігатись згенерований Go-код;
- engine — який рушій ми вибрали для проєкту.
У міграціях ./storage/schema можуть зустрічатись ALTER TABLE і sqlc вміє їх парсити (modifying the database schema).
Запуск генерації:
generate-dbs:
docker run --rm -v $(shell pwd):/src -w /src kjconroy/sqlc generate
make generate-dbs
docker run --rm -v ~/go/src/gitlab.com/go-yp/go-sqlc-example:/src -w /src kjconroy/sqlc generate
make generate-dbs ./storage/query/
tree ./storage/dbs/ -h
./storage/dbs/
├── [ 565] db.go
├── [ 534] models.go
└── [2.1K] users.sql.go
0 directories, 3 files
Розглянемо кожен згенерований файл.
cat ./storage/dbs/db.go
// Code generated by sqlc. DO NOT EDIT.
package dbs
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,
}
}
cat ./storage/dbs/models.go
// Code generated by sqlc. DO NOT EDIT.
package dbs
import (
"time"
)
type User struct {
ID int32
Username string
Created time.Time
}
cat ./storage/dbs/users.sql.go
// Code generated by sqlc. DO NOT EDIT.
// source: users.sql
package dbs
import (
"context"
"time"
)
const userGetByID = `-- name: UserGetByID :one
SELECT id, username, created
FROM users
WHERE id = $1
`
func (q *Queries) UserGetByID(ctx context.Context, id int32) (User, error) {
row := q.db.QueryRowContext(ctx, userGetByID, id)
var i User
err := row.Scan(&i.ID, &i.Username, &i.Created)
return i, err
}
const userNew = `-- name: UserNew :exec
INSERT INTO users (username, created)
VALUES ($1, $2)
`
type UserNewParams struct {
Username string
Created time.Time
}
func (q *Queries) UserNew(ctx context.Context, arg UserNewParams) error {
_, err := q.db.ExecContext(ctx, userNew, arg.Username, arg.Created)
return err
}
const userNewAndGet = `-- name: UserNewAndGet :one
INSERT INTO users (username, created)
VALUES ($1, $2)
RETURNING id, username, created
`
type UserNewAndGetParams struct {
Username string
Created time.Time
}
func (q *Queries) UserNewAndGet(ctx context.Context, arg UserNewAndGetParams) (User, error) {
row := q.db.QueryRowContext(ctx, userNewAndGet, arg.Username, arg.Created)
var i User
err := row.Scan(&i.ID, &i.Username, &i.Created)
return i, err
}
const users = `-- name: Users :many
SELECT id, username, created
FROM users
LIMIT $2 OFFSET $1
`
type UsersParams struct {
SqlOffset int32
SqlLimit int32
}
func (q *Queries) Users(ctx context.Context, arg UsersParams) ([]User, error) {
rows, err := q.db.QueryContext(ctx, users, arg.SqlOffset, arg.SqlLimit)
if err != nil {
return nil, err
}
defer rows.Close()
var items []User
for rows.Next() {
var i User
if err := rows.Scan(&i.ID, &i.Username, &i.Created); 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
}
const usersCount = `-- name: UsersCount :one
SELECT COUNT(*) FROM users
`
func (q *Queries) UsersCount(ctx context.Context) (int64, error) {
row := q.db.QueryRowContext(ctx, usersCount)
var count int64
err := row.Scan(&count)
return count, err
}
Як бачимо, для кожного запиту з файлу users.sql був створений відповідний метод в файлі users.sql.go.
Використання згенерованого коду
Щоб краще побачити всю картину, наведу приклад, в якому будуть використані згенеровані методи:
package tests
import (
"context"
"database/sql"
_ "github.com/lib/pq"
"github.com/stretchr/testify/require"
"gitlab.com/go-yp/go-sqlc-example/storage/dbs"
"testing"
"time"
)
const (
dataSourceName = "postgresql://yaroslav:[email protected]:5432/yaaws?sslmode=disable&timezone=UTC"
)
func TestUsers(t *testing.T) {
connection, err := sql.Open("postgres", dataSourceName)
require.NoError(t, err)
defer connection.Close()
require.NoError(t, connection.Ping())
// clear users
{
const (
// language=PostgreSQL
query = "TRUNCATE TABLE users RESTART IDENTITY CASCADE"
)
_, err := connection.Exec(query)
require.NoError(t, err)
}
var queries = dbs.New(connection)
// db.go
_ = queries.WithTx
// models.go
var _ dbs.User
// users.sql.go
_ = queries.UserNew
_ = queries.UserNewAndGet
_ = queries.UserGetByID
_ = queries.UsersCount
_ = queries.Users
var ctx = context.Background()
{
count, err := queries.UsersCount(ctx)
require.NoError(t, err)
require.Equal(t, int64(0), count)
}
var now = time.Now().UTC().Truncate(time.Second)
var (
expected = dbs.User{
ID: 1,
Username: "System",
Created: now,
}
)
// create
{
actual, err := queries.UserNewAndGet(ctx, dbs.UserNewAndGetParams{
Username: "System",
Created: now,
})
require.NoError(t, err)
require.Equal(t, expected, actual)
}
// get by id
{
actual, err := queries.UserGetByID(ctx, 1)
require.NoError(t, err)
require.Equal(t, expected, actual)
}
// get count
{
count, err := queries.UsersCount(ctx)
require.NoError(t, err)
require.Equal(t, int64(1), count)
}
// get users
{
users, err := queries.Users(ctx, dbs.UsersParams{
SqlOffset: 0,
SqlLimit: 32,
})
require.NoError(t, err)
require.Equal(t, []dbs.User{expected}, users)
}
}
docker exec yaaws_app go test ./tests/... -v -count=1
=== RUN TestUsers
--- PASS: TestUsers (0.03s)
PASS
ok gitlab.com/go-yp/go-sqlc-example/tests 0.033s
Генерація коду для складних SQL запитів
Для складніших запитів з JOIN-ами або рекурсією, sqlc також успішно генерує код.
Приклади складніших запитів:
tree ./storage/query/
./storage/query/
├── companies.sql
├── reviews.sql
├── users.sql
└── vacancies.sql
0 directories, 4 files
cat ./storage/query/vacancies.sql
-- name: VacanciesByCompanies :many
SELECT v.id AS vacancy_id,
v.title AS vacancy_title,
c.id AS company_id,
c.alias AS company_alias,
c.name AS company_name
FROM vacancies v
INNER JOIN companies c ON (v.company_id = c.id)
WHERE c.id = ANY (@companies::int[]);
cat ./storage/query/reviews.sql
-- name: ReviewsRootByCompany :many
SELECT *
FROM reviews
WHERE company_id = @company_id
AND parent_id IS NULL;
-- name: ReviewsNested :many
WITH RECURSIVE nested AS (
SELECT r.*
FROM reviews r
WHERE r.id = @id::INT
UNION ALL
SELECT r.*
FROM reviews r
INNER JOIN nested n ON (r.parent_id = n.id)
)
SELECT *
FROM nested n
WHERE n.company_id = @company_id::INT;
make generate-dbs
tree ./storage/dbs/
./storage/dbs/
├── companies.sql.go [+]
├── db.go
├── models.go
├── reviews.sql.go [+]
├── users.sql.go
└── vacancies.sql.go [+]
Робота з транзаціями
Розглянемо ще раз файл db.go :
// Code generated by sqlc. DO NOT EDIT.
package dbs
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
}
Як бачимо, Queries підтримує транзації.
func TestTransaction(t *testing.T) {
connection, err := sql.Open("postgres", dataSourceName)
require.NoError(t, err)
defer connection.Close()
{
require.NoError(t, connection.Ping())
var queries = dbs.New(connection)
count, err := queries.UsersCount(context.Background())
require.NoError(t, err)
require.Equal(t, int64(1), count)
}
{
tx, err := connection.Begin()
require.NoError(t, err)
defer func() {
require.NoError(t, tx.Rollback())
}()
var queries = dbs.New(tx)
count, err := queries.UsersCount(context.Background())
require.NoError(t, err)
require.Equal(t, int64(1), count)
}
}
Або ж можна зробити обгортку над транзакцією (оригінал):
func WithTransaction(db *sql.DB, fn func(queries *dbs.Queries) error) (err error) {
tx, err := db.Begin()
if err != nil {
return
}
defer func() {
if p := recover(); p != nil {
// a panic occurred, rollback and repanic
tx.Rollback()
panic(p)
} else if err != nil {
// something went wrong, rollback
tx.Rollback()
} else {
// all good, commit
err = tx.Commit()
}
}()
err = fn(dbs.New(tx))
return err
}
Стаття про defer українською мовою та з тестами.
Сильні сторони sqlc
- Чудово описана документація, лаконічна та інфомативна.
- Є playground.
-
Підтримка популярних іструментів для міграцій (modifying the database schema):
- dbmate
- golang-migrate
- goose
- sql-migrate
- tern
-
Є багато налаштувань, які описані в документації:
-
emit_json_tags — генерувати моделі з тегами для серіалізації JSON-у;
- emit_prepared_queries — для повторного використання statement.
- ...
Слабі сторони sqlc
Якщо потрібно побудувати SQL-запит, який має опціональні параметри або опціональні JOIN-и, то краще взяти інший інструмент, query builder або ORM.
Хоча для малого числа параметрів ще можна написати перевірку в SQL:
-- name: ReviewsRootByParams :many
SELECT *
FROM reviews
WHERE (@company_id::INT = 0 OR company_id = @company_id::INT)
AND (@created_by::INT = 0 OR created_by = @created_by::INT)
AND parent_id IS NULL;
Має кращу підтримку Postgres ніж MySQL.
Епілог
Код досутпний в репозиторії.
Якщо вам більше подобаються ORM-ки, то придивіться до бібліотеки github.com/ent/ent, яка також має кодогенерацією та ще взаємодію з gRPC.
