Connecting Node.js to PostgreSQL and MySQL

This article explains how Node.js interacts with relational database management systems (RDBMS) like PostgreSQL and MySQL. It covers the core communication mechanisms, including the role of asynchronous I/O, connection pooling, low-level database drivers, query builders, and Object-Relational Mappers (ORMs) that developers use to execute queries and manage data.

The Role of Asynchronous I/O

Node.js operates on a single-threaded event loop that utilizes non-blocking I/O operations. When Node.js communicates with a relational database, it does not block the execution of other code while waiting for the database to process a query. Instead, Node.js sends the query over a network socket and registers a callback, promise, or uses async/await syntax. Once the database server returns the data, the Node.js event loop handles the response and resumes code execution.

Low-Level Database Drivers

At the most basic level, Node.js interacts with databases using driver libraries. These drivers are npm packages written in JavaScript or C++ that implement the specific wire protocol of the target database.

• For PostgreSQL: The most common driver is pg (node-postgres).
• For MySQL: The standard drivers are mysql2 or the older mysql library.

Drivers establish the physical TCP connection to the database. They allow developers to write and execute raw SQL queries directly from their JavaScript code. The driver takes the SQL string, sends it to the database, receives the tabular binary or text result, and parses it into native JavaScript objects (arrays and JSON).

Connection Pooling

Database connections are expensive to open and close repeatedly. Drivers manage this by utilizing a “connection pool.” A connection pool maintains a cache of active connections. When a query needs to be run, Node.js borrows a connection from the pool, executes the query, and immediately returns the connection to the pool for reuse.

Query Builders

To avoid writing raw SQL strings, developers often use query builders like Knex.js. A query builder provides a programmatic interface to construct SQL queries using JavaScript methods.

For example, instead of writing SELECT * FROM users WHERE id = 1, a developer writes knex('users').where('id', 1). Knex converts this JavaScript method chain into the correct SQL syntax for either PostgreSQL or MySQL and executes it using the underlying database driver. Query builders offer security against SQL injection attacks by automatically parameterizing inputs.

Object-Relational Mappers (ORMs)

ORMs provide the highest level of abstraction. They map database tables to JavaScript objects or TypeScript classes. Popular ORMs for Node.js include Sequelize, TypeORM, and Prisma.

When using an ORM, you define a schema (or model) in your code. The ORM automatically translates operations on these models into SQL queries. For example, saving a new user object in your code will automatically trigger an INSERT statement in PostgreSQL or MySQL. ORMs also handle database migrations, relation mapping (like one-to-many joins), and data validation, reducing the amount of SQL syntax a developer must write manually.