Compare · 001

Your first query: MySQL and DuckDB

Every file of the MySQL and DuckDB versions side by side, with the lines that differ marked. 1 parameter or column types differ in Go.

Compare MySQL withSQLite PostgreSQL CockroachDB TimescaleDB MariaDB DuckDB ClickHouse

Configuration

sgl.yamldiffers

MySQL
DuckDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: tasks
4  - name: tasks
5    dialect: mysql
5    dialect: duckdb
6    migrations: db/migrations
6    migrations: db/migrations
7    queries: db/queries
7    queries: db/queries
8    go:
8    go:
9      package: store
9      package: store
10      out: internal/store
10      out: internal/store

Migrations

db/migrations/0001_init.up.sqldiffers

MySQL
DuckDB
1CREATE SEQUENCE task_ids;
2
1CREATE TABLE tasks (
3CREATE TABLE tasks (
2    id         BIGINT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
4    id         BIGINT    PRIMARY KEY DEFAULT nextval('task_ids'),
3    title      VARCHAR(200) NOT NULL,
5    title      VARCHAR   NOT NULL,
4    completed  INT          NOT NULL DEFAULT 0,
6    completed  BOOLEAN   NOT NULL DEFAULT false,
5    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
7    created_at TIMESTAMP NOT NULL DEFAULT current_timestamp
6);
8);

db/migrations/0001_init.down.sqldiffers

MySQL
DuckDB
1DROP TABLE tasks;
1DROP TABLE tasks;
2DROP SEQUENCE task_ids;

Queries

CreateTaskdiffers

MySQL · db/queries/tasks.sql
DuckDB · db/queries/tasks.sql
1-- sgl.query(CreateTask, execresult)
1-- sgl.query(CreateTask, one)
2INSERT INTO tasks (title) VALUES (?);
2INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, completed, created_at;

ListTasksidentical

MySQL · db/queries/tasks.sql
DuckDB · db/queries/tasks.sql
1-- sgl.query(ListTasks, many)
1-- sgl.query(ListTasks, many)
2SELECT id, title, completed, created_at FROM tasks ORDER BY id;
2SELECT id, title, completed, created_at FROM tasks ORDER BY id;

CompleteTaskdiffers

MySQL · db/queries/tasks.sql
DuckDB · db/queries/tasks.sql
1-- sgl.query(CompleteTask, execrows)
1-- sgl.query(CompleteTask, execrows)
2UPDATE tasks SET completed = 1 WHERE id = ?;
2UPDATE tasks SET completed = true WHERE id = $1;

What sgl generates

CreateTaskdiffers

MySQL
DuckDB
1func (queries *Queries) CreateTask(ctx context.Context, title string) (sql.Result, error)
1func (queries *Queries) CreateTask(ctx context.Context, title string) (CreateTaskRow, error)
2
3type CreateTaskRow struct {
4	ID        int64     `json:"id"`
5	Title     string    `json:"title"`
6	Completed bool      `json:"completed"`
7	CreatedAt time.Time `json:"created_at"`
8}

ListTasksdiffers

MySQL
DuckDB
1func (queries *Queries) ListTasks(ctx context.Context) ([]ListTasksRow, error)
1func (queries *Queries) ListTasks(ctx context.Context) ([]ListTasksRow, error)
2
2
3type ListTasksRow struct {
3type ListTasksRow struct {
4	ID        int64     `json:"id"`
4	ID        int64     `json:"id"`
5	Title     string    `json:"title"`
5	Title     string    `json:"title"`
6	Completed int32     `json:"completed"`
6	Completed bool      `json:"completed"`
7	CreatedAt time.Time `json:"created_at"`
7	CreatedAt time.Time `json:"created_at"`
8}
8}

CompleteTaskidentical

MySQL
DuckDB
1func (queries *Queries) CompleteTask(ctx context.Context, id int64) (int64, error)
1func (queries *Queries) CompleteTask(ctx context.Context, id int64) (int64, error)

The program

example.godiffers

MySQL
DuckDB
1package main
1package main
2
2
3import (
3import (
4	"context"
4	"context"
5	"database/sql"
5	"database/sql"
6	"fmt"
6	"fmt"
7	"io"
7	"io"
8
8
9	"seagull.sh/seagull/examples/mysql/001_first_query/internal/store"
9	"seagull.sh/seagull/examples/duckdb/001_first_query/internal/store"
10)
10)
11
11
12// run adds two tasks, completes the first and lists them all, through the methods sgl
12// run adds two tasks, completes the first and lists them all, through the methods sgl
13// generated from db/queries/tasks.sql.
13// generated from db/queries/tasks.sql.
14//
14//
15// MySQL has no RETURNING, so CreateTask is execresult: it returns the driver's
15// DuckDB has no auto-incrementing INTEGER PRIMARY KEY, so the migration draws each id
16// sql.Result, and LastInsertId gives the AUTO_INCREMENT id MySQL assigned. The id column
16// from a sequence with DEFAULT nextval('task_ids'), and RETURNING hands it back.
17// is BIGINT, so it is an int64 like LastInsertId's result and needs no conversion.
18//
17//
19// Takes database (*sql.DB) which is a migrated database.
18// Takes database (*sql.DB) which is a migrated database.
20// Takes output (io.Writer) which receives the task list.
19// Takes output (io.Writer) which receives the task list.
21//
20//
22// Returns error when a query fails.
21// Returns error when a query fails.
23func run(ctx context.Context, database *sql.DB, output io.Writer) error {
22func run(ctx context.Context, database *sql.DB, output io.Writer) error {
24	queries := store.New(database)
23	queries := store.New(database)
25
24
26	result, err := queries.CreateTask(ctx, "Feed the seagulls")
25	feed, err := queries.CreateTask(ctx, "Feed the seagulls")
27	if err != nil {
26	if err != nil {
28		return fmt.Errorf("creating a task: %w", err)
27		return fmt.Errorf("creating a task: %w", err)
29	}
28	}
30	feed, err := result.LastInsertId()
31	if err != nil {
32		return fmt.Errorf("reading the new task's id: %w", err)
33	}
34	if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
29	if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
35		return fmt.Errorf("creating a task: %w", err)
30		return fmt.Errorf("creating a task: %w", err)
36	}
31	}
37
32
38	completed, err := queries.CompleteTask(ctx, feed)
33	completed, err := queries.CompleteTask(ctx, feed.ID)
39	if err != nil {
34	if err != nil {
40		return fmt.Errorf("completing task %d: %w", feed, err)
35		return fmt.Errorf("completing task %d: %w", feed.ID, err)
41	}
36	}
42	fmt.Fprintf(output, "completed %d task\n", completed)
37	fmt.Fprintf(output, "completed %d task\n", completed)
43
38
44	tasks, err := queries.ListTasks(ctx)
39	tasks, err := queries.ListTasks(ctx)
45	if err != nil {
40	if err != nil {
46		return fmt.Errorf("listing tasks: %w", err)
41		return fmt.Errorf("listing tasks: %w", err)
47	}
42	}
48	for _, task := range tasks {
43	for _, task := range tasks {
49		fmt.Fprintf(output, "%d\t%-18s completed=%d\n", task.ID, task.Title, task.Completed)
44		fmt.Fprintf(output, "%d\t%-18s completed=%t\n", task.ID, task.Title, task.Completed)
50	}
45	}
51	return nil
46	return nil
52}
47}

Go type differences

The same SQL types map to different Go types, because each database reports them differently.

Query	Field	MySQL	DuckDB
ListTasks	column `completed`	`int32`	`bool`