Exchange Rates API Data in a Database: Schema, Indexes and As-Of Queries

Sooner or later, every app that uses an exchange rates API needs its own copy of the rates. Invoices must be reproducible next year. Reports must use the rate of the transaction date, not today's. And you do not want a page view to depend on a network call. This guide gives a schema that holds up: one table, the right keys, an as-of lookup, idempotent loading and a retention plan. The SQL is for PostgreSQL, with notes for MySQL and SQLite at the end.

The table

CREATE TABLE fx_rate (
  base        CHAR(3)        NOT NULL,             -- ISO 4217, e.g. 'USD'
  quote       CHAR(3)        NOT NULL,             -- 1 base = rate quote
  rate        NUMERIC(20,10) NOT NULL CHECK (rate > 0),
  rate_type   TEXT           NOT NULL CHECK (rate_type IN ('mid', 'official')),
  source      TEXT           NOT NULL,             -- 'allratestoday', 'ecb', 'rbi', ...
  rate_date   DATE           NOT NULL,             -- the date the rate applies to
  rate_time   TIMESTAMPTZ,                         -- the provider's own timestamp, if any
  fetched_at  TIMESTAMPTZ    NOT NULL DEFAULT now(),
  PRIMARY KEY (base, quote, rate_type, source, rate_date)
);

CREATE INDEX fx_rate_date_idx ON fx_rate (rate_date);

Why each column is there:

• base and quote fix the direction. A row USD, EUR, 0.9215 means 1 USD = 0.9215 EUR. Write that rule in a comment, because direction bugs are the most expensive kind.
• **rate as NUMERIC(20,10)**, never FLOAT or DOUBLE. Floating point turns 0.9215 into 0.92149999... and those errors show up as one-cent differences in totals. Ten decimal places covers small-unit currencies against strong ones, and ten integer digits covers rates such as USD/IRR or USD/VND.
• rate_type separates a live mid-market rate from an official central bank rate. They are different numbers with different purposes, and a report must never mix them by accident.
• source names who produced the rate. For official rates it is the institution, such as ecb. This is the column an auditor asks about.
• rate_date is the business date the rate applies to. rate_time is the provider's timestamp. fetched_at is when your system got it. Keep all three; they answer different questions.

Primary key and indexes

The primary key (base, quote, rate_type, source, rate_date) says: one rate per pair, per type, per source, per day. That is the right grain for invoices, accounting and reports.

The column order is deliberate. Lookups filter on pair, type and source, then range on date, so the primary key index serves the as-of query directly. The extra index on rate_date is for retention jobs and for "all rates on a date" exports.

If you also keep intraday snapshots, put them in a separate table keyed on rate_time instead of rate_date. Mixing grains in one table makes every query ask "which row of the day?".

As-of lookups: the latest rate on or before a date

Rates do not exist for every date. Official rates skip weekends and holidays, and your loader might miss a day. The standard question is "the latest rate on or before date D".

For several currencies at once, DISTINCT ON is the idiomatic Postgres answer:

SELECT DISTINCT ON (quote)
       quote, rate, rate_date
FROM fx_rate
WHERE base = 'USD'
  AND quote IN ('EUR', 'GBP', 'JPY')
  AND rate_type = 'mid'
  AND source = 'allratestoday'
  AND rate_date <= DATE '2026-11-08'
ORDER BY quote, rate_date DESC;

To attach a rate to each row of another table, use LATERAL. This converts invoices into USD with the rate on or before each invoice date, and refuses rates more than seven days older than the invoice:

SELECT i.id,
       i.amount,
       i.currency,
       r.rate,
       r.rate_date,
       CASE WHEN i.currency = 'USD' THEN i.amount
            ELSE round(i.amount / r.rate, 2) END AS amount_usd
FROM invoice i
LEFT JOIN LATERAL (
  SELECT rate, rate_date
  FROM fx_rate
  WHERE base = 'USD'
    AND quote = i.currency
    AND rate_type = 'mid'
    AND source = 'allratestoday'
    AND rate_date <= i.invoice_date
    AND rate_date >= i.invoice_date - 7
  ORDER BY rate_date DESC
  LIMIT 1
) r ON true;

Each lateral subquery is a single index probe on the primary key. LEFT JOIN ... ON true keeps invoices with no rate, so you see a NULL instead of silently losing rows. Since rate is EUR per USD, a EUR amount divided by the rate gives USD.

Why not store inverses

It is tempting to store both USD/EUR and EUR/USD. Do not:

• They can disagree. If one is loaded and the other is not, or both are rounded, a * b stops being exactly 1, and two reports give two answers.
• It doubles the rows and the load work for no new information.
• It hides direction bugs. With one direction stored, a query that uses the wrong direction returns nothing, which you notice. With both stored, it returns a plausible wrong number.

Store one direction per source and compute the inverse when you need it: 1 / rate. With NUMERIC, the inverse is exact to the precision you ask for.

Triangulation through USD or EUR

You do not need rows for every pair either. With 160 currencies there are over 25,000 pairs. Store each currency against one base and derive the rest. EUR/GBP is USD/GBP divided by USD/EUR:

SELECT a.rate_date,
       round(b.rate / a.rate, 10) AS eur_gbp
FROM fx_rate a
JOIN fx_rate b USING (base, rate_type, source, rate_date)
WHERE a.base = 'USD' AND a.quote = 'EUR'
  AND b.quote = 'GBP'
  AND a.rate_type = 'mid' AND a.source = 'allratestoday'
ORDER BY a.rate_date DESC
LIMIT 30;

The important rule: triangulate only within one source and one date. Official rates come in each institution's own base. The ECB quotes against EUR, a central bank in India quotes against INR. Store them as given, with their own base and source = 'ecb' and so on, and never combine an ECB rate with another bank's rate into a cross rate. The result would belong to no one. The base currency conversion guide goes deeper on cross rates.

Idempotent upserts with ON CONFLICT

Loaders get re-run: a cron fires twice, a job retries after a timeout, someone backfills a week by hand. The insert must be safe to repeat:

INSERT INTO fx_rate (base, quote, rate, rate_type, source, rate_date, rate_time)
VALUES ($1, $2, $3, 'mid', 'allratestoday', $4, $5)
ON CONFLICT (base, quote, rate_type, source, rate_date)
DO UPDATE SET rate       = EXCLUDED.rate,
              rate_time  = EXCLUDED.rate_time,
              fetched_at = now()
WHERE fx_rate.rate IS DISTINCT FROM EXCLUDED.rate;

Running it twice leaves one row. The WHERE clause skips writes when nothing changed, which keeps write volume and table bloat down. For official rates, consider DO NOTHING plus a log entry when a value differs, so a correction by the institution is visible rather than silently overwritten.

Loading daily from the exchange rates API

One request fetches every currency you need. The AllRatesToday rates endpoint takes a comma-separated target list and returns a JSON array; a multi-target call costs one request.

# load_rates.py   (pip install requests "psycopg[binary]")
import json
import os
from datetime import datetime
from decimal import Decimal

import psycopg
import requests

TARGETS = ["EUR", "GBP", "JPY", "CAD", "AUD", "CHF", "INR", "CNY"]

UPSERT = """
INSERT INTO fx_rate (base, quote, rate, rate_type, source, rate_date, rate_time)
VALUES (%s, %s, %s, 'mid', 'allratestoday', %s, %s)
ON CONFLICT (base, quote, rate_type, source, rate_date)
DO UPDATE SET rate = EXCLUDED.rate, rate_time = EXCLUDED.rate_time, fetched_at = now()
WHERE fx_rate.rate IS DISTINCT FROM EXCLUDED.rate
"""

resp = requests.get(
    "https://allratestoday.com/api/v1/rates",
    params={"source": "USD", "target": ",".join(TARGETS)},
    headers={"Authorization": f"Bearer {os.environ['ALLRATES_API_KEY']}"},
    timeout=15,
)
resp.raise_for_status()
rows = json.loads(resp.text, parse_float=Decimal)   # keep exact decimals

with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    for r in rows:
        ts = datetime.fromisoformat(r["time"].replace("Z", "+00:00"))
        conn.execute(UPSERT, (r["source"], r["target"], r["rate"], ts.date(), ts))

parse_float=Decimal keeps the rate exact from JSON to NUMERIC; going through a Python float would reintroduce rounding. Schedule the loader at a fixed time each day, for example just after your business close. If it runs twice the same day, the upsert keeps the later snapshot. Daily loading is about 30 requests a month, which fits any paid plan; commercial use starts on the Small plan, see pricing.

For backfills, /api/historical-rates?source=USD&target=EUR&period=1y returns weekly points and costs 12 requests per pair. For daily official rates over a date range, the central bank history endpoint, such as /api/v1/central-bank/ecb/history?source=USD&target=EUR&from=2026-01-01&to=2026-03-31, returns the institution's own dates and is billed per month of range. See the central bank rates API.

Retention

Daily rows are tiny. 160 currencies for one source is about 58,000 rows a year, a few megabytes with indexes. Keep daily rates forever; old invoices and audits need them.

Intraday snapshots are different. One snapshot a minute for 160 currencies is about 230,000 rows a day. Partition that table by month and drop partitions you no longer need, keeping only the daily row in fx_rate long term.

MySQL and SQLite notes

SQLite deserves a warning: a column declared NUMERIC stores 0.9215 as a floating-point value. Store rates as text and convert with a decimal type in your application.

If you are still choosing where the data comes from, the free currency API comparison lists update frequencies, history depth and limits.

FAQ

What data type should I use for exchange rates in SQL?

Use NUMERIC or DECIMAL, for example NUMERIC(20,10). Never use FLOAT or DOUBLE, because binary floating point cannot represent most decimal rates exactly and the errors show up in totals.

How do I get the exchange rate for a date with no rate?

Use an as-of lookup: the latest rate on or before the date. In Postgres, use DISTINCT ON for many currencies or a LATERAL subquery with ORDER BY rate_date DESC LIMIT 1 per row, plus a maximum age.

Should I store both USD/EUR and EUR/USD?

No. Store one direction per source and compute the inverse as 1 divided by the rate. Storing both lets them drift apart.

How do I avoid duplicate rows when the loader runs twice?

Make the primary key the natural key (base, quote, rate type, source, rate date) and insert with ON CONFLICT DO UPDATE. Running the load again then updates or skips the row instead of adding one.

Can I mix official and mid-market rates in one table?

Yes, as long as rate_type and source are part of the key and every query filters on them. Never build a cross rate from two different sources.