Simplify MySQL Database Operations in Node.js with japper-mysql

Introduction

Node.js is a popular runtime environment for building server-side applications, and MySQL is a widely used relational database management system. When working with Node.js and MySQL together, developers often need an efficient way to interact with the database and perform common CRUD (Create, Read, Update, Delete) operations. This is where “japper-mysql” comes in. In this article, we’ll explore how japper-mysql, a simple object mapper and CRUD helper for MySQL in Node.js, can simplify your database operations and boost your productivity.

What is japper-mysql?

japper-mysql is a TypeScript library inspired by Dapper and Dapper.SimpleCRUD. It provides an intuitive and straightforward approach to interact with MySQL databases in Node.js. With japper-mysql, you can perform database operations using object-oriented programming techniques and take advantage of TypeScript’s strong typing features.

Key Features and Benefits:

1. Simplified Database Operations: japper-mysql abstracts away the complexities of writing SQL queries and handling database connections, allowing you to focus on your application’s logic. It provides methods for common CRUD operations like selecting, inserting, updating, and deleting records.
2. Object Mapping: japper-mysql maps database entities to TypeScript objects, making it easy to work with data in your application. You can define TypeScript interfaces representing your database tables, and japper-mysql will handle the mapping between those interfaces and the corresponding database records.
3. Query Flexibility: Besides the basic CRUD operations, japper-mysql offers flexibility in executing custom queries. You can write SQL queries directly and pass parameters to retrieve specific data from the database. This enables you to leverage the full power of SQL while still benefiting from the library’s abstraction layer.
4. Transaction Support: japper-mysql allows you to perform multiple database operations as a transaction. Transactions ensure atomicity and consistency when working with related data, such as updating multiple tables simultaneously. You can easily execute a batch of operations as a single transaction, simplifying error handling and ensuring data integrity.

Installation:

Install japper-mysql

# npm install japper-mysql

Configuration:

# node node_modules/japper-mysql/lib/japper-setup.js


Please provide MySql credentials to set up database and create pojos
Host (example: 127.0.0.1): 127.0.0.1
Schema (example: mySchema): db
Username (example: myDbUser): usr
Password (example: myDbPwd): pwd
Port (example: 3306): 3306
Folder for table pojos (example: /MyProject/tables): /Users/o/p/tables
Folder for view pojos (example: /MyProject/view): /Users/o/p/views

Usage Examples:

To illustrate the usage of japper-mysql, let’s consider a few common scenarios:

Initialization

import { Japper } from "japper-mysql";
import { JapperConfig } from "japper-mysql/lib/japper-config";

const main = async () => {
  const config: JapperConfig = {
    host: '127.0.0.1',
    schema: 'db',
    username: 'usr',
    password: 'pwd',
    port: 3306,
    verbose: false,
  };
  const japper = new Japper(config);
};

Get db entity by Id

const user = await japper.getEntityById<Users>(
  new UsersMetadata(), 1000
);

Update db entity

const user = await japper.getEntityById<Users>(
  new UsersMetadata(), 1000
);
user.email = 'email2@email.com';
await japper.update(new UsersMetadata(), user);

Delete db entity

const user = await japper.getEntityById<Users>(
  new UsersMetadata(), 1197
);
await japper.delete(new UsersMetadata(), user);

Get multiple db entities

const users = await japper.getEntities<Users>(
  new UsersMetadata(),
  new Map([
	['name', 'Oscar'],
	['deleted', '0'],
  ])
);

const roleUsers = await japper.getEntitiesByIds<RoleUsers>(
    new RoleUsersMetadata(),
    [1100, 1200, 1300]
);

const roleUsers = await japper.getEntitiesByReferenceIds<RoleUsers>(
    new RoleUsersMetadata(),
    'user_id',
    users.map(({ id }) => id)
);

Custom queries

const sql = 'select * from Users where created_date < ? and name like ?';
const users = await japper.query<Users>(
    sql,
    new Map([
      ['created_date', new Date().toISOString()],
      ['name', '%Os%'],
    ])
);

Count

const count = await japper.getEntitiesCount<RoleUsers>(
  new RoleUsersMetadata(),
  new Map([
	['role_id', 1098],
	['deleted', 0],
  ])
);

Transactions

const japper = new Japper(config);
  const newRoleId= await japper.getId();
  const operations = new Queue<DBTransaction>();
  // Create new Role
  operations.enqueue(
    new DBTransaction(
        DBTransactionType.Add, 
        new RolesMetadata(),
        {
            id: newRoleId,
            name: 'Operators',
            deleted: 0
        }
    ));
  // Create new User
  const newUserId = await japper.getId();
  operations.enqueue(
    new DBTransaction(
        DBTransactionType.Add, 
        new UsersMetadata(),
        {
            id: newUserId, 
            name: 'Oscar',
            email: 'lomaky@gmail.com', 
            deleted: 0, 
            created_date: new Date()
        }
    ));
  // Add user to role
  operations.enqueue(
    new DBTransaction(
        DBTransactionType.Add, 
        new RoleUsersMetadata(),
        {
            id: await japper.getId(), 
            user_id: newUserId, 
            role_id: newRoleId,
            deleted: 0
        }
    ));
  await japper.executeTransaction(operations);

Updating or creating new Entities

Create your table in SQL

CREATE TABLE `Roles` (
  `id` decimal(60,0) NOT NULL,
  `name` varchar(45) NOT NULL,
  `deleted` decimal(60,0) NOT NULL,
  PRIMARY KEY (`id`)
);

Run the following sql script

select pojogen('Roles');

Alternatively can be generated from typescript

const japper = new Japper(config);
const pojo = await japper.getPojo('Users', false);

Or you can run the japper-setup script to update all entities

# node node_modules/japper-mysql/lib/japper-setup.js


Please provide MySql credentials to set up database and create pojos
Host (example: 127.0.0.1): 127.0.0.1
Schema (example: mySchema): db
Username (example: myDbUser): usr
Password (example: myDbPwd): pwd
Port (example: 3306): 3306
Folder for table pojos (example: /MyProject/tables): /Users/o/p/tables
Folder for view pojos (example: /MyProject/view): /Users/o/p/views

Conclusion

japper-mysql is a powerful tool that simplifies MySQL database operations in Node.js. It provides an intuitive API, object mapping capabilities, and transaction support, enabling you to work with databases more efficiently. By leveraging japper-mysql, you can focus on building your application’s business logic without getting bogged down in low-level database interactions. Give japper-mysql a try and experience the productivity boost it brings to your Node.js development workflow.

Resources:

• japper-mysql GitHub repository
• japper-mysql npm package