Techniques for Synchronous DB Access in TypeScript
Tags: typescript, node, orm, database
Published: 2024-07-05 17:46:07

I am developing an ORM library for TypeScript called [Accel Record](https://www.npmjs.com/package/accel-record). Unlike other TypeScript/JavaScript ORM libraries, Accel Record adopts a synchronous API instead of an asynchronous one.

However, to execute DB access synchronously, it was necessary to conduct thorough technical research.

In this article, I will introduce the techniques Accel Record employs to achieve synchronous DB access.

## Supported Databases

Accel Record supports the following databases:

- SQLite
- MySQL
- PostgreSQL

In the early stages of development, priority was given to supporting SQLite and MySQL. Therefore, this article focuses on SQLite and MySQL.

## SQLite

When using SQLite with Node.js, the [better-sqlite3](https://www.npmjs.com/package/better-sqlite3) library is commonly used. Other ORM libraries also frequently use `better-sqlite3` to access SQLite.

Upon investigation, we found that `better-sqlite3` inherently provides a synchronous API. Thus, executing queries to SQLite synchronously was easily achievable using `better-sqlite3`.

## MySQL

The challenge was with MySQL.

When using MySQL with Node.js, the [mysql2](https://www.npmjs.com/package/mysql2) library is commonly used. However, `mysql2` only offers an asynchronous API, making it impossible to use a synchronous API. We searched for other MySQL libraries that offered a synchronous API but could not find any that were well-maintained recently.

Next, we investigated whether there was a way to execute asynchronous APIs synchronously.

We found several older libraries claiming to execute MySQL queries synchronously, and we examined how they achieved synchronous processing.

The first method involved using [Atomics.wait()](https://developer.mozilla.org/docs/Web/JavaScript/Reference/Global_Objects/Atomics/wait). This method employs two threads: one for performing asynchronous operations and one for synchronously waiting for the result. Libraries such as [synckit](https://www.npmjs.com/package/synckit) wrap this functionality to make it more user-friendly. However, `synckit` cannot be used outside the main thread and is not easily usable in a multi-threaded environment. In the Accel Record project, we use Vitest for testing. Vitest performs parallel testing using Node.js `worker_threads`, making this constraint a barrier to adoption.

The second method led us to a library called [sync-rpc](https://www.npmjs.com/package/sync-rpc). This library uses [Node.js's child_process module](https://nodejs.org/api/child_process.html) to create a separate process for executing asynchronous operations and waits synchronously for the result. Upon testing, we found that we could use `sync-rpc` to leverage `mysql2`'s asynchronous API synchronously. However, since `sync-rpc` itself is an older library and did not always perform as expected, we incorporated its source code and made necessary modifications to achieve the desired functionality.

## How sync-rpc Works

`sync-rpc` operates as follows:

1. The main process specifies the entry point file and starts a child process.
2. The child process reads the entry point file and starts as a server.
3. The main process requests function execution from the child process and waits synchronously for the result.
4. The child process executes the asynchronous function and returns the result to the main process.
5. The main process receives the result from the child process and continues processing synchronously.
6. When the main process exits, the child process also terminates.

Using `sync-rpc`, we realized that any asynchronous process could be used synchronously from the perspective of the main process.

## Current Implementation of Accel Record

Currently, by using `sync-rpc`, we can execute asynchronous processes synchronously. Therefore, regardless of the database engine, queries are executed through `sync-rpc` for SQLite and PostgreSQL as well.

Specifically, SQL construction is performed in the main process, and only the query execution is handled by the child process using `sync-rpc`.

## Future Improvements

While the current implementation uses `sync-rpc` to execute asynchronous processes synchronously, it relies on launching a child process.

However, using child processes has its drawbacks:

- Overhead of inter-process communication
  - There is overhead due to data exchange between the main process and the child process.
  - Generally, this overhead is not significantly large compared to DB access latency, so it may not be a major issue in this case.
- Operational complexity
  - Launching child processes can complicate operations.
  - Currently, we depend on Node.js's `child_process` for launching child processes, which might make it difficult to operate in environments other than Node.js.
  - It is expected to work properly in typical Node.js environments and serverless environments where Node.js runs (e.g., AWS Lambda, Vercel Functions).

If we find a method that can overcome these drawbacks, we would consider adopting it.

## Summary

We introduced the techniques Accel Record considered and adopted to achieve synchronous DB access. During the research phase, we explored methods to execute asynchronous processes synchronously using multi-threading and inter-process communication. Ultimately, we adopted `sync-rpc`, which spawns a separate process, to execute queries synchronously.

Please check out '[Introduction to "Accel Record": A TypeScript ORM Using the Active Record Pattern](https://dev.to/koyopro/introduction-to-accel-record-a-typescript-orm-using-the-active-record-pattern-2oeh)' and the [README](https://github.com/koyopro/accella/blob/main/packages/accel-record/README.md) to see what kind of interface Accel Record can achieve by adopting a synchronous API.