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.

1// config.{ts|js}
2import { MySQLX } from 'crud-node';
3
4// Connection configuration object
5export const connection = {
6  host: 'localhost',
7  port: 33060,
8  schema: 'db',
9  password: 'user',
10  user: 'user',
11};
12
13// Automated connection pool
14export const pooling = {
15  enabled: true,
16  maxSize: 25,
17  maxIdleTime: 0,
18  queueTimeout: 0,
19};
20
21export const settings = {
22  ciCollation: 'utf8mb4_0900_ai_ci',
23};
24
25export const db = new MySQLX(connection, { pooling }, settings);
26await 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.

1// employeeSchema.{ts|js}
2import { IDocument, IDocumentSchema, IDocumentValidation, generateId, getDocument } from 'crud-node';
3
4export enum EmployeeProps {
5  _id = '_id',
6  createdAt = 'createdAt',
7  email = 'email',
8  lastName = 'lastName',
9  firstName = 'firstName',
10  responsibilities = 'responsibilities',
11  officeId = 'officeId',
12  fired = 'fired',
13}
14
15export const validation: IDocumentValidation<EmployeeProps> = {
16  level: 'strict',
17  schema: {
18    type: 'object',
19    description: 'Employee',
20    properties: {
21      _id: { type: 'string' },
22      createdAt: { type: 'string', description: 'Timestamp when the record was created' },
23      email: {
24        type: 'string',
25        description: 'The email of an employee, used as unique identifier for account registration',
26      },
27      lastName: { type: 'string', description: 'Last name of an employee' },
28      firstName: { type: 'string', description: 'First name of an employee' },
29      responsibilities: {
30        type: 'array',
31        items: { type: 'string' },
32        uniqueItems: true,
33        description: 'The responsibilities of an employee',
34      },
35      officeId: { type: 'string', description: 'The id of office, employee works at' },
36      fired: { type: 'boolean', description: '' },
37    },
38    required: [EmployeeProps._id, EmployeeProps.email],
39  },
40};
41
42export const employeeSchema: IDocumentSchema<EmployeeProps> = {
43  name: 'employee',
44  alias: 'emp',
45  validation,
46  generatedId: false,
47  unique: [[EmployeeProps.email]],
48  getDocument: (data: Partial<IDocument<EmployeeProps>>): IDocument<EmployeeProps> => {
49    const createdAt = Date.now().toString();
50    const defaults: Partial<IDocument<EmployeeProps>> = {
51      _id: generateId(employeeSchema.alias),
52      createdAt,
53    };
54    return getDocument(EmployeeProps, data, defaults);
55  },
56  toString: (data: IDocument<EmployeeProps>) => {
57    return `${data.firstName} ${data.lastName}`;
58  },
59};

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)

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

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.

1// employeeRouter.{ts|js}
2import { MySQLX } from 'crud-node';
3import { employeeSchema } from './schemas/employee';
4
5...
6
7const db = new MySQLX(connection, pooling, settings);
8db.connect().then(() => {
9  const employeeController = new CRUDMySQLX(db, employeeSchema);
10});

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

1// employeeController.{ts|js}
2import { CRUDMySQLX, IAppWithDatabase, MySQLX } from 'crud-node';
3
4import { EmployeeProps, employeeSchema } from './schemas/employee';
5
6export class EmployeeController extends CRUDMySQLX<EmployeeProps> {
7  constructor(app: IAppWithDatabase<MySQLX>) {
8    super(app.db, employeeSchema);
9  }
10}
11
12// 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.
13export const employeeController = new EmployeeController(app);

Create record with transaction

1// employeeRouter.{ts|js}
2import { employeeController } from './employeeController';
3
4// Executes operations in a single transaction
5const transacted = true;
6
7await db.usingSession(async session => {
8  const payload = {
9    email: 'leslie46@24mailin.com',
10    firstName: 'Leslie',
11    lastName: 'Brett',
12  };
13  const data = await employeeController.createDocument(session, payload);
14}, transacted);

Create record, if not exists

1// employeeRouter.{ts|js}
2import { employeeController } from './employeeController';
3
4const payload = {
5  email: 'leslie46@24mailin.com',
6  firstName: 'Leslie',
7  lastName: 'Brett',
8};
9const data = await employeeController.createDocumentIfNotExists(session, payload);

Update record

1// employeeRouter.{ts|js}
2import { employeeController } from './employeeController';
3
4const employeeId = '<_id>';
5const payload = {
6  email: 'leslie46@24mailin.com',
7  firstName: 'Leslie',
8  lastName: 'Brett',
9};
10
11const data = await employeeController.updateDocument(session, employeeId, payload);

Delete record

1// employeeRouter.{ts|js}
2import { employeeController } from './employeeController';
3
4const employeeId = '<_id>';
5
6const data = await employeeController.deleteDocument(session, employeeId, payload);

Delete all records

1// employeeRouter.{ts|js}
2! WARNING This deletes all rows from a table
3
4import { employeeController } from './employeeController';
5
6await employeeController.deleteAll(session);

Retrieve record

1// employeeRouter.{ts|js}
2import { employeeController } from './employeeController';
3
4const employeeId = '<_id>';
5
6const data = await employeeController.getDocument(session, employeeId);

List records

1// officeRouter.{ts|js}
2import { OffsetPagination, SortBy } from 'crud-node';
3
4import { officeController } from './officeController';
5import { OfficeProps } from './schemas/office';
6
7const pagination = OffsetPagination(1, 10);
8const sort = SortBy().asc(OfficeProps.places).toCriteria();
9
10const data = await officeController.getDocuments(session, pagination, sort);

Retrieve record by criteria

1// employeeRouter.{ts|js}
2import { employeeController } from './employeeController';
3import { EmployeeProps } from './schemas/employee';
4
5const officeId = '<_id>';
6
7const data = await employeeController.getDocumentByCriteria(session, { [EmployeeProps.officeId]: officeId });

Search records by criteria (Case-insensitive)

1// officeRouter.{ts|js}
2import { officeController } from './officeController';
3import { OfficeProps } from './schemas/office';
4
5const data = await officeController.searchDocumentsByCriteria(
6  session,
7  `${officeController.getSearchCriteria(OfficeProps.name, 'keyword1')}
8      OR ${officeController.getSearchCriteria(OfficeProps.name, 'keyword2')}
9      OR ${officeController.getSearchCriteria(OfficeProps.name, 'keyword3')}`,
10  {
11    keyword1: '%coworking%',
12    keyword2: '%flexible workspace%',
13    keyword3: '%serviced office space%',
14  },
15);

Search records (Case-insensitive)

1// officeRouter.{ts|js}
2import { officeController } from './officeController';
3
4const data = await officeController.searchDocuments(
5  session,
6  {
7    name: '%coworking%',
8    officeCode: '%coworking%',
9  },
10  'OR',
11);

Filter records by criteria

1// officeRouter.{ts|js}
2import { Condition, Filter, OffsetPagination, SortBy } from 'crud-node';
3
4import { officeController } from './officeController';
5import { OfficeProps } from './schemas/office';
6
7const filterOfficesInNYC = Filter.toCriteria(
8  Filter.and(Condition.like('address.city', '%New York%'), Condition.gre(OfficeProps.places, 1)),
9);
10const sortOfficesByAvailablePlaces = SortBy().asc(OfficeProps.places).toCriteria();
11const pagination = OffsetPagination(1, 10);
12
13const data = await officeController.filterDocumentsByCriteria(
14  session,
15  filterOfficesInNYC,
16  pagination,
17  sortOfficesByAvailablePlaces,
18);

Group records

1// employeeRouter.{ts|js}
2import { GroupBy } from 'crud-node';
3
4import { employeeController } from './employeeController';
5import { EmployeeProps } from './schemas/employee';
6
7const data = await employeeController.groupByDocuments<'fired' | EmployeeProps.createdAt>(
8  session,
9  GroupBy<EmployeeProps, 'fired' | EmployeeProps.createdAt>()
10    .fields(EmployeeProps.createdAt)
11    .aggregate(EmployeeProps._id, 'fired', AGG.COUNT)
12    .toCriteria(),
13);

Filter records

1// employeeRouter.{ts|js}
2import { OffsetPagination } from 'crud-node';
3import { employeeController } from './employeeController';
4
5const pagination = OffsetPagination(1, 10);
6
7const data = await employeeController.filterDocuments(session, { fired: true }, 'AND', pagination);

Filter records by ids

1// officeRouter.{ts|js}
2import { officeController } from './officeController';
3import { OfficeProps } from './schemas/office';
4
5const officeIds = ['<id1>', '<id2>'];
6const pagination = OffsetPagination(1, 10);
7const sort = SortBy().asc(OfficeProps.places).toCriteria();
8const data = await officeController.filterDocumentsByIds(session, officeIds, pagination, sort);

Retrieve all records

1// employeeRouter.{ts|js}
2import { employeeController } from './employeeController';
3
4const data = await employeeController.fetchAll(session);

Find record

1// employeeRouter.{ts|js}
2import { employeeController } from './employeeController';
3
4const employeeId = '<_id>';
5
6const data = await employeeController.findDocument(session, { employeeId });

Exists record

1// employeeRouter.{ts|js}
2import { employeeController } from './employeeController';
3
4const employeeId = '<_id>';
5
6const data = await employeeController.existsDocument(session, { employeeId });

Count records by filter

1// employeeRouter.{ts|js}
2import { EmployeeProps } from './schemas/employee';
3
4const officeId = '<_id>';
5
6const employeesByOffice = await this.employeeController.getCount(session, {
7  [EmployeeProps.officeId]: officeId,
8});

Retrieve total records

1// employeeRouter.{ts|js}
2import { employeeController } from './employeeController';
3
4const data = await employeeController.getTotal(session);

Call store procedure

1// employeeRouter.{ts|js}
2import { employeeController } from './employeeController';
3
4const 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