A new TypeScript Postgres query builder

It’s been almost 3 years since I started working on this query builder idea of mine. Today is finally the day Mammoth hits 1.0. Mammoth is a no-batteries-included type-safe Postgres query builder for TypeScript. Hooray!

db.with(
  `regionalSales`,
  () =>
    db
      .select(db.orderLog.region, sum(db.orderLog.amount).as(`totalSales`))
      .from(db.orderLog)
      .groupBy(db.orderLog.region),
  `topRegions`,
  ({ regionalSales }) =>
    db
      .select(regionalSales.region)
      .from(regionalSales)
      .where(
        regionalSales.totalSales.gt(
          db.select(sum(regionalSales.totalSales).divide(10)).from(regionalSales),
        ),
      ),
  ({ topRegions }) =>
    db
      .select(
        db.orderLog.region,
        db.orderLog.product,
        sum(db.orderLog.quantity).as(`productUnits`),
        sum(db.orderLog.amount).as(`productSales`),
      )
      .from(db.orderLog)
      .where(db.orderLog.region.in(db.select(topRegions.region).from(topRegions)))
      .groupBy(db.orderLog.region, db.orderLog.product),
);

The no-batteries-included part is a wink to Knex.js and hints at Mammoth’s opposing ideas. Knex.js supports many SQL dialects and bundles the approriate database drivers (as peer dependencies, yes). Mammoth, however, only supports one SQL dialect, Postgres, and bundles no database driver.

Creating a cross-database query builder like Knex.js is an amazing challenge, one that Knex.js set out to take on, but not me. To me, creating a cross-database query builder basically means constructing a new SQL dialect. For all the differences in the existing dialects you have to construct a new generic concept. But I like SQL. It's ubiquoutus and versatile. Especially Postgres. And this new language wouldn't be.

In Knex.js the INSERT INTO .. ON CONFLICT is nowhere to be found. This SQL clause is missing even though it's been released more than 4 years ago. The problem? The contributors had to introduce a new concept and try to avoid anything database-specific. I think this is an example how hard it is to create this new cross-database language. Weighing the pros and cons without being database-specific. This challenge becomes clear in this pull request discussing insert into on conflict.

An often touted benefit of a cross-database solution is to be able to easily switch between different databases. Not at runtime but if you later decide to migrate from one database to another. But a cross-database approach isn't the only option. Instead, I think, a multi-database solution is a good strategy when you're operating in a type-safe world. Where the former focusses on speaking to different databases using a single API, the latter offers a database-specific API, but type-safe, thus, multi. Meaning, you would still be able to switch between databases, and you would see breaking changes at compile time. Fix the errors and you can be confident you support the new database.

Of course this ignores the whole topic of needing to actually migrate data from one database to another. This is why, in my opinion, you never really just switch from one database to another.

Mammoth is sticking as close to SQL as possible. This comes with a set of challenges when building Mammoth, but it should make it easier to adopt Mammoth in a project or onboard new developers. I want a minimal abstraction, not a new generic SQL language, an ORM or something even more massive like Prisma. If you want to speak to your database, but it requires re-learning a lot you already know how to do in SQL, something is wrong. And all this re-learning is often not transferable to different languages or environment, which makes part of this learning wasteful.

As an alternative, there are amazing tools available which generate types by reading your queries and reading the remote database schema. While these do solve the type-safety challenges and stick to SQL, I feel requiring a watch on file changes so types can get re-generated isn't ideal. Instead, with an advanced enough type-safe query builder you can have the same features without this build step.

Mammoth aims to fix exactly that. Here are some examples that I think work great. All the result types are automatically inferred.

Left join — name automatically becomes optional

const rows = await db
    .select(db.foo.id, db.bar.name)
    .from(db.foo)
    .leftJoin(db.bar)
    .on(db.bar.fooId.eq(db.foo.id));

Simple select with count(*)

const [{count}] = await db.select(count()).from(db.foo);

Insert into .. on conflict

const affectedCount = await db
  .insertInto(db.foo)
  .values({
    name: `Test`,
  })
  .onConflict(`id`)
  .doUpdateSet({
    name: `Test 2`,
  });

Just an update returning the affected count

const affectedCount = await db.update(db.foo).set({ name: `Test`, value: 123 });

Select with where in expression

const rows = await db
  .select(db.foo.id)
  .from(db.foo)
  .where(db.foo.id.in(db.select(db.bar.fooId).from(db.bar)));

Mammoth 1.0 is production-ready. I've been using it in a project for some time already. It's not feature-complete though but should support most use cases and give you automatic type safety in a lot of places. It also offers excellent autocomplete and Mammoth tries hard not to pullute the public API. The first version of the Mammoth query builder documentation is also up.

I created a couple of issues labeled with good first issue. These issues are great for someone new to the codebase to pick up—and they really add value. I'm available to help you get started. Just claim the issue and let me know if you have any questions. If you always wanted to work on some open source, now is your time!

Last but no least, I started working on mammoth-cli to automatically generate migrations based on your table definitions. This is the next big piece to get right to make sure your database schema actually matches your table definitions. Also, this should make the developer experience of using Mammoth even better. The first beta version is available now.