Сучасна диджитал-освіта для дітей — безоплатне заняття в GoITeens ×
Mazda CX 30
×

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"
	"testing"
	"time"

	"gitlab.com/go-yp/go-sqlc-example/storage/dbs"

	"github.com/stretchr/testify/require"

	_ "github.com/lib/pq"
)

const (
	dataSourceName = "postgresql://yaroslav:AnySecretPassword!!@postgres: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

Слабі сторони 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.

👍ПодобаєтьсяСподобалось10
До обраногоВ обраному8
LinkedIn
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter

А який сенс в

UserNew

, і де update?

Для прикладу вибрав обмежене число SQL запитів, щоб показати основні можливості sqlc.

UPDATE буде мати анотацію :exec, яка вже була наведена в UserNew.

Дивно просто, навели дублікат insert (так і не зрозумів навіщо нам UserNew), а update і delete забули :)

Щоб показати основні можливості sqlc вистачило INSERT та SELECT.

Перечитайте статтю уважніше.

вибачте. я хотів наголосити на тому, що інформація якась не конситетна: два insert замість класичних insert/update/delete виглядають дивно. якщо основна ідея показати анотацію exec, то навести краще всі три команди для модифікації і insert з поверненням PK більш цікавий, ніж просто додавання :)
PS: доречі можна замінити insert/update на варіант з ON CONFLICT і це буде ще цікавіше.

Чим більше наведу SQL запитів у прикладах, тим більше буде коду у згенерованих файлах, тим більше буде розтягнута стаття й тим більше шанс, що із-за об’єму статті, її відкладуть на потім й забудуть.

А я хотів показати, що у INSERT-ів можуть бути різні команди в анотаціях (:exec та :one).

Звісно писати про ON CONFLICT WHERE цікавіше, але тоді треба видалити згадування про sqlc й повністю переписати на тему PostgreSQL.

Так а старий добрий sql

Гашо ьакі збочення

Дякую, мені як новачку було цікаво про це дізнатись. Так реально набагато зручніше. Єдине, що не зручно, це тримати актуальну схему БД в проєкті, коли міграції лежать в іншому.

То напишіть команду в Makefile, яка буде копіювати міграції в Go-проєкт, запускати контейнер з БД, накочувати міграції, зберігати з контейнера у файл схему БД, й цей файл використовуйте для sqlc.

не зрозумів, в чому ефективність, в міграціях?
Якщо так, то назвати тре було статтю

Go: ефективні SQL міграції

недавно пробував працювати з postgres, перше що нагуглив, то і зайшло
github.com/lib/pq

Для Postgres є ще github.com/jackc/pgx

На DOU поки відсутній функціонал вибору назви статті через голосування.

до чого тут голосування, якщо в заголовку одне, а в статті про міграції

Ви читали статтю повністю? github.com/kyleconroy/sqlc це теж міграції?

тоді

Go: ефективні SQL міграції і кодогенерація з sqlc

Треба красива назва, а відчуття краси суб’єктивне

Дякую за статтю, але, звичайно, так я робити не буду )

Мій вибір:
* міграції усередині апки (go:embed)
* github.com/golang-migrate/migrate + авто міграції при старті
* pgx, gorm (raw-запроси при потребі)

То sqlc ви можете використовувати для raw-запитів

sqlc підтримує ваш вибір github.com/golang-migrate/migrate , хоча для go:embed треба протестувати або ж вкласти в цей проєкт та додати підтримку go:embed

Ви маєте писати більше про ваш вибір, аргументувати, щоб інші також могли оцінити та зробити правильне рішення при розробці.

Ты один меня согрел бальше всех любимый ORM

Отличный материал, но мне больше нравиться GORM

Прикольно. Я в свое время делал доклад как работать с SQL на киевском го митапе. Пришел к тому, что сложные запросы приходится через go template шаблонизировать. Конечно работа с реляционками в го это по прежнему боль.

Через github.com/valyala/quicktemplate чи стандартний шаблонізатор?

Є запис доповіді?

Швидке рішення, але тільки для побудови запитів: Organizing PostgreSQL codebase using templates in Golang

А там тільки побудова SQL-запитів, навіть без генерації моделей

Спасибо за толковый материал, очень в тему !

Щодо схеми PostgreSQL. Вважається поганим тоном використовувати конструкції вигляду VARCHAR(N)
wiki.postgresql.org/...​varchar.28n.29_by_default

Дякую, прочитаю та виправлю. Раніше працював переважно з MySQL.

Нічого страшного. Долучайтеся до спільноти PostgreSQL Ukraine. Ми таке часто обговорюємо. :)
t.me/PostgresUkraine
www.facebook.com/groups/postgresql.ua

А хіба типи CHAR та VARCHAR не виділяють при зберіганні максимальну кількість байт під кожне поле? Я звісно не експерт з баз даних, тому і питаю — там особливий механізм зберігання якийсь, який не робить строки таблиці фіксованої довжини?

Чому це важливо: дані таблиць можливо відновити в разі збою саме через їхню структурованість. Саме через це у корпоративних задачах довгий час панувала думка, що зберігати при можливості дані треба саме у полях фіксованої довжини, а не TEXT, BLOB чи BYTEA.

Дякую за запитання. Ніт, це не так. Продублюю посилання: wiki.postgresql.org/...​varchar.28n.29_by_default

Some databases don’t have a type that can hold arbitrary long text, or if they do it’s not as convenient or efficient or well-supported as varchar(n). Users from those databases will often use something like varchar(255) when what they really want is text.

Навіть сама назва VARCHAR — означає variable length characters. Тому ні, не існує жодної бази даних, яка б виділяла максимальну кількість байт під таке поле. Проте існують бази, що не підтримують такий тип даних, а користуються лише CHAR (BPCHAR) — blank padded characters.

Тобто хочеш сказати, що Postgress зберігає текстові поля за межами основної таблиці? Чи виділяється фіксована кількість байт в самій таблиці, а все інше виноситься в інше місце? Тоді мав би бути спосіб, щоб визначити скільки саме — я про таке не чув.

Історично, тобто у Ораклі, Varchar — це лише char, який знає довжину строки, і відповідно обрізає її на видачі. І варчар без вказання довжини резервував максимально можливу кількість байт, виходячи з найгіршого для обраного кодування випадку. Для UTF-8 це було 3 байти на символ * 255 символів.

Тобто хочеш сказати, що Postgress зберігає текстові поля за межами основної таблиці?

Если не ошибаюсь, начиная с какой-то длины строки многие субд так ведут себя.

Длины строки всех данных строки таблицы или длины строки каждого значения поля? Как я понял из описания Postgress, то каждого значения поля.

Иначе говоря, до 2 килобайт у тебя каждое значение будет по-дефолту, просто за то что ты указал тип данных VARCHAR без параметра. 255 символов лимит не уверен что остался (атавизм как бы), так что 1кб на поле в utf8mb4 выжрет, а utf8mb6 не за горами.

Саме так. Дані, що не вміщаються на сторінку даних виносяться в спеціальне сховище. Деталі: wiki.postgresql.org/wiki/TOAST

Про Оракл не знаю, якщо це правда, то це дуже нераціонально.

Тобто, до 2 кілобайт на 1 поле по дефолту виділяється в таблиці — це вони таке нам рекомендують?

И эти люди нам говорят не ковыряться в носу?
© Вовочка, подсматривая за родителями

Пане Олексію, дуже радий, що ви іронізуєте в коментарях, але вам ніхто такого не рекомендує, і, насправді ©, все набагато складніше.

When a row that is to be stored is «too wide» (the threshold for that is 2KB by default), the TOAST mechanism first attempts to compress any wide field values. If that isn’t enough to get the row under 2KB, it breaks up the wide field values into chunks that get stored in the associated TOAST table

Якщо для вас рядок і поле рядка — це одне й те саме, або десь ви тут бачите пам’ять для поля по дефолту, то мені здається, що ви іронізуєте заради іронії.
Почитайте посилання, що я вже давав. Дякую

If you have many wide columns then the number of rows you can have in the table might be much less than 4 billion.

Это оттуда же, про лимит в 4 миллиарда вынесенных за пределы таблицы записей. Из чего я понимаю, что выносится каждое значение под своим идентификатором, соответственно и лимит ставится на значение ПОЛЯ таблицы, а не СТРОКИ целиком.

По самому хранению даётся пример:
You can modify the storage type like this:
alter table test_blob alter column column_name set storage EXTENDED;

Почему я и перестал покупать книги по IT вопросам вообще. Их пишут писатели, а не те, кто реально понимает противоречия. И пишут по принципу «бумага всё стерпит».

Кстати, нихрена не понял про 8кб лимит на строку таблицы. Это очень древняя статья? Или там реально жопа? Данные в полях типа TEXT бывают и побольше — но про Postgress не в курсе.

Пишут This page was last edited on 27 October 2016

8кб — розмір сторінки даних за замовчуванням. Вікі-сторінка 16 року, але посилається на останню версію мануала: www.postgresql.org/...​urrent/storage-toast.html

Згідно з офіційною документацією (www.postgresql.org/...​ocs/current/limits.html)
columns per table 1600
field size 1 GB

А що тоді лімітує 8Кб, я так і не зрозумів, чого стосується цей ліміт. Що таке взагалі «сторінка даних», для чого використовується?

Грубо кажучи, сторінка — це мінімальний об’єм файлу даних, який вичитується в кеш оперативної пам’яті і потім при необхідності записується на диск, або заміщається новою сторінкою з диска
habr.com/...​/postgrespro/blog/444536

Тобто, Postgress не має регулярної структури полів всередині таблиці, всі строки змінної довжини. Тому так, лімітувати varchar нема необхідності. Цікаво, чи це тільки в Postgress таке серед SQL баз даних? Бо по суті то вже не є табличною СУБД, скоріше класика NoSQL.

Як я розумію, Postgress вичитує завжди повну сторінку, тобто 4 рядки.

І навіть звідти незрозуміло, ЩО САМЕ з точки зору юзера лімітується 8К? Строка 2К, інше йде в TOAST. Поле може бути значно більшим за 2К і за 8К, воно тупо нарізається на 2К фрагменти. А що треба зробити, щоб перевищити ліміт 2К та упертися в ліміт 8К?

Постгрес має регулярну структуру полів. Не має лімітувати varchar (а також text, bytea, varbit, json, etc.) тому, що використовується система TOAST.

Я впевнений, що всі адекватні системи RDBM використовують схожі трюки.

Так, постгрес з диску завжди вичитує (і записує) повну сторінку.

Ніт, в сторінці може зберігатися більше 4 рядків.

Користувачу невідома внутрішня реалізація, і йому має бути все одно, як воно реалізовано. Тобто з точки зору користувача нічого не лімітується, ну, окрім 1600 полів на таблицю і максимального розміру поля в 1Гб.

Користувач не може зробити нічого такого (в рамках SQL), щоб кудись впертися, окрім як, спробувати створити таблицю на більше ніж 1600 полів, або спробувати залити текст більше 1Гб.

Усі питання, що ви ставите, вони мають сенс лише в контексті розробника самого сервера. Користувач ніколи не стикається з цими питаннями.

Якщо varchar не має регулярної структури, то не буде і регулярності інших полів. Тобто, база не має фіксованої структури рядка, натомість кожен рядок має інформацію про його структуру, як саме ділити його на поля.

Питання було в тому, чому в статті про TOAST спливає ліміт 8К, і щось мені підказує, що справжнім лімітом рядка є не 2К, а 8К. Ліміт 2К м′який, після якого вмикається механізм TOAST. Але TOAST може бути неефективним, і ніщо не заважає рядку мати довжину більше 2К, формат дозволяє. Щось мені підказує, цей TOAST можна навіть вимкнути для окремого поля. А сторінку можна зробити не 8К, а 16К або 32К, але для цього потрібно робити компіляцію власноруч — що викличе проблеми із оновленням цього щастя.

Ок, в нас з вами різні визначення структурності, тому не будемо про це.

8К — це розмір за замовчуванням. Так, ви праві. Можна вказувати різні методи зберігання полів. Так, сторінку можна зробити більшому на етапі компіляції. При оновленні у межах мажорної версії доведеться заново збирати, а при оновленні на наступну мажорну версії існують інші стратегії, бо в будь-якому випадку бінарні репрезентації файлі не збігатимуться

Можу порадити чудову книгу про внутрішню архітектуру www.interdb.jp/pg

Те, про що ми з вами розмовляємо, детально описано в розділі 1.3. Internal Layout of a Heap Table File

Наприклад, для таблиці
CREATE TABLE foo(i int);
в стандартну 8Кб сторіну вміститься близько 2000 рядків. Певна частина сторінки буде зайнята службовою інформацією

Допустив помилку в розрахунках. Забув про службові заголовки для кожного рядка плюс вирівнювання.

Якщо перевірити в лоб, то вийде якось так:

test=*> CREATE TABLE foo(i int);
CREATE TABLE

test=*> INSERT INTO foo SELECT s FROM generate_series(1, 500) g(s);
INSERT 0 500

test=*> SELECT ctid, * FROM foo OFFSET 220 LIMIT 10;
  ctid   |  i
---------+-----
 (0,221) | 221
 (0,222) | 222
 (0,223) | 223
 (0,224) | 224
 (0,225) | 225
 (0,226) | 226
 (1,1)   | 227
 (1,2)   | 228
 (1,3)   | 229
 (1,4)   | 230
(10 rows)

Тобто 226 рядків. Навантаження службової інформації на кожен рядок є проблемою для heap storage engine. Саме тому зараз активно розробляються інші, наприклад, zheap.

Mysql хранит длинные строки тоже подобно toast.

Но рекомендовано указывать длину поля и минимально возможную, это на выборках сказывается, потому что mysql выделяет память под каждую строчку исходя из указанного значения (умножить на многобайтовость кодировки если указана). Сортировки, группировки, вложенные запросы, всё что создаёт временные таблицы в памяти или на диске будут неоправданно «пухнуть» если там отфонаря указывать varchar(500), когда достаточно 30 символов.

А потом ещё кто-то индекс на такое поле решит создать и ревьювер не заметит, тоже оверхед по buffer pool (они же shared buffers в postgres) и меньше полезных данных в кеше будет.

Хочешь сказать, что таблицы в памяти зачем-то должны иметь регулярную структуру? Вообще непонятно зачем. Операции с памятью достаточно быстрые, чтобы иметь в каждой строке своё уникальное смещение для каждого поля.

Другое дело, что в абсолютном большинстве случаев кеширование запросов в Мускуле нужно отключить насколько это возможно. Ибо нефиг. Для кеширования есть куда более кошерные средства на бекенде.

Сортировки как правило выполняются на лету, то есть заранее берётся индекс, уже отсортированный по одному из полей запроса, на которое заказана сортировка. Запросов с большим объёмом данных, требующих пересортировать их во временной таблице — мизер. Так что пухнуть ничего не должно.

С индексом там вроде даже лимит есть, сколько первых символов в нём участвует.

Операции с памятью достаточно быстрые, чтобы иметь в каждой строке своё уникальное смещение для каждого поля.

операции с памятью, особенно выделение — дорого
а еще дороже обходиться фрагментация и так далее
потому и идет оперирование именно страницами
к слову в большей части ОС размер страницы — 4 Кб
Это к слову касается и разработки вообще всего оптимального — было ряд статей в том числе и по golang как правильно делать структуры чтобы не перегружать аллокатор

к слову в большей части ОС размер страницы — 4 Кб

Так это от процессора/архитектуры идёт

в памяти зачем-то должны иметь регулярную структуру

промежуточные результаты mysql хранит в memory таблицах, которые могут иметь только фиксированный размер записей, если принимается решение сразу писать на диск (что не гуд), то там уже не fixed size.

dev.mysql.com/...​-temporary-tables-storage

кеширование запросов в Мускуле нужно отключить

согласен, толку с него никакого в нормальном продакшене (это про кеш запросов, а не Buffer Pool Cache — данные + индексы, он то чем больше, тем лучше)

Сортировки как правило выполняются на лету

чаще нет чем да, нужно чтоб сошлитсь все звёзды — в where только условия на точное сравнение, составной индекс покрывающий условия в where + поле(я) order by, а если запрос с джойнами то и таблицу по которой идёт сортировка оптимизатор должен на первое место поставить. Этого всего можно добиться, но чаще не нужно и так быстро работает, несмотря на сортировку в памяти, а не по индексу.

С индексом там вроде даже лимит есть

лимит 767 или 3K байт (не символов) в зависимости от формата innodb таблицы, но при попытке навесить на колонку(и) сверх этого лимита запрос с ошибкой упадёт. Можно явно указать длину префикса для индекса. Но это ж думать нужно, оценивать, что делают далеко не все. А тем временем размер индекса растёт, глубина и размер b-tree дерева увеличиваются что на перфомансе всей системы может потихоньку сказываться (вымываются полезные данные из кеша, растёт I/O).

Соглашусь с одним: НУЖНО, чтобы звёзды таки сошлись. А у кого звёзды из жопы растут — так и не подпускать на пушечный выстрел к запросам, пусть ангулярничают.

Я подивився на приклади запитів в GORM і вирішив, що писати та читати голий SQL буде комфорніше.

Нарешті про це хтось написав без остраху бути закатованим ))

То я про це також на DOU в коментарях зустрічав

У меня всегда было ощущение, что в Go-сообществе и так не очень то и принято пользоваться GORM и вообще ORM (чему я несказанно рад), или это ложное впечатление у меня было? :D

GORM как огня следует избегать) Там N+1 проблема и схему он генерирует ущербную.

О! Я готую доповідь про підходи розробки. І мені було б дуже цікаво побачити приклади таких проблем, незручностей, нестиковок.

N+1 проблема

gorm.io/docs/preload.html ?

схему он генерирует ущербную

а зачем отдавать на откуп генерацию схемы каким-то сторонним тулзам?
они разве индексы сами умеют расставлять на основе статистики выполняемых запросов, где допустимо подождать, где наоборот нужна максимальная производительность выборки.

а в GO можно как то получить AST выражения и по нему сгенерировать SQL?
получился бы такой себе LINQ на минималках...

Так было всегда, надоело спорить с об этих недоделках.
То может, то не может. Правильный запрос всегда от руки.
Имхо

Підписатись на коментарі