a lightweight module that allows you to work with database queries without using ORM
MIT License
This package provides an abstract database client and a generic database interface for interacting with different SQL databases (MySQL and PostgreSQL). It allows for building and executing SQL queries in a fluent, chainable manner, abstracting the complexities of direct SQL syntax.
To install the package, use npm:
npm install livesey-database
Before using the package, configure your environment variables in a .env
file or directly in your environment:
DB_TYPE=mysql # or postgres
DB_HOST=#your host
DB_USER=#your username
DB_PASSWORD=#your password
DB_NAME= #your database name
DB_PORT=3306 # or 5432 for PostgreSQL
DB_SSL=true # or false for non-SSL connections
To use it you have to install and import dotenv package:
npm i dotenv
Import for ECMAScript:
import dotenv from 'dotenv';
dotenv.config();
Import for CommonJS:
const dotenv = require("dotenv");
dotenv.config();
You also have to install and set pools for database packages, you will work with:
npm i pg # for PostgreSQL
npm i mysql2 # for MySQL
If you use PostgreSQL(ESM):
import pkg from 'pg';
const { Pool } = pkg;
const postgresPool = new Pool({
host: dbHost,
user: dbUser,
password: dbPassword,
database: dbName,
port: dbPort,
ssl: dbSsl === "true" ? { rejectUnauthorized: false } : false,
});
If you use PostgreSQL(CJS):
const { Pool } = require("pg");
const postgresPool = new Pool({
host: dbHost,
user: dbUser,
password: dbPassword,
database: dbName,
port: dbPort,
ssl: dbSsl === "true" ? { rejectUnauthorized: false } : false,
});
If you use MySQL(ESM):
import mysql from "mysql2/promise";
const mySqlPool = mysql.createPool({
host: dbHost,
user: dbUser,
password: dbPassword,
database: dbName,
port: dbPort,
});
If you use MySQL(CJS):
const mysql = require("mysql2/promise");
const mySqlPool = mysql.createPool({
host: dbHost,
user: dbUser,
password: dbPassword,
database: dbName,
port: dbPort,
});
You need to initialize the database client based on the environment configuration (MySQL or PostgreSQL).
import { MySQLClient, PostgresClient, Database } from 'livesey-database';
import { mySqlPool, postgresPool } from './dbConfig.js';
// Determine the database client type based on the environment configuration
const dbClient = process.env.DB_TYPE === 'mysql' ? new MySQLClient(mySqlPool) : new PostgresClient(postgresPool);
You can create your own table using JSON syntax that follows structures like these:
import { createSchema } from 'livesey-database';
export const PermissionSchema = {
'Table': {
'tableName': 'Permission',
'columns': {
'permissionId': {
'type': 'uuid',
'primaryKey': true,
'unique': true,
'notNull': true
},
'permissionName': {
'type': 'varchar',
'length': 255,
'notNull': true
},
'description': {
'type': 'text'
}
}
}
};
await createSchema(dbClient, PermissionSchema);
console.log('Permission table created successfully.');
If you want to create relationship (OneToOne
, ManyToOne
, OneToMany
, ManyToMany
) you should define it in relations
sector.
export const RoleSchema = {
'Table': {
'tableName': 'Role',
'columns': {
'roleId': {
'type': 'uuid',
'primaryKey': true,
'unique': true,
'notNull': true
},
'roleName': {
'type': 'varchar',
'length': 255,
'notNull': true
}
},
'relations': {
'ManyToMany': {
'relatedEntity': 'Permission',
'foreignKey': 'roleId'
}
}
}
};
await createSchema(dbClient, RoleSchema);
console.log('Role table with ManyToMany relation was created successfully.');
Once you have a dbClient, you can create a Database instance for the desired table.
const db = new Database('User', dbClient);
To select all columns from a table:
const users = await db.select().execute();
console.log('All Users:', users);
To select specific columns with conditions:
const users = await db.select({ name: true, surname: true })
.where({ name: 'John', surname: 'Doe' })
.execute();
console.log('Selected Users:', users);
To insert data into a table:
await db.insert()
.into(['name', 'price', 'quantity'])
.values(['Product A', 10.99, 5])
.execute();
console.log('Product inserted successfully');
To update data in a table:
await db.update()
.set({ name: 'Timber', surname: 'Saw' })
.where({ userId: 'c5fe6661-93ea-43f9-8b6a-92f31f00aa16' })
.execute();
console.log('User updated successfully');
To delete data from a table:
await db.delete()
.where({ userId: 'c5fe6661-93ea-43f9-8b6a-92f31f00aa16' })
.execute();
console.log('User deleted successfully');
Don't forget to release the database client connection when you are done:
try {
//some logic here
} catch (error) {
console.error('❌ Database error:', error.message);
} finally {
await dbClient.release(); // break connection with db
}
DatabaseClient
An abstract class for creating a database client.
new DatabaseClient()
async connect()
: Establish a connection to the database. Must be implemented in derived classes.async query(queryText, params)
: Execute a SQL query with optional parameters. Must be implemented in derived classes.release()
: Close the database connection. Must be implemented in derived classes.MySQLClient
Implements the DatabaseClient
interface for MySQL databases.
new MySQLClient(mySqlPool)
newMySQLClient(mySqlPool)
: Allows to use client like: MySQLClient.newMySQLClient(mySqlPool)
.async connect()
: Returns a MySQL connection from the pool.async query(queryText, params)
: Executes a SQL query using MySQL connection.release()
: Ends all connections in the MySQL pool.PostgresClient
Implements the DatabaseClient
interface for PostgreSQL databases.
new PostgresClient(postgresPool)
newPostgresClient(postgresPool)
: Allows to use client like: PostgresClient.newPostgresClient(postgresPool)
.async connect()
: Returns a PostgreSQL connection from the pool.async query(queryText, params)
: Executes a SQL query using PostgreSQL connection.release()
: Ends all connections in the PostgreSQL pool.Database
A class to build and execute SQL queries for a specific table.
new Database(tableName: string, dbClient: string)
: Initializes a new instance of the Database
class for a given table and database client.select(fields: Array)
: Builds a SELECT SQL query. fields
is an array where keys are column names.where(conditions: Object)
: Adds a WHERE clause to the SQL query. conditions
is an object with column names and their corresponding values or operators.insert()
: Begins an INSERT SQL query.into(columns: Array)
: Specifies the columns for the INSERT SQL query.values(valuesArray: Array)
: Adds values for the INSERT SQL query.update()
: Begins an UPDATE SQL query.set(object: Object)
: Sets the columns and values to be updated.delete()
: Begins a DELETE SQL query.async execute()
: Executes the built SQL query.DatabaseFunction
Extends Database
to provide higher-level operations such as finding, saving, updating, and deleting records.
new DatabaseFunction(tableName: string, dbClient: string)
async findRecord(criteria: Object, selectFields: Array)
: Finds a record matching the criteria.async saveRecord(data: Object)
: Inserts a new record into the table.async updateRecord(criteria: Object, updateData: Object)
: Updates a record matching the criteria.async deleteRecord(criteria: Object)
: Deletes a record matching the criteria.createSchema
Creates a table from schema(json object).
createSchema(dbClient: string, schema: Object)
: Function to create tables and manage relationships.Indexes
Indexes are a crucial part of database optimization, improving the performance of queries by allowing faster data retrieval. The livesey-database
package provides utility functions to create, manage, and delete indexes in both MySQL and PostgreSQL databases.
The package provides three key functions for working with indexes:
createIndex
createUniqueIndex
dropIndex
These functions allow you to easily create and manage indexes on tables, supporting both MySQL and PostgreSQL syntax.
createIndex
Creates a standard index on one or more columns of a specified table.
tableName
(string): Name of the table on which the index will be created.dbClient
(DatabaseClient): The database client to use for the operation.dbType
(string): The type of database (mysql
or postgres
).columns
(string[]): The columns to be indexed.await createIndex('User', dbClient, 'postgres', 'name', 'email');
In this example, an index will be created on the name
and email
columns of the User
table in a PostgreSQL database.
createUniqueIndex
Creates a unique index on one or more columns, ensuring that values in the indexed columns are unique.
tableName
(string): Name of the table on which the unique index will be created.dbClient
(DatabaseClient): The database client to use for the operation.dbType
(string): The type of database (mysql
or postgres
).columns
(string[]): The columns to be indexed.await createUniqueIndex('User', dbClient, 'mysql', 'email');
This creates a unique index on the email
column of the User
table in a MySQL database.
dropIndex
Drops an existing index from a table.
tableName
(string): The name of the table from which the index will be dropped.dbClient
(DatabaseClient): The database client to use for the operation.dbType
(string): The type of database (mysql
or postgres
).indexName
(string): The name of the index to be dropped.await dropIndex('User', dbClient, 'postgres', 'User_email_idx');
This command will drop the index named User_email_idx
from the User
table in a PostgreSQL database.
You can test this module using tests in src/test
folder. Here is the structure:
tests
├── connection.test.js
├── database.test.js
├── envConfig.js
├── functions.test.js
├── indexes.test.js
└── serializer.test.js
1 directory, 6 files
If you want to run tests on your local machine, you have to follow these steps:
You have to install dotenv
package via NPM:
npm i dotenv
Create .env
file and put there these variables(you can put more if you want to use additional functionality in pools):
DB_TYPE=mysql # or postgres
DB_HOST=#your host
DB_USER=#your username
DB_PASSWORD=#your password
DB_NAME= #your database name
DB_PORT=3306 # for MySQL or 5432 for PostgreSQL
DB_SSL=true # or false for non-SSL connections
npm i pg # for PostgreSQL
npm i mysql2 # for MySQL
npm run test
to start testsYou can find this block of code in package.json
:
"scripts": {
"lint": "npx biome format . --write",
"test": "node --test tests/*.test.js"
}
So, if you want to correct syntax in test or in new code, you have to run npm run lint
command.
Contributions are welcome! Please submit a pull request or open an issue on the GitHub repository.
This project is licensed under the MIT License. See the LICENSE file for more information.
If you have any questions or need further assistance, please open an issue on our GitHub repository or contact the maintainer.
For any questions or inquiries, please contact [email protected].
By following the above documentation, you should be able to easily configure and use the database package in your application.