Compare · 001

Your first query: PostgreSQL and CockroachDB

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

Compare PostgreSQL withSQLite CockroachDB TimescaleDB MySQL MariaDB DuckDB ClickHouse

Configuration

sgl.yamldiffers

PostgreSQL
CockroachDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: tasks
4  - name: tasks
5    dialect: postgres
5    dialect: cockroachdb
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
CockroachDB
1CREATE TABLE tasks (
1CREATE TABLE tasks (
2    id         BIGSERIAL   PRIMARY KEY,
2    id         INT8        NOT NULL DEFAULT unique_rowid() PRIMARY KEY,
3    title      TEXT        NOT NULL,
3    title      STRING      NOT NULL,
4    completed  BOOLEAN     NOT NULL DEFAULT false,
4    completed  BOOL        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

PostgreSQL
CockroachDB
1DROP TABLE tasks;
1DROP TABLE tasks;

Queries

CreateTaskdiffers

PostgreSQL · db/queries/tasks.sql
CockroachDB · 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
CockroachDB · 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 created_at, id;

CompleteTaskdiffers

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