Compare · 001

Your first query: DuckDB and PostgreSQL

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

Compare DuckDB withSQLite PostgreSQL CockroachDB TimescaleDB MySQL MariaDB ClickHouse

Configuration

sgl.yamldiffers

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

DuckDB
PostgreSQL
1CREATE SEQUENCE task_ids;
2
3CREATE TABLE tasks (
1CREATE TABLE tasks (
4    id         BIGINT    PRIMARY KEY DEFAULT nextval('task_ids'),
2    id         BIGSERIAL   PRIMARY KEY,
5    title      VARCHAR   NOT NULL,
3    title      TEXT        NOT NULL,
6    completed  BOOLEAN   NOT NULL DEFAULT false,
4    completed  BOOLEAN     NOT NULL DEFAULT false,
7    created_at TIMESTAMP NOT NULL DEFAULT current_timestamp
5    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
8);
6);
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

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

Queries

CreateTaskdiffers

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

ListTasksdiffers

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

CompleteTaskdiffers

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

What sgl generates

CreateTaskidentical

DuckDB
PostgreSQL
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

DuckDB
PostgreSQL
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

DuckDB
PostgreSQL
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
PostgreSQL
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/postgres/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. The id column is BIGSERIAL, so ID is int64, and
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.
14//
16//
15// DuckDB has no auto-incrementing INTEGER PRIMARY KEY, so the migration draws each id
17// sgl.yaml sets go.doc_comments, so the comment above each query in tasks.sql becomes its
16// from a sequence with DEFAULT nextval('task_ids'), and RETURNING hands it back.
18// method's doc comment, and the migration's COMMENT ON TABLE and COMMENT ON COLUMN become
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.
17//
21//
18// Takes database (*sql.DB) which is a migrated database.
22// Takes database (*sql.DB) which is a migrated database, opened with pgx's database/sql
23// driver.
19// Takes output (io.Writer) which receives the task list.
24// Takes output (io.Writer) which receives the task list.
20//
25//
21// Returns error when a query fails.
26// Returns error when a query fails.
22func run(ctx context.Context, database *sql.DB, output io.Writer) error {
27func run(ctx context.Context, database *sql.DB, output io.Writer) error {
23	queries := store.New(database)
28	queries := store.New(database)
24
29
25	feed, err := queries.CreateTask(ctx, "Feed the seagulls")
30	feed, err := queries.CreateTask(ctx, "Feed the seagulls")
26	if err != nil {
31	if err != nil {
27		return fmt.Errorf("creating a task: %w", err)
32		return fmt.Errorf("creating a task: %w", err)
28	}
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.ID)
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.ID, 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=%t\n", task.ID, task.Title, task.Completed)
45	}
50	}
46	return nil
51	return nil
47}
52}