Is UQL a fit for EmDash's dynamic content types?

[wildfiremedia]

Not sure if this is the right place to ask, but curious about something. @ascorbic

I mostly just write raw SQL, so I'm not an ORM person really, but I stumbled on this thing called UQL (https://www.uql-orm.dev/) and it supports basically every DB out there, Postgres, MySQL/MariaDB, SQLite, MongoDB, CockroachDB, Supabase, Turso/libSQL, Cloudflare D1, Bun's SQL, PGlite, all of it.

Made me wonder, EmDash lets people create content types on the fly through the admin UI, right? So the tables don't exist until someone builds them in the UI. Most ORMs (and I think UQL too) want you to define your tables as code first. I noticed the README mentions npx emdash types regenerating TS types off the live schema. Not sure if that's the same type layer that backs Kysely's Database interface internally, or if the dynamic content tables get queried through Kysely more loosely underneath. That's actually the crux of what I'm trying to understand. Does that mean an entity-first ORM like UQL just structurally can't work for the dynamic content part, or was Kysely more "we wanted something light and low-level" and the dynamic schema thing isn't actually the blocker I think it is?

Just trying to understand if this is a closed question or not, not saying switch anything.

The UQL project has its own comparison against other ORMs (worth a read even though it's the author's own writeup, not a third-party benchmark):
https://www.uql-orm.dev/comparison

Their own benchmark:
https://www.uql-orm.dev/benchmark

@rogerpadilla ping, curious what you think too.

[rogerpadilla]

Hello @wildfiremedia, thanks for the ping; and yeah I am open to collaborate for sure.

EmDash uses portable abstractions at every layer -- Kysely for SQL, S3 API for storage -- that work with SQLite, D1, Turso, PostgreSQL, R2, AWS S3, or local files

I checked the emdash intro in the readme.md and can see this ^ note.

So I guess emdash already uses a SQL query builder to be portable across DBs from what I can understand.

IDK if non-relation DBs would also be a good fit/target for emdash, but if so, there is also where UQL shines, because it uses a unified query language based on JSON (and inspired on Mongo syntax), for all kind of databases, with the strongest type safety.

[wildfiremedia]

Thanks for the reply, and yeah, that tracks, Kysely's already solving the multi-DB portability problem for the relational side. Where I think UQL would actually add something new is if EmDash ever wanted a non-relational target like Mongo, that's structurally outside what Kysely can do at all.

That said, this doesn't quite touch the actual question I was trying to get at in the original post, the dynamic content types. Tables don't exist until someone builds them through the admin UI at runtime, there's no code-defined schema ahead of time. Does UQL's imperative defineEntity support registering an entity from a shape only known at runtime, not written by a developer beforehand? And if so, how much of the "strongest type safety" survives that, since it relies on TypeScript checking entities at compile time, which isn't possible for a table that didn't exist when the code was compiled.

Curious if that's solved already or genuinely open, even on the Mongo side, since schemaless just means the database won't complain, it doesn't mean TypeScript knows the shape yet.

[rogerpadilla]

Does UQL's imperative defineEntity support registering an entity from a shape only known at runtime, not written by a developer beforehand?

Yes, and I took some time to investigate how EmDash works internally before coming with an answer (and updated UQL docs).

So, AFAIK, an EmDash's content-type from _emdash_fields rows would register like any other entity:

const entity = { [ct.name]: class { id!: string; [column: string]: Scalar } }[ct.name];

defineEntity(entity, {
  name: ct.name,
  fields: { id: { type: 'uuid', isId: true }, ...columnsOf(ct) }, // columnsOf is yours
});

// existence check, then CREATE TABLE IF NOT EXISTS - no catalogue read
await new Migrator(pool).sync({ entity });

See more at new docs: https://www.uql-orm.dev/entities/runtime

And if so, how much of the "strongest type safety" survives that, since it relies on TypeScript checking entities at compile time, which isn't possible for a table that didn't exist when the code was compiled.

This part isn't unique to UQL: any ORM that builds its metadata at runtime has this, column names can't be checked, since none existed when your code compiled.

What does survive, vs the raw sql EmDash is using for ec_* today, is the query API itself: namely $where, $sort, $populate, projections that shape the returned row, every operator, and an ID that keeps its type (generate an interface per content type and the names come back too).