Skip to content
Fondazione ITS INCOM

Examples · Web Development

Test data for your database

A model writes fifteen customers and twenty-five orders for a small shop in Legnano, as JSON that follows your schema. Your code checks emails, dates, prices, totals and customer ids, refuses what is wrong, and loads the rest into SQLite.

Intermediate · 35 min

The idea

A web shop with an empty database is hard to test: the order page, the search and the monthly report all need data. Writing forty rows by hand is boring. A model writes them in a minute or two, with Italian names and real towns.

But test data must be right where it matters. If a total does not match its lines, your report looks broken, and you look for a bug in your code that is really in the data. So the work is split into three:

  1. the schema decides the shape: emails only at example.it or example.com, towns and products only from your lists;
  2. your code checks what a schema cannot: emails used twice, dates that do not exist, orders before the customer signed up, prices that are not in the catalogue, totals that do not add up, customers that do not exist;
  3. only the clean rows go into SQLite, and the database checks once more.

What you need

The catalogue

La Bottega is a small food shop in Legnano. Save its products as products.json. Prices are in cents, as integers.

[
  { "code": "OLIO-1L", "name": "Olio extravergine del Garda, 1 l", "price_cents": 1450 },
  { "code": "PASTA-500", "name": "Pasta di Gragnano, 500 g", "price_cents": 280 },
  { "code": "RISO-1KG", "name": "Riso Carnaroli, 1 kg", "price_cents": 420 },
  { "code": "PARM-500", "name": "Parmigiano Reggiano 24 mesi, 500 g", "price_cents": 1490 },
  { "code": "CAFFE-1KG", "name": "Caffè in grani, 1 kg", "price_cents": 1890 },
  { "code": "AMARETTI-400", "name": "Amaretti di Saronno, 400 g", "price_cents": 690 },
  { "code": "BARBERA-75", "name": "Barbera d'Asti, 75 cl", "price_cents": 950 },
  { "code": "MIELE-500", "name": "Miele di acacia, 500 g", "price_cents": 830 }
]

The program

Save it as seed.mjs, next to products.json:

import { readFileSync } from "node:fs";
import { DatabaseSync } from "node:sqlite";
import OpenAI from "openai";

const client = new OpenAI({ baseURL: "https://api.ai.itsincom.org/v1", apiKey: process.env.API_KEY });
const model = process.argv[2] ?? "qwen3.8-27b";

const products = JSON.parse(readFileSync("products.json", "utf8"));
const priceOf = Object.fromEntries(products.map((p) => [p.code, p.price_cents]));
const towns = ["Legnano", "Busto Arsizio", "Castellanza", "Rescaldina", "Cerro Maggiore", "San Vittore Olona",
               "Canegrate", "Parabiago", "Olgiate Olona", "Dairago", "Villa Cortese", "Gallarate"];
const day = { type: "string", pattern: "^2026-[0-9]{2}-[0-9]{2}$" };

const schema = {
  type: "object",
  properties: {
    customers: {
      type: "array",
      items: {
        type: "object",
        properties: {
          id: { type: "integer" },
          name: { type: "string" },
          // Test data must never reach a real inbox: example.it and example.com belong to nobody.
          email: { type: "string", pattern: "^[a-z0-9.]+@example\\.(it|com)$" },
          city: { type: "string", enum: towns },
          signed_up: day,
        },
        required: ["id", "name", "email", "city", "signed_up"],
      },
    },
    orders: {
      type: "array",
      items: {
        type: "object",
        properties: {
          id: { type: "integer" },
          customer_id: { type: "integer" },
          date: day,
          lines: {
            type: "array",
            items: {
              type: "object",
              properties: {
                product: { type: "string", enum: Object.keys(priceOf) }, // only products that exist
                quantity: { type: "integer" },
                unit_price_cents: { type: "integer" },
              },
              required: ["product", "quantity", "unit_price_cents"],
            },
          },
          total_cents: { type: "integer" },
        },
        required: ["id", "customer_id", "date", "lines", "total_cents"],
      },
    },
  },
  required: ["customers", "orders"],
};

const answer = await client.chat.completions.create({
  model,
  temperature: 0.7,
  messages: [
    { role: "system", content: "You write test data for the database of La Bottega, a small food shop in Legnano. " +
        "Write 15 customers and 25 orders. " +
        "Dates are between 2026-01-01 and 2026-09-30, and an order comes after the customer signed up. " +
        "An order has 1 to 4 lines; copy the unit price from the catalogue. " +
        "total_cents is the sum of quantity × unit_price_cents of its lines." },
    { role: "user", content: "Catalogue:\n" + JSON.stringify(products, null, 1) },
  ],
  response_format: { type: "json_schema", json_schema: { name: "seed", schema } },
});
// "length" means the JSON was cut off; an empty answer happened to us once with qwen3.8-27b.
const text = answer.choices[0].message.content?.trim();
if (answer.choices[0].finish_reason !== "stop" || !text) throw new Error("No complete answer: run it again.");
const data = JSON.parse(text);

// The model proposes, the code checks. Every problem is printed; only clean rows are loaded.
const problems = [];
function isRealDay(text) {
  const [y, m, d] = text.split("-").map(Number);
  const date = new Date(Date.UTC(y, m - 1, d));
  return date.getUTCMonth() === m - 1 && date.getUTCDate() === d && text >= "2026-01-01" && text <= "2026-09-30";
}

const customers = new Map();
const emails = new Set();
for (const c of data.customers) {
  if (customers.has(c.id)) problems.push(`customer ${c.id}: the id is used twice`);
  else if (emails.has(c.email)) problems.push(`customer ${c.id}: ${c.email} is already used`);
  else if (!isRealDay(c.signed_up)) problems.push(`customer ${c.id}: signed up on ${c.signed_up}?`);
  else { customers.set(c.id, c); emails.add(c.email); }
}

const orders = [];
const orderIds = new Set();
for (const o of data.orders) {
  const customer = customers.get(o.customer_id);
  const sum = o.lines.reduce((s, l) => s + l.quantity * l.unit_price_cents, 0);
  const badPrice = o.lines.find((l) => l.unit_price_cents !== priceOf[l.product]);
  if (orderIds.has(o.id)) problems.push(`order ${o.id}: the id is used twice`);
  else if (!customer) problems.push(`order ${o.id}: customer ${o.customer_id} does not exist`);
  else if (!isRealDay(o.date)) problems.push(`order ${o.id}: date ${o.date}?`);
  else if (o.date < customer.signed_up) problems.push(`order ${o.id}: on ${o.date}, before customer ${customer.id} signed up (${customer.signed_up})`);
  else if (o.lines.length === 0 || o.lines.some((l) => l.quantity < 1)) problems.push(`order ${o.id}: no lines, or a quantity under 1`);
  else if (badPrice) problems.push(`order ${o.id}: ${badPrice.product} at ${badPrice.unit_price_cents}, the catalogue says ${priceOf[badPrice.product]}`);
  else if (sum !== o.total_cents) problems.push(`order ${o.id}: total ${o.total_cents}, but the lines add up to ${sum}`);
  else { orders.push(o); orderIds.add(o.id); }
}

console.log(`${model}: ${data.customers.length} customers and ${data.orders.length} orders`);
for (const p of problems) console.log("  REFUSED", p);

// The database checks too: primary keys, UNIQUE and FOREIGN KEY stop a bad row even if your code misses it.
const db = new DatabaseSync("shop.db");
db.exec(`
  PRAGMA foreign_keys = ON;
  DROP TABLE IF EXISTS order_lines; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS customers;
  CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE,
                          city TEXT NOT NULL, signed_up TEXT NOT NULL);
  CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id),
                       date TEXT NOT NULL, total_cents INTEGER NOT NULL);
  CREATE TABLE order_lines (order_id INTEGER NOT NULL REFERENCES orders(id), product TEXT NOT NULL,
                            quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price_cents INTEGER NOT NULL);
`);
const addCustomer = db.prepare("INSERT INTO customers VALUES (?, ?, ?, ?, ?)");
const addOrder = db.prepare("INSERT INTO orders VALUES (?, ?, ?, ?)");
const addLine = db.prepare("INSERT INTO order_lines VALUES (?, ?, ?, ?)");
db.exec("BEGIN");
for (const c of customers.values()) addCustomer.run(c.id, c.name, c.email, c.city, c.signed_up);
for (const o of orders) {
  addOrder.run(o.id, o.customer_id, o.date, o.total_cents);
  for (const l of o.lines) addLine.run(o.id, l.product, l.quantity, l.unit_price_cents);
}
db.exec("COMMIT");

console.log(`loaded ${customers.size} customers and ${orders.length} orders into shop.db`);
const best = db.prepare(`SELECT c.name, c.city, COUNT(*) AS orders, SUM(o.total_cents) / 100.0 AS euro
                         FROM customers c JOIN orders o ON o.customer_id = c.id
                         GROUP BY c.id ORDER BY euro DESC LIMIT 3`).all();
console.table(best);

Run it with node seed.mjs. To try another model: node seed.mjs gemma-4-26b.

The program deletes the three tables before it creates them again: point it only at a test database, never at the real one.

What you should see

(node:376436) ExperimentalWarning: SQLite is an experimental feature and might change at any time
(Use `node --trace-warnings ...` to show where the warning was created)
qwen3.8-27b: 15 customers and 25 orders
loaded 15 customers and 25 orders into shop.db
┌─────────┬──────────────────┬───────────┬────────┬──────┐
│ (index) │ name             │ city      │ orders │ euro │
├─────────┼──────────────────┼───────────┼────────┼──────┤
│ 0       │ 'Laura Bellini'  │ 'Legnano' │ 2      │ 108  │
│ 1       │ 'Marco Bianchi'  │ 'Legnano' │ 3      │ 87.7 │
│ 2       │ 'Martina Ghezzi' │ 'Legnano' │ 2      │ 82   │
└─────────┴──────────────────┴───────────┴────────┴──────┘

The warning is only a warning: node --no-warnings seed.mjs hides it. The names change at every run.

qwen3.8-27b passed every check in all four of our runs, and it took between 57 and 138 seconds. It reasons silently before it writes: its answers used 7,800 to 16,400 completion tokens, while gemma-4-26b wrote the same amount of JSON in about 3,100 tokens and 30 seconds.

With node seed.mjs ministral-3-14b the checks have work to do:

ministral-3-14b: 15 customers and 25 orders
  REFUSED order 1: total 3380, but the lines add up to 3180
  REFUSED order 4: total 2860, but the lines add up to 2460
  REFUSED order 11: total 2300, but the lines add up to 2360
  …
  REFUSED order 25: total 3210, but the lines add up to 2960
loaded 15 customers and 16 orders into shop.db

Order 1 was 2 × OLIO-1L at 1450 and 1 × PASTA-500 at 280: that is 3180 cents, not 3380.

What the checks caught

Why the towns are an enum

In the first version of the program, the town was free text. qwen3.8-27b placed customers in Buggiano, which is in Tuscany, and in Bissone, which is in Switzerland. It wrote “Castaneta”, “Arlascio” and “Lomiana”, which are not towns of the area. Once it wrote “Arlasigned_up”: a town with the name of the next field glued to it.

With "enum": towns the model can only choose a town from your list. But an enum decides what is allowed, not what is varied: in all four runs, qwen3.8-27b put all 15 customers in Legnano. gemma-4-26b put 9 of 15 there. If your tests need several towns, say so in the system message, and count them in code.

Good to know

Exercise

All examples