Reusable Node Code Modules, Part 5 - Database Client

Seamless Data Access: Building Reusable Database Client Modules

Database clients are crucial for interacting with databases in backend applications, enabling CRUD operations and complex queries. Proper management and structuring of database clients ensure efficient data access, scalability, and maintainability. This guide covers how to structure reusable database client modules in Node.js using Express, manage database connections efficiently, and integrate common libraries.

Common Libraries and Tools

1. Sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite.

Key Features

• Promise-Based: Supports promises and async/await syntax

• Data Modeling: Provides a powerful and flexible data modeling tool

• Associations: Supports various types of associations, including one-to-one, one-to-many, and many-to-many

• Hooks and Transactions: Includes hooks for lifecycle events and transaction support

• Query Building: Offers a flexible and powerful query building tool

2. TypeORM

TypeORM is an ORM for TypeScript and JavaScript (ES6, ES7, ES8) that supports various databases, including MySQL, PostgreSQL, MariaDB, SQLite.

Key Features

• TypeScript Support: Full support for TypeScript

• Active Record and Data Mapper Patterns: Supports both patterns for data access

• Relations: Supports various types of relations

• Migrations: Built-in support for database migrations

• Repositories: Provides repository and custom repository patterns

3. Mongoose

Mongoose is an ODM (Object Data Modeling) library for MongoDB and Node.js

Key Features

• Schema-Based: Enforces schemas at the application layer

• Middleware: Supports pre and post hooks for various operations

• Validation: Provides built-in schema validation

• Plugins: Supports plugins to extend functionality

• Aggregation and Queries: Offers powerful aggregation and query capabilities

4. Knex.js

Knex.js is a SQL query builder for relational databases including PostgreSQL, MySQL, SQLite3, and Oracle

Key Features

• Query Building: Provides a powerful and flexible query builder

• Schema Building: Supports schema building and migrations

• Transactions: Full support for transactions

• Promise-Based: Supports promises and async/await syntax

• Flexible Configuration: Works with various database clients and configurations

Comparison

• Sequelize: Best for promise-based ORM with support for multiple relational databases

• TypeORM: Ideal for TypeScript projects needing a flexible ORM with active record and data mapper patterns

• Mongoose: Suitable for MongoDB applications with schema-based data modeling and validation

• Knex.js: Best for SQL query building with support for multiple relational databases and flexible configurations

Examples

Example 1: Sequelize

Setup:

$ npm install sequelize sequelize-cli mysql2

Configuration:

import { Sequelize } from 'sequelize';

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql',
});

export default sequelize;

Usage:

import { DataTypes } from 'sequelize';
import sequelize from './database-client';

const User = sequelize.define('User', {
  username: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  password: {
    type: DataTypes.STRING,
    allowNull: false,
  },
});

sequelize.sync();

const createUser = async (username, password) => {
  return await User.create({ username, password });
};

const getUser = async (username) => {
  return await User.findOne({ where: { username } });
};

export { createUser, getUser };

Example 2: TypeORM

Setup:

$ npm install typeorm reflect-metadata mysql

Configuration:

import 'reflect-metadata';
import { createConnection, Entity, PrimaryGeneratedColumn, Column, Repository } from 'typeorm';

@Entity()
class User {
  @PrimaryGeneratedColumn()
  id = undefined;

  @Column('varchar')
  username = '';

  @Column('varchar')
  password = '';
}

const connectDatabase = async () => {
  const connection = await createConnection({
    type: 'mysql',
    host: 'localhost',
    port: 3306,
    username: 'username',
    password: 'password',
    database: 'database',
    entities: [User],
    synchronize: true,
  });

  return connection.getRepository(User);
};

export default connectDatabase;

Usage:

import connectDatabase from './database-client';

const createUser = async (username, password) => {
  const userRepository = await connectDatabase();
  const user = new User();
  user.username = username;
  user.password = password;
  return await userRepository.save(user);
};

const getUser = async (username) => {
  const userRepository = await connectDatabase();
  return await userRepository.findOne({ where: { username } });
};

export { createUser, getUser };

Example 3: Mongoose

Setup:

$ npm install mongoose

Configuration:

import mongoose from 'mongoose';

const connectDatabase = async () => {
  await mongoose.connect('mongodb://localhost:27017/mydatabase', {
    useNewUrlParser: true,
    useUnifiedTopology: true,
  });
};

const userSchema = new mongoose.Schema({
  username: {
    type: String,
    required: true,
  },
  password: {
    type: String,
    required: true,
  },
});

const User = mongoose.model('User', userSchema);

export { connectDatabase, User };

Usage:

import { connectDatabase, User } from './database-client';

connectDatabase();

const createUser = async (username, password) => {
  const user = new User({ username, password });
  return await user.save();
};

const getUser = async (username) => {
  return await User.findOne({ username });
};

export { createUser, getUser };

Example 4: Knex.js

Setup:

$ npm install knex mysql

Configuration:

import knex from 'knex';

const db = knex({
  client: 'mysql',
  connection: {
    host: 'localhost',
    user: 'username',
    password: 'password',
    database: 'database',
  },
});

export default db;

Usage:

import db from './database-client';

const createUser = async (username, password) => {
  return await db('users').insert({ username, password });
};

const getUser = async (username) => {
  return await db('users').where({ username }).first();
};

export { createUser, getUser };