Joist 2.4 adds em.query and em.execute for the SQL-shaped queries and bulk writes that don't fit em.find and entity-based updates, plus lazy fields, pipelining on by default, and aligned soft-delete behavior.
This post explains pruning, a novel feature of the em.find query DSL in Joist, an entity-based ORM for TypeScript.
Specifically, we show how ergonomic pruning is for “semi-dynamic” endpoints that return the same kind of data on every request, such as GET /authors, but vary their filters, joins, and WHERE clauses according to the request parameters.
A quick example of Joist’s query DSL for GET /authors is:
// Supports multiple parameters, for example:// - `GET /authors?firstName=a1`// - `GET /authors?publisherId=1`// - `GET /authors?publisherName=p1`async function getAuthors(params: { firstName?: string; publisherId?: string; publisherName?: string;}): Promise<Author[]> { // Each variable is `string | undefined`, depending on what is sent const { firstName, publisherId, publisherName } = params; // We pass all 3 into `em.find` and it "just works" return em.find(Author, { firstName, // This join, and any other joins, are only added if needed publisher: { id: publisherId, name: publisherName }, });}Read on to learn how it works and how it compares with other query DSLs in the JavaScript and TypeScript ecosystem.
A key feature of any ORM, whether it uses a Knex-style query builder or a Joist-style domain model, is its query API: the DSL (domain-specific language) used to find rows in the database.
An ORM’s query DSL is usually one of its defining features. For query-builder libraries such as Knex, Drizzle, and Kysely, it is essentially the entire product, so query DSLs are a common area of exploration and innovation that can make or break the ergonomics of the library.
These DSLs can be as simple as “slightly decorated” SQL strings in postgres.js:
const rows = await sql`SELECT * FROM users WHERE name = ${name}`;They can also use fluent, method-chaining APIs such as Knex, Kysely, or Drizzle:
// Knexawait knex("users").where("name", "=", name);// Kyselyawait db.selectFrom('users').where('name', '=', name).execute();// Drizzleawait db.select().from(users).where(eq(users.name, name));Joist’s own em.find uses a POJO-style API:
const authors = await em.find(Author, { name });Each ORM generally tries to provide “the most idiomatic query DSL possible,” but this can become a contentious topic because the interpretation of “idiomatic” depends on:
What “looks idiomatic” in the host programming language
That is, an idiomatic TypeScript DSL differs from an idiomatic Java DSL, which in turn differs from an idiomatic Go DSL.
In my opinion, this is why Prisma’s circa-2021 experiment with a Rust-based engine shared by TypeScript and Go clients was quizzical from the start: Go and TypeScript differ sharply in taste and style.
What “looks idiomatic” depends on where the ORM sits on the “driver => query builder => domain model” spectrum.
That is, what looks idiomatic for a low-level node-postgres or postgres.js driver differs from what looks idiomatic for a higher-level Active Record or entity-based ORM such as MikroORM or Joist.
What “looks idiomatic” is fundamentally a personal judgment call.
(…except on Reddit, where the only correct answer is “raw SQL.” 💀)
That disclaimer aside, we’ll argue that Joist is idiomatic for an ORM at the domain-model end of the spectrum by examining two query patterns:
Here, “shape” means which columns and conditions the query uses and which tables or relationship paths it traverses.
A static query is one that always uses exactly the same tables and conditions.
An example would be a GET /oldBooks?authorId=1 endpoint that, for a given author, returns all books that are at least 10 years old.
Although the value of authorId may change, the query’s structure (tables and conditions) does not.
We’ll use this endpoint signature:
type OldBookParams = { authorId: string };
// `GET /oldBooks?authorId=1`declare function getOldBooks(params: OldBookParams): Promise<Book[]>;For brevity, these signatures use Book and Author throughout the post. postgres.js, Knex, Drizzle, and Prisma return row or record objects; MikroORM and Joist return hydrated entity instances.
First, postgres.js:
async function getOldBooks(params: OldBookParams): Promise<Book[]> { const { authorId } = params; // Driver-style "just a string" return await sql<Book[]>` SELECT b.* FROM books b WHERE b.author_id = ${authorId} AND b.published_at <= ${tenYearsAgo} `;}Next, Knex:
async function getOldBooks(params: OldBookParams): Promise<Book[]> { const { authorId } = params; // Knex/Kysely-style chained methods return await knex .select('b.*') .from('books as b') .where('b.author_id', '=', authorId) .where('b.published_at', '<=', tenYearsAgo);}Next, Drizzle:
async function getOldBooks(params: OldBookParams): Promise<Book[]> { const { authorId } = params; return await db .select() .from(books) .where(and( eq(books.authorId, authorId), lte(books.publishedAt, tenYearsAgo), ));}Next, MikroORM:
async function getOldBooks(params: OldBookParams): Promise<Book[]> { const { authorId } = params; return await em.find(Book, { author: authorId, publishedAt: { $lte: tenYearsAgo }, });}Finally, Joist:
async function getOldBooks(params: OldBookParams): Promise<Book[]> { const { authorId } = params; return await em.find(Book, { author: authorId, publishedAt: { lte: tenYearsAgo }, });}MikroORM and Joist take a fundamentally different approach: the query condition is a POJO literal. This is more succinct but also more limited because their em.find APIs return entities rather than arbitrary projected row shapes.
For these initial static-query examples, in my opinion the queries are all fairly simple/standard and could each be considered idiomatic in their own way.
Let’s move on to dynamic queries, whose relationship paths and conditions vary according to endpoint parameters or other business logic.
The example here is a GET /authors endpoint that always returns authors but can filter them by attributes of their publisher, books, or book reviews.
We’ll use the following type for the getAuthors query parameters. Each property is optional, indicating that clients may omit it:
type AuthorsParams = { /** Return authors that match this name. */ name?: string; /** Return authors whose publisher has this name. */ publisherName?: string; /** Return authors whose publisher has this status. */ publisherStatus?: string; /** Return authors with at least one book of this status. */ bookStatus?: number; /** Return authors with a rating-3 review when true or a rating-1 review when false. */ highRating?: boolean;};Disclaimer: “Return authors with a publisher of this name” is not elegant API design; I’m using it only as an ad hoc example of a dynamic endpoint.
For this new endpoint, let’s see how we would implement the query in each library.
The biggest difference is that we now have to integrate the conditional query shape into each library’s syntax.
async function getAuthors(filter: AuthorsParams): Promise<Author[]> { const { name, publisherName, publisherStatus, bookStatus, highRating } = filter; return await sql<Author[]>` SELECT DISTINCT a.* FROM authors a ${publisherName !== undefined || publisherStatus !== undefined ? sql`JOIN publishers p ON a.publisher_id = p.id` : sql``} ${bookStatus !== undefined || highRating !== undefined ? sql`JOIN books b ON a.id = b.author_id` : sql``} ${highRating !== undefined ? sql`JOIN book_reviews br ON b.id = br.book_id` : sql``} WHERE 1 = 1 ${name !== undefined ? sql`AND a.name = ${name}` : sql``} ${publisherName !== undefined ? sql`AND p.name = ${publisherName}` : sql``} ${publisherStatus !== undefined ? sql`AND p.status = ${publisherStatus}` : sql``} ${bookStatus !== undefined ? sql`AND b.status_id = ${bookStatus}` : sql``} ${highRating !== undefined ? sql`AND br.rating = ${highRating ? 3 : 1}` : sql``}; `;}A few notes:
We include a dummy 1 = 1 so that all our conditional predicates can always use an AND prefix.
Without this workaround, we would have to track which predicate comes first so that only subsequent predicates receive an AND prefix, which would be even more tedious. Disclaimer: this workaround was ChatGPT’s idea. 😅
Each conditional join check needs to know not only whether filters from its table are in use (if bookStatus is used, include books) but also whether filters from downstream tables are in use (if highRating is used, include books so that the book_reviews join works).
Next is Knex, which should be representative of method-chaining-style query builders:
async function getAuthors(filter: AuthorsParams): Promise<Author[]> { const { name, publisherName, publisherStatus, bookStatus, highRating } = filter; // Start building the query const query = knex<Author>('authors as a').select('a.*').distinct(); // Add conditional JOINs if (publisherName !== undefined || publisherStatus !== undefined) { query.join('publishers as p', 'a.publisher_id', '=', 'p.id'); } if (bookStatus !== undefined || highRating !== undefined) { query.join('books as b', 'a.id', '=', 'b.author_id'); } if (highRating !== undefined) { query.join('book_reviews as br', 'b.id', '=', 'br.book_id'); } // Add conditional WHERE clauses if (name !== undefined) query.where('a.name', name); if (publisherName !== undefined) query.where('p.name', publisherName); if (publisherStatus !== undefined) query.where('p.status', publisherStatus); if (bookStatus !== undefined) query.where('b.status_id', bookStatus); if (highRating !== undefined) query.where('br.rating', highRating ? 3 : 1); return await query;}Next is Drizzle. Its and helper omits undefined conditions, but the joins still need to be added conditionally:
import { type SQL, and, eq, getTableColumns } from 'drizzle-orm';
async function getAuthors(filter: AuthorsParams): Promise<Author[]> { const { name, publisherName, publisherStatus, bookStatus, highRating } = filter; const conditions: Array<SQL | undefined> = [ name !== undefined ? eq(authors.name, name) : undefined, publisherName !== undefined ? eq(publishers.name, publisherName) : undefined, publisherStatus !== undefined ? eq(publishers.status, publisherStatus) : undefined, bookStatus !== undefined ? eq(books.statusId, bookStatus) : undefined, highRating !== undefined ? eq(bookReviews.rating, highRating ? 3 : 1) : undefined, ]; const query = db .selectDistinct({ ...getTableColumns(authors) }) .from(authors) .$dynamic(); if (publisherName !== undefined || publisherStatus !== undefined) { query.innerJoin(publishers, eq(authors.publisherId, publishers.id)); } if (bookStatus !== undefined || highRating !== undefined) { query.innerJoin(books, eq(authors.id, books.authorId)); } if (highRating !== undefined) { query.innerJoin(bookReviews, eq(books.id, bookReviews.bookId)); } return await query.where(and(...conditions));}Next is Prisma, using its typed relation filters. This version never passes an explicit undefined, so it also works with Prisma’s strictUndefinedChecks option:
async function getAuthors(filter: AuthorsParams): Promise<Author[]> { const { name, publisherName, publisherStatus, bookStatus, highRating } = filter; const where: Prisma.AuthorWhereInput = {}; if (name !== undefined) { where.name = name; } if (publisherName !== undefined || publisherStatus !== undefined) { const publisher: Prisma.PublisherWhereInput = {}; if (publisherName !== undefined) publisher.name = publisherName; if (publisherStatus !== undefined) publisher.status = publisherStatus; where.publisher = { is: publisher }; } if (bookStatus !== undefined || highRating !== undefined) { const book: Prisma.BookWhereInput = {}; if (bookStatus !== undefined) book.status = bookStatus; if (highRating !== undefined) { book.bookReviews = { some: { rating: highRating ? 3 : 1 } }; } where.books = { some: book }; } return await prisma.author.findMany({ where });}MikroORM also uses a typed conditions object, which must be built conditionally:
import { type FilterQuery } from '@mikro-orm/core';
async function getAuthors(filter: AuthorsParams): Promise<Author[]> { const { name, publisherName, publisherStatus, bookStatus, highRating } = filter; const where: FilterQuery<Author> = {}; if (name !== undefined) { where.name = name; } if (publisherName !== undefined || publisherStatus !== undefined) { const publisher: FilterQuery<Publisher> = {}; if (publisherName !== undefined) publisher.name = publisherName; if (publisherStatus !== undefined) publisher.status = publisherStatus; where.publisher = publisher; } if (bookStatus !== undefined || highRating !== undefined) { const book: FilterQuery<Book> = {}; if (bookStatus !== undefined) book.status = bookStatus; if (highRating !== undefined) { book.reviews = { $some: { rating: highRating ? 3 : 1 } }; } where.books = { $some: book }; } return await em.find(Author, where);}Finally, here’s Joist’s dynamic version, which is noticeably simpler:
async function getAuthors(filter: AuthorsParams): Promise<Author[]> { const { name, publisherName, publisherStatus, bookStatus, highRating } = filter; return await em.find(Author, { name, publisher: { name: publisherName, status: publisherStatus }, books: { status: bookStatus, reviews: { rating: highRating === undefined ? undefined : highRating ? 3 : 1, }, }, });}After all that code, notice how few conditionals are in the Joist snippet compared with the other examples. 🎉
This is the pruning feature at work. Let’s look at how it works.
Joist’s insight is that, for most dynamic-query use cases, the maximal structure of the query remains essentially static, just like the static queries in the previous section, and then individual parts within that structure are turned on or off according to the input.
Several of the other query DSLs can omit individual “leaf” predicates that are undefined (Drizzle supports undefined conditions, Prisma omits them by default or uses Prisma.skip, and MikroORM can opt into similar behavior). However, these DSLs generally do not remove both unused conditions and now-unused relationship paths from one stable nested query shape, so application code still handles that boilerplate with if statements, ternaries, or conditional method chaining.
Joist instead determines which conditions and relationship paths to retain by tracking the usage of each one.
To do that, Joist leverages one of JavaScript’s core wat-isms: “Oh no, both null and undefined?!? 😱” For em.find:
null, such as firstName: null, becomes IS NULL.undefined, such as firstName: undefined, is unused.After dropping all unused conditions and relationship paths, Joist generates SQL that contains only the necessary predicates and traversals.
Here are some conditions that are pruned or retained based on their runtime values:
// Given two variables, e.g. from incoming query parametersconst firstName = undefined;const publisherId = null;// When this `em.find` call executes, each condition is...await em.find(Author, { name: { eq: undefined }, // pruned lastName: { in: undefined }, // pruned age: { gte: undefined }, // pruned firstName, // pruned ssn: null, // not pruned publisher: { id: publisherId }, // not pruned});So far we’ve looked at how Joist prunes its em.find “inline conditions,” which are predicates contained directly in the object literal passed to em.find; all of them become top-level AND conditions in the query.
For complex AND and OR expressions, Joist has a conditions property that accepts { and: [...] } and { or: [...] } structures. These also support pruning:
const [a, b] = aliases(Author, Book);// Find authors matching either a.lastName or b.title.await em.find( Author, // This second argument is the "join literal" that declares the maximal // relationship structure rooted at the `authors` table. { // `as` binds the `authors` table to `a` for use in conditions below. as: a, // Inline conditions become top-level AND conditions. firstName: { eq: "bob" }, // not pruned // The books path remains because b.title is used below. Joist may // implement this collection filter as a correlated EXISTS subquery. books: { as: b }, }, // This third argument contains complex AND/OR expressions. { conditions: { or: [ a.lastName.eq(undefined), // pruned b.title.eq("foo"), // not pruned ], }, },);
// Here is the same query without inline comments.await em.find( Author, { as: a, firstName: { eq: "bob" }, books: { as: b } }, { conditions: { or: [a.lastName.eq(undefined), b.title.eq("foo")] } },);As the comments indicate, a call to .eq, .in, or .gt on a column alias such as a.lastName or b.title is pruned when its argument is undefined.
If an { or: [...] } or { and: [...] } list is empty because all its conditions were pruned, the OR or AND clause is also pruned.
Putting it all together:
em.find argument is a “join literal” that declares the maximal query structure: all relationship paths and conditions that might be used.conditions property can contain complex conditions.And that’s it.
This setup lets Joist handle logic that application code would otherwise implement with repetitive language-level conditionals.
To show how succinct Joist queries are in practice, here are two queries copied from our production codebase.
Both are GraphQL query resolvers because our primary codebase is a GraphQL monolith, but the patterns apply equally to REST endpoints, gRPC services, and other APIs.
The first resolver implements query { items } with a filter; ItemFilter defines five optional fields that clients can combine freely:
# E.g., `query { items(filter: { version: 2 }) { name } }`input ItemFilter { id: [ID!] version: [Int!] costCode: [ID!] isSelection: Boolean status: [ItemStatus!]}Its em.findGql call is a one-liner 🎉:
export const items: Pick<QueryResolvers, "items"> = { items(root, { filter }, { em }) { const { costCode, version = [1], ...other } = filter ?? {}; return em.findGql(Item, { ...other, costCode: { id: costCode, version } }, { orderBy: { name: "ASC" } }); },};The second example is a similar query for the CostCode entity, but it uses an OR for the tradePartnerIds condition:
export const costCodes: Pick<QueryResolvers, "costCodes"> = { costCodes(root, { filter }, { em }) { const { tradePartnerIds, version = [1], ...others } = filter ?? {}; const [c, cco, bc] = aliases(Commitment, Commitment, BidContract); return em.findGql( CostCode, { // Pass along most of our filters 1:1 ...others, version, // The WHERE for tradePartnerIds returns "cost codes relevant to the // given trade partners". This requires several relationships/joins, // which we declare and then rely on Joist to prune if not provided. items: { projectItems: { commitmentLineItems: { commitment: { as: c }, changeOrder: { commitment: { as: cco } }, }, }, bidItems: { bidContractLineItems: { revision: { bidContract: { as: bc } } }, }, }, }, { conditions: { // If tradePartnerIds was provided, filter by those IDs. or: [ c.tradePartner.in(tradePartnerIds), cco.tradePartner.in(tradePartnerIds), bc.tradePartner.in(tradePartnerIds), ], }, orderBy: { number: "ASC" }, }, ); },};This query is not a one-liner 😅, but it is still relatively clean: the join literal defines the static structure, and the or: [...] expression makes tradePartnerIds conditional without cluttering the code.
It is also worth calling out the easy-to-miss ...others spread, which sends the remaining directly mapped GraphQL filters to Joist for default handling.
This highlights a key Joist principle: most API fields map directly to database fields. Therefore:
Joist’s em.find DSL has one major limitation: it always returns entities, such as Author or Book, rather than arbitrary projected row shapes.
This means it does not currently support arbitrary SQL queries that use features such as SUM and GROUP BY to return arbitrary POJOs.
This does not mean that applications cannot use those SQL features–we of course have a small percentage queries that need them, and currently we just use a lower-level query builder such as Knex for those queries.
I.e. we’re not dogmatic about “Joist must do everything” (…but maybe someday 😅🤞).
Historically, this has just been a pragmatic/ROI decision: because of Joist’s focus on entities, literally 95% of our codebase’s queries are all em.finds (yes we counted 🤣), and so we’ve not yet prioritized a low-level SQL builder.
See Issue #188 for tracking that idea, which we’re optimistic will be able to leverage the same core alias and usage-tracking/pruning features of the em.find API.
Personally, I feel like “building dynamic queries” is often overlooked when ORM authors design query DSLs, almost as if they were an afterthought.
My admittedly anecdotal evidence for this is:
Dynamic queries are at best boilerplately in most ORMs, and downright painful in a few.
The postgres.js, Knex, Drizzle, Prisma, and MikroORM examples all show how dynamic queries can expand query-construction code with boilerplate, particularly around conditional relationship paths/joins.
To me, this is a tell that the scenario was not treated as a first-class problem when the DSL was designed.
ORM tutorials and overviews often omit examples of how to add three or four relationship paths conditionally, relegating the topic to FAQs or leaving users to work it out themselves.
Granted, Joist might have gotten lucky in knowing “this is a problem to solve” because idiomatic GraphQL queries frequently have optional filters, which made this an acute pain point for us while developing our primary codebase.
But we also took our time designing the em.find API, refining it over several years of day-to-day/in-the-weeds feature development, before ending up at its current form–which we’re quite happy with! 😀
Joist 2.3 is out, headlined by Rails-style scopes, batched paginated finds, enum collections, native Node 26 Temporal support, smarter batched queries, and a large round of hot-path performance work.
Joist now auto-batches paginated em.find calls, so GraphQL fields like reviews(first: 5) can be nested deep in a query without causing N+1s.
Joist-driven implementation of closure tables as an example of our declarative business logic.
Joist 2.2 is out, including both the 2.1 and 2.2 releases with faster collection queries, soft-delete resurrection, recursive many-to-many relations, unloaded collection sets, and the node-pg driver migration.
Joist now supports dual release channels, allowing you to choose between a stable and a bleeding-edge version of the ORM.
Joist uses the unnest function, and our own unnest_arrays function, for bulk updates.
Joist's plugin infrastructure allowed shipping a non-trivial versioning as a "cross-cutting concern" with a minimum amount of boilerplate.
Joist leverages JavaScript's prototypes to blend great developer ergonomics/DX with performance.
Entity-based ORMs, going back to Java's Hibernate & earlier, often use decorators or annotations like @PrimaryKey to define the domain model; Joist pushes back on this pattern, and prefers schema-driven code generation.
Pipelining INSERTs and UPDATEs statements to make commits 3-6x faster
Joist’s mission is to model your application’s business logic, with first-class support for domain modeling features & concepts.
A great example of this is Joist’s support for something as simple as default values: for example, the Author.status field should default to Active.
Joist’s default values support grew from “the simplest thing possible” (requiring adhoc patterns that engineers would copy/paste around) to a robust, first-class feature (an explicit setDefault API that “just works”).
This is a microcosm of Joist’s goal to identify the repeated patterns and pain points involved in “building a domain model”, and provide elegant features with a great DX.
Joist’s initial defaults support was purposefully “as simple as possible”, and limited to DEFAULTs declared in the database schema, i.e. an is_archived field that defaults to FALSE, or a status_id that defaults to DRAFT:
CREATE TABLE example_table ( id SERIAL PRIMARY KEY, is_archived BOOL DEFAULT false, status_id INTEGER DEFAULT 1,);Joist’s codegen would recognize these, and “apply them immediately” when creating an entity:
const a = em.create(Author, {});expect(a.status).toBe(AuthorStatus.Draft); // already Draftexpect(a.isArchived).toBe(false); // already falseThis was super-simple, and had a few pros:
status is immediately within the em.create
em.flush to “see the database default”Being limited to static DEFAULT values is not great, so the first way of implementing more complicated “dynamic defaults” was using Joist’s beforeCreate hooks:
/** Any author created w/non-zero amount of books defaults to Published. */authorConfig.beforeCreate("books", a => { if (a.status === undefined) { a.status = a.books.get.length > 0 ? AuthorStatus.Published : AuthorStatus.Draft; }})This was a quick-win b/c Joist already supported beforeCreate hooks, but had a few cons:
em.flush
em.create) and “dynamic defaults” (applied during flush)a.status is already set (not a huge deal, but boilerplate)beforeCreate” wouldn’t be seen right away.We lived with the Version 1 & 2 options for several years, because they were “good enough”, but for the 3rd version, we wanted to start “setting defaults” on the road to being “more than just good enough”.
Specifically, we wanted a first-class, idiomatic way to “declaratively specify a field’s default value” instead of the previous “manually check the field in a beforeCreate hook”.
So we added config.setDefault, which accepts the field name, it’s dependencies (if any), and a lambda that would calculate the default value:
/** Calculate the Author.status default, based on number of books. */authorConfig.setDefault("status", "books", (a) => { // a.books.get is available, but a.firstName is not, b/c it's not listed as a dependency return a.books.get.length > 0 ? AuthorStatus.Published : AuthorStatus.Draft;})This was a great start, but we pushed it out knowingly half-baked:
ReactiveField infra and is great for ensuring dependencies aren’t missedsetDefault lambdas were still not invoked until em.flush
After having the setDefault API in production for a few months, the next improvement was to capitalize on “knowing our dependencies” and allow defaults to depend on other defaults.
For example, maybe our Author.status default needs to know whether any of the books are published (which itself is a default):
// In `Author.ts`authorConfig.setDefault("status", { books: "status" }, a => { const anyBookPublished = a.books.get.some(b => b.status === BookStatus.Published); return anyBookPublished ? AuthorStatus.Published : AuthorStatus.Draft;})
// In `Book.ts`bookConfig.setDefault("status", {}, b => { // Some business logic that dynamically determines the status return BookStatus.Published;});Now, if both a Book and an Author are created at the same time, em.flush will ensure that the Book.status is calculated before invoking the Author.status default–we’ve solved our ordering issue!
This was a major accomplishment–cross-entity defaults had been a thorn in our side for years.
(Fwiw we readily admit this is a rare/obscure need–in our domain model of 100s of entities, we have only ~2-3 of these “cross-entity defaults”, so we want to be clear this is not necessarily a “must have” feature–but, when you need it, it’s extremely nice to have!)
The next DX iteration was solving the duplication of “factories want the defaults too!”.
Looking more closely at this issue, Joist’s test factories are synchronous, which means we can create test data easily without any awaits:
// Given an authorconst a = newAuthor(em);// And a bookconst b = newBook(em, { author: a });// And setup something else using b.title// ...if there is "default title logic", it will not have ran yet, which// can be confusing for tests/other logic expecting that behaviorconsole.log(b.title);The lack of awaits is very nice! But it does mean, if we really wanted b.title to immediately reflect its production default, we had recode the default logic into the newBook factory:
export function newBook(em: EntityManager): DeepNew<Book> { return newTestInstance(em, Book, { title: "recode the Book default logic here", });}As before, for a while this was “good enough”–but finally in this iteration, we taught the factories to leverage their “each test’s data is already in memory” advantage and just invoke the defaults immediately during the newTestInstance calls.
This works even for setDefaults that use load hints, like “author status depends on its books”:
// In `Author.ts`authorConfig.setDefault("status", { books: "status" }, a => { const anyBookPublished = a.books.get.some(b => b.status === BookStatus.Published); return anyBookPublished ? AuthorStatus.Published : AuthorStatus.Draft;})In production, Joist can’t assume “the author’s books are already in-memory”, so em.flush would first load / await for the a.books to be loaded, and then invoke the lambda.
However, because our tests know that a.books is already in memory, they can skip this await, and immediately invoke the lambda.
Always looking ahead, the next itch we have is that, currently, default lambdas that call async methods like em.find or em.findOrCreate are still skipped during newTestInstance and only run during em.flush.
Which means, for these defaults, we still have remnants of the “write it twice” defaults anti-pattern–albeit very few of them!
We should be able to lift this restriction as well, with a little bit of work (…maybe :thinking:, the newBook call is fundamentally synchronous, so maybe not).
Wrapping up, besides a “walk down memory lane”, the larger point of this post is highlighting Joist’s journey of continually grinding on DX polish–we’re about five years into Joel’s Good Software Takes 10 Years, so only another 5 to go! :smile:
Of course, it’d be great for this evolution to happen more quickly–i.e. if we had a dependency-aware, factory-aware, amazing setDefault API from day one.
But, often times jumping to an abstraction can be premature, and result in a rushed design–so sometimes it doesn’t hurt to “sit with the itch” for a little while, evolve it through multiple iterations of “good enough”, until finally a pleasant/robust solution emerges.
And, perhaps most pragmatically, small iterations helps spread the implementation out over enough hack days that it can actually get shipped. :ship:
Joist’s development is currently very incremental, and doesn’t have “big release” milestones & release notes, but we recently released a notable new feature: recursive relations. Check them out! :tada:
I’ve been working on the Joist docs lately, specifically a Why Joist? page, which ended up focusing more on “why Domain Models?” than a feature-by-feature description of Joist.
Which is fine, but a good friend (and early Joist user) proofread it, and afterward challenged me that I was being too humble, and I should be more assertive about Joist being “THE BEST ORM FOR TYPESCRIPT AND POSTGRES” (his words), as he listed off his own personal highlights:
All of these are true.
But in thinking about his challenge, of pitching Joist specifically as “the best ORM for TypeScript & Postgres”, I actually think I can be even more bullish and assert Joist is, currently, the best ORM, in any language, ever, TypeScript or otherwise.
Which is crazy, right? How could I possibly assert this?
I have three reasons; admittedly the first two are not technically unique to Joist, but both foundational to its design and implementation, and the third that is one of Joist’s “special sauces”:
I’ve used many ORMs over the years, going back to Java’s Hibernate, Ruby’s ActiveRecord, and a few bespoke ones in between.
Invariably, they all suffer from N+1s.
I don’t want to repeat Joist’s existing Avoiding N+1s docs, but basically “entities are objects with fields/methods that incrementally lazy-load their relations from the database” is almost “too ergonomic”, and tempts programmers into using the abstraction when they shouldn’t (i.e. in a loop), at which point N+1s are inevitable.
Again as described in “Avoiding N+1s”, JavaScript’s event loop forcing all I/O calls to “wait just a sec”, until the end of the event loop tick, gives Joist an amazing opportunity, of course via dataloader, to de-dupe all the N+1s into a single SQL call.
For everything.
This works so well, that personally I don’t know that I ever want to work in a programming language/tech stack that cannot use this trick (at least to build backend/line-of-business applications).
Granted, JavaScript is not the only language with an event loop–async Rust is a thing, Python has asyncio, and even Vert.x on the JVM provides it (I prototyped “dataloader ported to Vert.x” several years ago), and either Rust or the JVM (Scala!) would be pretty tempting just in terms of “faster than JavaScript” performance.
But the event loop is only part of the story–another critical part is TypeScript’s type system.
Other TypeScript ORMs like Prisma & Drizzle “solve” N+1s by just not modeling your domain as entities (with lazy-loaded relations), and instead force/assume a single/large up-front query that returns an immutable tree of POJOs.
This does remove the most obvious N+1 footgun (lazy-loaded relations), but it also fundamentally restricts your ability to decompose business logic into smaller/reusable methods, because now any logic that touches the database must be done “in bulk” directly by your code, and often crafted in SQL specifically to how each individual endpoint is accessing the data.
(Concretely, if you had a saveAuthor endpoint with logic/queries to validate “this author is valid”, and now write a batch saveAuthors endpoint, you could not reuse the “written for one entity” logic without rewriting it to work at the new endpoint’s grouped/batch level of granularity. Or similar for saveBook logic that you want to use within a saveAuthor that also upserts multiple children books.)
Instead, Joist’s auto-batching lets you ergonomically write code at the individual entity abstraction level (whether in a loop, or in per-entity validation rules or lifecycle hooks), but still get performant-by-default batched queries.
After solving N+1s with the event loop, the next biggest ergonomic problem in traditional, entity-based ORMs is tracking (or basically not tracking) loaded-ness in the type system.
Because you can’t have your entire relational database in memory, domain models must incrementally load their data from the database, as your business logic’s codepaths decide which parts they need to read.
This was another downfall of the Hibernate/ActiveRecord ORMs: there was no notion of “is this relation loaded yet?”, and so any random relation access could trigger the surprise of an expensive database I/O call, as that relation was lazy-loaded from the database.
Joist solves this by statically typing all relations as “unloaded” by default, i.e. accessing an Author’s books requires calling a1.books.load(), which returns a Promise (which is also key to the N+1 prevention above).
Which is great, I/O calls are now obvious, but “do an await for every relation access” would really suck (we tried that), so Joist goes further and uses TypeScript’s type system to not only track individual relation loaded-ness (like author1.books or book2.authors), but mark entire subgraphs of entities as populated/loaded relations and hence synchronously accessible:
// Load the Author plus the specific books + reviews subgrpahconst a1 = await em.load(Author, "a:1", { populate: { books: { reviews: "comments" } },});
// a1 is typed as Loaded<Author, { books: { reviews: "comments" } }>// Tada, no more await Promise.alla1.books.get.forEach((book) => { book.reviews.get.forEach((review) => { console.log(review.comments.get.length); });})This combination of:
.load() / await calls for any I/O, but leveragingFor me, is also something that I never want to work without again. It’s just so nice.
Unlike JavaScript not having a monopoly on the event loop, for these mapped types I believe TypeScript effectively does have a lock on this capability, from a programming language/type system perspective.
Creating “new types” in other programming languages is generally handled by macros (Scala and Rust), or I suppose Haskell’s higher-kinded-types. But, as far as I know, none of them can combine TypeScript “mapped type + conditional type” features in a way that would allow this “take my user-defined type (Author)” and “this user-defined populate hint type” and fuse them together into a new type, that is “the author with this specific subgraph of fields marked as loaded”.
I’m happy to be corrected on this, but I think TypeScript is the only mainstream programming language that can really power Joist’s Loaded<Author, { books: "reviews" }>-style adhoc typing of subgraphs, or at least this easily.
Other TypeScript ORMs (Prisma, Drizzle, Kysley, etc.) also leverage TypeScript’s mapped types to create dynamic shapes of data, which is legitimately great.
However, they all have the fundamental approach of issuing “one-shot” queries that return immutable trees of POJOs, directly mapped from your SQL tables, and not subgraphs of entities that can have non-SQL abstractions & be further incrementally loaded as/if needed (see Why Joist for more on this).
You can generally see, for both issues covered so far (N+1s and statically-typed loaded-ness), most TypeScript ORMs have “solved” these issues by just removing the features all together, and restricting themselves to be “sophisticated query builders”.
Joist’s innovation is keeping the entity-based, incremental-loading mental model that is historically very popular/idiomatic for ORMs (particularly Ruby’s ActiveRecord), and just fundamentally fixing it to not suck.
This 3rd section is the first feature that is unique to Joist itself: Joist’s “backend reactivity”.
Many ORMs have lifecycle hooks (this entity was created, updated, or deleted–which Joist does as well), to organize side effects/business logic of “when X changes, do Y”.
But just lifecycle hooks by themselves can become tangled, complicated, and a well-known morass of complexity and “spooky action at a distance”.
This is because they’re basically “Web 1.0” imperative spaghetti code, where you have to manually instrument each mutation that might trigger a side effect.
(Concretely, lets say you have a rule that needs to look at both an author and its books. With raw lifecycle hooks, you must separately instrument both the “author update” and “book update” hooks to call your “make sure this author + books combination is still valid” logic. This can become tedious and error-prone, to get all the right hooks instrumented.)
Instead, Joist’s reactive fields and reactive validation rules take the lessons of “declarative reactivity” from the Mobx/Solid/reactivity-aware frontend world, and bring it to the backend: reactive rules & fields declare in one place what their “upstream dependencies” are, and Joist just handles wiring up the necessary cross-entity reactivity.
This brings a level of ease, specificity, and rigor to what are still effectively lifecycle hooks under the hood, that really makes them pleasant to work with.
The declarative nature of Joist’s domain model-wide reactivity graph is also very amenable to DX tooling & documentation generation, but we’ve not yet deeply explored/delivered any functionality that leverages it.
So, these three features are what back up my exaggerated “best ORM ever” assertion.
If tomorrow, I suddenly could not use Joist, and had to find another ORM to use (or, in general, build any sort of application backend on top of a relational database), in any current/mainstream programming language, without a doubt I would want:
And Joist is the only ORM that does all three of these: two of which are uniquely enabled by the JavaScript/TypeScript stack, and the third just part of Joist’s own innovation.
I usually don’t like making bold/absolutist claims, like “this or that framework is ‘the best’” or “technology x/y/z is terrible” or what not.
I did enough of that early in my career, and at this point I’m more interested in “what are the trade-offs?” and “what’s the best tool for this specific use case?”
So, I hold two somewhat incongruent thoughts in my head, as I am both:
My guess is if you tried Joist, you would quickly come to like it, but it’s also perfectly fine if not!
Similar to the two incongruent thoughts above, another two semi-contradictory thoughts is the disclaimer that:
If you have thoughts, questions, or feedback, please let us know! Feel free to join the Joist discord, or file issues on the GitHub repo if you try Joist and run into any issues.
Despite all the hubris in this post, we are still a very small project & community, and so have a lot of growth and improvement ahead of us.
Thanks for the read!
We’ve added a new NextJS + Joist sample app that shows how Joist can be used in a NextJS application, with several benefits:
This post gives a short overview; if you’d like to watch a video, we also have a YouTube video that walks through the sample app.
While building the sample app, we found two fundamental ways of structuring a NextJS app’s render tree:
table.tsx is a server component that loads all data for the treeauthor-rcc-card.tsx and book-rcc-preview.tsx are client components that accept prop-drilled datatable.tsx is a server component but only loads what it needsauthor-rsc-card.tsx and book-rsc-preview.tsx are RSC and do their own data loading
The top-level Table / table.tsx component renders each of these side-by-side, so we can see the differences, and observe some pros/cons of each approach.
With mostly RSC components, it’s easy to decompose data loading away from the top-level component.
For example, the AuthorRscCard can make its own data loading calls, and even if it’s render many pages on the page, Joist will de-dupe across the N sibling AuthorRscCards, and batch into a single SQL call.
type AuthorCardProps = { /** RSCs can accept the domain model enities as a prop. */ author: Author; addBook: (id: string) => Promise<void>;};
/** The RSC version of AuthorCard can load it's own data. */export async function AuthorRscCard({ author, addBook }: AuthorCardProps) { // This will be auto-batched if many cards render at once const books = await author.books.load(); // Or if you wanted a tree of data, this will also be auto-batched const loaded = await author.populate({ books: { reviews: "ratings" } }); return <div>...jsx</div>;}This is nice because it allows the AuthorRscCard to be more self-sufficient, and allow the parent table component to be unaware of its children loading details.
With mostly Client components, the opposite happens, and only the parent can make database / EntityManager calls, and so is responsible for loading all the data for its children, and passing it as JSON via props:
type AuthorCardProps = { /** RCCs must accept a POJO of `Author` + all nested data. */ author: AuthorPayload; addBook: (id: string) => Promise<void>;};
/** The RCC version of AuthorCard accepts the `AuthorPayload`. */export function AuthorRccCard({ author, addBook }: AuthorCardProps) { // can only use data already available on `author`}Even though the up-front data load can become awkward, it does give more opportunities for optimizations; for example Joist can use join-based preloading to load a single tree of Author + Book + Review entities in a single SQL call, which is even better optimization than the “one query per layer” N+1 prevention of the RSC-based approach.
In either approach, Joist’s N+1 prevention auto-batches database calls, even if they are made across separate component renders.
I.e. in the RSC components:
Table component makes 1 SQL call for all Author entities.AuthorRscCard cards each make their own author.books.load() (or author.populate(...)) call, but because they’re all rendered in the same event loop, Joist batches all the load calls into 1 SQL callload calls batched as well.In the React Client Component approach, this auto-batching is admittedly not as necessary, assuming a singular top-level component, like Table, loads all the data at once anyway (although, as mentioned later, Joist can optimize that as well).
See the Avoiding N+1s section of our docs for more information.
Since the client components cannot make their own async data calls, the top-level Table components is responsible for loading all the data into a JSON payload, and passing it down to the children as props.
Joist entities have an easy way of doing this is, via a toJSON method that takes the shape of data to create:
// Define the shape of data to createexport const authorHint = { id: true, firstName: true, books: { id: true, title: true, reviews: ["id", "rating"], }, customField: (a) => a.id + a.title,} satisfies JsonHint<Author>;
// This typedef can be used in the client-side props, or to match any// endpoint-based respones types like for REST/OpenAPI.export type AuthorPayload = JsonPayload<Author, typeof authorHint>;
const payload = await a.toJSON(authorHint);The toJSON implementation will:
authorHintcustomField to generate custom valuesAs with previous examples, all data loading is N+1 safe, and also potentially join-based preloaded.
See the toJSON docs for more information.
This recursive toJSON payload generation is a relatively new feature of Joist, so if you have feature ideas that would make it more useful, please let us know!
The last optimization that Joist can do is join-based preloading, which can be used in either the RSC or RCC approach.
This is also a newer feature that requires opt-ing in to, but in em.ts you can add a preloadPlugin:
/** Returns this request's `EntityManager` instance. */export const getEm = cache(() => { // Opt-in to preloading const preloadPlugin = new JsonAggregatePreloader(); return new EntityManager({}, { driver, preloadPlugin });});This will allow Joist to load a deep tree/subgraph of entities in a single SQL call.
For example, normally a Joist em.find a call like:
const a = await em.find( Author, { id: 1 }, {populate: { books: "reviews" } },);// Now access all the data in memoryconsole.log(a.books.get[0].reviews.get[0].rating)Will issue three SQL calls:
SELECT * FROM authors WHERE id = 1;SELECT * FROM books WHERE author_id = 1;SELECT * FROM reviews WHERE book_id IN (1, 2, 3, ...);But with the preloadPlugin enabled, it will use a single SQL call that uses CROSS JOIN LATERAL and json_agg to return the author’s books, and the book’s reviews (omitted for brevity) in a single row:
select a.id, _b._ as _b from authors as a cross join lateral -- create a tuple for each book, and aggregate then into an array of books select json_agg(json_build_array(_b.id, _b.title, _b.foreword, _b.author_id) order by _b.id) as _ from books _b where _b.author_id = a.id ) _b where a.id = ? limit ?Joist’s join-based preloading is still a beta feature, so if you run into any issues, please let us know!
So far, our queries have focused on loading “just entities”, and then putting those on the wire (or rendering them to HTML).
This is because Joist’s focus is on building robust domain models, and specifically helping solve the “write-side” of your application’s business logic (running the correct validation rules, lifecycle hooks, reactive updates), and less so on the “read-side” of complex queries (i.e. that using aggregates using GROUP BY, multiple nested subqueries/projections/etc.).
As such, Joist does not yet have a sophisticated query builder that can create arbitrary SQL queries, like Kysley or Drizzle.
Instead, Joist encourages an approach that uses its robust write-side features to create materialized columns in the database, such that the majority of your pages/responses really can be served by “super simple SELECT statements”, instead of using complicated queries to calculate aggregates on-the-fly.
Although you can of course use both approaches, and just use a lower-level query builder where needed.
Joist’s roots come from the GraphQL world, so this sample app was our first foray into using it for a NextJS application. If we’ve missed any key features that would make it easier to use Joist in a NextJS app, please let us know!