Skip to content

Database integration notes

Jump to bottom
Bojan Zivanovic edited this page Jun 29, 2022 · 8 revisions

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.

The currency_code is stored in a text column because PostgreSQL recommends against using char(3).

Example schema:

CREATE TYPE price AS (
   number NUMERIC,
   currency_code TEXT
);

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)

SQLite

SQLite doesn't support composite types either, which means that we need to follow the MySQL examples.

However, there is a catch: SQLite does not implement a true decimal type, the value is actually stored as a float. To maintain precision, we must store the price in its minor units (e.g. cents) using an integer.

Therefore, the price_number column should be an INTEGER(8), mapped to an int64 on the Go side. Use currency.NewAmountFromInt64() to create an amount from an int64. Use a.Int64() to convert the amount to an int64 again.

Clone this wiki locally