Compare · 001

Your first query: CockroachDB and TimescaleDB

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

Compare CockroachDB withSQLite PostgreSQL TimescaleDB MySQL MariaDB DuckDB ClickHouse

Configuration

sgl.yamldiffers

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

CockroachDB
TimescaleDB
1CREATE TABLE tasks (
1CREATE TABLE tasks (
2    id         INT8        NOT NULL DEFAULT unique_rowid() PRIMARY KEY,
2    id         BIGSERIAL   PRIMARY KEY,
3    title      STRING      NOT NULL,
3    title      TEXT        NOT NULL,
4    completed  BOOL        NOT NULL DEFAULT false,
4    completed  BOOLEAN     NOT NULL DEFAULT false,
5    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
5    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
6);
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

CockroachDB
TimescaleDB
1DROP TABLE tasks;
1DROP TABLE tasks;

Queries

CreateTaskdiffers

CockroachDB · db/queries/tasks.sql
TimescaleDB · 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

CockroachDB · db/queries/tasks.sql
TimescaleDB · 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 created_at, id;
3SELECT id, title, completed, created_at FROM tasks ORDER BY id;

CompleteTaskdiffers

CockroachDB · db/queries/tasks.sql
TimescaleDB · 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

CockroachDB
TimescaleDB
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
TimescaleDB
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
TimescaleDB
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
TimescaleDB
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/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// The code is what it would be on PostgreSQL; the differences are in the database.
17// sgl.yaml sets go.doc_comments, so the comment above each query in tasks.sql becomes its
16// CockroachDB fills id with unique_rowid(), a large int64 built from the time and the
18// method's doc comment, and the migration's COMMENT ON TABLE and COMMENT ON COLUMN become
17// node, so IDs are neither 1 and 2 nor the same from one run to the next, and the list
19// the doc comments of the Tasks model and its Completed field: open internal/store to see
18// prints titles instead. CockroachDB uses SERIALIZABLE isolation by default and may abort
20// them, or hover over queries.CreateTask in an editor.
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//
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	feed, err := queries.CreateTask(ctx, "Feed the seagulls")
30	feed, err := queries.CreateTask(ctx, "Feed the seagulls")
30	if err != nil {
31	if err != nil {
31		return fmt.Errorf("creating a task: %w", err)
32		return fmt.Errorf("creating a task: %w", err)
32	}
33	}
33	if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
34	if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
34		return fmt.Errorf("creating a task: %w", err)
35		return fmt.Errorf("creating a task: %w", err)
35	}
36	}
36
37
37	completed, err := queries.CompleteTask(ctx, feed.ID)
38	completed, err := queries.CompleteTask(ctx, feed.ID)
38	if err != nil {
39	if err != nil {
39		return fmt.Errorf("completing task %d: %w", feed.ID, err)
40		return fmt.Errorf("completing task %d: %w", feed.ID, err)
40	}
41	}
41	fmt.Fprintf(output, "completed %d task\n", completed)
42	fmt.Fprintf(output, "completed %d task\n", completed)
42
43
43	tasks, err := queries.ListTasks(ctx)
44	tasks, err := queries.ListTasks(ctx)
44	if err != nil {
45	if err != nil {
45		return fmt.Errorf("listing tasks: %w", err)
46		return fmt.Errorf("listing tasks: %w", err)
46	}
47	}
47	for _, task := range tasks {
48	for _, task := range tasks {
48		fmt.Fprintf(output, "%-18s completed=%t\n", task.Title, task.Completed)
49		fmt.Fprintf(output, "%d\t%-18s completed=%t\n", task.ID, task.Title, task.Completed)
49	}
50	}
50	return nil
51	return nil
51}
52}