Database integration notes

PostgreSQL

Applications using the pgx driver can store amounts in a composite type, thanks to the driver.Valuer and sql.Scanner interfaces.

Example schema:

CREATE TYPE price AS (
   number NUMERIC,
   currency_code CHAR(3)
);

CREATE TABLE products (
   id CHAR(26) PRIMARY KEY,
   name TEXT NOT NULL,
   price price NOT NULL,
   created_at TIMESTAMPTZ NOT NULL,
   updated_at TIMESTAMPTZ
);

Example struct:

type Product struct {
	ID          string
	Name        string
	Price       currency.Amount
	CreatedAt   time.Time
	UpdatedAt   time.Time
}

Example scan:

p := Product{}
row := tx.QueryRow(ctx, `SELECT id, name, price, created_at, updated_at FROM products WHERE id = $1`, id)
err := row.Scan(&p.ID, &p.Name, &p.Price, &p.CreatedAt, &p.UpdatedAt)

MySQL/MariaDB

MySQL and MariaDB don't support composite types, requiring us to store the number and currency code in separate columns. A decimal column is recommended for storing the number.

Example schema:

CREATE TABLE `products` (
   `id` CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
   `name` VARCHAR(255) NOT NULL,
   `price_number` DECIMAL(19,6) NOT NULL,
   `price_currency` CHAR(3) NOT NULL, 
   `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
   `updated_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Example struct (assuming a 1-1 mapping via sqlx):

type Product struct {
	ID            string    `db:"id"`
	Name          string    `db:"name"`
	PriceNumber   string    `db:"price_number"`
	PriceCurrency string    `db:"price_currency"`
	CreatedAt     time.Time `db:"created_at"`
	UpdatedAt     time.Time `db:"updated_at"`
}

func (p Product) Price() (currency.Amount, error) {
	return currency.NewAmount(p.PriceNumber, p.PriceCurrency)
}

Example scan:

p := Product{}
err = db.GetContext(context.Background(), &p, `SELECT * FROM products WHERE id = ?`, id)

Alternatively one could scan each property separately, and then construct a currency.Amount manually before setting it on a struct like the one from the PostgreSQL example.