How I made database joins type-safe in TypeScript.

How I made database joins type-safe in TypeScript.

Context: What is an ORM?

An ORM (Object-relational Mapper) is a tool that adds a layer of abstraction on to databases. For example, if we had a "team" table in a SQL database and wanted to fetch a team with TypeScript, we could do the following:

const res = await client.query('SELECT * FROM team WHERE id = $1', id);
console.log(res.rows[0]?.name)

And an ORM (such as TypeORM) will automatically generate and execute the query for us:

const team = await teamRepo.findOne({
  where: { id }
});
console.log(team?.name);

While the above example may not look any simpler than the manual query, it gets a lot more helpful when your queries are more complex, such as if you want to use joins. In TypeORM, you can use the relations property to automatically perform the joins:

const team = await teamRepo.findOne({
  where: { id },
  // This automatically left joins the correct tables
  // and stores the values in team.members and team.members[i].user
  relations: ['members', 'members.user']
});

for (const member of team.members) {
  console.log(member.user.username);
}

Notice how the ORM automatically populates the team object with all the information we need.

The Problem: Types when joining

It's extremely important to try and get things to crash at build time rather than at run time. One way we can do this is by ensuring our types are always correct. Then, our type-system will let us know if there's a case we're not handling. Unfortunately, the above example has some incorrect types.

The problem arises when we want to start accessing team.members. When we define the Team class, members must be either always optional or always required, depending on how you've defined it:

class Team {
  // Other columns here

  // Should it be optional?
  members?: Member[];
  // Or always available?
  members: Member[];
}

TypeORM will populate members, like you saw above, only if we do relations: ['members']. Otherwise, the members property will be undefined. So, because members could be undefined, we should make it optional, right?

If `members` is optional

If members is optional, our entire codebase becomes a hassle every single time we use relations:

const team = ...

if (team.members) {
  for (const member of team.members) {
    if (member.user) {
      // Well this sucks
      console.log(member.user.username)
    }
  }
}

Or we choose to ignore them using !, but this can be very risky.

const team = teamRepo.findOne({
  where: { id },
});

// Yay! Our code is reduced...
// But wait, we forgot the relations above.
// No type error, but we're going to crash here.
for (const member of team.members!) {
  console.log(member.user!.username);
}

Also, what if we have a function that should take in a team with members?

function printTeamInfo(team: Team) {
  // This is risky, since anyone who calls this function
  // might not be aware that it requires the `team.members` relation
  for (const member of team.members!) {}
}

If `members` is always available

If members isn't optional, we can access the property even if we haven't added the relation. This is even more unsafe, as I'm not given an error that I can ignore with !; I just have to remember to add the relation.

const team = teamRepo.findOne({
  where: { id },
});

// Not a single build error, and we're not
// even using a ! this time.
// Crash at runtime, undefined (team.members) is not iterable 
for (const member of team.members) {
  console.log(member.user.username);
}

In the above example, we want a build error to tell us that we can't use team.members unless we join it (by doing relations: ['members'] when fetching the team).

Possible Solution: DeepRequired

We could use the as keyword to apply some kind of DeepRequired to Team. However, this makes every single property required, even if we didn't add it to the relation:

const team = teamRepo.findOne({
  where: { id },
});

// Yay, error as expected because we didn't join `members`
console.log(team.members[0]);

// // // // // // // // //

const teamWithMembers = teamRepo.findOne({
  where: { id },
  relations: ['members']
}) as DeepRequired<Team>; // We added this `as` part.

// Yay, no error
for (const member of teamWithMembers.members) {
  // Uh oh. This is going to crash because
  // "members.user" isn't in our relation.
  // But there's no type error!
  console.log(member.user.username);
}

In the above example, when we didn't add any relations, we correctly got an error that team.members could be undefined. And when we added the relation and DeepRequired type, we were correctly able to loop through teamWithMembers. However, DeepRequired applies to every single property, so we got no type error when doing member.user.username, even though at runtime, member.user will be undefined!

Full Solution: MakeRequired

We want to only make certain properties required and not others. We can do that if we write a type called MakeRequired (feel free to propose a better name).

The way MakeRequired works is that it takes in your type and an object that represents which properties should be defined. Here's an example:

interface Order {
  id: number;
  customer?: Customer;
  items?: Item[];
}

interface Customer {
  name: string;
}

interface Item {
  price?: number;
}

function getTotalOrderPrice(
  // Here!
  // We're saying that Order will have order.items
  // and order.items[i].price defined.
  order: MakeRequired<Order, {
    items: { price: {} }
  }>
) {
  // This will fail if uncommented because
  // order.customer is still undefined
  // console.log(order.customer.name);

  // Normally this would fail because
  // order.items could be undefined.
  // But with MakeRequired, we're all good!
  return order.items
    .reduce(
      // Normally this would fail because
      // item.price could be undefined
      (total, item) => total + item.price,
      0,
    );
}

So, in order to solve our database problem, we can do:

const team = teamRepo.findOne({
  where: { id },
  relations: ['members']
}) as MakeRequired<Team, { members: {} }>;

for (const member of team.members) {
  // Finally, just an error here!
  console.log(member.user.username);
}

MakeRequired still respects null when you join a nullable value. For example, let's say we want to add a leaderMember property to the team. We'll make it optional with ? because we have to join to include it, but we'll also add | null because it's nullable in the database.

interface Team extends OldTeam {
  leaderMember?: Member | null; // Member, null, or undefined
}

const team = teamRepo.findOne({
  where: { id },
  relations: ['leaderMember']
}) as MakeRequired<Team, { leaderMember: {} }>;

// Because of MakeRequired, team.leaderMember
// is now only either a Member or null.

if (team.leaderMember !== null) {
  console.log(team.leaderMember.id);
  // Without the use of `MakeRequired`, the above line
  // would throw an error because `team.leaderMember`
  // could be undefined.
}

It's important that undefined means it hasn't been fetched from the database, and null means that it has no value in the database. If, in the above example, we simply used if (team.leaderMember), it may have achieved a similar effect, however it can cause issues:

if (team.leaderMember) does not clearly communicate if we're checking if it's null, if we're checking that it was fetched from the database, or both
If we used if (team.leaderMember), we wouldn't receive any errors if we forgot to join the leaderMember column (even if the type is correctly Team or MakeRequired<Team, {}>, which are both the same thing). This might cause the bug "why does the leaderMember username never get printed?", which would've been solved at build time if we used team.leaderMember !== null and MakeRequired.

MakeRequired as function parameters

We can even ensure that functions only accept teams that have leaderMember.user defined:

function doSomething(
  team: MakeRequired<Team, { leaderMember: { user: {} } }>
) {
  // No build errors, no runtime errors :D
  console.log(team.leaderMember.user.username);
}

// Fail, missing properties
doSomething(team as Team);

// Fail, we have leaderMember but not leaderMember.user
doSomething(team as MakeRequired<Team, {
  leaderMember: {}
});

// Success, we have the required properties
doSomething(team as MakeRequired<Team, {
  leaderMember: { user: {} }
});

// Success, we have the required properties
// plus some extras.
doSomething(team as MakeRequired<Team, {
  leaderMember: { user: { profile: {} } }
  members: { user: {} }
});

This means you can use MakeRequired both when fetching from the database, but also across your codebase to maintain type-safety.

Make it even safer

The above solution also works for manual queries, however if you are using something like TypeORM, you can go one step further. You could make the mistake of mismatching types:

const team = teamRepo.findOne({
  where: { id },
  // Uh oh! We claim we have `leaderMember.user` in the type,
  // but we don't include it in our relations!
  relations: ['leaderMember']
}) as MakeRequired<Team, { leaderMember: { user: {} } }>;

We can use a trick to fix this, by defining both the relations and type at once:

const team = await getTeamById(teamId, {
  members: { user: {} }
});

if (team) {
  // No errors here at all!
  const teamUsernames = team.members
    .map((member) => member.user.username)
    .join(', ');
}

// How we did it:

type PromiseTeam<D> = Promise<MakeRequired<Team, D> | undefined>;

async getTeamById<D extends Depth<Team> = {}>(
    id: string,
    depth?: D,
): PromiseReport<T> {
  return this.repository.findOne(
    { id },
    {
      // We're using depth both as value...
      relations: getRelations(depth),
    },
  // ...and as a type!
  ) as PromiseTeam<D>;
}

// Depth and getRelations can be found in the gists below

Let's see it!

Now, for only $9.99/month... just kidding, here are the gists:

MakeRequired: https://gist.github.com/demipixel/b14a6981dff823fda2ba1f1879d1446f

getRelations (from example above): https://gist.github.com/demipixel/6d92789db75b40eff9bbc514d12fa6b4

Cheers to Robinlemon#0001 on the official TypeScript discord who helped me put this together a couple years ago :)