Skip to main content

design

Deciding the databse​

Our core purpose is to store text based items generated by each user. This is quite a simple task and I decided to use postgresql database.

When it comes to choosing database system, there are many exotic options out there. In relational databases, we have MySql, Oracle etc. However, postgresql is quite mature and feature rich. And it's entirely open source which makes it an attractive option.

There are other databases like Cassandra, MongoDB that provide high availability and fault tolerance. However, the complexity of setting them up is not worth it. And for our application, a simple SQL based database will do the job.

Thinking about scalability, I researched and found there there are many products in the market that claim to scale relational databases in the cloud. Many of them provide compatibility with postgresql like CocroachDB, Yugabyte ect. So even if we need to scale later, then we can switch to these products.

Setting up the database​

Making it easier for developers to set up the development environment is an important and difficult challenge. To accomplish this task, we use docker to setup a postgres sql server on the machine.

We use binami's prepackaged container from docker hub, since it provides many useful environment variables for controlling the behaviour of the postgres server.

ORM​

We are using Drizzle ORM, since it provides a thin layer of abstraction between database and developer. It does not hide the generated SQL queries, hence giving the developer a sense of control. You can also switch to raw SQL, if you need even more control over the database layer.

On the other hand, it provides convinent methods for common CRUD operations on the database. It also provides you the option to select the database driver.

The 3 most common database driver options are:

  1. node-postgres
  2. node-postgres + pg-native
  3. postgres.js

In my experience, I found out that node-postgres is more stable and faster than postgres.js, so for now, I will stick with node-postgres.

The documentation also states that it can perform 10% faster with pg-native extension (option 2), though I did not try it.

Using Drizzle ORM for data manipulation​

Drizzle provides a convenient API for interacting with the database. First of all, we need to initialize the driver.

import { drizzle } from 'drizzle-orm/node-postgres';

const db = drizzle(process.env.DATABASE_URL);

We can also use a Pool, which is considered a good practive for reasons specified here.

And now you are ready to run queries in the database. Please note that you need to define and apply schemas first. See how schema's are organized and defined.

To find out, the possible column types in a schema, check this out.

  1. Inserting data

You can use the insert API.

await db.insert(tableName).values(items);

You can also add multiple items, or make the callback return the inserted items. See docs

If there's a conflict, for example you are trying to insert a duplicate value in a unique column, you can define the behaviour using: onConflictDoNothing or onConflictDoUpdate API's. See docs

You can also use Common Table Expressions (CTE's) if you wish. But I feel, these are better done with hands on raw sql, rather then their clumsy API.

Since, we are using typescript in this project. Drizzle provides us with handy API for getting the type of the data (aka model).

For example, you can define the type of the item to be inserted in a table as follows:

async function addUser(user: typeof usersTable.$inferInsert) {
    // insert the user in the users table
}
  1. Selecting data (or reteriving data)

Simple doing db.select() will fetch all rows and columns of the table.

const res = await db.select().from(usersTable);

The type information is automatically infered based on table schema.

You can select a subset of columns like this:

const res = await db.select({
    propertyOne users.id,
    propertyTwo: users.name,
}).from(usersTable);

const { propertyOne, propertyTwo } = res[0];

The propertyOne and properTwo fields will automatically take values from columns users.id and users.name.

You can define arbitrary expressions in SELECT clause.

It is possible to write complex queries in Drizzle ORM syntax, but I personally thing that it should be avoided.

Remember, you can always use the sql API to run raw SQL. Though, it takes away some of the benefits like type coersion.

Both offset based and cursor based pagination can be implemeneted. See docs.

A very simple method to get a subset of columns is this:

await db.query.posts.findMany({
  columns: {
    title: true,
  },
});

You just define the columns you need in the columns field and make them true.

For best support for a particular database, consider defining the mode option.

  1. Update

Use the update() API. This usually involves finding the records to update first, using some condition in where claus.

await db.update(usersTable)
    .set({ name: 'new name' })
    .where( eq( usersTable.name, 'old name' ) );

See docs for more details.

See docs for more detail.

  1. Delete

you can delete all rows in a table using delete():

await db.delete(usersTable);

Use returning() to get the deleted rows, as we did with insertion.

See docs for more detail.