I'm Building an ORM for Deno!
Tags: javascript, typescript, deno, opensource
Published: 2020-05-29 14:57:40

Hey developers! I hope you're doing well.

I just want to let you know that I'm currently working on an open-source project which I think is really cool.

I'm building an ORM library for one of the hottest yet immature technologies this year: [Deno](https://deno.land).

It's called [Cotton](https://github.com/rahmanfadhil/cotton), but don't ask me why I come up with this name, I have no idea.

I'm super excited about this project, and I want to share my experience with you.

Honestly, the reason why I start this project is not because I can. Shit, I don't even know how to write a raw SQL query to join two different tables.

Over the past years, I feel like I didn't do anything for the open-source community, even though I use open-source all the time. And I feel kind of bad about it.

So, I decided to contribute to the project that I currently fell in love with, which is Deno.

# How I build it?

The hardest part of building this project is that there is no tutorial on the internet that teaches you about "how to build an ORM". So, the only way I can learn about this is by reading hundreds of lines of existing ORMs on GitHub, such as [TypeORM](https://typeorm.io) (TypeScript), [Eloquent](https://github.com/laravel/framework/tree/7.x/src/Illuminate/Database) (PHP), and [ActiveRecord](https://github.com/rails/rails/tree/master/activerecord) (Ruby).

I finally understood that these gigantic tools can be separated into three main parts.

1. Query Builder
2. Database Adapter
3. Model

I know, most of them have their own unique and complicated structure, but at least here is what I can start with.

## Query Builder

Query builder is a class whose main task is to construct raw SQL queries. This is the building block of any ORMs, so it is one of the most important parts to understand.

You probably have seen one these. In [Laravel](https://laravel.com) for instance, this code will be transformed into an executable SQL query, which makes the development process much easier.

```php
DB::table('users')->where('name', 'John')->first();
// SELECT * FROM users WHERE name = 'John' LIMIT 1;
```

SQL is complicated, and as a developer, we don't want to work with complicated stuff. Just let the library do the heavy-lifting for us.

Writing query builder is the first thing I did for this project. For sure, it's not an easy thing to do. There are a lot of things that I need to learn about SQL to just implement this feature. But overall, I really enjoy it.

The first version of Cotton's query builder can only build queries with `WHERE` and `LIMIT` constraints, which is very terrible. But this feature is growing very quickly and more and more constraints are implemented to Cotton as time goes by.

Turns out, making the query builder itself is not as hard as I imagine. The difficult part, however, is implementing all available query constraints to it. But for me, it's just a matter of time until we cover them all.

In Cotton, the query builder is accessible via `queryBuilder` once you connected to your database using the `connect` function.

```ts
const db = await connect({
    type: 'sqlite',
    database: './db.sqlite3'
})

// SELECT * FROM users WHERE email = 'a@b.com' LIMIT 5;
await db.queryBuilder('users')
    .where('email', 'a@b.com')
    .limit(5)
    .execute()
```

If you don't want to execute the query and only want to get the raw SQL statement, you can use the `toSQL` method.

```ts
// SELECT * FROM users WHERE email = 'a@b.com' LIMIT 5;
const sql: string = db.queryBuilder('users')
    .where('email', 'a@b.com')
    .limit(5)
    .toSQL()
```

## Database Adapter

The database adapter allows you to work with different types of databases while using the same code structure. Sometimes, you want to develop your apps on your local machine using [SQLite](https://sqlite.org), because it's very convenient to setup. However, when you deploy your app to the production environment, you want to use a different kind of database that can handle more heavy stuff such as [PostgreSQL](https://www.postgresql.org) or [MySQL](https://www.mysql.com).

Each database has its own unique way to connect and perform queries, even though they use the same query language, which is SQL.

So the job of the database adapter is to handle that thing for you. You write your code once but use it anywhere without needing to change anything.

Currently, Cotton supports 3 popular databases: MySQL, PostgreSQL, and SQLite. At this point I'm going to focus on these three before jumping to things like Oracle.

To implement this feature in Cotton, I'm using some of the existing database drivers for Deno.

- SQLite3 _(via [sqlite](https://github.com/dyedgreen/deno-sqlite))_
- MySQL and MariaDB _(via [deno_mysql](https://manyuanrong/deno_mysql))_
- PostgresQL _(via [postgres](https://github.com/deno-postgres/deno-postgres))_

Because these drivers have a different set of APIs, I wrote an adapter class that can handle common database operations such as `connect`, `disconnect`, `execute` to run SQL statement, and `query` to fetch records from the database.

```ts
export abstract class BaseAdapter {
  /**
   * Run SQL query and get the result
   * 
   * @param query SQL query to run (ex: "SELECT * FROM users;")
   * @param values Bind values to query to prevent SQL injection
   */
  public abstract query<T>(query: string, values?: any[]): Promise<T[]>;

  /**
   * Execute SQL statement and save changes to database
   * 
   * @param query SQL query to run (ex: "INSERT INTO users (email) VALUES ('a@b.com');")
   * @param values Bind values to query to prevent SQL injection
   */
  public abstract execute(query: string, values?: any[]): Promise<void>;

  /**
   * Connect database
   */
  public abstract connect(): Promise<void>;

  /**
   * Disconnect database
   */
  public abstract disconnect(): Promise<void>;
}
```

Each database adapter needs to conform to this `BaseAdapter` class, which essentially makes us able to stick with the same set of APIs. So, I ended up creating three database driver classes which implement the same methods. Each class looks pretty much like this:

```ts
// Mysql
const adapter = new MysqlAdapter({
  database: 'test',
  password: 'test',
  hostname: '127.0.0.1'
  // other config...
})

// Postgres
const adapter = new PostgresAdapter({
  database: 'test',
  password: 'test',
  hostname: '127.0.0.1'
  // other config...
})

// Sqlite
const adapter = new SqliteAdapter({
  database: './db.sqlite3'
  // other config...
})
```

Because these adapters are conforming to the same abstract class, we can use the same method to interact with different kinds of databases.

```ts
await adapter.execute(`
  CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email VARCHAR(255),
  );
`)
```

Now, I can create a helper function called `connect` which automatically decide which adapter to use only by passing a single parameter.

```ts
const db = await connect({
  type: "sqlite", // available type: 'mysql', 'postgres', and 'sqlite'
  database: "./db.sqlite3",
  // other...
});
```

## Model

Finally, the last building block of an ORM is model. It's built on top of query builder, and this is what the developers will be working on most of the time.

If you are already familiar with the MVC pattern, model is the M of MVC. A model is responsible for handling CRUD operations on a single database table. Every ORM out there implements their model differently, and here are some examples.

**ActiveRecord**

```ruby
# Post model
class Post < ApplicationRecord
end

# Fetch all posts
Post.all
```

**Eloquent ORM**

```php
// Post model
class Post extends Model {}

// Fetch all posts
Post::all();
```

**TypeORM**

```ts
// Post model
@Entity()
class Post extends BaseEntity {
  @Field()
  public title: string;

  @Field()
  public content: string;
}

// Fetch all posts
const posts = await Post.find()
```

Once, I've built the MVP query builder and MVP database adapter, I can combine those two to execute CRUD operations via a single object.

Here is how I did it:

```ts
class User extends Model {
  static tableName = 'users';

  email: string;
}

db.addModel(User)

const users = await User.find()
```

First, you need to create your model which extends the `Model` class. You need to specify the table name for that model. There are other optional configurations available such as `primaryKey` to set the default primary key field.

Then, register the model class using `addModel` method. This allows your model to execute SQL queries to your database. Finally, you can do CRUD operations to your `users` table via this model.

# Conclusion

Even though this sounds like a lot of things, it actually has tons of crucial features that I haven't implemented yet. Things like schema migration, caching, or even the basic ones like model and query builder are not finished yet.

I will try to keep you posted on this interesting project. And, if you're the kind of person who wants to learn something new, let's improve [this project](https://github.com/rahmanfadhil/cotton) together!

Any contributions means a lot.

> Everyone writes bad code, but as long as we keep improving each other's, good things happen.

Finally, what do you think about this? Let me know in the comments below!