Compare · 001

Your first query: ClickHouse and PostgreSQL

Every file of the ClickHouse and PostgreSQL versions side by side, with the lines that differ marked. 3 parameter or column types differ in Go.

Compare ClickHouse withSQLite PostgreSQL CockroachDB TimescaleDB MySQL MariaDB DuckDB

Configuration

sgl.yamldiffers

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

ClickHouse
PostgreSQL
1CREATE TABLE tasks (
1CREATE TABLE tasks (
2    id         UInt64,
2    id         BIGSERIAL   PRIMARY KEY,
3    title      String,
3    title      TEXT        NOT NULL,
4    completed  UInt8    DEFAULT 0,
4    completed  BOOLEAN     NOT NULL DEFAULT false,
5    created_at DateTime DEFAULT now()
5    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
6) ENGINE = MergeTree() ORDER BY id;
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.sqlidentical

ClickHouse
PostgreSQL
1DROP TABLE tasks;
1DROP TABLE tasks;

Queries

CreateTaskdiffers

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

GetTaskonly ClickHouse

ClickHouse · db/queries/tasks.sql
PostgreSQL · not in this version
1-- sgl.query(GetTask, one)
2SELECT id, title, completed, created_at FROM tasks WHERE id = {id:UInt64};

ListTasksdiffers

ClickHouse · 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

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

What sgl generates

CreateTaskdiffers

ClickHouse
PostgreSQL
1func (queries *Queries) CreateTask(ctx context.Context, params CreateTaskParams) error
1func (queries *Queries) CreateTask(ctx context.Context, title string) (CreateTaskRow, error)
2
2
3type CreateTaskParams struct {
3type CreateTaskRow struct {
4	ID    uint64
4	ID        int64     `json:"id"`
5	Title string
5	Title     string    `json:"title"`
6	Completed bool      `json:"completed"`
7	CreatedAt time.Time `json:"created_at"`
6}
8}

GetTaskonly ClickHouse

ClickHouse
PostgreSQL · not in this version
1func (queries *Queries) GetTask(ctx context.Context, id uint64) (GetTaskRow, error)
2
3type GetTaskRow struct {
4	ID        uint64    `json:"id"`
5	Title     string    `json:"title"`
6	Completed uint8     `json:"completed"`
7	CreatedAt time.Time `json:"created_at"`
8}

ListTasksdiffers

ClickHouse
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        uint64    `json:"id"`
4	ID        int64     `json:"id"`
5	Title     string    `json:"title"`
5	Title     string    `json:"title"`
6	Completed uint8     `json:"completed"`
6	Completed bool      `json:"completed"`
7	CreatedAt time.Time `json:"created_at"`
7	CreatedAt time.Time `json:"created_at"`
8}
8}

CompleteTaskdiffers

ClickHouse
PostgreSQL
1func (queries *Queries) CompleteTask(ctx context.Context, id uint64) error
1func (queries *Queries) CompleteTask(ctx context.Context, id int64) (int64, error)

The program

example.godiffers

ClickHouse
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/clickhouse/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// ClickHouse has no auto-increment and no RETURNING, so the caller chooses each task's id
17// sgl.yaml sets go.doc_comments, so the comment above each query in tasks.sql becomes its
16// and reads a task back with GetTask. CompleteTask uses ALTER TABLE ... UPDATE, which
18// method's doc comment, and the migration's COMMENT ON TABLE and COMMENT ON COLUMN become
17// queues a mutation that the server applies in the background, so the query is declared
19// the doc comments of the Tasks model and its Completed field: open internal/store to see
18// asyncexec and returns only an error (concept 36 explains why). The connection sets
20// them, or hover over queries.CreateTask in an editor.
19// mutations_sync=2, which makes the server finish the mutation before it replies, so the
20// list below already shows the task completed.
21//
21//
22// 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.
23// Takes output (io.Writer) which receives the task list.
24// Takes output (io.Writer) which receives the task list.
24//
25//
25// Returns error when a query fails.
26// Returns error when a query fails.
26func run(ctx context.Context, database *sql.DB, output io.Writer) error {
27func run(ctx context.Context, database *sql.DB, output io.Writer) error {
27	queries := store.New(database)
28	queries := store.New(database)
28
29
29	tasks := []store.CreateTaskParams{
30	feed, err := queries.CreateTask(ctx, "Feed the seagulls")
30		{ID: 1, Title: "Feed the seagulls"},
31	if err != nil {
31		{ID: 2, Title: "Count the chips"},
32		return fmt.Errorf("creating a task: %w", err)
32	}
33	}
33	for _, task := range tasks {
34	if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
34		if err := queries.CreateTask(ctx, task); err != nil {
35		return fmt.Errorf("creating a task: %w", err)
35			return fmt.Errorf("creating task %d: %w", task.ID, err)
36		}
37	}
36	}
38
37
39	feed, err := queries.GetTask(ctx, 1)
38	completed, err := queries.CompleteTask(ctx, feed.ID)
40	if err != nil {
39	if err != nil {
41		return fmt.Errorf("getting task 1: %w", err)
42	}
43	fmt.Fprintf(output, "created task %d, %s\n", feed.ID, feed.Title)
44
45	if err := queries.CompleteTask(ctx, feed.ID); err != nil {
46		return fmt.Errorf("completing task %d: %w", feed.ID, err)
40		return fmt.Errorf("completing task %d: %w", feed.ID, err)
47	}
41	}
48	fmt.Fprintf(output, "completed task %d\n", feed.ID)
42	fmt.Fprintf(output, "completed %d task\n", completed)
49
43
50	listed, err := queries.ListTasks(ctx)
44	tasks, err := queries.ListTasks(ctx)
51	if err != nil {
45	if err != nil {
52		return fmt.Errorf("listing tasks: %w", err)
46		return fmt.Errorf("listing tasks: %w", err)
53	}
47	}
54	for _, task := range listed {
48	for _, task := range tasks {
55		fmt.Fprintf(output, "%d\t%-18s completed=%d\n", task.ID, task.Title, task.Completed)
49		fmt.Fprintf(output, "%d\t%-18s completed=%t\n", task.ID, task.Title, task.Completed)
56	}
50	}
57	return nil
51	return nil
58}
52}

Go type differences

The same SQL types map to different Go types, because each database reports them differently.

Query	Field	ClickHouse	PostgreSQL
ListTasks	column `id`	`uint64`	`int64`
ListTasks	column `completed`	`uint8`	`bool`
CompleteTask	parameter `id`	`uint64`	`int64`