csv

CSV import and export for a resource: parse, validate each row with Zod, write, stream, and size the inserts for D1’s 100-parameter limit. examples/crm uses it for its contacts.

Nothing in it knows the tenant. The route checks the member, sets organizationId on every row it writes and reads only its organization’s rows, as for any other write.

Uses zod and void/schema-d1’s types, and nothing from other Flare plugins.

A resource’s CSV

import { defineCsv } from "@recranet/flare/csv"

export const contactsCsv = defineCsv({
  columns: [
    { key: "firstName", header: "First name", aliases: ["Given name"], required: true },
    { key: "email", header: "Email", aliases: ["E-mail"] },
    { key: "company", header: "Company" },
  ],
  schema: ContactCreate.omit({ companyId: true }).extend({ company: z.string().max(200).optional() }),
  maxRows: 500,
})

Column field	What
key	The field in the schema and in the records you export
header	Written on export, read on import
aliases	More headers it is read from. Headers match ignoring case, spaces and punctuation; the key matches too
required	A file without this column is refused as a whole

Import

const { rows, errors } = contactsCsv.read(await c.req.text())
// rows:   [{ line: 2, data: { firstName: "Ada", email: "ada@example.com" } }, ...]
// errors: [{ line: 4, message: "Email: Enter a valid email address" }]

• Each row’s cells go to the schema by key, trimmed. An empty cell is left out, so an optional field stays optional and a required one says so.
• data is the schema’s output: coerced, transformed, defaults applied.
• line is the row a spreadsheet shows: the header is 1, and blank rows are skipped but counted.
• A message names the column by its header.
• Unknown columns are ignored. A missing required column, or more than maxRows rows (default 1000), refuses the whole file with one error.

The file may be comma, semicolon (Excel where the decimal mark is a comma) or tab separated, found from the header line; a byte order mark is dropped; quoted fields may hold the delimiter, "" and line breaks; lines end in CRLF, LF or CR.

For a file too large to hold or to write in one invocation, readStream(request.body, { size: 100 }) yields { rows, errors } in slices as the bytes arrive, to send to a queue one slice at a time.

Export

import { csvResponse } from "@recranet/flare/csv"

async function* pages() {
  for (let after = ""; ; ) {
    const page = await db.select().from(contacts).where(and(eq(contacts.organizationId, m.organizationId), gt(contacts.id, after))).orderBy(contacts.id).limit(500)
    yield page
    if (page.length < 500) return
    after = page.at(-1)!.id
  }
}
return csvResponse(contactsCsv.stream(pages()), "contacts.csv")

stream writes the header, then each record or page of records as the source yields it: the next page is read only when the client has taken the last, and the export never sits in memory whole. write(records) gives the whole text instead.

• Cells: null and undefined are empty, Dates ISO strings, objects JSON.
• Text a spreadsheet would run as a formula (a leading =, +, -, @, tab or CR) gets a leading '. Numbers are left alone, so -5 stays -5.
• The stream starts with a UTF-8 byte order mark, so Excel shows accents right ({ bom: false } to leave it out). Import drops it again.
• csvResponse(body, filename) sets text/csv; charset=utf-8 and an attachment with the file name safe in the header.

Writing to D1

D1 refuses a statement with more than 100 bound parameters. A multi-row insert binds one per column per row, so the rows per statement depend on the table:

import { batches, inserts } from "@recranet/flare/csv"

const statements = [
  ...inserts(db, contacts, values),       // 100 / columns rows each
  ...inserts(db, auditLog, auditRows),    // their audit rows, the same way
]
for (const group of batches(statements)) await db.batch(group)

Helper	What
inserts(db, table, rows)	Multi-row inserts, each under 100 parameters (all the table’s columns counted)
batches(statements, size = 50)	Groups for db.batch()
chunkRows(rows, columns, maxParams = 100)	The sizing, for your own statements (IN (...) lookups, upserts)
columnCount(table)	The table’s columns

A group commits together; groups don’t, so a failure part-way leaves the earlier groups written. D1 also allows 50 queries per invocation on the free plan and 1000 on Paid, each statement of a batch counted. Size maxRows so an import stays under it, or run slices from a queue (decision 5).

Lower level

parseCsv(text) (rows, blank ones left out), parseCsvStream(stream), createCsvParser() (push(chunk), end()), detectDelimiter(text), csvCell, csvLine, toCsv, csvStream(rows).

In examples/crm

• lib/contacts-csv.ts: the contacts’ columns and aliases on ContactCreate, 500 rows. Import looks up companies by name and contacts by email in IN chunks, creates the missing companies once, and writes with createMany and updateMany. An email twice in a file: the later row updates what the earlier wrote.
• lib/records.ts: createMany and updateMany, the records, their audit rows and their search documents in multi-row statements, in batches.
• routes/api/contacts/export.ts: streamed, 500 contacts a page.
• routes/api/contacts/import.ts, pages/contacts/import.vue: a 1 MB body.

500 rows stay under the Paid plan’s 1000 queries even when every row is an update (an UPDATE each, plus the multi-row audit and search statements).

Notes

• Not here: encodings other than UTF-8 (a file saved as Windows-1252 shows � for accents), Excel’s .xlsx, a column mapping UI, dry runs.
• read holds the whole file; readStream holds one slice.