Sample e-commerce database: 8 related tables as JSON, CSV and PostgreSQL

What is in the data

Two sizes of the same shop. The small set (12 customers, 24 orders) is meant for reading and unit tests, the medium set (60 customers, 160 orders) for dashboards, pagination and joins. They are separate datasets, not prefixes of each other. In the files, net means before tax and gross means after tax.

How the tables relate

categories 1 ── n products 1 ── n order_items n ── 1 orders
customers  1 ── n addresses                          │
customers  1 ── n orders ─────────────────────────────┤
orders     1 ── 0..1 payments                         │
orders     1 ── 0..1 shipments ───────────────────────┘
orders.billing_address_id, orders.shipping_address_id -> addresses.id

Rules the data follows

• Money in cents. A line item has net = quantity × unit price, tax = round(net × rate / 100) and gross = net + tax.
• Tax and shipping. Sales tax is added on top of the price: 0 % on food, 7 % on everything else. This is a fictional rule that does not follow any real state. Shipping is 5.99 USD and untaxed, free from 75.00 USD of goods.
• Order totals are the sums of the line items plus shipping, and total gross = total net + total tax. A payment amount always equals the order total.
• Timelines make sense. Ordered, then paid, then shipped, then delivered. Invoice payments may arrive after shipping, and a refund comes after delivery. Everything lies before the fixed “today”.
• References are valid. Every foreign key points to an existing row, and an order's addresses belong to the order's customer.
• Edge cases are built in: sold-out and inactive products, a customer without a phone number, customers who never ordered, cancelled orders with and without a failed payment, refunds, and shipped orders whose invoice is still pending.

These rules are checked by automated tests, including a load of the generated SQL into a real database with foreign keys switched on.

Load it into PostgreSQL

Download the seed script of the size you want (it creates the tables and inserts the rows inside one transaction), then run:

createdb shop_demo
psql shop_demo -f shop-us-small-seed.sql

If you already have your own schema, use the schema file as a reference and load the per-table CSV files with \copy, or insert the JSON from a script:

import { readFileSync } from 'node:fs'
const shop = JSON.parse(readFileSync('shop-us-small.json', 'utf8'))
for (const order of shop.orders) { /* insert with your ORM */ }

Example queries

These run unchanged on PostgreSQL and SQLite and return rows on the medium set.

SELECT status, COUNT(*) AS orders, SUM(total_gross_cents) / 100.0 AS revenue
FROM orders
GROUP BY status
ORDER BY revenue DESC;

SELECT p.name, SUM(i.quantity) AS units, SUM(i.net_cents) / 100.0 AS net_revenue
FROM order_items i
JOIN products p ON p.id = i.product_id
GROUP BY p.id, p.name
ORDER BY net_revenue DESC
LIMIT 5;

SELECT o.order_number, c.email, o.total_gross_cents / 100.0 AS amount, o.ordered_at
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN payments pay ON pay.order_id = o.id
WHERE pay.status = 'pending' AND o.status IN ('shipped', 'delivered')
ORDER BY o.ordered_at;

SELECT c.customer_number, c.email
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

SELECT tax_rate, SUM(net_cents) / 100.0 AS net, SUM(tax_cents) / 100.0 AS tax
FROM order_items
GROUP BY tax_rate
ORDER BY tax_rate;

License

The files on this page are generated from this site's own code and word lists and are released under CC0 1.0 Universal, a public domain dedication. You may copy, modify and use these files for any purpose, including commercial projects, without asking and without attribution. The files are provided as they are, without any warranty. All data is invented and must not be used to represent real people, accounts or transactions. The license covers these generated files only, not the text of this site.

Related

The test data edge cases page has deliberately broken versions of one order from this shop, one violated rule each. The German flavour of the same shop (EUR, VAT, SEPA payments with test IBANs created like those of the test IBAN generator) is in the German library: Beispiel-Shop-Datenbank (in German). Order numbers follow the idea behind the invoice number generator.