A new TypeScript Postgres query builder

Martijn de Haan — Sun, 25 Oct 2020 21:40:54 +0000

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.

Real-Time Feature Flags Using React Hooks

Martijn de Haan — Tue, 23 Jul 2019 06:02:48 +0000

Are you building a feature which is not ready for prime time yet? But still want to show it to a select group of users? Do you want to skip the terrible thing which is called a staging environment? Look no more!

You are not alone. More and more teams are moving away from their staging environment to just simply use feature flags to control for which users a feature is enabled. Staging only adds latency to your feature delivery.

const MyComponent = () => {
  const { isEnabled } = useBoolean(`my-feature`);

  if (isEnabled) {
    return (
      <div>
        // New component here
      </div>
    );
  }

  return (
    <div>
      // Old component stuff here
    </div>
  );
}

This article is specifically about React, but we have different client libraries available. Check out the list at Boolean.

Getting started using feature flags is this easy. npm i @ff00ff/boolean-react and add the provider component in the root of your component tree and you're all set. You can sign up and create a free account at Boolean.

<BooleanProvider apiKey={process.env.BOOLEAN_API_KEY}>
  {/* Your component tree here */}
</BooleanProvider>

When the BooleanProvider mounts, the library starts a WebSocket connection to get the initial state of your features. And when you toggle your features in the Boolean dashboard, the changes are pushed over these WebSocket connections to all your users. Instantly.

Advanced Targeting

There is more! Using Boolean, per feature, you can target specific users. This all happens without Boolean receiving a copy of the data you have on your users, because the rules are evaluated in the library. We think this privacy-first approach is really important.

// Prop drilling is just an example here, you can also use a hook or any other way to get the user data in
const MyComponent = ({ user }) => {
  const { isEnabled } = useBoolean(`my-feature`, user.id, {
    // Add whatever attributes you want here
    plan: user.plan,
  });

  if (isEnabled) {
    return (
      <div>
        // New component here
      </div>
    );
  }

  return (
    <div>
      // Old component stuff here
    </div>
  );
}

Now, in the Boolean dashboard, you can use the user's plan attribute to enable a feature based on the plan they have. You can read more on the library on the Boolean's React page.

I'm Martijn, founder of Boolean. If you have any questions just send me a ping. Are you ready to start using feature flags?

DEV Community: Martijn de Haan

A new TypeScript Postgres query builder

Real-Time Feature Flags Using React Hooks

Advanced Targeting