# How DBX Generates SQL for Data Grid and Virtual‑Scrolled Tables

> Learn how DBX generates database-agnostic SQL for data grids and virtual-scrolled tables. Discover its pipeline for converting metadata and pagination into dialect-specific SELECT statements.

- Repository: [skyler/dbx](https://github.com/t8y2/dbx)
- Tags: how-to-guide
- Published: 2026-07-05

---

**DBX generates database‑agnostic SQL through a centralized pipeline that converts table metadata and pagination parameters into dialect‑specific SELECT statements with proper identifier quoting.**

The DBX project (`t8y2/dbx`) implements a sophisticated SQL generation layer that powers its high‑performance data grid and virtual‑scrolled tables. Instead of loading entire datasets into memory, DBX dynamically constructs paginated queries that respect each database’s specific syntax requirements. This article examines the exact mechanism by which DBX generates SQL for data grid views, tracing the flow from UI components through to the backend execution layer.

## The SQL Generation Pipeline

DBX constructs SQL through a five‑stage pipeline that separates UI concerns from database‑specific syntax generation.

### Step 1: Requesting Paginated Data

The process begins when the data grid component requests a page of results. In [`apps/desktop/src/composables/useDataGridActions.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/composables/useDataGridActions.ts), the composable collects the table identifier, page size, and offset, then invokes the SQL builder.

```typescript
// apps/desktop/src/composables/useDataGridActions.ts
async function loadPage (table: TableInfo, page: number, pageSize: number) {
  const sql = await buildTableSelectSql({
    databaseType: table.databaseType,
    schema: table.schema,
    tableName: table.name,
    limit: pageSize,
    offset: page * pageSize,          // virtual‑scroll offset
  })
  return api.runQuery({ sql })
}

```

This approach ensures that the UI never loads the complete table, keeping memory usage constant regardless of table size.

### Step 2: Quoting Identifiers Safely

Before constructing the statement, DBX must generate syntactically correct identifiers for the target database type. The `quoteTableIdentifier` function in [`apps/desktop/src/lib/table/tableSelectSql.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/table/tableSelectSql.ts) applies the appropriate quoting characters—double quotes for PostgreSQL, backticks for MySQL, or square brackets for SQL Server.

```typescript
// apps/desktop/src/lib/table/tableSelectSql.ts
export function quoteTableIdentifier({ databaseType, schema, tableName }) {
  const q = (ident) => quoteIdentifier(ident, databaseType)   // uses DB‑type‑aware quoting
  return schema ? `${q(schema)}.${q(tableName)}` : q(tableName)
}

```

This guarantees that identifiers containing spaces or reserved words remain valid across all supported engines.

### Step 3: Constructing the SELECT Statement

The actual SQL assembly happens in the backend adapters. In [`apps/desktop/src/lib/backend/http.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/backend/http.ts), the `buildTableSelectSql` function receives an options object containing `databaseType`, `schema`, `tableName`, `columns`, `limit`, and `offset`. It generates a statement following the pattern:

```sql
SELECT <columns>
FROM <quoted‑table>
[WHERE <filter‑clause>]
ORDER BY <order‑by‑if‑needed>
LIMIT <limit> OFFSET <offset>

```

The exact syntax varies by `databaseType`. For example, MySQL and PostgreSQL use `LIMIT … OFFSET …`, while SQL Server uses `TOP …` and Oracle uses `FETCH FIRST … ROWS ONLY`.

```typescript
// apps/desktop/src/lib/backend/http.ts
export async function buildTableSelectSql(options) {
  const {
    databaseType = 'sqlite',
    schema,
    tableName,
    columns = '*',
    limit,
    offset,
  } = options

  const quoted = quoteTableIdentifier({ databaseType, schema, tableName })
  const cols = Array.isArray(columns) ? columns.map(c => quoteIdentifier(c, databaseType)).join(', ') : columns

  // Generic LIMIT/OFFSET – DB‑specific tweaks are added in later versions
  const sql = `SELECT ${cols} FROM ${quoted} LIMIT ${limit} OFFSET ${offset}`
  return sql
}

```

### Step 4: Routing Through the Backend Abstraction

DBX uses a thin forwarder pattern to route SQL generation requests to the appropriate backend implementation. The [`apps/desktop/src/lib/backend/api.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/backend/api.ts) file defines a `forward` helper that sends the request to either the native Tauri bridge or the HTTP server, depending on the build configuration.

```typescript
// apps/desktop/src/lib/backend/api.ts
export const buildTableSelectSql = forward('buildTableSelectSql')

```

This abstraction allows the same UI code to run against both local SQLite databases via Tauri and remote PostgreSQL or MySQL instances via HTTP without modification.

### Step 5: Executing and Streaming Results

Finally, the generated SQL string passes to the database driver layer (`api.runQuery`), which executes the statement and streams rows back to the UI component. The data grid renders only the received slice, triggering a new request only when the user scrolls beyond the current page boundaries.

## Key Implementation Files

The SQL generation system spans several critical files in the DBX codebase:

- **[`apps/desktop/src/lib/table/tableSelectSql.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/table/tableSelectSql.ts)** – Public façade that UI components call for SQL generation and the `quoteTableIdentifier` implementation.
- **[`apps/desktop/src/lib/backend/http.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/backend/http.ts)** – HTTP‑side implementation of `buildTableSelectSql` with dialect‑specific query construction.
- **[`apps/desktop/src/lib/backend/tauri.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/backend/tauri.ts)** – Tauri‑side implementation using identical logic but different transport mechanisms.
- **[`apps/desktop/src/lib/backend/api.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/backend/api.ts)** – Forwarding layer that routes calls to the correct backend implementation.
- **[`apps/desktop/src/composables/useDataGridActions.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/composables/useDataGridActions.ts)** – Vue composable that coordinates pagination and virtual scrolling requests.
- **[`apps/desktop/src/stores/queryStore.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/stores/queryStore.ts)** – State management for active queries and pagination metadata.

## Why Virtual Scrolling Requires This Architecture

Virtual scrolling demands that the UI display only the currently visible subset of a potentially massive dataset. By generating specific `LIMIT … OFFSET …` queries (or their dialect equivalents), DBX ensures that:

- **Memory remains constant** – Only the page size (typically 50‑200 rows) resides in browser memory.
- **Initial load stays fast** – The grid renders immediately without waiting for millions of rows to transfer.
- **Database optimization works** – The database engine can use indexes efficiently rather than performing full table scans.

This architecture also enables **database agnosticism**. Centralizing SQL generation in `buildTableSelectSql` allows DBX to support PostgreSQL, MySQL, SQLite, SQL Server, and Oracle from a single codebase, with dialect‑specific handling encapsulated in the backend adapters.

## Summary

DBX generates SQL for its data grid and virtual‑scrolled tables through a pipeline that:

- **Encapsulates complexity** in [`apps/desktop/src/lib/table/tableSelectSql.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/table/tableSelectSql.ts) and backend adapters.
- **Quotes identifiers safely** using `quoteTableIdentifier` to prevent syntax errors across database types.
- **Constructs paginated queries** with `LIMIT` and `OFFSET` clauses (or dialect equivalents) to support virtual scrolling.
- **Routes execution** through a forwarding abstraction in [`apps/desktop/src/lib/backend/api.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/backend/api.ts) that supports both HTTP and Tauri transports.
- **Maintains performance** by loading only the visible data slice, keeping memory usage low for tables containing millions of rows.

## Frequently Asked Questions

### How does DBX handle different SQL dialects?

DBX detects the `databaseType` (e.g., `postgresql`, `mysql`, `sqlite`) passed to `buildTableSelectSql` and adjusts the generated syntax accordingly. For instance, PostgreSQL and MySQL use `LIMIT … OFFSET …`, while SQL Server uses `TOP …` and Oracle uses `FETCH FIRST … ROWS ONLY`. This logic resides in the backend adapter files such as [`apps/desktop/src/lib/backend/http.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/backend/http.ts).

### Is DBX's SQL generation vulnerable to injection attacks?

DBX mitigates injection risks through strict identifier quoting. The `quoteTableIdentifier` and `quoteIdentifier` functions in [`apps/desktop/src/lib/table/tableSelectSql.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/table/tableSelectSql.ts) ensure that table names, schemas, and column names are properly escaped according to the target database's rules. However, for user‑provided filter values, DBX relies on parameterized queries executed by the underlying database drivers (`sqlite3`, `pg`, `mysql2`, etc.) rather than string concatenation.

### Why does DBX use a forward pattern in the API layer?

The `forward` pattern in [`apps/desktop/src/lib/backend/api.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/backend/api.ts) creates a clean abstraction between the UI and the execution environment. This allows the same `buildTableSelectSql` call to work transparently whether the application runs as a desktop Tauri app with local SQLite or connects to remote databases via HTTP, without requiring conditional logic in the UI components.

### Can I customize the generated SQL for specific tables?

Currently, DBX generates SQL through the centralized `buildTableSelectSql` function based on the options object passed from `useDataGridActions`. While the system supports custom column selection and basic filtering, advanced customization would require modifying the backend adapter implementations in [`apps/desktop/src/lib/backend/http.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/backend/http.ts) or [`apps/desktop/src/lib/backend/tauri.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/backend/tauri.ts) to handle additional query parameters.