crud-node

crud-node is an agnostic nodejs client that simplifies crud operations to a database. The package is written in javaScript, and supports typeScript bindings.

• example
• documentation

🤡 Goal

The goal of crud-node is to offer a consistent way of executing CRUD (Create, Read, Update, and Delete) operations across numerous databases, including MySQL, MySQLX, Mongo, Cassandra, and more.

⚡️ Installation

Install crud-node package running the following command:

npm install crud-node

OR

yarn add crud-node

👀 Features

• CRUD
• Sorting
• Filtering
• Grouping
• Pagination

📃 Available methods

• init()
• toString()
• createDocument()
• createDocumentIfNotExists() 🆕
• updateDocument()
• deleteDocument()
• getDocument()
• getDocuments()
• getDocumentByCriteria() 🆕
• searchDocumentsByCriteria() 🆕
• searchDocuments() 🆕
• groupByDocuments() 🆕
• filterDocumentsByCriteria() 🆕
• filterDocuments() 🆕
• filterDocumentsByIds() 🆕
• existsDocument()
• findDocument() 🆕
• fetchAll() 🆕
• getCount() 🆕
• getTotal()
• deleteAll()
• callStoredProcedure() 🆕

❗ Schemas

To ensure consistency of implementation across multiple databases we use json schema to valiate data types.

💨 Examples

In this examples we will use MySQLX controller to show how the package works and what benefits it brings. For MySQL check the examples directory.

Connection config example for MySQL Document Store

A connection with a MySQL server can be established by creating an instance of MySQLX. The connection will be established via call connect. Check also examples directory.

// config.{ts|js}
import { MySQLX } from 'crud-node';

// Connection configuration object
export const connection = {
  host: 'localhost',
  port: 33060,
  schema: 'db',
  password: 'user',
  user: 'user',
};

// Automated connection pool
export const pooling = {
  enabled: true,
  maxSize: 25,
  maxIdleTime: 0,
  queueTimeout: 0,
};

export const settings = {
  ciCollation: 'utf8mb4_0900_ai_ci',
};

export const db = new MySQLX(connection, { pooling }, settings);
await db.connect();

Define schema

You have to define a schema like in the example bellow for you data that you want to insert in the database.

The package use schema approach to help user understand what data will insert in database. Doesn't matter if we speak about MySQL adapter or MySQLX adapter you have to define a schema. Each adapter has is own schema definition. Check examples for a better understanding.

// employeeSchema.{ts|js}
import { IDocument, IDocumentSchema, IDocumentValidation, generateId, getDocument } from 'crud-node';

export enum EmployeeProps {
  _id = '_id',
  createdAt = 'createdAt',
  email = 'email',
  lastName = 'lastName',
  firstName = 'firstName',
  responsibilities = 'responsibilities',
  officeId = 'officeId',
  fired = 'fired',
}

export const validation: IDocumentValidation<EmployeeProps> = {
  level: 'strict',
  schema: {
    type: 'object',
    description: 'Employee',
    properties: {
      _id: { type: 'string' },
      createdAt: { type: 'string', description: 'Timestamp when the record was created' },
      email: {
        type: 'string',
        description: 'The email of an employee, used as unique identifier for account registration',
      },
      lastName: { type: 'string', description: 'Last name of an employee' },
      firstName: { type: 'string', description: 'First name of an employee' },
      responsibilities: {
        type: 'array',
        items: { type: 'string' },
        uniqueItems: true,
        description: 'The responsibilities of an employee',
      },
      officeId: { type: 'string', description: 'The id of office, employee works at' },
      fired: { type: 'boolean', description: '' },
    },
    required: [EmployeeProps._id, EmployeeProps.email],
  },
};

export const employeeSchema: IDocumentSchema<EmployeeProps> = {
  name: 'employee',
  alias: 'emp',
  validation,
  generatedId: false,
  unique: [[EmployeeProps.email]],
  getDocument: (data: Partial<IDocument<EmployeeProps>>): IDocument<EmployeeProps> => {
    const createdAt = Date.now().toString();
    const defaults: Partial<IDocument<EmployeeProps>> = {
      _id: generateId(employeeSchema.alias),
      createdAt,
    };
    return getDocument(EmployeeProps, data, defaults);
  },
  toString: (data: IDocument<EmployeeProps>) => {
    return `${data.firstName} ${data.lastName}`;
  },
};

Create schema on the fly

A schema in a database can be created by using .init() function of a controller. If a schema already exists, it will not be recreated!

This method is available only for MySQL X Protocol (Document Store)

await db.usingSession(async session => {
  await employeeController.init(session);
});

Access schema

For a clean architecture, you can create a controller responsible for accessing the desired schema (table) or simply you can use it inside a route.

// employeeRouter.{ts|js}
import { MySQLX } from 'crud-node';
import { employeeSchema } from './schemas/employee';

...

const db = new MySQLX(connection, pooling, settings);
db.connect().then(() => {
  const employeeController = new CRUDMySQLX(db, employeeSchema);
});

Use the power of JavaScript inheritance and extend CRUD Controller with custom logic:

// employeeController.{ts|js}
import { CRUDMySQLX, IAppWithDatabase, MySQLX } from 'crud-node';

import { EmployeeProps, employeeSchema } from './schemas/employee';

export class EmployeeController extends CRUDMySQLX<EmployeeProps> {
  constructor(app: IAppWithDatabase<MySQLX>) {
    super(app.db, employeeSchema);
  }
}

// This can be placed in a middleware where will leave all the controllers or can be called inside a route where you have access to app object.
export const employeeController = new EmployeeController(app);

Create record with transaction

// employeeRouter.{ts|js}
import { employeeController } from './employeeController';

// Executes operations in a single transaction
const transacted = true;

await db.usingSession(async session => {
  const payload = {
    email: '[email protected]',
    firstName: 'Leslie',
    lastName: 'Brett',
  };
  const data = await employeeController.createDocument(session, payload);
}, transacted);

Create record, if not exists

// employeeRouter.{ts|js}
import { employeeController } from './employeeController';

const payload = {
  email: '[email protected]',
  firstName: 'Leslie',
  lastName: 'Brett',
};
const data = await employeeController.createDocumentIfNotExists(session, payload);

Update record

// employeeRouter.{ts|js}
import { employeeController } from './employeeController';

const employeeId = '<_id>';
const payload = {
  email: '[email protected]',
  firstName: 'Leslie',
  lastName: 'Brett',
};

const data = await employeeController.updateDocument(session, employeeId, payload);

Delete record

// employeeRouter.{ts|js}
import { employeeController } from './employeeController';

const employeeId = '<_id>';

const data = await employeeController.deleteDocument(session, employeeId, payload);

Delete all records

// employeeRouter.{ts|js}
! WARNING This deletes all rows from a table

import { employeeController } from './employeeController';

await employeeController.deleteAll(session);

Retrieve record

// employeeRouter.{ts|js}
import { employeeController } from './employeeController';

const employeeId = '<_id>';

const data = await employeeController.getDocument(session, employeeId);

List records

// officeRouter.{ts|js}
import { OffsetPagination, SortBy } from 'crud-node';

import { officeController } from './officeController';
import { OfficeProps } from './schemas/office';

const pagination = OffsetPagination(1, 10);
const sort = SortBy().asc(OfficeProps.places).toCriteria();

const data = await officeController.getDocuments(session, pagination, sort);

Retrieve record by criteria

// employeeRouter.{ts|js}
import { employeeController } from './employeeController';
import { EmployeeProps } from './schemas/employee';

const officeId = '<_id>';

const data = await employeeController.getDocumentByCriteria(session, { [EmployeeProps.officeId]: officeId });

Search records by criteria (Case-insensitive)

// officeRouter.{ts|js}
import { officeController } from './officeController';
import { OfficeProps } from './schemas/office';

const data = await officeController.searchDocumentsByCriteria(
  session,
  `${officeController.getSearchCriteria(OfficeProps.name, 'keyword1')}
      OR ${officeController.getSearchCriteria(OfficeProps.name, 'keyword2')}
      OR ${officeController.getSearchCriteria(OfficeProps.name, 'keyword3')}`,
  {
    keyword1: '%coworking%',
    keyword2: '%flexible workspace%',
    keyword3: '%serviced office space%',
  },
);

Search records (Case-insensitive)

// officeRouter.{ts|js}
import { officeController } from './officeController';

const data = await officeController.searchDocuments(
  session,
  {
    name: '%coworking%',
    officeCode: '%coworking%',
  },
  'OR',
);

Filter records by criteria

// officeRouter.{ts|js}
import { Condition, Filter, OffsetPagination, SortBy } from 'crud-node';

import { officeController } from './officeController';
import { OfficeProps } from './schemas/office';

const filterOfficesInNYC = Filter.toCriteria(
  Filter.and(Condition.like('address.city', '%New York%'), Condition.gre(OfficeProps.places, 1)),
);
const sortOfficesByAvailablePlaces = SortBy().asc(OfficeProps.places).toCriteria();
const pagination = OffsetPagination(1, 10);

const data = await officeController.filterDocumentsByCriteria(
  session,
  filterOfficesInNYC,
  pagination,
  sortOfficesByAvailablePlaces,
);

Group records

// employeeRouter.{ts|js}
import { GroupBy } from 'crud-node';

import { employeeController } from './employeeController';
import { EmployeeProps } from './schemas/employee';

const data = await employeeController.groupByDocuments<'fired' | EmployeeProps.createdAt>(
  session,
  GroupBy<EmployeeProps, 'fired' | EmployeeProps.createdAt>()
    .fields(EmployeeProps.createdAt)
    .aggregate(EmployeeProps._id, 'fired', AGG.COUNT)
    .toCriteria(),
);

Filter records

// employeeRouter.{ts|js}
import { OffsetPagination } from 'crud-node';
import { employeeController } from './employeeController';

const pagination = OffsetPagination(1, 10);

const data = await employeeController.filterDocuments(session, { fired: true }, 'AND', pagination);

Filter records by ids

// officeRouter.{ts|js}
import { officeController } from './officeController';
import { OfficeProps } from './schemas/office';

const officeIds = ['<id1>', '<id2>'];
const pagination = OffsetPagination(1, 10);
const sort = SortBy().asc(OfficeProps.places).toCriteria();
const data = await officeController.filterDocumentsByIds(session, officeIds, pagination, sort);

Retrieve all records

// employeeRouter.{ts|js}
import { employeeController } from './employeeController';

const data = await employeeController.fetchAll(session);

Find record

// employeeRouter.{ts|js}
import { employeeController } from './employeeController';

const employeeId = '<_id>';

const data = await employeeController.findDocument(session, { employeeId });

Exists record

// employeeRouter.{ts|js}
import { employeeController } from './employeeController';

const employeeId = '<_id>';

const data = await employeeController.existsDocument(session, { employeeId });

Count records by filter

// employeeRouter.{ts|js}
import { EmployeeProps } from './schemas/employee';

const officeId = '<_id>';

const employeesByOffice = await this.employeeController.getCount(session, {
  [EmployeeProps.officeId]: officeId,
});

Retrieve total records

// employeeRouter.{ts|js}
import { employeeController } from './employeeController';

const data = await employeeController.getTotal(session);

Call store procedure

// employeeRouter.{ts|js}
import { employeeController } from './employeeController';

const data = await employeeController.callStoredProcedure(session, '<sp_name>', ['<parameter>']);

Filters

Filter Operations

• eq Equal
• in In
• gr Greater
• gre Greater or Equal
• like Like
• ls Less
• lse Less or Equal
• noteq Not Equal
• empty Empty

Reference

• /crud-node/lib/filter/Filter.ts (ln. 9)
• /crud-node/lib/filter/FilterBy.ts (ln. 63)

Sort

Sorting options

• asc Ascending
• desc Descending

Reference

• /crud-node/lib/filter/Sort.ts (ln. 31)

Pagination

Pagination methods

• OffsetPagination (/crud-node/lib/pagination/OffsetPagination.ts (ln. 10))
• calculateLimit (/crud-node/lib/pagination/OffsetPagination.ts (ln. 25))
• calculateTotalPages (/crud-node/lib/pagination/OffsetPagination.ts (ln. 44))
• resultSet (/crud-node/lib/pagination/OffsetPagination.ts (ln. 54))
• limitOffset (/crud-node/lib/pagination/OffsetPagination.ts (ln. 71))

Errors

| Code | Name | Description | | -------- | -------------------------- | ------------------------------------- | | ERRDB001 | forbidden | Forbidden | | ERRDB002 | notFound | Not found | | ERRDB003 | internalServerError | Sorry, something went wrong | | ERRDB004 | notImplemented | Not impemented | | ERRDB005 | errorConnectionNotOpen | Database connection is not opened | | ERRDB006 | errorConnectionAlreadyOpen | Database connection is already opened | | ERRDB007 | errorDuplicatedDocument | Duplicated document | | ERRDB008 | errorNothingWasDeleted | Nothing was deleted | | ERRDB009 | errorNoIdProvided | Cannot get document without [id] | | ERRDB010 | errorNoCriteriaProvided | Cannot get document without criteria | | ERRDB011 | errorDocumentNotFound | Document not found | | ERRDB012 | errorDbInstruction | Fail to receive data | | ERRDB013 | unsupportedFilterOperation | Unsupported filter operation | | ERRDB014 | duplicatedSortingCondition | Duplicated sorting condition | | ERRDB015 | dbAnyError | Something went wrong! | | | | |

🔨 Issues

If you identify any errors in this module, or have an idea for an improvement, please open an issue. We're excited to see what the community thinks of this project, and we would love your input!

📖 API Documentation

In addition to the above getting-started guide, we have API documentation.

👉🏻 Contributing

We welcome contributions large and small.

👽 Supported databases

• MySQL
• MySQL Document Store
• Percona MySQL
• Percona MySQL Document Store

🔜 Roadmap

• MongoDB October 2023
• PostgreSQL October 2023
• Cassandra November 2023
• OracleDB November 2023
• SQLite December 2023
• CouchDB December 2023

📝 Notes

No notes!

🔝 Used in production by

Delimia - On-demand courier delivery service

⚠️ License

MIT