Compare · 001

Your first query: DuckDB and MySQL

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

Compare DuckDB withSQLitePostgreSQLCockroachDBTimescaleDBMySQLMariaDBClickHouse

Configuration

sgl.yamldiffers
DuckDB
MySQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: tasks
4 - name: tasks
5 dialect: duckdb
5 dialect: mysql
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
DuckDB
MySQL
1CREATE SEQUENCE task_ids;
2
3CREATE TABLE tasks (
1CREATE TABLE tasks (
4 id BIGINT PRIMARY KEY DEFAULT nextval('task_ids'),
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
5 title VARCHAR NOT NULL,
3 title VARCHAR(200) NOT NULL,
6 completed BOOLEAN NOT NULL DEFAULT false,
4 completed INT NOT NULL DEFAULT 0,
7 created_at TIMESTAMP NOT NULL DEFAULT current_timestamp
5 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
8);
6);
db/migrations/0001_init.down.sqldiffers
DuckDB
MySQL
1DROP TABLE tasks;
1DROP TABLE tasks;
2DROP SEQUENCE task_ids;

Queries

CreateTaskdiffers
DuckDB · db/queries/tasks.sql
MySQL · db/queries/tasks.sql
1-- sgl.query(CreateTask, one)
1-- sgl.query(CreateTask, execresult)
2INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, completed, created_at;
2INSERT INTO tasks (title) VALUES (?);
ListTasksidentical
DuckDB · db/queries/tasks.sql
MySQL · 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
DuckDB · db/queries/tasks.sql
MySQL · db/queries/tasks.sql
1-- sgl.query(CompleteTask, execrows)
1-- sgl.query(CompleteTask, execrows)
2UPDATE tasks SET completed = true WHERE id = $1;
2UPDATE tasks SET completed = 1 WHERE id = ?;

What sgl generates

CreateTaskdiffers
DuckDB
MySQL
1func (queries *Queries) CreateTask(ctx context.Context, title string) (CreateTaskRow, error)
1func (queries *Queries) CreateTask(ctx context.Context, title string) (sql.Result, 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
DuckDB
MySQL
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 bool `json:"completed"`
6 Completed int32 `json:"completed"`
7 CreatedAt time.Time `json:"created_at"`
7 CreatedAt time.Time `json:"created_at"`
8}
8}
CompleteTaskidentical
DuckDB
MySQL
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
DuckDB
MySQL
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/duckdb/001_first_query/internal/store"
9 "seagull.sh/seagull/examples/mysql/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// DuckDB has no auto-incrementing INTEGER PRIMARY KEY, so the migration draws each id
15// MySQL has no RETURNING, so CreateTask is execresult: it returns the driver's
16// from a sequence with DEFAULT nextval('task_ids'), and RETURNING hands it back.
16// sql.Result, and LastInsertId gives the AUTO_INCREMENT id MySQL assigned. The id column
17// is BIGINT, so it is an int64 like LastInsertId's result and needs no conversion.
17//
18//
18// Takes database (*sql.DB) which is a migrated database.
19// Takes database (*sql.DB) which is a migrated database.
19// Takes output (io.Writer) which receives the task list.
20// Takes output (io.Writer) which receives the task list.
20//
21//
21// Returns error when a query fails.
22// Returns error when a query fails.
22func run(ctx context.Context, database *sql.DB, output io.Writer) error {
23func run(ctx context.Context, database *sql.DB, output io.Writer) error {
23 queries := store.New(database)
24 queries := store.New(database)
24
25
25 feed, err := queries.CreateTask(ctx, "Feed the seagulls")
26 result, err := queries.CreateTask(ctx, "Feed the seagulls")
26 if err != nil {
27 if err != nil {
27 return fmt.Errorf("creating a task: %w", err)
28 return fmt.Errorf("creating a task: %w", err)
28 }
29 }
30 feed, err := result.LastInsertId()
31 if err != nil {
32 return fmt.Errorf("reading the new task's id: %w", err)
33 }
29 if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
34 if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
30 return fmt.Errorf("creating a task: %w", err)
35 return fmt.Errorf("creating a task: %w", err)
31 }
36 }
32
37
33 completed, err := queries.CompleteTask(ctx, feed.ID)
38 completed, err := queries.CompleteTask(ctx, feed)
34 if err != nil {
39 if err != nil {
35 return fmt.Errorf("completing task %d: %w", feed.ID, err)
40 return fmt.Errorf("completing task %d: %w", feed, err)
36 }
41 }
37 fmt.Fprintf(output, "completed %d task\n", completed)
42 fmt.Fprintf(output, "completed %d task\n", completed)
38
43
39 tasks, err := queries.ListTasks(ctx)
44 tasks, err := queries.ListTasks(ctx)
40 if err != nil {
45 if err != nil {
41 return fmt.Errorf("listing tasks: %w", err)
46 return fmt.Errorf("listing tasks: %w", err)
42 }
47 }
43 for _, task := range tasks {
48 for _, task := range tasks {
44 fmt.Fprintf(output, "%d\t%-18s completed=%t\n", task.ID, task.Title, task.Completed)
49 fmt.Fprintf(output, "%d\t%-18s completed=%d\n", task.ID, task.Title, task.Completed)
45 }
50 }
46 return nil
51 return nil
47}
52}

Go type differences

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

QueryFieldDuckDBMySQL
ListTaskscolumn completedboolint32