Compare · 023

Transactions: PostgreSQL and TimescaleDB

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

Compare PostgreSQL withSQLiteCockroachDBTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamlidentical
PostgreSQL
TimescaleDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: bank
4 - name: bank
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 interface:
12 interface:
13 enabled: true
13 enabled: true
14 migrate:
14 migrate:
15 dsn: ${env:DATABASE_URL}
15 dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_accounts.up.sqlidentical
PostgreSQL
TimescaleDB
1CREATE TABLE accounts (
1CREATE TABLE accounts (
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY 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
PostgreSQL
TimescaleDB
1DROP TABLE accounts;
1DROP TABLE accounts;

Queries

OpenAccountidentical
PostgreSQL · 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 ($1, $2) RETURNING id;
2INSERT INTO accounts (owner, balance) VALUES ($1, $2) RETURNING id;
Depositidentical
PostgreSQL · 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
PostgreSQL · 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
PostgreSQL · 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

OpenAccountidentical
PostgreSQL
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 int32
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}
Depositidentical
PostgreSQL
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 int32
4 Amount int32
5 ID int64
5 ID int64
6}
6}
Withdrawidentical
PostgreSQL
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 int32
4 Amount int32
5 ID int64
5 ID int64
6}
6}
ListAccountsidentical
PostgreSQL
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 int32 `json:"balance"`
6 Balance int32 `json:"balance"`
7}
7}

The program

example.goidentical
PostgreSQL
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/postgres/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
13// sqlStateError is a server error that carries a SQLSTATE code, as pgx's *pgconn.PgError
14// does.
14// does.
15type sqlStateError interface {
15type sqlStateError interface {
16 error
16 error
17
17
18 // SQLState returns the five-character SQLSTATE code.
18 // SQLState returns the five-character SQLSTATE code.
19 SQLState() string
19 SQLState() string
20}
20}
21
21
22// 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
23// 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.
24//
24//
25// sgl.yaml sets go.interface, so the generated package also declares store.Querier, an
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
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
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
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.
29// transaction sees before it commits or rolls back; a test could pass it a fake instead.
30//
30//
31// Takes database (*sql.DB) which is a migrated database.
31// Takes database (*sql.DB) which is a migrated database.
32// Takes output (io.Writer) which receives each step and the balances.
32// Takes output (io.Writer) which receives each step and the balances.
33//
33//
34// 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.
35func run(ctx context.Context, database *sql.DB, output io.Writer) error {
35func run(ctx context.Context, database *sql.DB, output io.Writer) error {
36 queries := store.New(database)
36 queries := store.New(database)
37
37
38 pip, gull, err := openAccounts(ctx, database, queries)
38 pip, gull, err := openAccounts(ctx, database, queries)
39 if err != nil {
39 if err != nil {
40 return fmt.Errorf("opening accounts: %w", err)
40 return fmt.Errorf("opening accounts: %w", err)
41 }
41 }
42 fmt.Fprintln(output, "opened accounts for Pip and Gull: committed")
42 fmt.Fprintln(output, "opened accounts for Pip and Gull: committed")
43 if err := printBalances(ctx, queries, output, "balances"); err != nil {
43 if err := printBalances(ctx, queries, output, "balances"); err != nil {
44 return err
44 return err
45 }
45 }
46
46
47 if err := transfer(ctx, database, queries, output, pip, gull, 1500); err != nil {
47 if err := transfer(ctx, database, queries, output, pip, gull, 1500); err != nil {
48 return fmt.Errorf("first transfer: %w", err)
48 return fmt.Errorf("first transfer: %w", err)
49 }
49 }
50 fmt.Fprintln(output, "transferred 1500 from Pip to Gull: committed")
50 fmt.Fprintln(output, "transferred 1500 from Pip to Gull: committed")
51 if err := printBalances(ctx, queries, output, "balances"); err != nil {
51 if err := printBalances(ctx, queries, output, "balances"); err != nil {
52 return err
52 return err
53 }
53 }
54
54
55 // 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
56 // 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
57 // undone with it.
57 // undone with it.
58 err = transfer(ctx, database, queries, output, gull, pip, 9000)
58 err = transfer(ctx, database, queries, output, gull, pip, 9000)
59 if err == nil {
59 if err == nil {
60 return errors.New("an overdrawing transfer committed")
60 return errors.New("an overdrawing transfer committed")
61 }
61 }
62
62
63 // pgx reports a server error as a *pgconn.PgError, whose SQLState method gives the
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,
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.
65 // not the message, as the wording differs between servers and versions.
66 failure, hasState := errors.AsType[sqlStateError](err)
66 failure, hasState := errors.AsType[sqlStateError](err)
67 if !hasState {
67 if !hasState {
68 return fmt.Errorf("an overdrawing transfer failed without a SQLSTATE: %w", err)
68 return fmt.Errorf("an overdrawing transfer failed without a SQLSTATE: %w", err)
69 }
69 }
70 fmt.Fprintf(output, "transferred 9000 from Gull to Pip: rolled back (SQLSTATE %s)\n", failure.SQLState())
70 fmt.Fprintf(output, "transferred 9000 from Gull to Pip: rolled back (SQLSTATE %s)\n", failure.SQLState())
71 return printBalances(ctx, queries, output, "balances")
71 return printBalances(ctx, queries, output, "balances")
72}
72}
73
73
74// 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.
75// 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
76// 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.
77//
77//
78// Takes database (*sql.DB) which begins the transaction.
78// Takes database (*sql.DB) which begins the transaction.
79// Takes queries (*store.Queries) which are bound to it.
79// Takes queries (*store.Queries) which are bound to it.
80//
80//
81// Returns pipID (int64) which is Pip's account ID.
81// Returns pipID (int64) which is Pip's account ID.
82// Returns gullID (int64) which is Gull's account ID.
82// Returns gullID (int64) which is Gull's account ID.
83// 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.
84func 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) {
85 transaction, err := database.BeginTx(ctx, nil)
85 transaction, err := database.BeginTx(ctx, nil)
86 if err != nil {
86 if err != nil {
87 return 0, 0, err
87 return 0, 0, err
88 }
88 }
89 // 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
90 // return.
90 // return.
91 defer func() { _ = transaction.Rollback() }()
91 defer func() { _ = transaction.Rollback() }()
92
92
93 inTransaction := queries.WithTx(transaction)
93 inTransaction := queries.WithTx(transaction)
94 pip, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Pip", Balance: 5000})
94 pip, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Pip", Balance: 5000})
95 if err != nil {
95 if err != nil {
96 return 0, 0, err
96 return 0, 0, err
97 }
97 }
98 gull, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Gull", Balance: 2000})
98 gull, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Gull", Balance: 2000})
99 if err != nil {
99 if err != nil {
100 return 0, 0, err
100 return 0, 0, err
101 }
101 }
102 return pip.ID, gull.ID, transaction.Commit()
102 return pip.ID, gull.ID, transaction.Commit()
103}
103}
104
104
105// 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,
106// 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
107// returns an error. After the credit it prints the balances the transaction sees, through
107// returns an error. After the credit it prints the balances the transaction sees, through
108// the same printBalances the caller uses.
108// the same printBalances the caller uses.
109//
109//
110// Takes database (*sql.DB) which begins the transaction.
110// Takes database (*sql.DB) which begins the transaction.
111// 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.
112// Takes output (io.Writer) which receives the balances inside the transaction.
113// Takes from (int64) which is the account to debit.
113// Takes from (int64) which is the account to debit.
114// Takes to (int64) which is the account to credit.
114// Takes to (int64) which is the account to credit.
115// Takes amount (int32) which is the sum to move, in pence.
115// Takes amount (int32) which is the sum to move, in pence.
116//
116//
117// 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.
118func transfer(ctx context.Context, database *sql.DB, queries *store.Queries, output io.Writer, from, to int64, amount int32) error {
118func transfer(ctx context.Context, database *sql.DB, queries *store.Queries, output io.Writer, from, to int64, amount int32) error {
119 return queries.RunInTx(ctx, database, func(inTransaction *store.Queries) error {
119 return queries.RunInTx(ctx, database, func(inTransaction *store.Queries) error {
120 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 {
121 return err
121 return err
122 }
122 }
123 if err := printBalances(ctx, inTransaction, output, "inside the transaction"); err != nil {
123 if err := printBalances(ctx, inTransaction, output, "inside the transaction"); err != nil {
124 return err
124 return err
125 }
125 }
126 return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
126 return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
127 })
127 })
128}
128}
129
129
130// printBalances writes every account's balance on one line. It takes the generated
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
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.
132// queries on the database, the queries bound to a transaction, or a fake in a test.
133//
133//
134// Takes queries (store.Querier) which read the accounts.
134// Takes queries (store.Querier) which read the accounts.
135// 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.
136// Takes label (string) which says whose view of the balances the line shows.
137//
137//
138// Returns error when the accounts cannot be listed.
138// Returns error when the accounts cannot be listed.
139func printBalances(ctx context.Context, queries store.Querier, output io.Writer, label string) error {
139func printBalances(ctx context.Context, queries store.Querier, output io.Writer, label string) error {
140 accounts, err := queries.ListAccounts(ctx)
140 accounts, err := queries.ListAccounts(ctx)
141 if err != nil {
141 if err != nil {
142 return fmt.Errorf("listing accounts: %w", err)
142 return fmt.Errorf("listing accounts: %w", err)
143 }
143 }
144 fmt.Fprintf(output, " %s:", label)
144 fmt.Fprintf(output, " %s:", label)
145 for _, account := range accounts {
145 for _, account := range accounts {
146 fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
146 fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
147 }
147 }
148 fmt.Fprintln(output)
148 fmt.Fprintln(output)
149 return nil
149 return nil
150}
150}