Compare · 001

Your first query: CockroachDB and DuckDB

Every file of the CockroachDB and DuckDB versions side by side, with the lines that differ marked.

Compare CockroachDB withSQLite PostgreSQL TimescaleDB MySQL MariaDB DuckDB ClickHouse

Configuration

sgl.yamldiffers

CockroachDB
DuckDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: tasks
4  - name: tasks
5    dialect: cockroachdb
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

CockroachDB
DuckDB
1CREATE SEQUENCE task_ids;
2
1CREATE TABLE tasks (
3CREATE TABLE tasks (
2    id         INT8        NOT NULL DEFAULT unique_rowid() PRIMARY KEY,
4    id         BIGINT    PRIMARY KEY DEFAULT nextval('task_ids'),
3    title      STRING      NOT NULL,
5    title      VARCHAR   NOT NULL,
4    completed  BOOL        NOT NULL DEFAULT false,
6    completed  BOOLEAN   NOT NULL DEFAULT false,
5    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
7    created_at TIMESTAMP NOT NULL DEFAULT current_timestamp
6);
8);

db/migrations/0001_init.down.sqldiffers

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

Queries

CreateTaskidentical

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

ListTasksdiffers

CockroachDB · 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 created_at, id;
2SELECT id, title, completed, created_at FROM tasks ORDER BY id;

CompleteTaskidentical

CockroachDB · db/queries/tasks.sql
DuckDB · 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 = true WHERE id = $1;

What sgl generates

CreateTaskidentical

CockroachDB
DuckDB
1func (queries *Queries) CreateTask(ctx context.Context, title string) (CreateTaskRow, error)
1func (queries *Queries) CreateTask(ctx context.Context, title string) (CreateTaskRow, error)
2
2
3type CreateTaskRow struct {
3type 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

CockroachDB
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 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

CockroachDB
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

CockroachDB
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/cockroachdb/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// The code is what it would be on PostgreSQL; the differences are in the database.
15// DuckDB has no auto-incrementing INTEGER PRIMARY KEY, so the migration draws each id
16// CockroachDB fills id with unique_rowid(), a large int64 built from the time and the
16// from a sequence with DEFAULT nextval('task_ids'), and RETURNING hands it back.
17// node, so IDs are neither 1 and 2 nor the same from one run to the next, and the list
18// prints titles instead. CockroachDB uses SERIALIZABLE isolation by default and may abort
19// one with SQLSTATE 40001 when it conflicts with another. The generated RunInTx does not
20// retry, so code that uses it on CockroachDB retries the whole function itself.
21//
17//
22// Takes database (*sql.DB) which is a migrated database.
18// Takes database (*sql.DB) which is a migrated database.
23// Takes output (io.Writer) which receives the task list.
19// Takes output (io.Writer) which receives the task list.
24//
20//
25// Returns error when a query fails.
21// Returns error when a query fails.
26func run(ctx context.Context, database *sql.DB, output io.Writer) error {
22func run(ctx context.Context, database *sql.DB, output io.Writer) error {
27	queries := store.New(database)
23	queries := store.New(database)
28
24
29	feed, err := queries.CreateTask(ctx, "Feed the seagulls")
25	feed, err := queries.CreateTask(ctx, "Feed the seagulls")
30	if err != nil {
26	if err != nil {
31		return fmt.Errorf("creating a task: %w", err)
27		return fmt.Errorf("creating a task: %w", err)
32	}
28	}
33	if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
29	if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
34		return fmt.Errorf("creating a task: %w", err)
30		return fmt.Errorf("creating a task: %w", err)
35	}
31	}
36
32
37	completed, err := queries.CompleteTask(ctx, feed.ID)
33	completed, err := queries.CompleteTask(ctx, feed.ID)
38	if err != nil {
34	if err != nil {
39		return fmt.Errorf("completing task %d: %w", feed.ID, err)
35		return fmt.Errorf("completing task %d: %w", feed.ID, err)
40	}
36	}
41	fmt.Fprintf(output, "completed %d task\n", completed)
37	fmt.Fprintf(output, "completed %d task\n", completed)
42
38
43	tasks, err := queries.ListTasks(ctx)
39	tasks, err := queries.ListTasks(ctx)
44	if err != nil {
40	if err != nil {
45		return fmt.Errorf("listing tasks: %w", err)
41		return fmt.Errorf("listing tasks: %w", err)
46	}
42	}
47	for _, task := range tasks {
43	for _, task := range tasks {
48		fmt.Fprintf(output, "%-18s completed=%t\n", task.Title, task.Completed)
44		fmt.Fprintf(output, "%d\t%-18s completed=%t\n", task.ID, task.Title, task.Completed)
49	}
45	}
50	return nil
46	return nil
51}
47}