How to Implement a Basic ORM for PostgreSQL in Node.js

  • Node.js
  • Gabriele Romanato

An ORM (Object-Relational Mapper) allows you to interact with a database using JavaScript objects instead of explicit SQL queries. In this article, we’ll see how to implement a simple ORM for PostgreSQL using Node.js and the pg package.

1. Installing the Required Packages

First, create a new directory for the project and install the required packages:

npm init -y
npm install pg

2. Connecting to PostgreSQL

Let’s create a module to handle the database connection:

// db.js
const { Pool } = require('pg');

const pool = new Pool({
  user: 'your_user',
  host: 'localhost',
  database: 'your_database',
  password: 'your_password',
  port: 5432,
});

module.exports = {
  query: (text, params) => pool.query(text, params),
};

3. Creating the Base ORM Class

Now let’s create a base class that will represent our ORM:

// orm.js
const db = require('./db');

class ORM {
  constructor(tableName) {
    this.tableName = tableName;
  }

  async findAll() {
    const res = await db.query(`SELECT * FROM ${this.tableName}`);
    return res.rows;
  }

  async findById(id) {
    const res = await db.query(
      `SELECT * FROM ${this.tableName} WHERE id = $1`,
      [id]
    );
    return res.rows[0];
  }

  async create(data) {
    const keys = Object.keys(data);
    const values = Object.values(data);
    const placeholders = keys.map((_, i) => `$${i + 1}`).join(', ');

    const res = await db.query(
      `INSERT INTO ${this.tableName} (${keys.join(', ')}) VALUES (${placeholders}) RETURNING *`,
      values
    );
    return res.rows[0];
  }

  async update(id, data) {
    const keys = Object.keys(data);
    const values = Object.values(data);
    const set = keys.map((key, i) => `${key} = $${i + 1}`).join(', ');

    const res = await db.query(
      `UPDATE ${this.tableName} SET ${set} WHERE id = $${keys.length + 1} RETURNING *`,
      [...values, id]
    );
    return res.rows[0];
  }

  async delete(id) {
    const res = await db.query(
      `DELETE FROM ${this.tableName} WHERE id = $1 RETURNING *`,
      [id]
    );
    return res.rows[0];
  }
}

module.exports = ORM;

4. Using the ORM

Here’s an example of how to use it with a table called users:

// index.js
const ORM = require('./orm');
const users = new ORM('users');

(async () => {
  const newUser = await users.create({ name: 'Mario', email: 'mario@example.com' });
  console.log('Created:', newUser);

  const allUsers = await users.findAll();
  console.log('All users:', allUsers);

  const singleUser = await users.findById(newUser.id);
  console.log('User found:', singleUser);

  const updated = await users.update(newUser.id, { name: 'Luigi' });
  console.log('Updated:', updated);

  const deleted = await users.delete(newUser.id);
  console.log('Deleted:', deleted);
})();

Conclusion

We’ve seen how to build a basic ORM for PostgreSQL with Node.js. While simple, this approach can be useful for small projects or to better understand how more advanced ORMs like Sequelize or TypeORM work.

Back to top