Designing the ultimate TypeScript query builder

The sequel to SQL​

Important note: this is a query builder for EdgeQL, the object-oriented query language of EdgeDB. It’s designed as a spiritual successor to SQL, and solves some it’s biggest usability issues. Throughout this post, click the “EdgeQL” tab to see the EdgeQL equivalent of each query builder expression.

const query = e.select(e.Movie, movie => ({
  id: true,
  title: true,
  actors: {
    name: true,
  },
  filter: e.op(movie.title, '=', 'Iron Man 3')
}));

// inferred type:
// { id: string; title: string; actors: { name: string }[]}

select Movie {
  id,
  title,
  actors: {
    name
  }
}
filter .title = "Iron Man 3"

Note the GraphQL-style “selection shape” to specify which fields to fetch. As you’d expect, this query returns a structured JSON-like result, not a flat list of rows.

{
  "id": "9278d96e-1932-44e4-a9b3-34e49b592c26",
  "title": "Iron Man 3",
  "release_year": 2013,
  "actors": [
    { "name": "Robert Downey Jr." },
    { "name": "Gwyneth Paltrow" },
    { "name": "Ben Kingsley" }
  ]
}

The query above assumes the following schema, as defined with EdgeDB’s schema definition language.

type Movie {
  required property title -> str;
  property release_year -> int64;
  multi link actors -> Person;
}

type Person {
  required property name -> str;
}

Generating the query builder

To get started with the query builder, you’ll need to spin up an EdgeDB instance. The easiest way to do so it to install the EdgeDB CLI and run edgedb project init in your project directory. Follow the quickstart for a more complete introduction.

Then install the edgedb package from NPM and executing the following npx command.

$ 

npm install edgedb

$ 

npx edgeql-js

This command introspects your database and generates a handful of files into the dbschema/edgeql-js directory. (By convention, dbschema is the directory used to store anything EdgeDB-related, like schema and migration files.) We recommend importing the query builder as a single variable called e.

import e from "./dbschema/edgeql-js";

This variable contains everything you need to define arbitrarily complex EdgeQL queries, but let’s start small: a “Hello World” query.

import e from "./dbschema/edgeql-js";

const query = e.select("hello world!");

The e.select function returns an object that represents an EdgeQL queries; we’ll refer to this as a “query builder expression” or simply “expression”.

To execute the expression, pass a client into the expression’s .run() method.

import {createClient} from "edgedb";
import e from "./dbschema/edgeql-js";

const client = createClient();

const query = e.select("Hello world!");
const result = await query.run(client);
// => "Hello world!"

The .run method returns a strongly typed Promise; the query builder automatically infers the return type of all expressions. In the example above result has a string type. You can extract this type with the $infer helper.

import {createClient} from "edgedb";
import e, {$infer} from "./dbschema/edgeql-js";

const client = createClient();

const query = e.select("Hello world!");
type query = $infer<typeof query>;
// string

Let’s start looking at some non-trivial queries.

Inserting objects​

Use the e.insert function to write insert queries.

e.insert(e.Movie, {
  title: "Doctor Strange in the Multiverse of Madness",
  release_year: 2022
});

insert Movie {
  title := "Doctor Strange in the Multiverse of Madness",
  release_year := 2022
}

The first argument is an object type; these are auto-generated by the query builder. The second argument contains the data to be inserted. Note that we aren’t including an id property; that gets autogenerated by EdgeDB.

Since the title property has type str, e.insert naturally expects a string value. Similarly release_year has type int64, so it expects a number. The table below maps each EdgeDB scalar type to its closest TypeScript equivalent.

For certain types like duration that have no JavaScript equivalent, we’ve implemented custom classes to represent that data type.

e.insert(e.Movie, {
  title: "Iron Man",
  release_year: 2008,
  actors: e.set(
    e.insert(e.Person, { name: "Robert Downey Jr." }),
    e.insert(e.Person, { name: "Gwyneth Paltrow" })
  ),
});

insert Movie {
  title := "Iron Man",
  release_year : 2008,
  actors := {
    (insert Person { name := "Robert Downey Jr." }),
    (insert Person { name := "Gwyneth Paltrow" })
  }
}

Selecting objects​

Now onto the meat and potatoes: selecting objects. Let’s start by selecting all movies in the database.

const query = e.select(e.Movie, () => ({
  id: true,
  title: true
}));

const result = await query.run(client);
// {id: string; title: string;}[]

select Movie {
  id,
  title,
  release_year
}

As a shorthand for selecting all properties of an object, use the spread operator in conjunction with the special * property. This is a query builder feature with no EdgeQL equivalent (yet); plain EdgeQL doesn’t support select * functionality.

const query = e.select(e.Movie, () => ({
  ...e.Movie['*']
}));

const result = await query.run(client);
/* {
  id: string;
  title: string;
  release_year: number | null;
}[] */

As you’d expect, the type of the release_year property is number | null as it’s an optional property.

const query = e.select(e.Movie, () => ({
  title: true,
  actors: {
    name: true,
  }
}));

const result = await query.run(client);
// { title: string, actors: {name: string}[] }[]

select Movie {
  title,
  actors: {
    name
  }
}

const query = e.select(e.Movie, (movie) => ({
  title: true,
  title_upper: e.str_upper(movie.title),
  cast_size: e.count(movie.actors)
}));

const result = await query.run(client);
// { title: string; title_upper: string; cast_size: number }[]

select Movie {
  title,
  title_upper := str_upper(.title),
  cast_size := count(.actors)
}

[
  {
    title: "Iron Man",
    title_upper: "IRON MAN",
    cast_size: 2
  },
  // etc.
]

e.select(e.Movie, (movie) => ({
  title: true,
  release_year: true,
  filter: e.op(movie.title, "ilike", "%matrix%"),
}));

select Movie {
  title,
  release_year
} filter .title ilike "%matrix%"

e.select(e.Movie, (movie) => ({
  title: true,
  release_year: true,
  filter: e.op(movie.title[0], "=", "A"),
}));

select Movie {
  title,
  release_year
} filter .title[0] = "A"

e.select(e.Movie, (movie) => ({
  id: true,
  title: true,
  actors: (actor) => ({
    name: true,
    filter: e.op(actor.name, "ilike", "chris")
  }),
  filter: e.op(movie.release_year, "=", 2022)
}));

prisma.movie.findMany({
  where: {
    release_year: {
      eq: 2022
    }
  },
  select: {
    id: true,
    title: true,
    actors: {
      select: {
        name: true
      },
      where: {
        name: {
          contains: "chris",
          mode: "insensitive"
        }
      }
    }
  }
});

const query = e.select(e.Movie, (movie) => ({
  title: true,
  filter: e.op(movie.id, '=', e.uuid('2053a8b4-49b1-437a-84c8-e1b0291ccd9f'))
}));

const result = await query.run(client);
// { title: string } | null

  const query = e.select(e.Movie, (movie) => ({
    title: true,
    filter: e.op(movie.id, '=', e.uuid('2053a8b4-49b1-437a-84c8-e1b0291ccd9f'))
    filter: e.op(movie.release_year, '=', 2022)
  }));

  const result = await query.run(client);
  // { title: string }[]

e.select(e.Movie, (movie) => ({
  title: true,
  order_by: e.count(movie.actors),
  limit: 10,
  offset: 40
}));

select Movie {
  title
}
order by count(.actors)
limit 10
offset 40

e.select(e.Movie, (movie) => ({
  title: true,
  order_by: [
    {
      expression: e.count(movie.actors),
      direction: e.ASC,
      empty: e.EMPTY_LAST,
    },
    {
      expression: movie.title,
      direction: e.DESC,
    }
  ]
}));

Updating objects​

e.update(e.Movie, (movie) => ({
  filter: e.op(movie.title, '=', 'Avengers: Infinity War - Part II'),
  set: {
    title: 'Avengers: Endgame',
  },
}));

update Movie
filter .title = 'Avengers: Infinity War - Part II'
set {
  title := 'Avengers: Endgame'
}

e.update(e.Movie, (movie) => ({
  set: {
    title: e.str_trim(movie.title),
  },
}));

update Movie
set {
  title := str_trim(.title)
}

const actors = e.select(e.Person, person => ({
  filter: e.op(person.name, 'in', e.set('Benedict Cumberbatch', 'Rachel McAdams'))
}));

const query = e.update(e.Movie, (movie) => ({
  filter: e.op(movie.title, '=', "Doctor Strange"),
  set: {
    actors: {'+=': actors},
  }
})).run(client);

with actors := (
  select Person
  filter .name in {'Benedict Cumberbatch', 'Rachel McAdams'}
)
update Movie
filter .title = "Doctor Strange"
set {
  actors += actors
}

Composing queries​

The previous update example also demonstrates one of the query builder’s greatest strengths: compositionality. Because the declaration and execution of queries are two distinct steps, it’s possible to declare pieces of a query separately, then put them all together later. Writing complex queries feels like writing a script.

For instance, we can compose multiple expressions to perform a nested insert + selection in one query.

const rdj = e.insert(e.Person, {
  name: "Robert Downey Jr."
});

const ironMan = e.insert(e.Movie, {
  title: "Iron Man",
  release_year: 2008,
  actors: rdj
});

const query = e.select(ironMan, () => ({
  title: true,
  release_year: true,
  num_actors: e.count(ironMan.actors)
}));

const result = await query.run(client);
// {title: string; release_year: number; num_actors: number}

with
  rdj := (
    insert Person {
      name := "Robert Downey Jr."
    }
  ),
  ironMan := (
    insert Movie {
      title := "Iron Man",
      release_year := 2008
    }
  )
select ironMan {
  title,
  release_year,
  num_actors := count(.actors)
};

The query builder detects that newMovie occurs multiple times inside query and extracts it into a with block (AKA a “common table expression” in SQL parlance). Note that there’s only one await. We aren’t executing rdj and ironMan; they are subqueries that get composed into the final “superquery”, which can be executed in a single round trip to the database.

Using query parameters​

As a final cherry on top, the query builder makes it painless to parameterize your queries. This lets you use external data (say, the body of an incoming POST request) in a typesafe way.

const query = e.params(
  { title: e.str, release_year: e.int64 },
  ($) => {
    return e.insert(e.Movie, {
      title: $.title,
      release_year: $.release_year,
    });
  }
);

const result = await query.run(client, {
  title: 'Thor: Love and Thunder',
  release_year: 2022,
});

with
  title := <str>$title,
  release_year := <int64>$release_year
insert Movie {
  title := title,
  release_year := release_year
}

For a parameterized query, you pass the parameters as the second argument to .run(); they are strongly typed and validated at runtime.

Comparison to ORMs​

Hopefully it’s clear from the examples above, but in terms of expressive power, the query builder is beyiond every ORM we’re aware of. By and large, ORMs can only represent relatively basic read/write operations, whereas a proper query language can express much more:

• string modifications

• indexing and slicing of iterables

• aggregations

• math

• temporal logic

• coalescing and defaults (?? in JavaScript)

• conditionals (a ? b : c in JavaScript)

• parameterization

• set logic like union or in

• type logic and casting

• query compositionality (AKA common table expressions)

• computed properties

• polymorphic queries

• self-referential updates

This is to say nothing of schema modeling. EdgeDB supports yet more functionality lacking in most ORMs:

• computed properties—these are “virtual” properties corresponding to an EdgeQL that is dynamically executed whenever the property is fetched

• computed defaults

• complex constraint logic

• schema mixins (inheritance)

• link properties

• a full range of numerical types (int{16,|32|64}, float{32|64}, bigint, and decimal)

• temporal types like duration and non-timezone-aware dates and times

• a database-native migration system including a planner and tracking system

Future directions​

The query builder has been available since the EdgeDB 1.0 release in February 2022, and is stable and production-ready. The query builder was recently upgraded to support all EdgeDB 2.0 features, such as the e.group statement and global schema variables. This post only covers a subset of the query builder’s full functionality; refer to the Documentation for a more comprehensive look!

We’re releasing EdgeDB 2.0 during a livestreamed launch event on July 28th, 2022. Join us there for some lightning talks about the biggest new features and the first public demo of EdgeDB’s new admin UI.