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.

It's called 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 (TypeScript), Eloquent (PHP), and 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 for instance, this code will be transformed into an executable SQL query, which makes the development process much easier.

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.

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.

// 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, 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 or MySQL.

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)
• MySQL and MariaDB (via deno_mysql)
• PostgresQL (via 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.

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:

// 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.

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.

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

# Post model
class Post < ApplicationRecord
end

# Fetch all posts
Post.all

Eloquent ORM

// Post model
class Post extends Model {}

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

TypeORM

// 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:

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 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!