Your first query: MariaDB and MySQL
Every file of the MariaDB and MySQL versions side by side, with the lines that differ marked.
Configuration
sgl.yamldiffers
MariaDB
MySQL
1
version: 11
version: 12
2
3
databases:3
databases:4
- name: tasks4
- name: tasks5
dialect: mariadb5
dialect: mysql6
migrations: db/migrations6
migrations: db/migrations7
queries: db/queries7
queries: db/queries8
go:8
go:9
package: store9
package: store10
out: internal/store10
out: internal/storeMigrations
db/migrations/0001_init.up.sqlidentical
MariaDB
MySQL
1
CREATE TABLE tasks (1
CREATE TABLE tasks (2
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,2
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,3
title VARCHAR(200) NOT NULL,3
title VARCHAR(200) NOT NULL,4
completed INT NOT NULL DEFAULT 0,4
completed INT NOT NULL DEFAULT 0,5
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP5
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP6
);6
);db/migrations/0001_init.down.sqlidentical
MariaDB
MySQL
1
DROP TABLE tasks;1
DROP TABLE tasks;Queries
CreateTaskdiffers
MariaDB · db/queries/tasks.sql
MySQL · db/queries/tasks.sql
1
-- sgl.query(CreateTask, one)1
-- sgl.query(CreateTask, execresult)2
INSERT INTO tasks (title) VALUES (?) RETURNING id, title, completed, created_at;2
INSERT INTO tasks (title) VALUES (?);ListTasksidentical
MariaDB · db/queries/tasks.sql
MySQL · db/queries/tasks.sql
1
-- sgl.query(ListTasks, many)1
-- sgl.query(ListTasks, many)2
SELECT id, title, completed, created_at FROM tasks ORDER BY id;2
SELECT id, title, completed, created_at FROM tasks ORDER BY id;CompleteTaskidentical
MariaDB · db/queries/tasks.sql
MySQL · db/queries/tasks.sql
1
-- sgl.query(CompleteTask, execrows)1
-- sgl.query(CompleteTask, execrows)2
UPDATE tasks SET completed = 1 WHERE id = ?;2
UPDATE tasks SET completed = 1 WHERE id = ?;What sgl generates
CreateTaskdiffers
MariaDB
MySQL
1
func (queries *Queries) CreateTask(ctx context.Context, title string) (CreateTaskRow, error)1
func (queries *Queries) CreateTask(ctx context.Context, title string) (sql.Result, error)2
3
type CreateTaskRow struct {4
ID int64 `json:"id"`5
Title string `json:"title"`6
Completed int32 `json:"completed"`7
CreatedAt time.Time `json:"created_at"`8
}ListTasksidentical
MariaDB
MySQL
1
func (queries *Queries) ListTasks(ctx context.Context) ([]ListTasksRow, error)1
func (queries *Queries) ListTasks(ctx context.Context) ([]ListTasksRow, error)2
2
3
type ListTasksRow struct {3
type 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 int32 `json:"completed"`7
CreatedAt time.Time `json:"created_at"`7
CreatedAt time.Time `json:"created_at"`8
}8
}CompleteTaskidentical
MariaDB
MySQL
1
func (queries *Queries) CompleteTask(ctx context.Context, id int64) (int64, error)1
func (queries *Queries) CompleteTask(ctx context.Context, id int64) (int64, error)The program
example.godiffers
MariaDB
MySQL
1
package main1
package main2
2
3
import (3
import (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/mariadb/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 sgl12
// run adds two tasks, completes the first and lists them all, through the methods sgl13
// generated from db/queries/tasks.sql.13
// generated from db/queries/tasks.sql.14
//14
//15
// MariaDB, unlike MySQL, has INSERT ... RETURNING, so CreateTask is a one query that15
// MySQL has no RETURNING, so CreateTask is execresult: it returns the driver's16
// hands back the new row, the AUTO_INCREMENT id and the column defaults included.16
// sql.Result, and LastInsertId gives the AUTO_INCREMENT id MySQL assigned. The id column17
// 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.22
func run(ctx context.Context, database *sql.DB, output io.Writer) error {23
func 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=%d\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 nil51
return nil47
}52
}