Compare · 023

Transactions: MariaDB and SQLite

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

Compare MariaDB withSQLite PostgreSQL CockroachDB TimescaleDB MySQL DuckDB

Configuration

sgl.yamldiffers

MariaDB
SQLite
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: bank
4  - name: bank
5    dialect: mysql
5    dialect: sqlite
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.sqldiffers

MariaDB
SQLite
1CREATE TABLE accounts (
1CREATE TABLE accounts (
2    id      BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
2    id      INTEGER PRIMARY KEY,
3    owner   VARCHAR(50) NOT NULL,
3    owner   TEXT    NOT NULL,
4    balance INT         NOT NULL,
4    balance INTEGER NOT NULL CHECK (balance >= 0)
5    CONSTRAINT balance_not_negative CHECK (balance >= 0)
6);
5);

db/migrations/0001_accounts.down.sqlidentical

MariaDB
SQLite
1DROP TABLE accounts;
1DROP TABLE accounts;

Queries

OpenAccountdiffers

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

Depositidentical

MariaDB · db/queries/accounts.sql
SQLite · 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
SQLite · 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
SQLite · 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

MariaDB
SQLite
1func (queries *Queries) OpenAccount(ctx context.Context, params OpenAccountParams) (sql.Result, 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 int64
6}
6}
7
8type OpenAccountRow struct {
9	ID int64 `json:"id"`
10}

Depositdiffers

MariaDB
SQLite
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 int64
5	ID     int64
5	ID     int64
6}
6}

Withdrawdiffers

MariaDB
SQLite
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 int64
5	ID     int64
5	ID     int64
6}
6}

ListAccountsdiffers

MariaDB
SQLite
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 int64  `json:"balance"`
7}
7}

The program

example.godiffers

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

Go type differences

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

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