Compare · 001

Your first query: PostgreSQL and DuckDB

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

Compare PostgreSQL withSQLite CockroachDB TimescaleDB MySQL MariaDB DuckDB ClickHouse

Configuration

sgl.yamldiffers

PostgreSQL
DuckDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: tasks
4  - name: tasks
5    dialect: postgres
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
11      driver: database/sql
12      doc_comments: true
13    migrate:
14      dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_init.up.sqldiffers

PostgreSQL
DuckDB
1CREATE SEQUENCE task_ids;
2
1CREATE TABLE tasks (
3CREATE TABLE tasks (
2    id         BIGSERIAL   PRIMARY KEY,
4    id         BIGINT    PRIMARY KEY DEFAULT nextval('task_ids'),
3    title      TEXT        NOT NULL,
5    title      VARCHAR   NOT NULL,
4    completed  BOOLEAN     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);
7
8COMMENT ON TABLE tasks IS 'Jobs to do around the harbour.';
9COMMENT ON COLUMN tasks.completed IS 'Whether the job is done.';

db/migrations/0001_init.down.sqldiffers

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

Queries

CreateTaskdiffers

PostgreSQL · db/queries/tasks.sql
DuckDB · db/queries/tasks.sql
1-- CreateTask adds a task and returns it with the id and creation time the database
2-- gave it.
3-- sgl.query(CreateTask, one)
1-- sgl.query(CreateTask, one)
4INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, completed, created_at;
2INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, completed, created_at;

ListTasksdiffers

PostgreSQL · db/queries/tasks.sql
DuckDB · db/queries/tasks.sql
1-- ListTasks returns every task, oldest first.
2-- sgl.query(ListTasks, many)
1-- sgl.query(ListTasks, many)
3SELECT id, title, completed, created_at FROM tasks ORDER BY id;
2SELECT id, title, completed, created_at FROM tasks ORDER BY id;

CompleteTaskdiffers

PostgreSQL · db/queries/tasks.sql
DuckDB · db/queries/tasks.sql
1-- CompleteTask marks a task done and returns how many tasks it changed.
2-- sgl.query(CompleteTask, execrows)
1-- sgl.query(CompleteTask, execrows)
3UPDATE tasks SET completed = true WHERE id = $1;
2UPDATE tasks SET completed = true WHERE id = $1;

What sgl generates

CreateTaskidentical

PostgreSQL
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

PostgreSQL
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

PostgreSQL
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

PostgreSQL
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/postgres/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. The id column is BIGSERIAL, so ID is int64, and
13// generated from db/queries/tasks.sql.
14// created_at is TIMESTAMPTZ, so CreatedAt is a time.Time; the example leaves it out of
15// the output, as it changes on every run.
16//
14//
17// sgl.yaml sets go.doc_comments, so the comment above each query in tasks.sql becomes its
15// DuckDB has no auto-incrementing INTEGER PRIMARY KEY, so the migration draws each id
18// method's doc comment, and the migration's COMMENT ON TABLE and COMMENT ON COLUMN become
16// from a sequence with DEFAULT nextval('task_ids'), and RETURNING hands it back.
19// the doc comments of the Tasks model and its Completed field: open internal/store to see
20// them, or hover over queries.CreateTask in an editor.
21//
17//
22// Takes database (*sql.DB) which is a migrated database, opened with pgx's database/sql
18// Takes database (*sql.DB) which is a migrated database.
23// driver.
24// Takes output (io.Writer) which receives the task list.
19// Takes output (io.Writer) which receives the task list.
25//
20//
26// Returns error when a query fails.
21// Returns error when a query fails.
27func run(ctx context.Context, database *sql.DB, output io.Writer) error {
22func run(ctx context.Context, database *sql.DB, output io.Writer) error {
28	queries := store.New(database)
23	queries := store.New(database)
29
24
30	feed, err := queries.CreateTask(ctx, "Feed the seagulls")
25	feed, err := queries.CreateTask(ctx, "Feed the seagulls")
31	if err != nil {
26	if err != nil {
32		return fmt.Errorf("creating a task: %w", err)
27		return fmt.Errorf("creating a task: %w", err)
33	}
28	}
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.ID)
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.ID, 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=%t\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}