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.

#typescript #postgres #database #programming #developer