SQL tag redesign plan

Proposal for a dependency-free, dialect-aware SQL fragment API for Flowblade.

Status: proposal. This page describes a possible direction, not released behavior. The current @flowblade/sql-tag API remains the supported API until an implementation and migration path are released.

Goal

Replace the sql-template-tag implementation with a small Flowblade-owned fragment builder. Preserve the familiar tagged-template composition model while making target dialect rendering explicit and enabling future static tooling. Keep query execution, database connections, and schema introspection outside the SQL-tag package.

Use @typed-sql/postgres as a source of design ideas—not as a dependency or as a scope target. Its dialect-owned renderer, strict handling of unsupported syntax, query structure and runtime contracts provide useful lessons. Its PostgreSQL grammar, catalog introspection, schema inference and codecs are substantially broader than the Flowblade tag and should not be pulled into the initial rewrite.

Proposed API

A factory creates a tag whose render target is explicit:

import { createSqlTag } from "@flowblade/sql-tag";

const sql = createSqlTag({ dialect: "postgres" });

const query = sql<{ id: number; name: string }>`
  SELECT id, name
  FROM ${sql.identifier("person")}
  WHERE id IN (${sql.join(ids)})
  ${sql.if(name !== undefined, () => sql`AND name = ${name}`)}
`;

const compiled = query.compile();
// { sql: "...", values: [...] }

The generic row type remains a compile-time declaration by the caller. It does not validate database results. Database-aware type inference and runtime row validation would need schema evidence and belong in a separate analyzer or adapter feature.

The dialect option selects SQL rendering rules such as placeholders and identifier quoting; it does not parse or validate the SQL grammar. Initial dialects should be selected based on Flowblade adapter usage and verified against actual drivers. PostgreSQL, DuckDB, MySQL, SQLite and SQL Server are candidates, not a promise of immediate support for every engine/version.

Internal representation

Represent a query as an immutable sequence of literal text and typed interpolation parts rather than pre-rendered SQL strings:

• Value parts hold parameter values and are always bound, never stringified into SQL.
• Nested fragment parts compose other tagged queries without losing value order.
• Identifier parts are quoted by the selected dialect.
• Raw parts are an explicit unsafe escape hatch and remain clearly marked as trusted SQL text.

Render the complete fragment sequence only at compile(). This lets nested fragments and helpers share one placeholder counter, avoids renumbering bugs, and creates a natural future boundary for validation, fingerprints or prepared-statement shape checks. It also avoids pretending that interpolated values can safely stand for table names, column names or SQL keywords.

Safety and composition requirements

1. Interpolated JavaScript values are parameters by default. Rendering errors for unsupported values should be explicit; never silently convert objects, symbols or functions into SQL source.
2. identifier() quotes each identifier component according to the selected dialect. A qualified name should be represented as components rather than quoted as one dotted string.
3. raw() is documented as unsafe and reserved for trusted, application-owned SQL fragments. It must not be presented as a way to bind values.
4. join() composes values or fragments in order. Define and test empty-array behavior so callers cannot accidentally generate malformed or broader SQL.
5. bulk() produces a fragment with parameterized values and validates row width consistently; define empty input behavior explicitly.
6. if() evaluates only the selected branch. The current empty fragment and conditional composition remain available.

These features reduce accidental injection risks but cannot make arbitrary dynamic SQL safe. In particular, user input must not be passed to raw().

Dialect rendering

Each dialect renderer owns only the rules needed to compile fragments, at minimum:

• placeholder sequence and binding order;
• identifier quoting and escaping;
• any explicit dialect-specific rendering differences required by supported adapters.

Do not assume one placeholder style is interchangeable across drivers. Verify the rendered statement and values with each target driver. Keep rendering separate from formatting: a SQL formatter’s dialect option is not a query parameter renderer.

compile() should return one canonical result, initially proposed as:

type CompiledSql = {
  readonly sql: string;
  readonly values: readonly unknown[];
};

The current package exposes sql, text, statement and values through the upstream object. A migration must document which getters remain, what dialect they represent, and how they relate to compile(). Do not silently change their meanings. Keep the legacy default tag and output behavior during a deprecation period, or release a breaking major if equivalent compatibility cannot be maintained.

Lessons from typed-sql/postgres

The following ideas can improve Flowblade without copying its entire compiler/runtime:

@typed-sql/postgres also supports grammar-level analysis, catalog snapshots, live verification, plan inspection, and native bulk capabilities. These are valuable for a full typed query compiler, but are not requirements for this small SQL construction library.

Delivery plan

Phase 1: Fragment builder

• Implement immutable literal/value/fragment/identifier/raw parts without an external SQL-template dependency.
• Preserve sql, join, bulk, if, empty and generic row typing.
• Add createSqlTag() with a deliberately small, documented dialect set.
• Add compile() returning SQL and ordered values.
• Keep current imports and output properties working through compatibility aliases where feasible.

Phase 2: Contract and security tests

Test exact SQL and value order for basic templates, nested templates, multiple levels of composition, joins, conditionals, empty fragments and bulk rows. Test placeholder continuity across nested fragments, identifier escaping for each dialect, and rejection of invalid fragment inputs. Add driver-backed smoke coverage for every initially supported renderer, especially DuckDB, which consumes the tag today.

Phase 3: Adoption and migration

Update source-duckdb to construct or consume the DuckDB-targeted tag without changing query semantics. Audit other consumers for assumptions about .sql, .text and .statement. Document migration examples and deprecation timelines, then remove sql-template-tag only after no runtime or type dependency remains.

Later, separately scoped work

Evaluate typed table/column descriptors, schema-aware static checking, runtime codecs, query fingerprinting, and prepared-query shape validation as independent additions. Each requires its own public contract and evidence model; none should be implied by the base tag factory.

Acceptance criteria

• No runtime or declaration dependency on sql-template-tag.
• Existing composition features and adapter behavior are covered by regression tests.
• Every factory-created query renders deterministically for its declared dialect and returns values in matching placeholder order.
• Values remain parameterized; identifiers are escaped; raw SQL is explicitly unsafe.
• sql<Row> is documented as a type assertion, not schema validation.
• Compatibility behavior and breaking changes are explicit before release.

See the current SQL tag guide for released behavior and the @typed-sql/postgres execution guide for the driver-neutral execution and capability design that informed this proposal.