Why TypeScript is awesome for SQL queries

TL;DR

Node.js with Typescript and SQL databases are a great combination. The type system of Typescript with types Omit and Pick is able to capture the results of queries both precisely and concisely at the same time. Knex as the framework of choice to access the SQL database offers a simple query builder that works well with the type system.

Omitting and picking

Let's assume we want to offer an API of books. There are GET, PUT, POST and DELETE endpoints which persist books into the database.
We can represent a book with an interface.

export interface Book {
  id: string;
  workspaceId: string;
  createdAt: Date;
  updatedAt: Date;
  name: string;
}

A book consists of an id, an id of the workspace the book is part of, two timestamps tracking when the book has been created and last updated and the name of the book. A book is uniquely identified by the combination of its id and workspace id. With Knex, we can write a query that selects a book given these two ids.

import { pg } from "./database";

export function selectBook(
  workspaceId: string,
  bookId: string
): Promise<Omit<"workspaceId", Book> | undefined> {
  return pg
    .table<Book>("book")
    .where("workspaceId", workspaceId)
    .andWhere("id", bookId)
    .first("id", "name", "createdAt", "updatedAt");
}

Note how the return type is excluding the workspaceId from the Book type. That is due to the circumstance that users querying the API for a book are always acting within their workspace. There is no value for them to have the workspace id returned since it will always be the same.

The type Omit<"workspaceId", Book> naturally represents this exclusion of the workspace id without the need to specify a different type.

export interface BookWithoutWorkspaceId {
  id: string;
  createdAt: Date;
  updatedAt: Date;
  name: string;
}

The type Omit and its counterpart Pick come in handy in various situations. A uniquely identified resource like Book usually gets its unique id by the system with the return of the call POST /books. When users call that endpoint, they pass a book without that id. The Omit type allows to express this with Omit<"id", Book> and even allows to exclude other irrelevant properties as well. Omit<"id" | "workspaceId" | "createdAt" | "updatedAt", Book>.

You might be thinking that using the Book type to both represent the persisted entity and the public interface unnecessarily exposes implementation details and that therefore there should be a database entity BookEntity and a data transfer object BookDTO. However, note that this does not contradict the usage of Pick and Omit since you would profit writing a more precise function function mapToDTO(book: Omit<"workspaceId", BookEntity>): Omit<"workspaceId", BookDTO>; instead of function mapToDTO(book: BookEntity): BookDTO;.

Filtering and selecting

While users can retrieve books within their workspace, we would like to allow admins to access all books across workspaces. In the case a user queries, a filter for their workspace should be applied. In the case an admin queries, the filter should not be applied. We can elegantly model this within the flow of the query builder.

import { pg } from "./database";

export function selectBooks(
  workspaceId: string | undefined,
  allWorkspaces: boolean
): Promise<Book[]> {
  const columns = ["id", "createdAt", "updatedAt", "name"];
  return pg
    .table<Book>("book")
    .where((builder) =>
      allWorkspaces ? builder : builder.where("workspaceId", workspaceId)
    )
    .select(columns.concat(allWorkspaces ? ["workspaceId"] : []));
}

While the workspace id is not relevant for a standard user, it is relevant for an admin accessing multiple workspaces. Therefore it is conditionally included in the selected columns. Note that the return type is Book nevertheless, it does not capture the fact that the workspace id is generally not present if the parameter allWorkspaces is true. This could be fixed by separating this query into two queries selectAllBooks and selectWorkspaceBooks. However, this is a tradeoff since every addition like a filter on the name or a sort on the creation date would need to be implemented twice if both queries are accessed via the same endpoint. While aiming for more precise types often helps avoid bugs, there is a point where it can cause more effort than you gain from avoiding bugs.

Inserting and updating

The query builders for inserting and updating have the same style of querying. Assuming we have a book from a PUT /books request, we can try to insert it and on conflict of the primary key update it and leave out the createdAt date such that it won't be overriden.

Note that this combination of methods leads to a list of books being returned and the type system not properly inferred, hence the cast to unknown.

import { omit } from "lodash/fp";
import { pg } from "./database";

export function upsertBook(
  book: Book
): Promise<Omit<"workspaceId", book> | undefined> {
  return pg
    .table<Book>("book")
    .returning(["id", "name", "createdAt", "updatedAt"])
    .insert(book)
    .onConflict(["workspaceId", "id"])
    .merge(omit("createdAt", Book))
    .then((books) =>
      books.length > 0 ? books[0] : undefined
    ) as unknown as Promise<Book>;
}

Beforehand, we can call a function createBook to add an id and the two timestamps to the book passed via the request body.

function createBook(book: Omit<"id" | "createdAt" | "updatedAt", Book>): Book {
  return {
    ...book,
    id: uuid(),
    createdAt: new Date(),
    updatedAt: new Date(),
  };
}

Conclusion

We have seen how Typescript with Knex can be used to query access an SQL database concisely and precisely. The Omit and Pick types avoid redundant type definitions and bugs in the business logic.

Published by Fabian Böller

Visit author page