Luis Gustavo Macedo

Posted on Jul 3, 2023

Typescript - Type System

#typescript #react #functional #programming

Hi guys,

I have always wanted to produce content about programming and technology from some of the subjects I know, so that more people can learn.

So I am getting courage and starting to produce content.

Besides sharing knowledge I also intend to learn a lot from these articles. As I am not a genius and do not know everything, so please correct me, give suggestions, theories and let's make this space a environment for discussion and exchange of experiences and knowledge.

I rolled a lot in the introduction, so let to that is important.

You know typescript?

Do you know how to extract the maximum of typescript?

In this article we will create a type system for the a query builder to study and explore the maximum of the typescript. So will focus on the type and not in the implementation. Final code

What is the query builder?

As this is not the proposal, I asked for the GPT
"A query builder is a software tool or library that allows developers to construct database queries in a more intuitive and efficient way. Instead of writing raw SQL statements, which can be complex and error-prone, a query builder provides a higher-level abstraction that enables users to build queries using a more human-readable and structured syntax."

We will build something like this

const user = await QueryBuilder.database(myDbInstance)
    .from("user")
    .select(["id", "email", "name"])
    .where("id = :id", { id })
    .build();

We will learn and have a little notion about

generics
keyof operator
infer operator and conditional
manipulate strings
mapped and Indexed Access types

#1 Mapping the database and table of query

In this step we will use some of generics.

First we have to type the function which will server as builder, how this function use the design pattern builder, do not forget to return the own function to make this composition .from(...).select(...).where(...)

Using this database shape for tests

interface User {
  id: number;
  name: string;
  email: string;
  password: string;
  updated_at: Date;
}

interface Product {
  id: number;
  name: string;
  description: string;
  price: number;
}

interface Order {
  id: number;
  user_id: number;
  product_id: number;
  quantity: number;
  created_at: Date;
}
// Use  this DB for tests
type DataBaseTest = {
  user: User;
  product: Product;
  order: Order;
}

Init of query builder

declare namespace QueryBuilder {
  function database(db: DataBaseTest): QueryBuilderMethods;
}

type QueryBuilderMethods = {
  from: (table: keyof DataBaseTest) => QueryBuilderMethods,
  build: () => Promise<void>
}

Ts playground

keyof

The keyof takes all the keys of the object and returns the union them.
Then, in this case all keys of DataBaseTest, that is

// "user" | "product" | "order"
type Tables = keyof DataBaseTest;

Dynamic?

But that way our queryBuilder is not dynamic. If we want to use it in another database?

So add a generic to get the database that was selected via params of database() methods.

declare namespace QueryBuilder {
  function database<DB>(db: DB): QueryBuilderMethods<DB>;
}

type QueryBuilderMethods<DB> = {
  from: (table: keyof DB) => QueryBuilderMethods<DB>,
  build: () => Promise<void>
}

Ts playground

The generic will capture the type that was passed in database() method and pass to QueryBuilderMethods for him use as he sees fit.

With this change the build became dynamic, it can receive any database

const myDbInstance = {} as DataBaseTest;

QueryBuilder.database(myDbInstance)
  // "user" | "product" | "order"
  .from("order")
  .build()

const anotherDbInstance = {} as AnotherDatabase;

QueryBuilder.database(anotherDbInstance)
  // "user" | "payment"
  .from("payment")
  .build()

#2 Mapping the all columns of table selected

In this step we'll add the .select() method, that can receive all columns of table, in other words, receive all keys from the table.

How the .select() depends on what was passed on .from(), we have to add one more generic, the Table in question.

type QueryBuilderMethods<
  DB,
  Table extends keyof DB = keyof DB
> = {
  from: (table: Table) => QueryBuilderMethods<DB>,
  select: (s: Array<keyof Table>) => QueryBuilderMethods<DB>,
  build: () => Promise<void>
}

The extends in generic, works like typing for him. The Table can receive the keys of DB and added a default value that is keyof DB
But if you test, ts playground, it won't work yet, why?

That's because we made it too generic. Then we have add more specificity in some points.

type QueryBuilderMethods<
  DB,
  Table extends keyof DB = keyof DB
> = {
  // Here, needs specify the table, not any `key` of `DB`
  from: <T extends Table>(table: T) => QueryBuilderMethods<DB, T>,
  select: (s: Array<keyof DB[Table]>) => QueryBuilderMethods<DB, Table>,
  // Don't forget to always pass the generics to 
  // `QueryBuilderMethods`, in all methods, it will serve for 
  // the next method that is used in the builder
  build: () => Promise<void>
}

We already have that ts playground

#3 `.where()` method

"WHERE user.id = 1"

The idea of this method is receive a string with condition, and a object with the values that will be used in the condition.

The string should follow this pattern "column operator :value"

Let's create a utility to make the where string and add new .where() method

type QueryBuilderMethods<
  DB,
  Table extends keyof DB = keyof DB,
  Columns extends keyof DB[Table] = keyof DB[Table]
> = {
  // ...,
  where: (
    w: MakeWhereString<Columns & string>,
  ) => QueryBuilderMethods<DB, Table>,
  // ...
}

type MakeWhereString<
  Column extends string, 
  Operator extends string = ComparisonOperators, 
  Value extends string = string
> = `${Column} ${Operator} :${Value}`;

type ComparisonOperators = "=" | "<>" | ">=" | "<=";

Beyond utility, i moved keyof DB[Table] to Columns generic for more readability.

Ts playground

The where can only accept one string that has "column_of_table_selected ComparisonOperators :value"

QueryBuilder.database(myDbInstance)
  .from("order")
  .select(["id", "created_at", "quantity"])
  // ERROR: Only receive columns of order table
  .where("field = :id")
  .build()

QueryBuilder.database(myDbInstance)
  .from("order")
  .select(["id", "created_at", "quantity"])
  // ERROR: Only receive one of operators
  .where("id === :id")
  .build()

QueryBuilder.database(myDbInstance)
  .from("order")
  .select(["id", "created_at", "quantity"])
  // ERROR: Must have the ":"
  .where("id = id")
  .build()

See the errors Ts playground

Use template literal types in typescript is very useful, this is a simple approach to use them.

#4 Extract the column of where string

Now we will use infer and conditionalto extract the which column that was specified

Let's make a utility type for this

type ExtractColumnByWhere<Str> = Str extends MakeWhereString<infer C>
  ? C
  : never;

infer basically extracts from within another type structure, some type. Combining with conditional, we can return this type if typescript could infer the type correctly. Read more about infer keyword.

So let's put all this into our structure and make the object with the values using mapped types

type QueryBuilderMethods<
  DB,
  Table extends keyof DB = keyof DB,
  Columns extends keyof DB[Table] = keyof DB[Table],
> = {
  //...
  where: <
    W extends MakeWhereString<Columns & string>,
    V extends ExtractColumnByWhere<W> 
  >(
    w: W,
    v: { [K in V]: DB[Table][V & Columns] }
  ) => QueryBuilderMethods<DB, Table>,
  //...
}

Ts playground

The Mapped types is a way to create new types of objects without repeating yourself, it iterates over the union of keys and creating the new object.

Works like this

type NewObjectKeys = "key" | "key1" | "key3"
type NewObject = { 
  [K in NewObjectKeys]: string
}
// Result in: {
//     key: string;
//     key1: string;
//     key3: string;
// }

Ts playground

#5 Make the `build` return

In the end, we need of the columns selected in the .select() methods, to make the return of our query builder.

Let's make a simple change to capture selected columns from generics

type QueryBuilderMethods<
  DB,
  Table extends keyof DB = keyof DB,
  Columns extends keyof DB[Table] = keyof DB[Table],
  // New Generics, based on the table columns
  ColumnsSelected extends Array<Columns> = Array<Columns>,
> = {
  from: <T extends Table>(table: T) => QueryBuilderMethods<DB, T>,
  // Here should be more specific, not just any columns array, but the columns array that is passed by params
  // and pass this columns again to return of .select() through generics of QueryBuilderMethods
  select: <S extends Array<Columns> = Array<Columns>>(s: S) => QueryBuilderMethods<DB, Table, Columns, S>,
  where: <
    W extends MakeWhereString<Columns & string>,
    V extends ExtractColumnByWhere<W> 
  >(
    w: W,
    v: { [K in V]: DB[Table][V & Columns] }
  // Don't forget of pass the generics to  `QueryBuilderMethods`
  ) => QueryBuilderMethods<DB, Table, Columns, ColumnsSelected>,
  // ...
}

Ts playground

Now that we have the columns selected, can use the Pick and Indexed Access to return object with only keys we selected in .select()

type QueryBuilderMethods<
  DB,
  Table extends keyof DB = keyof DB,
  Columns extends keyof DB[Table] = keyof DB[Table],
  ColumnsSelected extends Array<Columns> = Array<Columns>,
> = {
  // ...,
  build: () => Promise<Pick<DB[Table], ColumnsSelected[number]>>
}

Ts playground
What a workaround this is?
I confess that this is not at all pretty, but I thought this approach was nice.

The Indexed Access we can get a specific type from an indexed type. How ColumnsSelected is a array, we can access all its elements by accessing your number property.

Some examples

const arrayNumbers = [1, 2, 3, 4, 5] as const;
type ArrayNumbers = typeof arrayNumbers

type SpecificNumbers = ArrayNumbers[number] // 1 | 2 | 3 | 4 | 5
type SpecificNumbersIndex = ArrayNumbers[2] // 3

// Works with object
interface User {
  id: number;
  name: string;
  email: string;
  password: string;
  updated_at: Date;
}
type UserEmail = User["email"] // string

Ts playground

That's it. Now we have a query builder fully typed and you probably won't get the column syntax wrong again.

const user = QueryBuilder.database(myDbInstance)
  .from("user")
  .select(["id", "created_at", "email"]) // Only accept the user keys
  .where("email = :email", { email: "email@email.com" }) // String only accept correct user table keys, specific Operators and specific object as second parameter 
  .build()
// Returns ->
// {
//   id: number;
//   email: string;
//   password: string;
// }

Feel free to test the code here
Gist

Conclusion

I hope you understood and left here with some new knowledge.

That's it guys, I want to bring more in-depth content on these subjects and other things from the world of technology.

This month I will create a routine of bringing you some articles every week

DEV Community

Typescript - Type System

You know typescript?

Do you know how to extract the maximum of typescript?

What is the query builder?

#1 Mapping the database and table of query

keyof

Dynamic?

#2 Mapping the all columns of table selected

#3 `.where()` method

#4 Extract the column of where string

#5 Make the `build` return

Conclusion

Top comments (0)

Read next

🌍 Landmark API: Real-time Data for Landmarks, Tourism, and Travel Tech

Supercharging Svelte with Web Workers: Unleashing the Power of Background Processing for Smoother, Faster Apps

DUCK (file structure) YOU!

TypeScript 5.7 RC Released, New npm Alternative, The Future of JavaScript Frameworks and more

You know typescript?

Do you know how to extract the maximum of typescript?

What is the query builder?

#1 Mapping the database and table of query

keyof

Dynamic?

#2 Mapping the all columns of table selected

#3 .where() method

#4 Extract the column of where string

#5 Make the build return

Conclusion

Read next

🌍 Landmark API: Real-time Data for Landmarks, Tourism, and Travel Tech

Supercharging Svelte with Web Workers: Unleashing the Power of Background Processing for Smoother, Faster Apps

DUCK (file structure) YOU!

TypeScript 5.7 RC Released, New npm Alternative, The Future of JavaScript Frameworks and more

#3 `.where()` method

#5 Make the `build` return