Your first query: DuckDB and CockroachDB
Every file of the DuckDB and CockroachDB versions side by side, with the lines that differ marked.
Configuration
sgl.yamldiffers
DuckDB
CockroachDB
1
version: 11
version: 12
2
3
databases:3
databases:4
- name: tasks4
- name: tasks5
dialect: duckdb5
dialect: cockroachdb6
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.sqldiffers
DuckDB
CockroachDB
1
CREATE SEQUENCE task_ids;2
3
CREATE TABLE tasks (1
CREATE TABLE tasks (4
id BIGINT PRIMARY KEY DEFAULT nextval('task_ids'),2
id INT8 NOT NULL DEFAULT unique_rowid() PRIMARY KEY,5
title VARCHAR NOT NULL,3
title STRING NOT NULL,6
completed BOOLEAN NOT NULL DEFAULT false,4
completed BOOL NOT NULL DEFAULT false,7
created_at TIMESTAMP NOT NULL DEFAULT current_timestamp5
created_at TIMESTAMPTZ NOT NULL DEFAULT now()8
);6
);db/migrations/0001_init.down.sqldiffers
DuckDB
CockroachDB
1
DROP TABLE tasks;1
DROP TABLE tasks;2
DROP SEQUENCE task_ids;Queries
CreateTaskidentical
DuckDB · db/queries/tasks.sql
CockroachDB · db/queries/tasks.sql
1
-- sgl.query(CreateTask, one)1
-- sgl.query(CreateTask, one)2
INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, completed, created_at;2
INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, completed, created_at;ListTasksdiffers
DuckDB · db/queries/tasks.sql
CockroachDB · 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 created_at, id;CompleteTaskidentical
DuckDB · db/queries/tasks.sql
CockroachDB · db/queries/tasks.sql
1
-- sgl.query(CompleteTask, execrows)1
-- sgl.query(CompleteTask, execrows)2
UPDATE tasks SET completed = true WHERE id = $1;2
UPDATE tasks SET completed = true WHERE id = $1;What sgl generates
CreateTaskidentical
DuckDB
CockroachDB
1
func (queries *Queries) CreateTask(ctx context.Context, title string) (CreateTaskRow, error)1
func (queries *Queries) CreateTask(ctx context.Context, title string) (CreateTaskRow, error)2
2
3
type CreateTaskRow struct {3
type CreateTaskRow 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 bool `json:"completed"`7
CreatedAt time.Time `json:"created_at"`7
CreatedAt time.Time `json:"created_at"`8
}8
}ListTasksidentical
DuckDB
CockroachDB
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 bool `json:"completed"`6
Completed bool `json:"completed"`7
CreatedAt time.Time `json:"created_at"`7
CreatedAt time.Time `json:"created_at"`8
}8
}CompleteTaskidentical
DuckDB
CockroachDB
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
DuckDB
CockroachDB
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/duckdb/001_first_query/internal/store"9
"seagull.sh/seagull/examples/cockroachdb/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
// DuckDB has no auto-incrementing INTEGER PRIMARY KEY, so the migration draws each id15
// The code is what it would be on PostgreSQL; the differences are in the database.16
// from a sequence with DEFAULT nextval('task_ids'), and RETURNING hands it back.16
// CockroachDB fills id with unique_rowid(), a large int64 built from the time and the17
// node, so IDs are neither 1 and 2 nor the same from one run to the next, and the list18
// prints titles instead. CockroachDB uses SERIALIZABLE isolation by default and may abort19
// one with SQLSTATE 40001 when it conflicts with another. The generated RunInTx does not20
// retry, so code that uses it on CockroachDB retries the whole function itself.17
//21
//18
// Takes database (*sql.DB) which is a migrated database.22
// Takes database (*sql.DB) which is a migrated database.19
// Takes output (io.Writer) which receives the task list.23
// Takes output (io.Writer) which receives the task list.20
//24
//21
// Returns error when a query fails.25
// Returns error when a query fails.22
func run(ctx context.Context, database *sql.DB, output io.Writer) error {26
func run(ctx context.Context, database *sql.DB, output io.Writer) error {23
queries := store.New(database)27
queries := store.New(database)24
28
25
feed, err := queries.CreateTask(ctx, "Feed the seagulls")29
feed, err := queries.CreateTask(ctx, "Feed the seagulls")26
if err != nil {30
if err != nil {27
return fmt.Errorf("creating a task: %w", err)31
return fmt.Errorf("creating a task: %w", err)28
}32
}29
if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {33
if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {30
return fmt.Errorf("creating a task: %w", err)34
return fmt.Errorf("creating a task: %w", err)31
}35
}32
36
33
completed, err := queries.CompleteTask(ctx, feed.ID)37
completed, err := queries.CompleteTask(ctx, feed.ID)34
if err != nil {38
if err != nil {35
return fmt.Errorf("completing task %d: %w", feed.ID, err)39
return fmt.Errorf("completing task %d: %w", feed.ID, err)36
}40
}37
fmt.Fprintf(output, "completed %d task\n", completed)41
fmt.Fprintf(output, "completed %d task\n", completed)38
42
39
tasks, err := queries.ListTasks(ctx)43
tasks, err := queries.ListTasks(ctx)40
if err != nil {44
if err != nil {41
return fmt.Errorf("listing tasks: %w", err)45
return fmt.Errorf("listing tasks: %w", err)42
}46
}43
for _, task := range tasks {47
for _, task := range tasks {44
fmt.Fprintf(output, "%d\t%-18s completed=%t\n", task.ID, task.Title, task.Completed)48
fmt.Fprintf(output, "%-18s completed=%t\n", task.Title, task.Completed)45
}49
}46
return nil50
return nil47
}51
}