Compare · 001

Your first query: TimescaleDB and PostgreSQL

TimescaleDB and PostgreSQL run the same files for this example: the SQL and the Go are identical.

Compare TimescaleDB withSQLitePostgreSQLCockroachDBMySQLMariaDBDuckDBClickHouse

Configuration

sgl.yamlidentical
TimescaleDB
PostgreSQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: tasks
4 - name: tasks
5 dialect: postgres
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
11 driver: database/sql
12 doc_comments: true
12 doc_comments: true
13 migrate:
13 migrate:
14 dsn: ${env:DATABASE_URL}
14 dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_init.up.sqlidentical
TimescaleDB
PostgreSQL
1CREATE TABLE tasks (
1CREATE TABLE tasks (
2 id BIGSERIAL PRIMARY KEY,
2 id BIGSERIAL PRIMARY KEY,
3 title TEXT NOT NULL,
3 title TEXT NOT NULL,
4 completed BOOLEAN 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
7
8COMMENT ON TABLE tasks IS 'Jobs to do around the harbour.';
8COMMENT ON TABLE tasks IS 'Jobs to do around the harbour.';
9COMMENT ON COLUMN tasks.completed IS 'Whether the job is done.';
9COMMENT ON COLUMN tasks.completed IS 'Whether the job is done.';
db/migrations/0001_init.down.sqlidentical
TimescaleDB
PostgreSQL
1DROP TABLE tasks;
1DROP TABLE tasks;

Queries

CreateTaskidentical
TimescaleDB · db/queries/tasks.sql
PostgreSQL · db/queries/tasks.sql
1-- CreateTask adds a task and returns it with the id and creation time the database
1-- CreateTask adds a task and returns it with the id and creation time the database
2-- gave it.
2-- gave it.
3-- sgl.query(CreateTask, one)
3-- sgl.query(CreateTask, one)
4INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, completed, created_at;
4INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, completed, created_at;
ListTasksidentical
TimescaleDB · db/queries/tasks.sql
PostgreSQL · db/queries/tasks.sql
1-- ListTasks returns every task, oldest first.
1-- ListTasks returns every task, oldest first.
2-- sgl.query(ListTasks, many)
2-- sgl.query(ListTasks, many)
3SELECT id, title, completed, created_at FROM tasks ORDER BY id;
3SELECT id, title, completed, created_at FROM tasks ORDER BY id;
CompleteTaskidentical
TimescaleDB · db/queries/tasks.sql
PostgreSQL · db/queries/tasks.sql
1-- CompleteTask marks a task done and returns how many tasks it changed.
1-- CompleteTask marks a task done and returns how many tasks it changed.
2-- sgl.query(CompleteTask, execrows)
2-- sgl.query(CompleteTask, execrows)
3UPDATE tasks SET completed = true WHERE id = $1;
3UPDATE tasks SET completed = true WHERE id = $1;

What sgl generates

CreateTaskidentical
TimescaleDB
PostgreSQL
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
TimescaleDB
PostgreSQL
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
TimescaleDB
PostgreSQL
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.goidentical
TimescaleDB
PostgreSQL
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/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. The id column is BIGSERIAL, so ID is int64, and
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
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.
15// the output, as it changes on every run.
16//
16//
17// sgl.yaml sets go.doc_comments, so the comment above each query in tasks.sql becomes its
17// sgl.yaml sets go.doc_comments, so the comment above each query in tasks.sql becomes its
18// method's doc comment, and the migration's COMMENT ON TABLE and COMMENT ON COLUMN become
18// method's doc comment, and the migration's COMMENT ON TABLE and COMMENT ON COLUMN become
19// the doc comments of the Tasks model and its Completed field: open internal/store to see
19// the doc comments of the Tasks model and its Completed field: open internal/store to see
20// them, or hover over queries.CreateTask in an editor.
20// them, or hover over queries.CreateTask in an editor.
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, opened with pgx's database/sql
23// driver.
23// driver.
24// Takes output (io.Writer) which receives the task list.
24// Takes output (io.Writer) which receives the task list.
25//
25//
26// Returns error when a query fails.
26// Returns error when a query fails.
27func run(ctx context.Context, database *sql.DB, output io.Writer) error {
27func run(ctx context.Context, database *sql.DB, output io.Writer) error {
28 queries := store.New(database)
28 queries := store.New(database)
29
29
30 feed, err := queries.CreateTask(ctx, "Feed the seagulls")
30 feed, err := queries.CreateTask(ctx, "Feed the seagulls")
31 if err != nil {
31 if err != nil {
32 return fmt.Errorf("creating a task: %w", err)
32 return fmt.Errorf("creating a task: %w", err)
33 }
33 }
34 if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
34 if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
35 return fmt.Errorf("creating a task: %w", err)
35 return fmt.Errorf("creating a task: %w", err)
36 }
36 }
37
37
38 completed, err := queries.CompleteTask(ctx, feed.ID)
38 completed, err := queries.CompleteTask(ctx, feed.ID)
39 if err != nil {
39 if err != nil {
40 return fmt.Errorf("completing task %d: %w", feed.ID, err)
40 return fmt.Errorf("completing task %d: %w", feed.ID, err)
41 }
41 }
42 fmt.Fprintf(output, "completed %d task\n", completed)
42 fmt.Fprintf(output, "completed %d task\n", completed)
43
43
44 tasks, err := queries.ListTasks(ctx)
44 tasks, err := queries.ListTasks(ctx)
45 if err != nil {
45 if err != nil {
46 return fmt.Errorf("listing tasks: %w", err)
46 return fmt.Errorf("listing tasks: %w", err)
47 }
47 }
48 for _, task := range tasks {
48 for _, task := range tasks {
49 fmt.Fprintf(output, "%d\t%-18s completed=%t\n", task.ID, task.Title, task.Completed)
49 fmt.Fprintf(output, "%d\t%-18s completed=%t\n", task.ID, task.Title, task.Completed)
50 }
50 }
51 return nil
51 return nil
52}
52}