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 -
keyofoperator -
inferoperator andconditional - manipulate strings
-
mappedandIndexed Accesstypes
#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>
}
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>
}
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]toColumnsgeneric for more readability.
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>,
//...
}
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;
// }
#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>,
// ...
}
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
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
Top comments (0)