Server row model to SQL
What the backend runs for each request: planQuery turns the ServerRequest into a QueryPlan against an entity schema, planToSql renders it for Postgres, MySQL or SQLite, and the panel shows the statements createSqlDataSource would hand your executor for the request that just went out - grouped or flat rows, the count, the grand total, and the two statements a pivot needs. The rows come from the in-memory reference source over the same plan. (requires @svgrid/enterprise)
A live, editable Svelte 5 data grid example from the SvGrid gallery (Server-Side Row Model). See the SvGrid documentation for the full API.
About this example
The SQL behind each server-side row model request in a Svelte 5 data grid. planQuery turns a ServerRequest into a QueryPlan against an entity schema, so only declared fields reach the query, and planToSql renders the plan for Postgres, MySQL or SQLite. The panel shows the statements createSqlDataSource would hand your executor for the request that just went out: the grouped or flat rows with WHERE, GROUP BY, ORDER BY, LIMIT and OFFSET, the count, the grand total, and the two statements a server-side pivot needs. The rows come from the in-memory reference source over the same plan.
What the backend runs for each request the row model sends. planQuery turns a ServerRequest into a QueryPlan against an entity schema (only declared fields get through), planToSql renders the plan in a dialect, and the panel shows the statements createSqlDataSource would hand your executor for the request that just went out: the rows, the count, the grand total, and the two-step pivot. The rows themselves come from the in-memory reference source over the same plan, so what you see is what the SQL would return.
The row model and the SQL planner are Enterprise; the datasource contract is free.
Imports, features and API used
Imports: @svgrid/grid, @svgrid/enterprise, ../shared/mock-api
Table features registered: rowSortingFeature, columnFilteringFeature
Frequently asked questions
Where do these statements run in a real app?
In a SvelteKit endpoint or any Node handler: createSqlDataSource takes the schema, the table and an execute function for your driver, plans each request the same way and runs exactly these statements with the bound params. Drizzle, pg, mysql2 and better-sqlite3 all fit the executor shape.
Why is the pivot two statements?
SQL cannot make columns out of values it has not seen. The first statement selects the distinct pivot key paths; the second is the grouped SELECT with one conditional aggregate per key path and aggregation, aliased the way the grid expects them in pivotResultFields.
Can a request name a column that is not in the schema?
No. planQuery only admits fields the EntitySchema declares, for sorting, filtering, grouping, aggregation and pivoting alike, so the request shape from the browser cannot become an injection or a wildcard read.
Related documentation
Related articles
- Inside SvGrid: The Row Model and Sorting - How sorting shaped SvGrid's row-model pipeline - the decisions made early that every later feature inherited.
- Using SvGrid with TanStack Query in Svelte - Wire TanStack Query's caching and background refetch into SvGrid for a server-driven grid that pages instantly and never shows a blank screen.
- A Svelte Data Grid with SvelteKit and Supabase - Wire SvGrid to a Supabase Postgres backend with server-side pagination, sorting, and filtering - keeping credentials on the server and queries fast with proper indexing.
Source code (472-server-sql-planner.svelte)
<!-- Documented in: docs/help/server/server-grouping.md -->
<script lang="ts">
/**
* 472. Server row model to SQL
* ----------------------------
* What the backend runs for each request the row model sends. `planQuery`
* turns a `ServerRequest` into a `QueryPlan` against an entity schema (only
* declared fields get through), `planToSql` renders the plan in a dialect,
* and the panel shows the statements `createSqlDataSource` would hand your
* executor for the request that just went out: the rows, the count, the
* grand total, and the two-step pivot. The rows themselves come from the
* in-memory reference source over the same plan, so what you see is what
* the SQL would return.
*
* The row model and the SQL planner are Enterprise; the datasource
* contract is free.
*/
import { SvGrid, renderComponent, tableFeatures, rowSortingFeature, columnFilteringFeature, type GridColumns, type ServerRequest, type ServerDataSource } from '@svgrid/grid'
import {
setLicenseKey,
createInMemoryDataSource,
createServerRowModel,
planQuery,
planToSql,
serverGroupText,
SvGroupCell,
SvRowGroupPanel,
type EntitySchema,
type SqlDialect,
type ServerRowModel,
type ServerRowModelState,
type ServerRowModelGridRow,
} from '@svgrid/enterprise'
import { createPrng } from '../shared/mock-api'
setLicenseKey('SVENTERPRISE-DEV-LOCAL')
const features = tableFeatures({ rowSortingFeature, columnFilteringFeature })
// ---- The table -------------------------------------------------------------
type Sale = { id: number; region: string; country: string; rep: string; product: string; year: number; qty: number; amount: number }
const WORLD: Record<string, string[]> = { Americas: ['US', 'BR', 'CA'], EMEA: ['DE', 'UK', 'FR'], APAC: ['JP', 'AU', 'IN'] }
const REPS = ['Ada', 'Grace', 'Linus', 'Margaret', 'Ken', 'Barbara', 'Dennis', 'Donald']
const PRODUCTS = ['Desk', 'Chair', 'Lamp', 'Monitor', 'Cabinet', 'Whiteboard']
const rng = createPrng(0x5a1e5)
const DB: Sale[] = Array.from({ length: 50_000 }, (_, i) => {
const region = rng.pick(Object.keys(WORLD))
const qty = rng.int(1, 12)
return {
id: i + 1,
region,
country: rng.pick(WORLD[region]!),
rep: rng.pick(REPS),
product: rng.pick(PRODUCTS),
year: rng.int(2023, 2026),
qty,
amount: qty * rng.int(90, 900),
}
})
const schema: EntitySchema<Sale> = {
name: 'sales',
fields: [
{ field: 'id', type: 'number', primaryKey: true, readonly: true },
{ field: 'region', type: 'text' },
{ field: 'country', type: 'text' },
{ field: 'rep', type: 'text' },
{ field: 'product', type: 'text' },
{ field: 'year', type: 'number' },
{ field: 'qty', type: 'number' },
{ field: 'amount', type: 'number' },
],
}
const memory = createInMemoryDataSource(DB, schema)
// ---- Dialects --------------------------------------------------------------
type DialectName = 'postgres' | 'mysql' | 'sqlite'
const DIALECT_LABEL: Record<DialectName, string> = { postgres: 'PostgreSQL', mysql: 'MySQL', sqlite: 'SQLite' }
const DIALECTS: Record<DialectName, SqlDialect> = {
postgres: { quote: '"', placeholders: '$', ilike: true },
mysql: { quote: '`', placeholders: '?' },
sqlite: { quote: '"', placeholders: '?' },
}
let dialect = $state<DialectName>('postgres')
// ---- The statements for one request ----------------------------------------
// The same assembly `createSqlDataSource` does, kept as text: the plan is
// the seam, so this is exactly what an executor receives.
type Statement = { label: string; sql: string; params: unknown[] }
type Entry = { seq: number; request: ServerRequest; ms: number }
function statementsFor(request: ServerRequest, name: DialectName): Statement[] {
const plan = planQuery(schema, request)
const sql = planToSql(plan, DIALECTS[name])
const q = DIALECTS[name].quote ?? '"'
const t = `${q}sales${q}`
const out: Statement[] = []
let select = sql.select
let grandTotalSelect = sql.grandTotalSelect
if (plan.groupBy && plan.pivotBy?.length) {
out.push({ label: 'pivot keys', sql: `SELECT ${sql.pivotKeysSelect}\nFROM ${t}\n${sql.whereText}`.trim(), params: sql.params })
// The key paths the first statement returns; the in-memory table has
// them, so the second statement can be shown in full.
const keyRows = [...new Set(DB.map((r) => r.year))].sort().map((year) => ({ year }))
const pivot = sql.pivotSelect(keyRows)
select = pivot.select
if (plan.grandTotal) grandTotalSelect = pivot.grandTotalSelect
}
out.push({
label: plan.groupBy ? 'group rows' : 'rows',
sql: plan.groupBy
? `SELECT ${select}\nFROM ${t}\n${sql.whereText}\n${sql.groupByText}\n${sql.orderByText}\nLIMIT ${sql.limit} OFFSET ${sql.offset}`
: `SELECT *\nFROM ${t}\n${sql.whereText}\n${sql.orderByText}\nLIMIT ${sql.limit} OFFSET ${sql.offset}`,
params: sql.params,
})
out.push({ label: 'count', sql: `SELECT ${sql.countText} AS count\nFROM ${t}\n${sql.whereText}`, params: sql.params })
if (plan.grandTotal && grandTotalSelect) {
out.push({ label: 'grand total', sql: `SELECT ${grandTotalSelect}\nFROM ${t}\n${sql.grandTotalWhereText}`, params: sql.grandTotalParams })
}
return out.map((s) => ({ ...s, sql: s.sql.replace(/\n{2,}/g, '\n').replace(/\n$/, '') }))
}
let log = $state<Entry[]>([])
let seq = 0
const source: ServerDataSource<Sale> = {
async getRows(req) {
const t0 = performance.now()
await new Promise((r) => setTimeout(r, 100))
const result = await memory.getRows(req)
log = [{ seq: seq++, request: req, ms: Math.round(performance.now() - t0) }, ...log].slice(0, 12)
return result
},
}
// ---- The model ---------------------------------------------------------------
type Row = ServerRowModelGridRow<Sale>
let view = $state<ServerRowModelState<Sale>>()
let pivot = $state(false)
let groupBy = $state<string[]>(['region', 'country'])
const usd = { type: 'number' as const, options: { style: 'currency' as const, currency: 'USD', maximumFractionDigits: 0 } }
const groupColumn: GridColumns<Row>[number] = {
id: 'group',
header: 'Group',
width: 220,
sortable: false,
filterable: false,
fieldFn: (row) => serverGroupText(row, 'rep'),
cell: (ctx) =>
renderComponent(SvGroupCell, {
row: ctx.row.original,
onToggle: () => ctl.group.onToggle(ctx.row.original),
leafField: 'rep',
}),
}
const ctl: ServerRowModel<Sale> = createServerRowModel<Sale>(source, {
groupBy: [...groupBy],
aggregations: [
{ col: 'amount', fn: 'sum' },
{ col: 'qty', fn: 'sum' },
],
grandTotalRow: 'pinnedBottom',
childCount: (r) => (r as { childCount?: number }).childCount,
pivotBy: ['year'],
pivotMode: false,
// One value column per (year x measure): the field name says which measure.
pivotResultColumn: (field, def) =>
field.endsWith('_qty') ? { ...def, header: 'Qty', width: 90, format: { type: 'number' } } : { ...def, header: 'Amount', width: 120, format: usd },
// Pivot columns replace the grid's columns; the group column leads them.
pivotLeadingColumns: [groupColumn],
blockSize: 50,
skeletonRows: 4,
filterValues: async (columnId) => [...new Set(DB.map((r) => String(r[columnId as keyof Sale])))].sort(),
onChange: (s) => (view = s),
})
ctl.refresh()
$effect(() => () => ctl.dispose())
function setGroupBy(next: string[]) {
groupBy = next
ctl.setGroupBy(next)
}
function setPivot(next: boolean) {
pivot = next
ctl.setPivot({ pivotMode: next })
}
const groupCols = [
{ id: 'region', label: 'Region' },
{ id: 'country', label: 'Country' },
{ id: 'rep', label: 'Rep' },
{ id: 'product', label: 'Product' },
{ id: 'year', label: 'Year' },
]
// ---- Columns -----------------------------------------------------------------
const columns = $derived<GridColumns<Row>>([
...(groupBy.length
? [{ ...groupColumn, header: groupBy.map((g) => g[0]!.toUpperCase() + g.slice(1)).join(' / ') }]
: [{ field: 'rep', header: 'Rep', width: 120 } as GridColumns<Row>[number]]),
{ field: 'product', header: 'Product', width: 120 },
{ field: 'year', header: 'Year', width: 80, align: 'right' },
{ field: 'qty', header: 'Qty', width: 90, align: 'right', format: { type: 'number' } },
{ field: 'amount', header: 'Amount', width: 130, align: 'right', format: usd },
])
const latest = $derived(log[0] ?? null)
const statements = $derived(latest ? statementsFor(latest.request, dialect) : [])
const describe = (r: ServerRequest) => {
const parts = [`rows ${r.startRow}-${r.endRow}`]
if (r.groupKeys?.length) parts.push(`under ${r.groupKeys.join(' > ')}`)
else if (r.groupBy?.length) parts.push('top level')
if (r.sortModel.length) parts.push(`sort ${r.sortModel.map((s) => `${s.id}${s.desc ? ' desc' : ''}`).join(', ')}`)
const cols = Object.keys(r.filterModel.columns ?? {})
if (cols.length) parts.push(`filter ${cols.join(', ')}`)
if (r.filterModel.global) parts.push(`search "${r.filterModel.global}"`)
if (r.pivotMode) parts.push(`pivot ${r.pivotBy?.join(', ')}`)
if (r.needsGrandTotal) parts.push('+ grand total')
return parts.join(' · ')
}
</script>
<section class="wrap">
<header class="chrome">
<div class="seg dialect-seg" role="group" aria-label="SQL dialect">
{#each Object.keys(DIALECTS) as d (d)}
<button type="button" class:is-on={dialect === d} aria-pressed={dialect === d} onclick={() => (dialect = d as DialectName)}>{DIALECT_LABEL[d as DialectName]}</button>
{/each}
</div>
<label class="chk"><input type="checkbox" checked={pivot} onchange={(e) => setPivot(e.currentTarget.checked)} /> Pivot by year</label>
<span class="note">
Expand a region, sort a column, open a column filter, type a search, turn the pivot on: each is one
request, and the panel shows the statements <code>planToSql</code> renders for it in the dialect you
picked. <code>planQuery</code> admits only fields the schema declares, so a request cannot name a
column that is not there.
</span>
</header>
<SvRowGroupPanel columns={groupCols} {groupBy} onChange={setGroupBy} />
<div class="body">
<div class="gridpane">
<SvGrid
responsive={true}
columnResize
rowModel={ctl}
{columns}
{features}
sortable
filterable
filterMode="menu"
selectionMode="none"
rowHeight={32}
containerHeight="100%"
/>
</div>
<aside class="sql" aria-label="SQL for the last request">
<div class="sql-head">
<span>Last request</span>
{#if latest}<span class="muted">{describe(latest.request)} · {latest.ms} ms</span>{/if}
</div>
{#if latest}
{#each statements as st, i (i)}
<div class="sql-block">
<div class="sql-label">{st.label}</div>
<pre class="sql-text">{st.sql}</pre>
{#if st.params.length}<div class="sql-params">params: {JSON.stringify(st.params)}</div>{/if}
</div>
{/each}
{:else}
<div class="muted sql-empty">No request yet.</div>
{/if}
</aside>
</div>
</section>
<style>
.wrap { display: flex; flex-direction: column; flex: 1; gap: 10px; height: 100%; min-height: 0; }
.chrome { display: flex; align-items: center; gap: 12px; flex-wrap: wrap; flex: none; }
.note { font-size: 12px; color: var(--sg-muted, #64748b); flex: 1 1 320px; }
.chk {
display: inline-flex;
align-items: center;
gap: 5px;
font-size: 12px;
color: var(--sg-fg, #0f172a);
white-space: nowrap;
}
.chk input { accent-color: var(--sg-accent, #2563eb); }
.seg {
display: inline-flex;
flex: none;
border: 1px solid var(--sg-border, #e2e8f0);
border-radius: 6px;
overflow: hidden;
background: var(--sg-bg, #fff);
}
.seg > button {
font: inherit;
font-size: 12px;
padding: 3px 10px;
border: 0;
background: transparent;
color: var(--sg-fg, #0f172a);
cursor: pointer;
white-space: nowrap;
}
.seg > button + button { border-left: 1px solid var(--sg-border, #e2e8f0); }
.seg > button.is-on { background: var(--sg-accent, #2563eb); color: var(--sg-on-accent, #fff); }
.seg > button:focus-visible { outline: 2px solid var(--sg-accent, #2563eb); outline-offset: -2px; }
.body { display: flex; gap: 10px; flex: 1; min-height: 0; }
.gridpane { flex: 1; min-width: 0; min-height: 0; }
.sql {
width: 400px;
flex: none;
overflow: auto;
border: 1px solid var(--sg-border, #e2e8f0);
border-radius: 10px;
background: var(--sg-bg, #fff);
font-size: 12px;
}
.sql-head {
position: sticky;
top: 0;
display: flex;
flex-direction: column;
gap: 2px;
padding: 8px 10px;
font-weight: 600;
color: var(--sg-fg, #0f172a);
background: var(--sg-header-bg, #f8fafc);
border-bottom: 1px solid var(--sg-border, #e2e8f0);
}
.sql-block { padding: 8px 10px; border-bottom: 1px solid var(--sg-border, #e2e8f0); }
.sql-label { font-size: 10.5px; font-weight: 600; letter-spacing: 0.04em; text-transform: uppercase; color: var(--sg-muted, #64748b); margin-bottom: 4px; }
.sql-text {
margin: 0;
white-space: pre-wrap;
word-break: break-word;
font-family: ui-monospace, SFMono-Regular, Menlo, Consolas, monospace;
font-size: 11.5px;
line-height: 1.45;
color: var(--sg-fg, #0f172a);
}
.sql-params { margin-top: 4px; font-family: ui-monospace, SFMono-Regular, Menlo, Consolas, monospace; font-size: 11px; color: var(--sg-muted, #64748b); word-break: break-all; }
.sql-empty { padding: 10px; }
.muted { color: var(--sg-muted, #64748b); font-weight: 400; }
@media (max-width: 900px) {
.body { flex-direction: column; }
.sql { width: auto; max-height: 220px; }
}
</style>More Server-Side Row Model examples
- Server-Side Row Model: 1,000,000 rows - One grid, one rowModel prop, a million rows that stay on the server. Sort, filter, global search, grouping to any depth (Region > Country > Rep), infinite scroll or paging, inline edits applied back as transactions with the subtotal following, add and delete, select-all across rows the grid never loaded with a bulk edit by rule, failed blocks with Retry, and a request log that shows every call to the columnar warehouse behind it. The row model ships in @svgrid/enterprise; the datasource contract is free.
- Server-side pivot - The pivot designer in server mode over a million rows: Rows become groupBy, Columns pivotBy, Values aggregations, and every applied layout is one request. The backend answers with one field per pivot key and aggregation and lists them in pivotResultFields; the model builds the column groups from that list. Apply / Cancel hold a slice-and-dice session to one request, and a grand total row is pinned at the bottom.
- Server grouping (row model) - Server-side grouping through one getRows contract: the request carries groupBy + groupKeys, and createServerRowModel owns the group tree - a block cache per level, lazy expand, per-group sums and a subtotal footer, race-safety - mounted through the one rowModel prop. Leaves arrive by scroll, behind a Load N more row, or paged across the whole tree, and the group panel regroups on the fly. Here a 63,000-row in-memory server behind 200ms latency; the grid holds only the groups you expand. The row model ships in @svgrid/enterprise.
- Server tree data (row model) - A file tree the grid never holds whole: expanding a folder is one getRows with the folder path as groupKeys, answered with that folder's entries one block at a time. createServerRowModel in treeData mode owns the lazy expand, a block cache per folder, open-by-default, expand and collapse all, a per-folder refresh that re-reads one folder in place, and transactions that add or delete a file without a refetch. The server generates each folder from a seeded PRNG on first request, five levels deep.
- Server transactions (live feed) - A socket-style feed of changes the server already made, applied without a refetch: a price tick patches the loaded row in place with a flash (updateRowData), a new order lands at the top of its warehouse and a shipped one leaves (applyTransactionAsync, batched every 500 ms, addressed by route). Every result carries a status the log shows: applied, cancelled under the veto hook, storeNotFound for a warehouse whose level is not cached. Refresh totals recomputes the sums a transaction leaves alone.