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.

Note that the number and currency_code columns can have any name, only their ordering matters.

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:

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

Example scan:

	var priceNumber, priceCurrency string
	p := Product{}
	row := tx.QueryRowContext(ctx, `SELECT id, name, price_number, price_currency, created_at, updated_at FROM products WHERE id = ?`, id)
	err := row.Scan(&p.ID, &p.Name, &priceNumber, &priceCurrency, &p.CreatedAt, &p.UpdatedAt)
	if err != nil {
		return err
	} 
	p.Price, _ = currency.NewAmount(priceNumber, priceCurrency)

Many sites use sqlx to marshal rows into structs without having to scan individual columns. This requires a different struct with separate fields for price number and currency.

Example struct (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 (sqlx):

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