lite – select() vs execute(), Paging and Inserts

execute() returns raw column names — always alias

select() with a columns array aliases each column to its key for you. execute() does not: it returns the database column names (Demand_ID, User_Name), while frontends expect camelCase (demandId, userName).

A route that bypasses select() for a raw execute() — commonly a post-insert re-fetch — returns PascalCase and breaks the frontend silently: the response is a clean 200, the data is just unusable.

execute(
  `SELECT ID AS id, Demand_ID AS demandId, User_Name AS userName` +
  ` FROM ${db}.SomeView WHERE ID = ${sanitize(id)}`
);

Better still, share one column-alias constant between the GET route and the re-fetch so the two can't drift.

Never fetch-all and slice in JS

A list endpoint that is instant under npm run dev (Node) but times out at ~60s in Classic ASP is almost always this:

// ❌
const { data } = select(db, view, { itemsPerPage: 10000, paginate: false });
const page = data.filter(…).slice(start, end);

Why the runtimes differ: lite reads ASP results through an ADO recordset where every row and every field is a COM round-trip. Thousands of rows × N columns is tens of thousands of COM calls. The Node dev database is small enough that it never shows up locally. This is a data-volume cliff that gets worse as production grows — not a lite bug; upgrading lite will not fix it.

// ✅
const { data, meta } = select(db, view, {
  page,
  itemsPerPage,
  filter: [{ key: 'name', isNotNull: true }],   // exclusions belong in SQL
  sort: [{ key: sortKey, desc }],
});

paginate: true (the default) emits a ROW_NUMBER() paged query returning only that page, plus a separate getCount() for meta.totalItems — one page marshalled instead of the whole view.

• Push every JS row-exclusion into the SQL filter, so the COUNT and the page agree.
• Allowlist sort keys and map frontend names to real columns (email → prefEmail).
• For reporting that genuinely needs all rows, push the aggregation into SQL group (GROUP BY). Don't sum marshalled rows in JS.
• Bounded lookups (per-user roles, small reference tables) are fine as-is.

Inserts: NEWID() comes from the PRIMARY KEY

When a route calls insertInto() without supplying an ID, lite generates a uniqueidentifier for it — but only because that column is the table's PRIMARY KEY. There is no DEFAULT NEWID() on the column; the PK is what triggers it.

For migrations and CREATE TABLE against a lite-backed table:

• Define ID uniqueidentifier NOT NULL plus PRIMARY KEY (ID) and lite fills it on insert. Don't add DEFAULT NEWID() — it just creates schema drift from the rest of the database.
• A raw INSERT from SSMS or sqlcmd that omits ID will fail with "Cannot insert NULL into column 'ID'". That's expected — only the lite API path auto-fills it.

Column naming: camelCase → Title_Snake

lite maps JS keys to columns by capitalising each word and joining with underscores:

Note Id → ID, not Id.