Compare · 001

Your first query: CockroachDB and SQLite

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

Compare CockroachDB withSQLite PostgreSQL TimescaleDB MySQL MariaDB DuckDB ClickHouse

Configuration

sgl.yamldiffers

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

CockroachDB
SQLite
1CREATE TABLE tasks (
1CREATE TABLE tasks (
2    id         INT8        NOT NULL DEFAULT unique_rowid() PRIMARY KEY,
2    id         INTEGER PRIMARY KEY,
3    title      STRING      NOT NULL,
3    title      TEXT    NOT NULL,
4    completed  BOOL        NOT NULL DEFAULT false,
4    completed  INTEGER NOT NULL DEFAULT 0,
5    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
5    created_at TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
6);
6);

db/migrations/0001_init.down.sqlidentical

CockroachDB
SQLite
1DROP TABLE tasks;
1DROP TABLE tasks;

Queries

CreateTaskdiffers

CockroachDB · db/queries/tasks.sql
SQLite · db/queries/tasks.sql
1-- sgl.query(CreateTask, one)
1-- sgl.query(CreateTask, one)
2INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, completed, created_at;
2INSERT INTO tasks (title) VALUES (?) RETURNING id, title, completed, created_at;

ListTasksdiffers

CockroachDB · 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 created_at, id;
2SELECT id, title, completed, created_at FROM tasks ORDER BY id;

CompleteTaskdiffers

CockroachDB · db/queries/tasks.sql
SQLite · db/queries/tasks.sql
1-- sgl.query(CompleteTask, execrows)
1-- sgl.query(CompleteTask, execrows)
2UPDATE tasks SET completed = true WHERE id = $1;
2UPDATE tasks SET completed = 1 WHERE id = ?;

What sgl generates

CreateTaskdiffers

CockroachDB
SQLite
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 int64  `json:"completed"`
7	CreatedAt time.Time `json:"created_at"`
7	CreatedAt string `json:"created_at"`
8}
8}

ListTasksdiffers

CockroachDB
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        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 int64  `json:"completed"`
7	CreatedAt time.Time `json:"created_at"`
7	CreatedAt string `json:"created_at"`
8}
8}

CompleteTaskidentical

CockroachDB
SQLite
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
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/cockroachdb/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// The code is what it would be on PostgreSQL; the differences are in the database.
16// CockroachDB fills id with unique_rowid(), a large int64 built from the time and the
17// node, so IDs are neither 1 and 2 nor the same from one run to the next, and the list
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//
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	feed, err := queries.CreateTask(ctx, "Feed the seagulls")
22	feed, err := queries.CreateTask(ctx, "Feed the seagulls")
30	if err != nil {
23	if err != nil {
31		return fmt.Errorf("creating a task: %w", err)
24		return fmt.Errorf("creating a task: %w", err)
32	}
25	}
33	if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
26	if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
34		return fmt.Errorf("creating a task: %w", err)
27		return fmt.Errorf("creating a task: %w", err)
35	}
28	}
36
29
37	completed, err := queries.CompleteTask(ctx, feed.ID)
30	completed, err := queries.CompleteTask(ctx, feed.ID)
38	if err != nil {
31	if err != nil {
39		return fmt.Errorf("completing task %d: %w", feed.ID, err)
32		return fmt.Errorf("completing task %d: %w", feed.ID, err)
40	}
33	}
41	fmt.Fprintf(output, "completed %d task\n", completed)
34	fmt.Fprintf(output, "completed %d task\n", completed)
42
35
43	tasks, err := queries.ListTasks(ctx)
36	tasks, err := queries.ListTasks(ctx)
44	if err != nil {
37	if err != nil {
45		return fmt.Errorf("listing tasks: %w", err)
38		return fmt.Errorf("listing tasks: %w", err)
46	}
39	}
47	for _, task := range tasks {
40	for _, task := range tasks {
48		fmt.Fprintf(output, "%-18s completed=%t\n", task.Title, task.Completed)
41		fmt.Fprintf(output, "%d\t%-18s completed=%d\n", task.ID, task.Title, task.Completed)
49	}
42	}
50	return nil
43	return nil
51}
44}

Go type differences

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

Query	Field	CockroachDB	SQLite
CreateTask	column `completed`	`bool`	`int64`
CreateTask	column `created_at`	`time.Time`	`string`
ListTasks	column `completed`	`bool`	`int64`
ListTasks	column `created_at`	`time.Time`	`string`