Compare · 001

Your first query: SQLite and CockroachDB

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

Compare SQLite withPostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDBClickHouse

Configuration

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

Migrations

db/migrations/0001_init.up.sqldiffers
SQLite
CockroachDB
1CREATE TABLE tasks (
1CREATE TABLE tasks (
2 id INTEGER PRIMARY KEY,
2 id INT8 NOT NULL DEFAULT unique_rowid() PRIMARY KEY,
3 title TEXT NOT NULL,
3 title STRING NOT NULL,
4 completed INTEGER NOT NULL DEFAULT 0,
4 completed BOOL NOT NULL DEFAULT false,
5 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
5 created_at TIMESTAMPTZ NOT NULL DEFAULT now()
6);
6);
db/migrations/0001_init.down.sqlidentical
SQLite
CockroachDB
1DROP TABLE tasks;
1DROP TABLE tasks;

Queries

CreateTaskdiffers
SQLite · db/queries/tasks.sql
CockroachDB · db/queries/tasks.sql
1-- sgl.query(CreateTask, one)
1-- sgl.query(CreateTask, one)
2INSERT INTO tasks (title) VALUES (?) RETURNING id, title, completed, created_at;
2INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, completed, created_at;
ListTasksdiffers
SQLite · db/queries/tasks.sql
CockroachDB · 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 created_at, id;
CompleteTaskdiffers
SQLite · db/queries/tasks.sql
CockroachDB · db/queries/tasks.sql
1-- sgl.query(CompleteTask, execrows)
1-- sgl.query(CompleteTask, execrows)
2UPDATE tasks SET completed = 1 WHERE id = ?;
2UPDATE tasks SET completed = true WHERE id = $1;

What sgl generates

CreateTaskdiffers
SQLite
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 int64 `json:"completed"`
6 Completed bool `json:"completed"`
7 CreatedAt string `json:"created_at"`
7 CreatedAt time.Time `json:"created_at"`
8}
8}
ListTasksdiffers
SQLite
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 int64 `json:"completed"`
6 Completed bool `json:"completed"`
7 CreatedAt string `json:"created_at"`
7 CreatedAt time.Time `json:"created_at"`
8}
8}
CompleteTaskidentical
SQLite
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
SQLite
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/sqlite/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.
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//
15// Takes database (*sql.DB) which is a migrated database.
22// Takes database (*sql.DB) which is a migrated database.
16// Takes output (io.Writer) which receives the task list.
23// Takes output (io.Writer) which receives the task list.
17//
24//
18// Returns error when a query fails.
25// Returns error when a query fails.
19func run(ctx context.Context, database *sql.DB, output io.Writer) error {
26func run(ctx context.Context, database *sql.DB, output io.Writer) error {
20 queries := store.New(database)
27 queries := store.New(database)
21
28
22 feed, err := queries.CreateTask(ctx, "Feed the seagulls")
29 feed, err := queries.CreateTask(ctx, "Feed the seagulls")
23 if err != nil {
30 if err != nil {
24 return fmt.Errorf("creating a task: %w", err)
31 return fmt.Errorf("creating a task: %w", err)
25 }
32 }
26 if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
33 if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
27 return fmt.Errorf("creating a task: %w", err)
34 return fmt.Errorf("creating a task: %w", err)
28 }
35 }
29
36
30 completed, err := queries.CompleteTask(ctx, feed.ID)
37 completed, err := queries.CompleteTask(ctx, feed.ID)
31 if err != nil {
38 if err != nil {
32 return fmt.Errorf("completing task %d: %w", feed.ID, err)
39 return fmt.Errorf("completing task %d: %w", feed.ID, err)
33 }
40 }
34 fmt.Fprintf(output, "completed %d task\n", completed)
41 fmt.Fprintf(output, "completed %d task\n", completed)
35
42
36 tasks, err := queries.ListTasks(ctx)
43 tasks, err := queries.ListTasks(ctx)
37 if err != nil {
44 if err != nil {
38 return fmt.Errorf("listing tasks: %w", err)
45 return fmt.Errorf("listing tasks: %w", err)
39 }
46 }
40 for _, task := range tasks {
47 for _, task := range tasks {
41 fmt.Fprintf(output, "%d\t%-18s completed=%d\n", task.ID, task.Title, task.Completed)
48 fmt.Fprintf(output, "%-18s completed=%t\n", task.Title, task.Completed)
42 }
49 }
43 return nil
50 return nil
44}
51}

Go type differences

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

QueryFieldSQLiteCockroachDB
CreateTaskcolumn completedint64bool
CreateTaskcolumn created_atstringtime.Time
ListTaskscolumn completedint64bool
ListTaskscolumn created_atstringtime.Time