FlashQL Language Reference

This page documents FlashQL's current SQL surface as it exists in the codebase today.

It is intentionally written as a usage reference, not as a speculative wishlist. Where support is partial or runtime-specific, that is stated plainly.

Two framing points matter before anything else:

• FlashQL speaks real SQL in PostgreSQL and MySQL flavors
• FlashQL also adds LinkedQL-specific syntax such as DeepRefs and version binding

Just as important is what this page is not saying:

• it does not claim full PostgreSQL or full MySQL compatibility
• it does not treat every parsed construct as fully supported at runtime
• it does not treat storage-transaction APIs as identical to SQL DDL support

Reading this page

Use this page in three ways:

• as a map of what statement families are already strong
• as a guide to FlashQL-specific language additions
• as a companion to the deeper capability docs

For more focused guides, also see:

• DeepRefs
• JSON Literals
• UPSERT
• Version Binding

1. Query language at a glance

FlashQL is strongest today in the application-facing query layer:

• SELECT
• INSERT
• UPDATE
• DELETE
• RETURNING
• WITH / CTEs
• joins, including LATERAL
• VALUES
• set-returning functions and ROWS FROM
• set operations such as UNION, INTERSECT, and EXCEPT
• window-function expressions used in tested execution paths

These are not just parser-level claims. They are exercised across the parser, desugaring layer, and engine tests.

2. DQL: querying data

Basic SELECT

const result = await db.query(`
  SELECT id, name
  FROM public.users
  WHERE active = true
  ORDER BY id
`);

Supported common building blocks include:

• projection and aliases
• WHERE
• ORDER BY
• LIMIT / OFFSET
• grouping and aggregates
• subqueries

Joins

FlashQL supports the mainstream join family:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• CROSS JOIN

And it also supports LATERAL joins in tested paths.

const result = await db.query(`
  SELECT t.id, s.val
  FROM public.t_nums t
  CROSS JOIN LATERAL generate_series(1, t.n) AS s(val)
  ORDER BY t.id, s.val
`);

const result = await db.query(`
  SELECT t.id, s.val
  FROM public.t_nums t
  LEFT JOIN LATERAL generate_series(1, t.n) AS s(val) ON true
  ORDER BY t.id, s.val
`);

VALUES and derived row sets

const result = await db.query(`
  SELECT *
  FROM (VALUES (1, 'Ada'), (2, 'Linus')) AS v(id, name)
  ORDER BY id
`);

This is useful when:

• you need a tiny inline dataset
• you want to join query data against app-supplied rows
• you are composing more advanced CTE pipelines

Set-returning functions and ROWS FROM

FlashQL parses and executes tested forms such as:

const result = await db.query(`
  SELECT *
  FROM ROWS FROM (
    generate_series(1, 2),
    unnest(ARRAY['a','b'])
  ) AS t(c1, c2)
`);

This matters because FlashQL is not restricted to base tables. It can treat function output as relational input.

Set operations

Supported and tested:

• UNION
• UNION ALL
• INTERSECT
• EXCEPT

const result = await db.query(`
  SELECT id FROM public.a
  UNION ALL
  SELECT id FROM public.b
`);

CTEs

Common table expressions are an important part of FlashQL's usable language surface.

const result = await db.query(`
  WITH active_users AS (
    SELECT id, name
    FROM public.users
    WHERE active = true
  )
  SELECT *
  FROM active_users
  ORDER BY id
`);

Writable CTE pipelines are also part of the tested surface when combined with RETURNING.

3. DML: writing data

INSERT

await db.query(`
  INSERT INTO public.users (id, name)
  VALUES (1, 'Ada'), (2, 'Linus')
`);

Common tested forms include:

• single-row insert
• multi-row insert
• DEFAULT VALUES
• INSERT ... RETURNING

UPDATE

await db.query(`
  UPDATE public.users
  SET name = 'Ada Lovelace'
  WHERE id = 1
`);

Tested forms include:

• regular SET expressions
• UPDATE ... FROM
• UPDATE ... RETURNING

DELETE

await db.query(`
  DELETE FROM public.users
  WHERE id = 1
  RETURNING *
`);

Tested forms include:

• regular conditional deletes
• DELETE ... USING
• DELETE ... RETURNING

RETURNING

RETURNING is a major part of the FlashQL ergonomics story.

It supports:

• RETURNING *
• named columns
• aliases
• expressions
• function calls
• subqueries

const result = await db.query(`
  INSERT INTO public.returning_demo (id, val)
  VALUES (1, 'x')
  RETURNING id * 10 AS id_times_10, upper(val) AS upper_val
`);

UPSERT

FlashQL supports PostgreSQL-style INSERT ... ON CONFLICT.

const result = await db.query(`
  INSERT INTO public.users (id, name, age)
  VALUES (30, 'SamX', 41)
  ON CONFLICT (id) DO UPDATE
  SET name = EXCLUDED.name, age = EXCLUDED.age
  RETURNING *
`);

See also: UPSERT

4. Window functions

Window functions are part of the parser surface and are exercised in engine tests for tested shapes such as:

• ROW_NUMBER() OVER (...)
• SUM(amount) OVER ()
• RANK() OVER (...)

const result = await db.query(`
  WITH changed AS (
    UPDATE public.returning_demo
    SET val = 'y'
    WHERE id = 1
    RETURNING id, val
  )
  SELECT
    id,
    val,
    ROW_NUMBER() OVER (ORDER BY id) AS rn
  FROM changed
`);

This is one of the areas where the older docs were badly behind the code. Window-function support is not merely "planned."

5. DDL and schema operations

FlashQL supports meaningful parts of DDL, but this is also where its surface is less complete than its DQL/DML surface.

Supported today

At the SQL-facing level, tested and used support includes:

• CREATE TABLE
• DROP TABLE

At the storage-transaction level, support exists for:

• createTable()
• alterTable()
• dropTable()
• createView()
• alterView()
• dropView()
• createNamespace()

Important nuance about views

FlashQL has a strong view model, but it is currently centered on the storage transaction API rather than on a full SQL CREATE VIEW surface.

In other words:

• view behavior is very real
• sync-enabled views are very real
• but you should not read that as "full SQL CREATE VIEW parity is finished"

Important nuance about ALTER

Some schema-evolution paths exist, but broader DDL parity is still catching up. If your application depends heavily on runtime ALTER TABLE compatibility with mainstream servers, treat that as an area requiring validation.

6. Transactions

FlashQL supports transactions at the client API level:

await db.transaction(async (tx) => {
  const users = tx.getTable({ namespace: 'public', name: 'users' });
  await users.insert({ id: 1, name: 'Ada' });
});

That is the supported application-facing transaction model today.

This does not mean the SQL command family:

• BEGIN
• COMMIT
• ROLLBACK

should be read as the primary way to control transactions in FlashQL. The JS transaction API is the intended surface.

7. JSON literals and structured data

FlashQL extends SQL with native JSON-style literals.

See:

• JSON Literals
• Structured Writes

This matters especially when you want application-shaped SQL without constantly escaping back into imperative JavaScript.

8. DeepRefs

DeepRefs are a LinkedQL language extension for relational traversal and structured writes.

Operators include:

• ~> for forward traversal
• <~ for reverse traversal

Example:

SELECT title, author ~> name
FROM public.posts

DeepRefs also show up in write syntax and desugaring workflows.

See: DeepRefs

9. Version binding

FlashQL supports version-qualified relation references such as:

SELECT *
FROM public.users@=3

And in joins:

SELECT a.id, b.name
FROM public.vja@=1 a
LEFT JOIN public.vjb@=1 b ON a.rel = b.id

What version binding means here:

• the query states the relation version it expects
• if the stored relation version does not satisfy that expectation, the query fails

What it does not mean:

• it is not the same thing as historical row snapshotting

For point-in-time replay, use FlashQL boot options such as versionStop.

See: Version Binding

10. Point-in-time replay

Point-in-time replay is not a query operator. It is a FlashQL boot mode.

const historical = new FlashQL({
  keyval,
  versionStop: 'public.snap_tbl@1',
});

await historical.connect();

This replays persisted history to a chosen relation-version boundary and boots the engine there.

That is separate from version binding inside a query.

11. Dialect notes

PostgreSQL flavor

This is the strongest and most fully exercised dialect path today.

Particularly strong areas include:

• RETURNING
• ON CONFLICT
• ROWS FROM
• LATERAL
• version binding
• many parser and execution tests

MySQL flavor

MySQL-flavored parsing is supported, and FlashQL can switch dialect per client or per query. But the broadest execution coverage still centers on PostgreSQL-style usage.

That does not make MySQL support fake. It just means readers should calibrate expectations correctly.

12. Practical reading of "support"

When evaluating whether FlashQL supports a given language feature, use this order of confidence:

1. feature is exercised by parser, desugaring, and engine tests
2. feature is exercised at least by parser and engine tests
3. feature parses but is not yet documented here as runtime-stable

That is why this page focuses on the features that are clearly alive in the current codebase rather than pretending every parsed form has equal runtime maturity.

Where to go next

• Query Interface
• DeepRefs
• JSON Literals
• Structured Writes
• Version Binding