Mammoth 1.0: A New TypeScript Postgres Query Builder

Mammoth 1.0: A New TypeScript Postgres Query Builder

Mammoth 1.0: A New TypeScript Postgres Query Builder. Mammoth is a new no-batteries-included type-safe Postgres query builder for TypeScript

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

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

What is TypeScript? Why TypeScript? and Why Not TypeScript?

TypeScript extends JavaScript by adding Types. There are many great reasons to switch to TypeScript. Especially if your team uses JavaScript. There are some reasons to not use TypeScript as there are with any language or framework.

How long does it take to develop/build an app?

This article covers A-Z about the mobile and web app development process and answers your question on how long does it take to develop/build an app.

Developer Career Path: To Become a Team Lead or Stay a Developer?

For a developer, becoming a team leader can be a trap or open up opportunities for creating software. Two years ago, when I was a developer, ... by Oleg Sklyarov, Fullstack Developer at Skyeng company

Tracking a Developer’s Journey From Documentation Visit

Measuring website activity provides only half the story. See how to best track the developer's journey and what funnel stages makes sense for API-first products

Offshore Software Development - Best Practices

To make the most out of the benefits of offshore software development, you should understand the crucial factors that affect offshore development.