MySQLPool

A MySQL connection pool solution designed specifically for Nodejs, featuring intuitive chaining syntax that integrates query builder capabilities with read-write separation.

Features

• Dual Connection Pools: Separate read and write database connections
• Query Builder: Fluent interface for building SQL queries
• Environment Configuration: Easy setup using environment variables
• Connection Management: Automatic connection pooling and cleanup
• Slow Query Detection: Automatic logging of queries taking over 20ms
• JOIN Operations: Support for INNER, LEFT, and RIGHT joins
• CRUD Operations: Complete Create, Read, Update, Delete functionality
• UPSERT Support: Insert or update on duplicate key

Installation

npm install @pardnchiu/mysql-pool

Environment Configuration

Set up your database connections using environment variables:

Read Database (Optional)

DB_READ_HOST=localhostDB_READ_PORT=3306DB_READ_USER=read_userDB_READ_PASSWORD=read_passwordDB_READ_DATABASE=your_databaseDB_READ_CHARSET=utf8mb4DB_READ_CONNECTION=8

Write Database (Required for write operations)

DB_WRITE_HOST=localhostDB_WRITE_PORT=3306DB_WRITE_USER=write_userDB_WRITE_PASSWORD=write_passwordDB_WRITE_DATABASE=your_databaseDB_WRITE_CHARSET=utf8mb4DB_WRITE_CONNECTION=4

Quick Start

importMySQLPoolfrom"@pardnchiu/mysql-pool";// init poolawaitMySQLPool.init();// simple queryconstusers=awaitMySQLPool.table("users").where("status","active").get();// Close connections when doneawaitMySQLPool.close();

API Reference

Initialization

init(): Promise<void>

Initialize the database connection pools.

awaitMySQLPool.init();

close(): Promise<void>

Close all database connections.

awaitMySQLPool.close();

Query Builder Methods

table(tableName: string, target?: "read" | "write"): MySQLPool

Set the target table and target pool

MySQLPool.table("users");// use read poolMySQLPool.table("users","write");// use write pool

select(...fields: string[]): MySQLPool

Specify columns to select.

MySQLPool.table("users").select("id","name","email");MySQLPool.table("users").select("COUNT(*) as total");

where(column: string, operator: any, value?: any): MySQLPool

Add WHERE conditions.

// Basic whereMySQLPool.table("users").where("id",1);MySQLPool.table("users").where("age",">",18);// LIKE operator (automatically adds % wildcards)MySQLPool.table("users").where('name',"LIKE","John");// IN operatorMySQLPool.table("users").where("id","IN",[1,2,3]);

JOIN Operations

// INNER JOINMySQLPool.table("users").innerJoin("profiles","users.id","profiles.user_id");// LEFT JOINMySQLPool.table("users").leftJoin("orders","users.id","orders.user_id");// RIGHT JOIN with custom operatorMySQLPool.table("users").rightJoin("posts","users.id","!=","posts.author_id");

orderBy(column: string, direction?: "ASC" | "DESC"): MySQLPool

Add ORDER BY clause.

MySQLPool.table("users").orderBy("created_at","DESC");MySQLPool.table("users").orderBy("name");// Defaults to ASC

limit(num: number): MySQLPool

Limit the number of results.

MySQLPool.table("users").limit(10);

offset(num: number): MySQLPool

Skip a number of records.

MySQLPool.table("users").offset(20).limit(10);// Pagination

total(): MySQLPool

Include total count in results (adds COUNT(*) OVER()).

constresults=awaitMySQLPool.table("users").total().limit(10).get();// Results will include 'total' field with complete count

Data Operations

get<T>(): Promise<T[]>

Execute SELECT query and return results.

constusers=awaitMySQLPool.table("users").where("status","active").orderBy("created_at","DESC").get();

insert(data: Record<string, any>): Promise<number | null>

Insert a new record.

constuserId=awaitMySQLPool.table("users").insert({name: "John Doe",email: "john@example.com",created_at: "NOW()"});

update(data?: Record<string, any>): Promise<ResultSetHeader>

Update existing records.

// Update with dataconstresult=awaitMySQLPool.table("users").where("id",1).update({name: "Jane Doe",updated_at: "NOW()"});// Update using increase() methodawaitMySQLPool.table("users").where("id",1).increase("login_count",1).update();

upsert(data: Record<string, any>, updateData?: Record<string, any> | string): Promise<number | null>

Insert or update on duplicate key.

// Simple upsert (updates all fields)constid=awaitMySQLPool.table("users").upsert({email: "john@example.com",name: "John Doe",login_count: 1});// Upsert with specific update dataconstid2=awaitMySQLPool.table("users").upsert({email: "john@example.com",name: "John Doe",login_count: 1},{updated_at: "NOW()"});

Raw Queries

read<T>(query: string, params?: any[]): Promise<T>

Execute read query using read pool.

constusers=awaitMySQLPool.read(`SELECT COUNT(*) as total FROM users WHERE status = ?`,['active']);

write<T>(query: string, params?: any[]): Promise<T>

Execute write query using write pool.

constresult=awaitMySQLPool.write(`UPDATE users SET last_login = NOW() WHERE id = ?`,[userId]);

Advanced Examples

Complex Query with Joins and Conditions

constreports=awaitMySQLPool.table("orders").select("orders.id","users.name","products.title","orders.total").innerJoin("users","orders.user_id","users.id").leftJoin("order_items","orders.id","order_items.order_id").leftJoin("products","order_items.product_id","products.id").where("orders.status","completed").where("orders.created_at",">","2023-01-01").orderBy("orders.created_at","DESC").limit(50).get();

Pagination with Total Count

constpage=1;constperPage=10;constresults=awaitMySQLPool.table("users").select("id","name","email","created_at").where("status","active").total().orderBy("created_at","DESC").limit(perPage).offset((page-1)*perPage).get();console.log(`total: ${results[0]?.total||0}`);console.log(`page: ${page}`);

Transaction-like Operations

try{// Create userconstuserId=awaitMySQLPool.table("users","write").insert({name: "John Doe",email: "john@example.com"});// Create user profileawaitMySQLPool.table("profiles","write").insert({user_id: userId,bio: "Software Developer",avatar: "avatar.jpg"});console.log("created successfully");}catch(error){console.error("failed to create:",error);}

Supported MySQL Functions

The following MySQL functions are supported in update operations:

• NOW()
• CURRENT_TIMESTAMP
• UUID()
• RAND()
• CURDATE()
• CURTIME()
• UNIX_TIMESTAMP()
• UTC_TIMESTAMP()
• SYSDATE()
• LOCALTIME()
• LOCALTIMESTAMP()
• PI()
• DATABASE()
• USER()
• VERSION()

Error Handling

try{awaitMySQLPool.init();constusers=awaitMySQLPool.table("users").where("invalid_column","value").get();}catch(error){console.error("error:",error.message);}finally{awaitMySQLPool.close();}

Performance Features

• Connection Pooling: Automatic connection reuse
• Slow Query Detection: Queries over 20ms are automatically logged
• Prepared Statements: All queries use parameterized statements to prevent SQL injection
• Connection Management: Automatic connection release after each query

License

This source code project is licensed under the MIT license.

Creator

邱敬幃 Pardn Chiu

---

©️ 2025 邱敬幃 Pardn Chiu