Compare · 023

Transactions: MariaDB and MySQL

MariaDB and MySQL run the same files for this example: the SQL and the Go are identical.

Compare MariaDB withSQLitePostgreSQLCockroachDBTimescaleDBMySQLDuckDB

Configuration

sgl.yamlidentical
MariaDB
MySQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: bank
4 - name: bank
5 dialect: mysql
5 dialect: mysql
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_accounts.up.sqlidentical
MariaDB
MySQL
1CREATE TABLE accounts (
1CREATE TABLE accounts (
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3 owner VARCHAR(50) NOT NULL,
3 owner VARCHAR(50) NOT NULL,
4 balance INT NOT NULL,
4 balance INT NOT NULL,
5 CONSTRAINT balance_not_negative CHECK (balance >= 0)
5 CONSTRAINT balance_not_negative CHECK (balance >= 0)
6);
6);
db/migrations/0001_accounts.down.sqlidentical
MariaDB
MySQL
1DROP TABLE accounts;
1DROP TABLE accounts;

Queries

OpenAccountidentical
MariaDB · db/queries/accounts.sql
MySQL · db/queries/accounts.sql
1-- sgl.query(OpenAccount, execresult)
1-- sgl.query(OpenAccount, execresult)
2INSERT INTO accounts (owner, balance) VALUES (?, ?);
2INSERT INTO accounts (owner, balance) VALUES (?, ?);
Depositidentical
MariaDB · db/queries/accounts.sql
MySQL · 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
MariaDB · db/queries/accounts.sql
MySQL · 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
MariaDB · db/queries/accounts.sql
MySQL · 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
MariaDB
MySQL
1func (queries *Queries) OpenAccount(ctx context.Context, params OpenAccountParams) (sql.Result, error)
1func (queries *Queries) OpenAccount(ctx context.Context, params OpenAccountParams) (sql.Result, error)
2
2
3type OpenAccountParams struct {
3type OpenAccountParams struct {
4 Owner string
4 Owner string
5 Balance int32
5 Balance int32
6}
6}
Depositidentical
MariaDB
MySQL
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
MariaDB
MySQL
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
MariaDB
MySQL
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
MariaDB
MySQL
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 "strings"
9 "strings"
10
10
11 "seagull.sh/seagull/examples/mysql/023_transactions/internal/store"
11 "seagull.sh/seagull/examples/mysql/023_transactions/internal/store"
12)
12)
13
13
14// run opens two accounts in a transaction it manages itself, then makes two transfers
14// run opens two accounts in a transaction it manages itself, then makes two transfers
15// with RunInTx: one commits, and one breaks the balance check part-way and rolls back.
15// with RunInTx: one commits, and one breaks the balance check part-way and rolls back.
16//
16//
17// Takes database (*sql.DB) which is a migrated database.
17// Takes database (*sql.DB) which is a migrated database.
18// Takes output (io.Writer) which receives each step and the balances.
18// Takes output (io.Writer) which receives each step and the balances.
19//
19//
20// Returns error when a query fails other than as the example expects.
20// Returns error when a query fails other than as the example expects.
21func run(ctx context.Context, database *sql.DB, output io.Writer) error {
21func run(ctx context.Context, database *sql.DB, output io.Writer) error {
22 queries := store.New(database)
22 queries := store.New(database)
23
23
24 pip, gull, err := openAccounts(ctx, database, queries)
24 pip, gull, err := openAccounts(ctx, database, queries)
25 if err != nil {
25 if err != nil {
26 return fmt.Errorf("opening accounts: %w", err)
26 return fmt.Errorf("opening accounts: %w", err)
27 }
27 }
28 fmt.Fprintln(output, "opened accounts for Pip and Gull: committed")
28 fmt.Fprintln(output, "opened accounts for Pip and Gull: committed")
29 if err := printBalances(ctx, queries, output); err != nil {
29 if err := printBalances(ctx, queries, output); err != nil {
30 return err
30 return err
31 }
31 }
32
32
33 if err := transfer(ctx, database, queries, pip, gull, 1500); err != nil {
33 if err := transfer(ctx, database, queries, pip, gull, 1500); err != nil {
34 return fmt.Errorf("first transfer: %w", err)
34 return fmt.Errorf("first transfer: %w", err)
35 }
35 }
36 fmt.Fprintln(output, "transferred 1500 from Pip to Gull: committed")
36 fmt.Fprintln(output, "transferred 1500 from Pip to Gull: committed")
37 if err := printBalances(ctx, queries, output); err != nil {
37 if err := printBalances(ctx, queries, output); err != nil {
38 return err
38 return err
39 }
39 }
40
40
41 // Gull holds 3500, so this transfer credits Pip and then fails the CHECK constraint when
41 // Gull holds 3500, so this transfer credits Pip and then fails the CHECK constraint when
42 // it debits Gull. RunInTx rolls back, and the credit to Pip that had already run is
42 // it debits Gull. RunInTx rolls back, and the credit to Pip that had already run is
43 // undone with it. MySQL reports the failure as error 3819, naming the constraint.
43 // undone with it. MySQL reports the failure as error 3819, naming the constraint.
44 // MariaDB words it differently and names the database too, so the example checks for the
44 // MariaDB words it differently and names the database too, so the example checks for the
45 // constraint's name and prints its own line.
45 // constraint's name and prints its own line.
46 err = transfer(ctx, database, queries, gull, pip, 9000)
46 err = transfer(ctx, database, queries, gull, pip, 9000)
47 if err == nil {
47 if err == nil {
48 return errors.New("an overdrawing transfer committed")
48 return errors.New("an overdrawing transfer committed")
49 }
49 }
50 if !strings.Contains(err.Error(), "balance_not_negative") {
50 if !strings.Contains(err.Error(), "balance_not_negative") {
51 return fmt.Errorf("second transfer failed other than at the balance check: %w", err)
51 return fmt.Errorf("second transfer failed other than at the balance check: %w", err)
52 }
52 }
53 fmt.Fprintln(output, "transferred 9000 from Gull to Pip: rolled back, as the debit broke balance_not_negative")
53 fmt.Fprintln(output, "transferred 9000 from Gull to Pip: rolled back, as the debit broke balance_not_negative")
54 return printBalances(ctx, queries, output)
54 return printBalances(ctx, queries, output)
55}
55}
56
56
57// openAccounts opens both accounts in one transaction that it begins and commits itself.
57// openAccounts opens both accounts in one transaction that it begins and commits itself.
58// WithTx returns a copy of queries bound to that transaction, which suits a transaction
58// WithTx returns a copy of queries bound to that transaction, which suits a transaction
59// begun with options or shared with code that does not use Seagull.
59// begun with options or shared with code that does not use Seagull.
60//
60//
61// Takes database (*sql.DB) which begins the transaction.
61// Takes database (*sql.DB) which begins the transaction.
62// Takes queries (*store.Queries) which are bound to it.
62// Takes queries (*store.Queries) which are bound to it.
63//
63//
64// Returns pipID (int64) which is Pip's account ID.
64// Returns pipID (int64) which is Pip's account ID.
65// Returns gullID (int64) which is Gull's account ID.
65// Returns gullID (int64) which is Gull's account ID.
66// Returns err (error) when an account cannot be opened or the commit fails.
66// Returns err (error) when an account cannot be opened or the commit fails.
67func openAccounts(ctx context.Context, database *sql.DB, queries *store.Queries) (pipID, gullID int64, err error) {
67func openAccounts(ctx context.Context, database *sql.DB, queries *store.Queries) (pipID, gullID int64, err error) {
68 transaction, err := database.BeginTx(ctx, nil)
68 transaction, err := database.BeginTx(ctx, nil)
69 if err != nil {
69 if err != nil {
70 return 0, 0, err
70 return 0, 0, err
71 }
71 }
72 // Rollback after a successful Commit does nothing, so deferring it covers every early
72 // Rollback after a successful Commit does nothing, so deferring it covers every early
73 // return.
73 // return.
74 defer func() { _ = transaction.Rollback() }()
74 defer func() { _ = transaction.Rollback() }()
75
75
76 inTransaction := queries.WithTx(transaction)
76 inTransaction := queries.WithTx(transaction)
77 pip, err := openAccount(ctx, inTransaction, "Pip", 5000)
77 pip, err := openAccount(ctx, inTransaction, "Pip", 5000)
78 if err != nil {
78 if err != nil {
79 return 0, 0, err
79 return 0, 0, err
80 }
80 }
81 gull, err := openAccount(ctx, inTransaction, "Gull", 2000)
81 gull, err := openAccount(ctx, inTransaction, "Gull", 2000)
82 if err != nil {
82 if err != nil {
83 return 0, 0, err
83 return 0, 0, err
84 }
84 }
85 return pip, gull, transaction.Commit()
85 return pip, gull, transaction.Commit()
86}
86}
87
87
88// openAccount opens an account and returns the id MySQL gave it. MySQL has no RETURNING,
88// openAccount opens an account and returns the id MySQL gave it. MySQL has no RETURNING,
89// so OpenAccount is execresult, and LastInsertId reads the AUTO_INCREMENT id from the
89// so OpenAccount is execresult, and LastInsertId reads the AUTO_INCREMENT id from the
90// result of the INSERT, on the transaction's own connection.
90// result of the INSERT, on the transaction's own connection.
91//
91//
92// Takes queries (*store.Queries) which run the INSERT.
92// Takes queries (*store.Queries) which run the INSERT.
93// Takes owner (string) which names the account holder.
93// Takes owner (string) which names the account holder.
94// Takes balance (int32) which is the opening balance, in pence.
94// Takes balance (int32) which is the opening balance, in pence.
95//
95//
96// Returns int64 which is the new account's ID.
96// Returns int64 which is the new account's ID.
97// Returns error when the INSERT fails.
97// Returns error when the INSERT fails.
98func openAccount(ctx context.Context, queries *store.Queries, owner string, balance int32) (int64, error) {
98func openAccount(ctx context.Context, queries *store.Queries, owner string, balance int32) (int64, error) {
99 result, err := queries.OpenAccount(ctx, store.OpenAccountParams{Owner: owner, Balance: balance})
99 result, err := queries.OpenAccount(ctx, store.OpenAccountParams{Owner: owner, Balance: balance})
100 if err != nil {
100 if err != nil {
101 return 0, err
101 return 0, err
102 }
102 }
103 return result.LastInsertId()
103 return result.LastInsertId()
104}
104}
105
105
106// transfer moves an amount between two accounts with RunInTx, which begins a transaction,
106// transfer moves an amount between two accounts with RunInTx, which begins a transaction,
107// passes fn a Queries bound to it, and commits when fn returns nil or rolls back when it
107// passes fn a Queries bound to it, and commits when fn returns nil or rolls back when it
108// returns an error.
108// returns an error.
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 from (int64) which is the account to debit.
112// Takes from (int64) which is the account to debit.
113// Takes to (int64) which is the account to credit.
113// Takes to (int64) which is the account to credit.
114// Takes amount (int32) which is the sum to move, in pence.
114// Takes amount (int32) which is the sum to move, in pence.
115//
115//
116// Returns error when either update fails; the transaction is then rolled back.
116// Returns error when either update fails; the transaction is then rolled back.
117func transfer(ctx context.Context, database *sql.DB, queries *store.Queries, from, to int64, amount int32) error {
117func transfer(ctx context.Context, database *sql.DB, queries *store.Queries, from, to int64, amount int32) error {
118 return queries.RunInTx(ctx, database, func(inTransaction *store.Queries) error {
118 return queries.RunInTx(ctx, database, func(inTransaction *store.Queries) error {
119 if err := inTransaction.Deposit(ctx, store.DepositParams{Amount: amount, ID: to}); err != nil {
119 if err := inTransaction.Deposit(ctx, store.DepositParams{Amount: amount, ID: to}); err != nil {
120 return err
120 return err
121 }
121 }
122 return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
122 return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
123 })
123 })
124}
124}
125
125
126// printBalances writes every account's balance on one line.
126// printBalances writes every account's balance on one line.
127//
127//
128// Takes queries (*store.Queries) which read the accounts.
128// Takes queries (*store.Queries) which read the accounts.
129// Takes output (io.Writer) which receives the line.
129// Takes output (io.Writer) which receives the line.
130//
130//
131// Returns error when the accounts cannot be listed.
131// Returns error when the accounts cannot be listed.
132func printBalances(ctx context.Context, queries *store.Queries, output io.Writer) error {
132func printBalances(ctx context.Context, queries *store.Queries, output io.Writer) error {
133 accounts, err := queries.ListAccounts(ctx)
133 accounts, err := queries.ListAccounts(ctx)
134 if err != nil {
134 if err != nil {
135 return fmt.Errorf("listing accounts: %w", err)
135 return fmt.Errorf("listing accounts: %w", err)
136 }
136 }
137 fmt.Fprint(output, " balances:")
137 fmt.Fprint(output, " balances:")
138 for _, account := range accounts {
138 for _, account := range accounts {
139 fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
139 fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
140 }
140 }
141 fmt.Fprintln(output)
141 fmt.Fprintln(output)
142 return nil
142 return nil
143}
143}