Techniques for Synchronous DB Access in TypeScript

I am developing an ORM library for TypeScript called 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 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 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(). This method employs two threads: one for performing asynchronous operations and one for synchronously waiting for the result. Libraries such as 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. This library uses Node.js's child_process module 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' and the README to see what kind of interface Accel Record can achieve by adopting a synchronous API.