#Portable SQL

Repository methods cover most queries, but analytics work — grouping by day, measuring elapsed time, bounding a rolling window — needs raw SQL. Written by hand, that SQL is not portable.

1import { $inject, z } from "alepha";2import { $repository, DatabaseProvider, SqlExpressionProvider } from "alepha/orm";3import { sql } from "drizzle-orm";

#Why dates diverge

There is one root cause: Postgres stores timestamps as native timestamps, SQLite stores them as epoch-millisecond integers. Cloudflare D1 is SQLite, so any app that develops on Postgres and deploys on D1 hits this on its first chart.

The result is code like this, doubled for every expression:

1// Don't do this.2const isSqlite = this.database.dialect === "sqlite";3const day = isSqlite4  ? sql`DATE(${this.quests.table.createdAt} / 1000, 'unixepoch')`5  : sql`DATE(${this.quests.table.createdAt})`;

Two branches, two chances to get it wrong, and no test catches a divergence until someone compares a local chart against production.

#SqlExpressionProvider

Inject it and write the expression once:

 1class CampaignStatsController { 2  protected readonly sqlx = $inject(SqlExpressionProvider); 3  protected readonly database = $inject(DatabaseProvider); 4  protected readonly quests = $repository(quests); 5  6  questsPerDay = async () => { 7    return this.database.run( 8      sql`SELECT ${this.sqlx.dateDay(this.quests.table.createdAt)} AS day, 9                 COUNT(*) AS total10          FROM ${this.quests.table}11          GROUP BY 112          ORDER BY 1`,13      z.object({ day: z.text(), total: z.coerce.number() }),14    );15  };16}

#dateDay(column)

A timestamp truncated to its day, as sortable 'YYYY-MM-DD' text.

Text on both dialects is deliberate. Postgres DATE(col) decodes to a Date while SQLite yields a string, so a single result schema could not describe both. Returning text means one z.text() covers every database.

#dateWeek(column)

A sortable ISO year-week label, e.g. '2026-W11'.

ISO on both dialects, also deliberate. Postgres IYYY-IW and SQLite %Y-%W are different numbering schemes — %W counts Monday-started weeks from the first Monday of the year (00–53), ISO weeks run 01–53 with different year-boundary rules. Hand-written code that pairs them produces different labels for the same row depending on where it runs.

SQLite has no ISO week function, so this uses the Thursday rule: the ISO week of a date is the week containing the Thursday of that date's Monday-started week, and the ISO year is that Thursday's calendar year.

1const weekly = await this.database.run(2  sql`SELECT ${this.sqlx.dateWeek(this.quests.table.completedAt)} AS week,3             COUNT(*) AS total4      FROM ${this.quests.table}5      WHERE ${this.quests.table.completedAt} IS NOT NULL6      GROUP BY 1 ORDER BY 1`,7  z.object({ week: z.text(), total: z.coerce.number() }),8);

#dateDiff(end, start, unit)

Elapsed time between two timestamp columns, as a floating-point count of "seconds" | "minutes" | "hours" | "days".

NULL on either side propagates rather than collapsing to zero, so it composes with AVG over partially-complete rows — an unfinished row is skipped, not averaged in as 0.

1const [cycle] = await this.database.run(2  sql`SELECT AVG(${this.sqlx.dateDiff(3        this.quests.table.completedAt,4        this.quests.table.acceptedAt,5        "hours",6      )}) AS hours7      FROM ${this.quests.table}`,8  z.object({ hours: z.number().nullable() }),9);

The Postgres form is cast to double precision. Without it, EXTRACT(EPOCH …) yields numeric, which the Postgres driver returns as a string to protect precision — so the same query would decode as a number on SQLite and a string on Postgres. The cast makes both a JS number, which is why the schema above is z.number() and not z.coerce.number().

#ago(amount, unit)

A timestamp amount × unit before now, comparable against a timestamp column.

1const recent = await this.database.run(2  sql`SELECT COUNT(*) AS n FROM ${this.quests.table}3      WHERE ${this.quests.table.completedAt} >= ${this.sqlx.ago(7, "days")}`,4  z.object({ n: z.coerce.number() }),5);

Instant-aligned on both dialects, not midnight-aligned. If you want calendar-day boundaries, bucket with dateDay instead of reaching for ago — mixing the two is how a "last 7 days" window comes to mean different things on different databases.

#COUNT and AVG still need coercion

The helpers cover their own output, not yours. Postgres returns COUNT(*) (bigint) and bare AVG(numeric) as strings; SQLite returns numbers. For aggregates you write yourself, decode with z.coerce.number():

1z.object({ total: z.coerce.number() })

dateDiff is the exception — it casts, so z.number() is correct there.

#When to keep writing raw SQL

These helpers are date-shaped on purpose. Reach for raw sql when the expression is not:

• upserts and counter increments (SET count = count + 1)
• LIKE / JSON containment scans
• window functions, CASE ladders, recursive CTEs

Those are portable already, or portable enough that a helper would add indirection without removing a branch. The test for whether something belongs here is simple: does it need a dialect === check? If yes, it is a candidate. If no, write the SQL.

#Testing

Anything built on these helpers should be tested against both dialects, because that is the whole point. The framework's own suite uses a shared test function run twice:

 1describe("dateWeek", () => { 2  it("should return ISO week labels (sqlite)", async () => { 3    await testDateWeek( 4      Alepha.create({ env: { DATABASE_URL: "sqlite://:memory:" } }), 5    ); 6  }); 7  it("should return ISO week labels (postgres)", async () => { 8    await testDateWeek(Alepha.create().with(AlephaOrmPostgres)); 9  });10});