A simple Typescript ORM using the Active Record pattern
In my early days of learning web development I started using Ruby on Rails. I was amazed at how quickly you could create web applications. Since then I have moved on to using mainly Typescript. A few concepts have stuck since the early days, and that's the use of the Active Record pattern. Recently I wanted to see if I could implement this in Typescript, and managed to create a simple ORM.
Why make your own implementation? I don't want to digg into a new library, reading docs, etc. I just want a basic typesafe way of abstracting away my postgres queries, such that I can go on with more productive tasks. Figuring out the right abstractions gives you productivity gains, so its worth investing in. I am sure Prisma and the likes are nice, but I don't want to invest time and energy so I can use it.
This is how consumer interfaces with the active record pattern. In this example we query some user data and store it in the database.
// find a user by email
user.findFirst('email', 'test@example.com');
// find a user by an id
const userId = 123;
user.findFirst(userId);
// save a user
const newUser: User = {
email: 'johndoe@example.com',
name: 'John Doe',
};
user.save(newUser);
Under the hood, this class knows how to talk to the database, make the right queries to read and write to a database. Could be any database, wheter its postgres, mysql, dynamodb etc. The consumer doesn't have to care about which database we are using, how the sql queries looks like, etc. All of this should be abstracted away from the point of view of the consumer.
Here is how the active record pattern can look like
import { QueryResult } from 'pg';
import { camelToSnake, snakeToCamel, strCamelToSnakeCase } from '../utils';
export abstract class ActiveRecord<T, IDType> {
tableName: string;
idField: string;
query: (text: string, params?: any[]) => Promise<QueryResult<any>>;
constructor(
tableName: string,
idField: string,
query: (text: string, params?: any[]) => Promise<QueryResult<any>>,
) {
this.tableName = tableName;
this.idField = idField;
this.query = query;
}
async save(fields: Partial<T>): Promise<T> {
const keys = Object.keys(camelToSnake(fields));
const values = Object.values(fields);
const placeholders = keys.map((_, index) => `$${index + 1}`).join(', ');
const keysString = keys.join(', ');
const sql = `
INSERT INTO ${this.tableName}
(${keysString}) VALUES (${placeholders})
RETURNING *;`;
try {
const res = await this.query(sql, values);
return snakeToCamel(res.rows[0]) as T;
} catch (error: any) {
throw new Error(`Failed to save record: ${error.message}`);
}
}
async saveAll(records: Partial<T>[]): Promise<T[]> {
return Promise.all(records.map((r) => this.save(r)));
}
async update(id: IDType, fields: Partial<T>): Promise<void> {
const keys = Object.keys(camelToSnake(fields));
const values = Object.values(fields);
if (keys.length === 0) {
throw new Error('No fields to update.');
}
const idIndex = keys.length + 1;
const setClause = keys
.map((key, index) => `${key} = $${index + 1}`)
.join(', ');
const sql = `
UPDATE ${this.tableName}
SET ${setClause}
WHERE ${this.idField} = $${idIndex}
`;
try {
await this.query(sql, [...values, id]);
return undefined;
} catch (error: any) {
throw new Error(`Failed to update record: ${error.message}`);
}
}
async findAll(): Promise<T[]> {
const sql = `SELECT * FROM ${this.tableName}`;
try {
const { rows } = await this.query(sql);
return rows.map((v) => snakeToCamel(v)) as T[];
} catch (error: any) {
throw new Error(`Failed to retrieve records: ${error.message}`);
}
}
async findAllWhere<K extends keyof T>(key: K, value: T[K]): Promise<T[]> {
const sql = `
SELECT * FROM ${this.tableName}
WHERE ${strCamelToSnakeCase(String(key))} = $1
`;
try {
const { rows } = await this.query(sql, [value]);
return rows.map((v) => snakeToCamel(v)) as T[];
} catch (error: any) {
throw new Error(`Failed to retrieve record: ${error.message}`);
}
}
async findFirst(id: IDType): Promise<T | null> {
const sql = `
SELECT * FROM ${this.tableName}
WHERE ${this.idField} = $1
LIMIT 1
`;
try {
const { rows } = await this.query(sql, [id]);
return (snakeToCamel(rows[0]) as T) || null;
} catch (error: any) {
throw new Error(`Failed to retrieve record: ${error.message}`);
}
}
async findFirstWhere<K extends keyof T>(
key: K,
value: T[K],
): Promise<T | null> {
const sql = `
SELECT * FROM ${this.tableName}
WHERE ${strCamelToSnakeCase(String(key))} = $1
LIMIT 1
`;
try {
const { rows } = await this.query(sql, [value]);
const row = rows[0];
if (row) {
return snakeToCamel(row) as T;
} else {
return null;
}
} catch (error: any) {
throw new Error(`Failed to retrieve record: ${error.message}`);
}
}
async delete(id: IDType): Promise<void> {
const sql = `
DELETE FROM ${this.tableName}
WHERE ${this.idField} = $1
`;
try {
await this.query(sql, [id]);
return;
} catch (error: any) {
throw new Error(`Failed to delete record: ${error.message}`);
}
}
}
The Active Record pattern knows how to query the db. It can dynamically build the right query. Let's say we want to update an existing record, but only a few of the rows, then the update method can easily figure out which fields we are passing into the method, build the query and execute it to our database.
Now we can have multiple different models that uses this pattern.
export interface User {
userId: number;
name: string;
email: string;
createdAt: string;
updatedAt: string;
lastSeenAt: string;
}
export class UserRecord extends ActiveRecord<User, number> {
constructor(
query: (text: string, params?: any[]) => Promise<QueryResult<any>>,
) {
super('users', 'user_id', query);
this.saveRecords = this.saveRecords.bind(this);
}
}
With the UserRecord defined, and given that the table exists in the database. We can start using the UserRecord.
// A connect to the postgres db has already been setup previously.
const query = (text: string, params?: any[]) => dbClient.query(text, params);
const user = new UserRecord(query);
// now we can query the db.
user.findFirst('email', 'test@example.com');
const userId = 123;
user.findFirst(userId);
const newUser: User = {
email: 'johndoe@example.com',
name: 'John Doe',
};
user.save(newUser);
Awesome.
One thing I have done in my application is to wrap my active record methods with Either from fp-ts. This removes those pesky throw error from my code, making it more clear that my methods can fail, and the consumer has to choose how to respond to it.
Next steps would be to create different adapters for running the queries towards different types of databases, wheter its MySQL, Redis, DynamoDb etc. And one could even get data from tables that are related to the one you are querying. For instance if the User has many Post entries, we could also fetch all of the post entries when fetching the user data.
Happy coding 🤠