Compare · 001

Your first query: ClickHouse and SQLite

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

Compare ClickHouse withSQLitePostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
ClickHouse
SQLite
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: tasks
4 - name: tasks
5 dialect: clickhouse
5 dialect: sqlite
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
ClickHouse
SQLite
1CREATE TABLE tasks (
1CREATE TABLE tasks (
2 id UInt64,
2 id INTEGER PRIMARY KEY,
3 title String,
3 title TEXT NOT NULL,
4 completed UInt8 DEFAULT 0,
4 completed INTEGER NOT NULL DEFAULT 0,
5 created_at DateTime DEFAULT now()
5 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
6) ENGINE = MergeTree() ORDER BY id;
6);
db/migrations/0001_init.down.sqlidentical
ClickHouse
SQLite
1DROP TABLE tasks;
1DROP TABLE tasks;

Queries

CreateTaskdiffers
ClickHouse · db/queries/tasks.sql
SQLite · db/queries/tasks.sql
1-- sgl.query(CreateTask, exec)
1-- sgl.query(CreateTask, one)
2INSERT INTO tasks (id, title) VALUES ({id:UInt64}, {title:String});
2INSERT INTO tasks (title) VALUES (?) RETURNING id, title, completed, created_at;
GetTaskonly ClickHouse
ClickHouse · db/queries/tasks.sql
SQLite · not in this version
1-- sgl.query(GetTask, one)
2SELECT id, title, completed, created_at FROM tasks WHERE id = {id:UInt64};
ListTasksidentical
ClickHouse · db/queries/tasks.sql
SQLite · db/queries/tasks.sql
1-- sgl.query(ListTasks, many)
1-- sgl.query(ListTasks, many)
2SELECT id, title, completed, created_at FROM tasks ORDER BY id;
2SELECT id, title, completed, created_at FROM tasks ORDER BY id;
CompleteTaskdiffers
ClickHouse · db/queries/tasks.sql
SQLite · db/queries/tasks.sql
1-- sgl.query(CompleteTask, asyncexec)
1-- sgl.query(CompleteTask, execrows)
2ALTER TABLE tasks UPDATE completed = 1 WHERE id = {id:UInt64};
2UPDATE tasks SET completed = 1 WHERE id = ?;

What sgl generates

CreateTaskdiffers
ClickHouse
SQLite
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 int64 `json:"completed"`
7 CreatedAt string `json:"created_at"`
6}
8}
GetTaskonly ClickHouse
ClickHouse
SQLite · 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
SQLite
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 int64 `json:"completed"`
7 CreatedAt time.Time `json:"created_at"`
7 CreatedAt string `json:"created_at"`
8}
8}
CompleteTaskdiffers
ClickHouse
SQLite
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
SQLite
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/sqlite/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// ClickHouse has no auto-increment and no RETURNING, so the caller chooses each task's id
16// and reads a task back with GetTask. CompleteTask uses ALTER TABLE ... UPDATE, which
17// queues a mutation that the server applies in the background, so the query is declared
18// asyncexec and returns only an error (concept 36 explains why). The connection sets
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//
22// Takes database (*sql.DB) which is a migrated database.
15// Takes database (*sql.DB) which is a migrated database.
23// Takes output (io.Writer) which receives the task list.
16// Takes output (io.Writer) which receives the task list.
24//
17//
25// Returns error when a query fails.
18// Returns error when a query fails.
26func run(ctx context.Context, database *sql.DB, output io.Writer) error {
19func run(ctx context.Context, database *sql.DB, output io.Writer) error {
27 queries := store.New(database)
20 queries := store.New(database)
28
21
29 tasks := []store.CreateTaskParams{
22 feed, err := queries.CreateTask(ctx, "Feed the seagulls")
30 {ID: 1, Title: "Feed the seagulls"},
23 if err != nil {
31 {ID: 2, Title: "Count the chips"},
24 return fmt.Errorf("creating a task: %w", err)
32 }
25 }
33 for _, task := range tasks {
26 if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
34 if err := queries.CreateTask(ctx, task); err != nil {
27 return fmt.Errorf("creating a task: %w", err)
35 return fmt.Errorf("creating task %d: %w", task.ID, err)
36 }
37 }
28 }
38
29
39 feed, err := queries.GetTask(ctx, 1)
30 completed, err := queries.CompleteTask(ctx, feed.ID)
40 if err != nil {
31 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)
32 return fmt.Errorf("completing task %d: %w", feed.ID, err)
47 }
33 }
48 fmt.Fprintf(output, "completed task %d\n", feed.ID)
34 fmt.Fprintf(output, "completed %d task\n", completed)
49
35
50 listed, err := queries.ListTasks(ctx)
36 tasks, err := queries.ListTasks(ctx)
51 if err != nil {
37 if err != nil {
52 return fmt.Errorf("listing tasks: %w", err)
38 return fmt.Errorf("listing tasks: %w", err)
53 }
39 }
54 for _, task := range listed {
40 for _, task := range tasks {
55 fmt.Fprintf(output, "%d\t%-18s completed=%d\n", task.ID, task.Title, task.Completed)
41 fmt.Fprintf(output, "%d\t%-18s completed=%d\n", task.ID, task.Title, task.Completed)
56 }
42 }
57 return nil
43 return nil
58}
44}

Go type differences

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

QueryFieldClickHouseSQLite
ListTaskscolumn iduint64int64
ListTaskscolumn completeduint8int64
ListTaskscolumn created_attime.Timestring
CompleteTaskparameter iduint64int64