Compare · 023

Transactions: SQLite and TimescaleDB

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

Compare SQLite withPostgreSQL CockroachDB TimescaleDB MySQL MariaDB DuckDB

Configuration

sgl.yamldiffers

SQLite
TimescaleDB
1version: 1
1version: 1
2
2
3databases:
3databases:
- name: bank
- name: bank
  dialect: sqlite
  dialect: postgres
  migrations: db/migrations
  migrations: db/migrations
  queries: db/queries
  queries: db/queries
  go:
  go:
    package: store
    package: store
    out: internal/store
    out: internal/store
    driver: database/sql
    interface:
      enabled: true
  migrate:
    dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_accounts.up.sqldiffers

SQLite
TimescaleDB
1CREATE TABLE accounts (
1CREATE TABLE accounts (
2    id      INTEGER PRIMARY KEY,
2    id      BIGINT  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3    owner   TEXT    NOT NULL,
3    owner   TEXT    NOT NULL,
4    balance INTEGER NOT NULL CHECK (balance >= 0)
4    balance INTEGER NOT NULL CHECK (balance >= 0)
5);
5);

db/migrations/0001_accounts.down.sqlidentical

SQLite
TimescaleDB
1DROP TABLE accounts;
1DROP TABLE accounts;

Queries

OpenAccountdiffers

SQLite · db/queries/accounts.sql
TimescaleDB · db/queries/accounts.sql
1-- sgl.query(OpenAccount, one)
1-- sgl.query(OpenAccount, one)
2INSERT INTO accounts (owner, balance) VALUES (?, ?) RETURNING id;
2INSERT INTO accounts (owner, balance) VALUES ($1, $2) RETURNING id;

Depositidentical

SQLite · db/queries/accounts.sql
TimescaleDB · db/queries/accounts.sql
1-- sgl.query(Deposit, exec)
1-- sgl.query(Deposit, exec)
2UPDATE accounts SET balance = balance + :amount WHERE id = :id;
2UPDATE accounts SET balance = balance + :amount WHERE id = :id;

Withdrawidentical

SQLite · db/queries/accounts.sql
TimescaleDB · db/queries/accounts.sql
1-- sgl.query(Withdraw, exec)
1-- sgl.query(Withdraw, exec)
2UPDATE accounts SET balance = balance - :amount WHERE id = :id;
2UPDATE accounts SET balance = balance - :amount WHERE id = :id;

ListAccountsidentical

SQLite · db/queries/accounts.sql
TimescaleDB · db/queries/accounts.sql
1-- sgl.query(ListAccounts, many)
1-- sgl.query(ListAccounts, many)
2SELECT id, owner, balance FROM accounts ORDER BY id;
2SELECT id, owner, balance FROM accounts ORDER BY id;

What sgl generates

OpenAccountdiffers

SQLite
TimescaleDB
1func (queries *Queries) OpenAccount(ctx context.Context, params OpenAccountParams) (OpenAccountRow, error)
1func (queries *Queries) OpenAccount(ctx context.Context, params OpenAccountParams) (OpenAccountRow, error)
2
2
3type OpenAccountParams struct {
3type OpenAccountParams struct {
4	Owner   string
4	Owner   string
5	Balance int64
5	Balance int32
6}
6}
7
7
8type OpenAccountRow struct {
8type OpenAccountRow struct {
9	ID int64 `json:"id"`
9	ID int64 `json:"id"`
10}
10}

Depositdiffers

SQLite
TimescaleDB
1func (queries *Queries) Deposit(ctx context.Context, params DepositParams) error
1func (queries *Queries) Deposit(ctx context.Context, params DepositParams) error
2
2
3type DepositParams struct {
3type DepositParams struct {
4	Amount int64
4	Amount int32
5	ID     int64
5	ID     int64
6}
6}

Withdrawdiffers

SQLite
TimescaleDB
1func (queries *Queries) Withdraw(ctx context.Context, params WithdrawParams) error
1func (queries *Queries) Withdraw(ctx context.Context, params WithdrawParams) error
2
2
3type WithdrawParams struct {
3type WithdrawParams struct {
4	Amount int64
4	Amount int32
5	ID     int64
5	ID     int64
6}
6}

ListAccountsdiffers

SQLite
TimescaleDB
1func (queries *Queries) ListAccounts(ctx context.Context) ([]ListAccountsRow, error)
1func (queries *Queries) ListAccounts(ctx context.Context) ([]ListAccountsRow, error)
2
2
3type ListAccountsRow struct {
3type ListAccountsRow struct {
4	ID      int64  `json:"id"`
4	ID      int64  `json:"id"`
5	Owner   string `json:"owner"`
5	Owner   string `json:"owner"`
6	Balance int64  `json:"balance"`
6	Balance int32  `json:"balance"`
7}
7}

The program

example.godiffers

SQLite
TimescaleDB
1package main
1package main
2
2
3import (
3import (
4	"context"
4	"context"
5	"database/sql"
5	"database/sql"
6	"errors"
6	"errors"
7	"fmt"
7	"fmt"
8	"io"
8	"io"
9
9
10	"seagull.sh/seagull/examples/sqlite/023_transactions/internal/store"
10	"seagull.sh/seagull/examples/postgres/023_transactions/internal/store"
11)
11)
12
12
13// sqlStateError is a server error that carries a SQLSTATE code, as pgx's *pgconn.PgError
14// does.
15type sqlStateError interface {
16	error
17
18	// SQLState returns the five-character SQLSTATE code.
19	SQLState() string
20}
21
13// run opens two accounts in a transaction it manages itself, then makes two transfers
22// run opens two accounts in a transaction it manages itself, then makes two transfers
14// with RunInTx: one commits, and one breaks the balance check part-way and rolls back.
23// with RunInTx: one commits, and one breaks the balance check part-way and rolls back.
15//
24//
25// sgl.yaml sets go.interface, so the generated package also declares store.Querier, an
26// interface of every query method, which *store.Queries implements whether it runs on the
27// database or inside a transaction. printBalances takes a store.Querier, so the same
28// function reads the committed balances and, inside each transfer, the balances the
29// transaction sees before it commits or rolls back; a test could pass it a fake instead.
30//
16// Takes database (*sql.DB) which is a migrated database.
31// Takes database (*sql.DB) which is a migrated database.
17// Takes output (io.Writer) which receives each step and the balances.
32// Takes output (io.Writer) which receives each step and the balances.
18//
33//
19// Returns error when a query fails other than as the example expects.
34// Returns error when a query fails other than as the example expects.
20func run(ctx context.Context, database *sql.DB, output io.Writer) error {
35func run(ctx context.Context, database *sql.DB, output io.Writer) error {
21	queries := store.New(database)
36	queries := store.New(database)
22
37
23	pip, gull, err := openAccounts(ctx, database, queries)
38	pip, gull, err := openAccounts(ctx, database, queries)
24	if err != nil {
39	if err != nil {
25		return fmt.Errorf("opening accounts: %w", err)
40		return fmt.Errorf("opening accounts: %w", err)
26	}
41	}
27	fmt.Fprintln(output, "opened accounts for Pip and Gull: committed")
42	fmt.Fprintln(output, "opened accounts for Pip and Gull: committed")
28	if err := printBalances(ctx, queries, output); err != nil {
43	if err := printBalances(ctx, queries, output, "balances"); err != nil {
29		return err
44		return err
30	}
45	}
31
46
32	if err := transfer(ctx, database, queries, pip, gull, 1500); err != nil {
47	if err := transfer(ctx, database, queries, output, pip, gull, 1500); err != nil {
33		return fmt.Errorf("first transfer: %w", err)
48		return fmt.Errorf("first transfer: %w", err)
34	}
49	}
35	fmt.Fprintln(output, "transferred 1500 from Pip to Gull: committed")
50	fmt.Fprintln(output, "transferred 1500 from Pip to Gull: committed")
36	if err := printBalances(ctx, queries, output); err != nil {
51	if err := printBalances(ctx, queries, output, "balances"); err != nil {
37		return err
52		return err
38	}
53	}
39
54
40	// Gull holds 3500, so this transfer credits Pip and then fails the CHECK constraint when
55	// Gull holds 3500, so this transfer credits Pip and then fails the CHECK constraint when
41	// it debits Gull. RunInTx rolls back, and the credit to Pip that had already run is
56	// it debits Gull. RunInTx rolls back, and the credit to Pip that had already run is
42	// undone with it.
57	// undone with it.
43	err = transfer(ctx, database, queries, gull, pip, 9000)
58	err = transfer(ctx, database, queries, output, gull, pip, 9000)
44	if err == nil {
59	if err == nil {
45		return errors.New("an overdrawing transfer committed")
60		return errors.New("an overdrawing transfer committed")
46	}
61	}
47	fmt.Fprintf(output, "transferred 9000 from Gull to Pip: rolled back (%v)\n", err)
62
48	return printBalances(ctx, queries, output)
63	// pgx reports a server error as a *pgconn.PgError, whose SQLState method gives the
64	// standard five-character code: 23514 for a failed CHECK constraint. Match on the code,
65	// not the message, as the wording differs between servers and versions.
66	failure, hasState := errors.AsType[sqlStateError](err)
67	if !hasState {
68		return fmt.Errorf("an overdrawing transfer failed without a SQLSTATE: %w", err)
69	}
70	fmt.Fprintf(output, "transferred 9000 from Gull to Pip: rolled back (SQLSTATE %s)\n", failure.SQLState())
71	return printBalances(ctx, queries, output, "balances")
49}
72}
50
73
51// openAccounts opens both accounts in one transaction that it begins and commits itself.
74// openAccounts opens both accounts in one transaction that it begins and commits itself.
52// WithTx returns a copy of queries bound to that transaction, which suits a transaction
75// WithTx returns a copy of queries bound to that transaction, which suits a transaction
53// begun with options or shared with code that does not use Seagull.
76// begun with options or shared with code that does not use Seagull.
54//
77//
55// Takes database (*sql.DB) which begins the transaction.
78// Takes database (*sql.DB) which begins the transaction.
56// Takes queries (*store.Queries) which are bound to it.
79// Takes queries (*store.Queries) which are bound to it.
57//
80//
58// Returns pipID (int64) which is Pip's account ID.
81// Returns pipID (int64) which is Pip's account ID.
59// Returns gullID (int64) which is Gull's account ID.
82// Returns gullID (int64) which is Gull's account ID.
60// Returns err (error) when an account cannot be opened or the commit fails.
83// Returns err (error) when an account cannot be opened or the commit fails.
61func openAccounts(ctx context.Context, database *sql.DB, queries *store.Queries) (pipID, gullID int64, err error) {
84func openAccounts(ctx context.Context, database *sql.DB, queries *store.Queries) (pipID, gullID int64, err error) {
62	transaction, err := database.BeginTx(ctx, nil)
85	transaction, err := database.BeginTx(ctx, nil)
63	if err != nil {
86	if err != nil {
64		return 0, 0, err
87		return 0, 0, err
65	}
88	}
66	// Rollback after a successful Commit does nothing, so deferring it covers every early
89	// Rollback after a successful Commit does nothing, so deferring it covers every early
67	// return.
90	// return.
68	defer func() { _ = transaction.Rollback() }()
91	defer func() { _ = transaction.Rollback() }()
69
92
70	inTransaction := queries.WithTx(transaction)
93	inTransaction := queries.WithTx(transaction)
71	pip, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Pip", Balance: 5000})
94	pip, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Pip", Balance: 5000})
72	if err != nil {
95	if err != nil {
73		return 0, 0, err
96		return 0, 0, err
74	}
97	}
75	gull, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Gull", Balance: 2000})
98	gull, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Gull", Balance: 2000})
76	if err != nil {
99	if err != nil {
77		return 0, 0, err
100		return 0, 0, err
78	}
101	}
79	return pip.ID, gull.ID, transaction.Commit()
102	return pip.ID, gull.ID, transaction.Commit()
80}
103}
81
104
82// transfer moves an amount between two accounts with RunInTx, which begins a transaction,
105// transfer moves an amount between two accounts with RunInTx, which begins a transaction,
83// passes fn a Queries bound to it, and commits when fn returns nil or rolls back when it
106// passes fn a Queries bound to it, and commits when fn returns nil or rolls back when it
84// returns an error.
107// returns an error. After the credit it prints the balances the transaction sees, through
108// the same printBalances the caller uses.
85//
109//
86// Takes database (*sql.DB) which begins the transaction.
110// Takes database (*sql.DB) which begins the transaction.
87// Takes queries (*store.Queries) which run the transfer.
111// Takes queries (*store.Queries) which run the transfer.
112// Takes output (io.Writer) which receives the balances inside the transaction.
88// Takes from (int64) which is the account to debit.
113// Takes from (int64) which is the account to debit.
89// Takes to (int64) which is the account to credit.
114// Takes to (int64) which is the account to credit.
90// Takes amount (int64) which is the sum to move, in pence.
115// Takes amount (int32) which is the sum to move, in pence.
91//
116//
92// Returns error when either update fails; the transaction is then rolled back.
117// Returns error when either update fails; the transaction is then rolled back.
93func transfer(ctx context.Context, database *sql.DB, queries *store.Queries, from, to, amount int64) error {
118func transfer(ctx context.Context, database *sql.DB, queries *store.Queries, output io.Writer, from, to int64, amount int32) error {
94	return queries.RunInTx(ctx, database, func(inTransaction *store.Queries) error {
119	return queries.RunInTx(ctx, database, func(inTransaction *store.Queries) error {
95		if err := inTransaction.Deposit(ctx, store.DepositParams{Amount: amount, ID: to}); err != nil {
120		if err := inTransaction.Deposit(ctx, store.DepositParams{Amount: amount, ID: to}); err != nil {
96			return err
121			return err
97		}
122		}
123		if err := printBalances(ctx, inTransaction, output, "inside the transaction"); err != nil {
124			return err
125		}
98		return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
126		return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
99	})
127	})
100}
128}
101
129
102// printBalances writes every account's balance on one line.
130// printBalances writes every account's balance on one line. It takes the generated
131// store.Querier rather than *store.Queries, so it reads through whatever it is given: the
132// queries on the database, the queries bound to a transaction, or a fake in a test.
103//
133//
104// Takes queries (*store.Queries) which read the accounts.
134// Takes queries (store.Querier) which read the accounts.
105// Takes output (io.Writer) which receives the line.
135// Takes output (io.Writer) which receives the line.
136// Takes label (string) which says whose view of the balances the line shows.
106//
137//
107// Returns error when the accounts cannot be listed.
138// Returns error when the accounts cannot be listed.
108func printBalances(ctx context.Context, queries *store.Queries, output io.Writer) error {
139func printBalances(ctx context.Context, queries store.Querier, output io.Writer, label string) error {
109	accounts, err := queries.ListAccounts(ctx)
140	accounts, err := queries.ListAccounts(ctx)
110	if err != nil {
141	if err != nil {
111		return fmt.Errorf("listing accounts: %w", err)
142		return fmt.Errorf("listing accounts: %w", err)
112	}
143	}
113	fmt.Fprint(output, "  balances:")
144	fmt.Fprintf(output, "  %s:", label)
114	for _, account := range accounts {
145	for _, account := range accounts {
115		fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
146		fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
116	}
147	}
117	fmt.Fprintln(output)
148	fmt.Fprintln(output)
118	return nil
149	return nil
119}
150}

Go type differences

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

Query	Field	SQLite	TimescaleDB
OpenAccount	parameter `balance`	`int64`	`int32`
Deposit	parameter `amount`	`int64`	`int32`
Withdraw	parameter `amount`	`int64`	`int32`
ListAccounts	column `balance`	`int64`	`int32`