livesey-database

Overview

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.

Features

• Fluent API for building SQL queries.
• Database-agnostic: Supports multiple databases (MySQL and PostgreSQL).
• Chainable methods: Build complex queries by chaining method calls.
• Error handling: Provides meaningful error messages for database operations.

Table of Contents

• Getting Started
  • Installation
  • Configuration
  • Usage
    • Initialize the Database Client
    • Schema Creation and Serialization
    • Create a Database Instance
    • Perform Database Operations
    • Close the Database Connection
• API Reference
  • DatabaseClient
  • MySQLClient
  • PostgresClient
  • Database
  • Indexes
• Tests
• Contributing
• License
• Support
• Contact

---

Getting Started

Installation

To install the package, use npm:

npm install livesey-database

Configuration

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:

• Installation:

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:

• Installation:

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,
});

---

Usage

1. Initialize the Database Client

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);

2. Schema Creation and Serialization

Define and Create Schemas

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.');

Foreign Keys and Relationships

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.');

3. Create a Database Instance

Once you have a dbClient, you can create a Database instance for the desired table.

const db = new Database('User', dbClient);

4. Perform Database Operations

Select Queries

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);

Insert Queries

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');

Update Queries

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');

Delete Queries

To delete data from a table:

await db.delete()
  .where({ userId: 'c5fe6661-93ea-43f9-8b6a-92f31f00aa16' })
  .execute();
console.log('User deleted successfully');

5. Close the Database Connection

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
    }

---

API Reference

DatabaseClient

An abstract class for creating a database client.

Constructor

• new DatabaseClient()

Methods

• 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.

Constructor

• new MySQLClient(mySqlPool)

Methods

• 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.

Constructor

• new PostgresClient(postgresPool)

Methods

• 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.

Constructor

• new Database(tableName: string, dbClient: string) : Initializes a new instance of the Database class for a given table and database client.

Methods

• 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.

Constructor

• new DatabaseFunction(tableName: string, dbClient: string)

Methods

• 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).

Methods

• 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.

Types of Indexes

• Standard Index: Speeds up data retrieval based on the values in one or more columns.
• Unique Index: Ensures that the values in the indexed column(s) are unique across all rows.

Functions for Managing Indexes

The package provides three key functions for working with indexes:

1. createIndex
2. createUniqueIndex
3. 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.

Parameters:

• 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.

Example:

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.

Parameters:

• 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.

Example:

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.

Parameters:

• 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.

Example:

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.

---

Tests

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:

1. Install and configure environmental variables

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

2. You have to install pg or mysql2 package via NPM:

npm i pg # for PostgreSQL
npm i mysql2 # for MySQL

3. Use npm run test to start tests

You 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.

4. Happy testing 👩‍💻

---

Contributing

Contributions are welcome! Please submit a pull request or open an issue on the GitHub repository.

---

License

This project is licensed under the MIT License. See the LICENSE file for more information.

---

Support

If you have any questions or need further assistance, please open an issue on our GitHub repository or contact the maintainer.

---

Contact

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.