alepha@docs:~/docs/guides/persistence$
cat 8-portable-sql.md | pretty
4 min read
Last commit:

#Portable SQL

Analytics work - grouping by day, measuring elapsed time, bounding a rolling window - needs date expressions, and written by hand those are not portable. SqlExpressionProvider writes them once for every dialect. Its expressions go straight into repository.aggregate() as { expr }, or into raw SQL when the query leaves the repository's model.

typescript 
1import { $inject, z } from "alepha";2import {3  $repository,4  DatabaseProvider,5  sql,6  SqlExpressionProvider,7} from "alepha/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:

typescript 
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. Through aggregate(), typed from the expression:

typescript 
 1class CampaignStatsController { 2  protected readonly sqlx = $inject(SqlExpressionProvider); 3  protected readonly quests = $repository(quests); 4  5  questsPerDay = async () => { 6    const t = this.quests.table; 7    return this.quests.aggregate({ 8      select: { 9        day: { expr: this.sqlx.dateDay(t.createdAt) },10        id: { count: true },11      },12      groupBy: ["day"],13      orderBy: "day",14    });15    // -> Array<{ day: string; id: { count: number } }>16  };17}

Or in raw SQL:

typescript 
 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.

#dateHour(column)

A timestamp truncated to its hour, as sortable 'YYYY-MM-DDTHH' text (a T, not a space). Its day is a prefix of its hour, and it is the bucket key alepha/api/analytics stores, so a query can fold a table into analytics buckets with no date arithmetic. Timezone handling is dateDay's, so the two never disagree about which day an hour belongs to.

#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 to 53), ISO weeks run 01 to 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.

typescript 
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.

typescript 
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.

Prefer a bound computed from DateTimeProvider instead:

typescript 
1const [recent] = await this.quests.aggregate({2  select: { id: { count: true } },3  where: {4    completedAt: { gte: this.dateTime.now().subtract(7, "day").toISOString() },5  },6});

ago() reads the database clock (NOW() on Postgres, STRFTIME('%s', 'now') on SQLite), so DateTimeProvider.travel() cannot move it and a "last 7 days" test is not reproducible. A bound from DateTimeProvider reads the application clock, which tests control. ago() stays for raw SQL that wants the database's own notion of now:

typescript 
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

aggregate() coerces count, sum and avg itself. In raw SQL, 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():

typescript 
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:

  • 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. Upserts and counter increments are no longer on this list: upsert / upsertMany take a typed set, and repository.excluded(key) reads the incoming value of a column (see upsertMany). 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:

typescript 
 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});