BonORM

About The Project

BonORM is my own implementation of an object-relational mapping (ORM) designed for use on the NodeJS platform and in projects developed using Typescript. I created this tool to ensure that users can use only the tools they need for their work, making the process of interacting with the database much easier and more enjoyable. BonORM makes it easy to interact with the database, providing efficient management of objects and their relationships so that your development is more productive.

Built With

This section describes the technologies and frameworks ORM can be used with.

Getting Started

Installation

Here you can get in touch with installation and basic setup guide

Firstly, you should install a package from npm

• npm (as a simple dependency)

  npm i bonorm

• npm (as a dev dependency)

  npm install bonorm --save-dev

Environment file

To work with ORM you also should create .envfile where you can place all your secrets and type of database you want to work with.

Here is example for your .env file:

DB_TYPE=postgres // type of database(mysql or postgres)!!!
process.env.USER: 'user', // your username
process.env.HOST=localhost  // name of your host
process.env.DATABASE: 'database1', // name of your database
process.env.PASSWORD: 'root', // password for your database
process.env.PORT: 5432 // port which your database is running on

Configuration files

If you want to get start with concrete database, you can generate a desirable configuration

• Create configuartion file for MySQL

  npm run create:mySqlConfig <path> 

• Create configuartion file for PostgreSQL

  npm run create:pgConfig <path> 

• Your script block in package.json should look like this code snippet:

  "scripts": {
  "create:pgConfig": "ts-node node_modules/bonorm/src/cli.ts create:pgConfig",
  "create:mySqlConfig": "ts-node node_modules/bonorm/src/cli.ts create:mySqlConfig"
  }

• Also, to connect to the database you need to create a folder called configs in the working directory, and generate the config file there, here's how this process looks like:

  cd <your project name>
  mkdir configs
  npm run create:pgConfig ./configs

• Then you need to provide all the necessary information about the database in place of the brackets:

// pgConfig.ts
import { Pool } from 'pg';
export const pgConfig = new Pool({
      user: 'your data',
      host: 'your data',
      database: 'your data',
      password: 'your data',
      port: 5432,
});

// mySqlConfig.ts
import { Connection, createConnection } from 'mysql2/promise';
const mySqlConfig = () => {
    return createConnection({
        host: 'your data',
        user: 'your data',
        port: 'your port',
        password: 'your data',
        database: 'your data'
    });
};
export default mySqlConfig;

Features

Data types

In this section, you can learn more about the available data types that ORM works with. To access a built-in data type, you must import a right module for your database:

import { pgDataType } from "bonorm"; // for PostgreSQL types
import { mySqlDataType } from "bonorm"; // for MySQL types

• Numeric Types

for PostgreSQL:

pgDataType.Integer // signed four-byte integer
pgDataType.Float // single precision floating-point number (4 bytes)
pgDataType.SmallInt // signed two-byte integer
pgDataType.SmallSerial // autoincrementing two-byte integer
pgDataType.SmallSerial // autoincrementing four-byte integer
pgDataType.Double // double precision floating-point number (8 bytes)

for MySQL:

mySqlDataType.Integer // whole number, signed
mySqlDataType.SmallInt // small integer, signed
mySqlDataType.Decimal // fixed-point number
mySqlDataType.Numeric // fixed-point number
mySqlDataType.Float // floating-point number (single precision)
mySqlDataType.Real // floating-point number (single precision)
mySqlDataType.BigInt // large integer, signed

• Text Types

for PostgreSQL:

pgDataType.String // variable-length character string
pgDataType.Text // variable-length character string

for MySQL:

mySqlDataType.Char // fixed-length character string
mySqlDataType.Varchar // variable-length character string
mySqlDataType.Text // variable-length character string (large)

• Date/Time Types

for PostgreSQL:

pgDataType.Date // calendar date (year, month, day)
pgDataType.Timestamp // date and time (no time zone)
pgDataType.TimestampWithTimeZone // date and time, including time zone
pgDataType.Time // time of day (no date)
pgDataType.TimeWithTimeZone // time of day, including time zone
pgDataType.Interval // time interval

for MySQL:

mySqlDataType.Date // calendar date (year, month, day)
mySqlDataType.Time // time of day
mySqlDataType.DateTime // date and time
mySqlDataType.Timestamp // date and time

• Binary Types

for PostgreSQL:

pgDataType.Boolean // logical Boolean (true/false)

for MySQL:

mySqlDataType.Binary // fixed-length binary string
mySqlDataType.Varbinary // variable-length binary string
mySqlDataType.Blob // binary large object

Specific Data Types

• Network Address Types

for PostgreSQL:

pgDataType.Inet // IPv4 or IPv6 host address

• Text Search Types

for PostgreSQL:

pgDataType.TsQuery // text search query
pgDataType.TsVector // text search document

• Monetary Types

for PostgreSQL:

pgDataType.Money // currency amount

• UUID Type

for PostgreSQL:

pgDataType.UUID // universally unique identifier

for MySQL:

  mySqlDataType.UUID // universally unique identifier

• XML Type

for PostgreSQL:

pgDataType.XML // XML data

• JSON Types

for PostgreSQL:

pgDataType.Object // textual JSON data
pgDataType.ObjectB // binary JSON data, decomposed

• Spatial Data Types

for MySQL:

  mySqlDataType.Geometry // geometric object
  mySqlDataType.Point // geometric point
  mySqlDataType.LineString // geometric line
  mySqlDataType.Polygon // geometric polygon
  mySqlDataType.MultiPoint // collection of points
  mySqlDataType.MultiLineString // collection of lines
  mySqlDataType.MultiPolygon // collection of polygons
  mySqlDataType.GeometryCollection // collection of geometric objects

Entities

To define your own entity, you need to use @Entity("name for your table") decorator. After that, you need to use the Model class and pass the name of the table to it as arguments and to define there columns with needed options. Here is an example:

import {Column, pgDataType, Entity, Model} from "bonorm";
@Entity("table1")
export class table1 extends Model{
    @PrimaryGeneratedColumn()
    id: number
    @Column({type: pgDataType.String})
    name: string
}

Columns

• @Column()

Creates a default column where you can specify options.

import {Column, pgDataType, Entity, Model} from "bonorm";
@Entity("table1")
export class table1 extends Model{
    @Column({type: pgDataType.String})
    name: string
}

Attributes

• type

Each attribute must be assigned a type, so you can use data types that already exist in data-types

@Column({type: pgDataType.String})
name: string

Find more here: data types

• unique

To create an unique index you need to specify { unique: true } in the attribute options

@Column({type: pgDataType.String, unique: false})
name: string

• allowNull

Makes column NULL or NOT NULL in the database. By default column is { allowNull: true }.

@Column({type: pgDataType.String, allowNull: false})
name: string

• autoIncrement

Indicates whether the attribute should auto-increment

• defaultValue

The defaultValue field in the context of creating a table model in the database indicates the default value for a particular attribute of this table. This value will be used for new records if no specific value is specified for this attribute when inserting. Example: { defaultValue: 'Unknown' }, { defaultValue: 0 }.

@Column({type: pgDataType.String, nullable: true, defaultValue: "Zalupenko"})
    vorname: string

• @PrimaryColumn()

Defines a primary key column in an entity. It accepts an optional column type parameter, which defaults to INTEGER if not specified.

import {Column, pgDataType, Entity, Model} from "bonorm";
@Entity("table1")
export class table1 extends Model{
    @PrimaryColumn()
    id: number
}

Attributes

• "uuid" - handles UUID generation based on the database type specified in the environment variables.

@PrimaryColumn("uuid")
id: string

• @PrimaryGeneratedColumn()

Defines a primary column which value will be automatically generated with an auto-increment value.

import { PrimaryGeneratedColumn, pgDataType, Entity, Model } from "bonorm";
@Entity("table1")
export class table1 extends Model{
    @PrimaryGeneratedColumn()
    id: number
}

Attributes

• "uuid" - handles UUID generation based on the database type specified in the environment variables.

@PrimaryGeneratedColumn("uuid")
id: string

Usage example

In this code, a Player model is created with attributes id and name. The id attribute is of type integer, unique, not nullable, and auto-incremented. The name attribute is of type string and unique. Additionally, the model-wide option of timestamps is set to true, indicating the inclusion of creation and update timestamps.

import { Column, Entity, Model, pgDataType, PrimaryGeneratedColumn } from "bonorm";
import { IsEmail } from "class-validator";

@Entity("Player13")
export class Player13 extends Model {
    @PrimaryGeneratedColumn()
    id: number;

    @Column({ type: pgDataType.String, unique: true })
    name: string;

    @Column({ type: pgDataType.String, default: () => "Zalupenko" }) // Setting a default value
    surname: string;

    @Column({ type: pgDataType.Boolean, default: true }) // Example of a boolean column with a default value
    isActive: boolean;

    @Column({ type: pgDataType.Boolean, default: false })
    isAdmin: boolean;

    @Column({ type: pgDataType.String, nullable: true }) // Example of a nullable column
    notes: string;

    @Column({ type: pgDataType.String, nullable: false })
    @IsEmail()
    email: string;
}

// Usage example
const examplePlayer = new Player13("Player13");

examplePlayer.create({
    name: "Johny",
    surname: "Doe",
    isActive: true,
    isAdmin: false,
    notes: "This player prefers email communication.",
    email: "[email protected]"
}, Player13);

Validation

If you want to use validation in your entity you should use class-validator library. Here is a small example how you can use it:

import { Column, Entity, Model, pgDataType, PrimaryGeneratedColumn } from "bonorm";
import { IsEmail, Length, Min } from "class-validator";

@Entity("Player13")
export class Player13 extends Model {
    @PrimaryGeneratedColumn()
    id: number;

    @Column({ type: pgDataType.String, unique: true })
    @Length(10, 20)
    name: string;

    @Column({ type: pgDataType.String, default: () => "Zalupenko" })
    @Length(10, 20)
    surname: string;

    @Column({ type: pgDataType.String, nullable: true })
    @Min(0)
    notes: string;

    @Column({ type: pgDataType.String, nullable: false })
    @IsEmail()
    email: string;
    
    @Column({ type: pgDataType.String, nullable: false })
    @IsISBN()
    isbn: string;
}

Errors

The dbError class is a custom error class extending the built-in Error class. It is designed to handle various types of errors related to database operations. here is a typical example of using the dbError class to check for the presence of a type in an attribute

if(!type) {
  dbError.QueryError(`Type for attribute ${attribute} is undefined.`);
}

• ExistingDataError

Throws an error when attempting to insert data that already exists in the database.

static ExistingDataError(values: any[]);

• ConnectionError

Throws an error when unable to connect to the database.

static ConnectionError();

• QueryError

Throws an error when there is an issue with executing a database query.

static QueryError(message: any[] | string);

• EmptyQuery

Throws an error when attempting to perform a database query with no data for insertion.

static EmptyQuery();

• DbTypeError

Throws an error when user used invalid name of database.

static DbTypeError();

• InvalidFormat

Throws an error when user provided invalid data format

static InvalidFormat()

Migrations

You can also use the migration generator and additional tools to install and roll back migrations. You have two methods in migration file: updefines the changes that should be applied to the database schema when migrating up and down to revert the changes made by the up function. To generate migrations, you need to add a few lines of code to the "scripts" area of the package.json file:

"scripts": {
    "generate:migration": "ts-node node_modules/bonorm/src/cli.ts generate:migration <path to directory>",
    "up:migration": "ts-node node_modules/bonorm/src/cli.ts up:migration <path to file with generated migration>",
    "down:migration": "ts-node node_modules/bonorm/src/cli.ts down:migration <path to file with generated migration>"
  }

Here is an example of a generated migration file named MigrationV1700835172879.ts:

import {MigrationInterface, runQuery} from "bonorm"
export class ${fileName} implements MigrationInterface {
    migrationName = '${fileName}';
    public async up() {
        // Your migration logic
        await runQuery('CREATE TABLE IF NOT EXISTS example (id SERIAL PRIMARY KEY, name VARCHAR(255));');
    }
    public async down() {
        // Your rollback logic
        await runQuery('DROP TABLE IF EXISTS example;');
    }
}
export { ${fileName} as Migration };

Basic operations

• create

The create function designed to simplify the process of inserting new records into a specified database table. This function supports asynchronous execution and returns a Promise that resolves to a QueryResult object.

playerTable.create({name: 'Nazar'});

Arguments:

• data(optional): A JavaScript object representing the data to be inserted into the database table. It should be in the form of key-value pairs, where keys correspond to column names and values represent the data to be inserted.

Usage:

const dbModel = new Model('Employers');
const newData = {
  name: 'John Doe',
  age: 30,
  email: '[email protected]'
};
const result = await dbModel.create(newData);

• find

The find function was designed to facilitate the retrieval of records from a specified database table based on specified criteria. This function supports asynchronous execution and returns a Promise that resolves to a QueryResult object.

Example:

const dbModel = new Model('Employers');
const findResult = await dbModel.find({
  select: ['id', 'name'],
  where: { name: 'Example Team' },
  order: { id: 'ASC' }
});

Arguments:

options (required):

• select: An array of column names to be selected.
• relations: An array of table names for LEFT JOIN operations.
• where: A JavaScript object representing the conditions for the WHERE clause.
• order: A JavaScript object representing the order criteria.
• skip: The number of records to skip (for pagination).
• take: The maximum number of records to retrieve.

Example:

const dbModel = new Model('Employers');
const findResult = await dbModel.find({
    select: ['id', 'name'],
    relations: ['relatedTable'],
    where: { name: 'Example Team' },
    order: { id: 'ASC' },
    take: 1
});

• findOne

The findOne function was designed to retrieve a single record from a specified database table based on specified criteria. This function supports asynchronous execution and returns a Promise that resolves to a QueryResult object.

Arguments:

options (required):

• where: A JavaScript object representing the conditions for the WHERE clause.

Example:

const data = await playerTable.find({
    where: { name: "Nazar" }
});

• save

The save function was designed for updating existing records in a specified database table. This function supports asynchronous execution and returns a Promise that resolves to a QueryResult object.

Example:

const dbModel = new Model('Employers');
const saveResult = await dbModel.save();

• delete

The delete function was designed to simplify the process of removing records from a specified database table. This function supports asynchronous execution and returns a Promise that resolves to a QueryResult object.

Arguments:

options (required):

• where: A JavaScript object representing the conditions for the WHERE clause.

Example:

const dbModel = new Model('Employers');
const deleteResult = await dbModel.delete({
  where: { id: 123 }
});

Relations

ORM also makes it possible to create relations between databases. Relations help you to work with related entities easily. There are several types of relationships:

• One-To-One

In a one-to-one relationship, each record in one table is associated with exactly one record in another table, and vice versa. This is achieved by having a foreign key in one table that references the primary key of the other table.

Arguments

createOneToManyRelation("tableName", "key", "referenceTable", "referenceKey");

Where:

tableName: The name of the primary table where the one-to-one relationship is being created.

Example: "player"

key: The foreign key column to be added to the primary table tableName that references the related table's primary key.

Example: "teamId"

referenceTable: The name of the related table that forms the other side of the one-to-one relationship.

Example: "team"

referenceTable: The primary key column in the related table (referenceTable) that is being referenced by the foreign key in the primary table.

Example: "id"

import { createOneToOneRelation } from "bonorm"

const playerTable = new Model('Player');
// ...
const teamTable = new Model('Team');
// ...
createOneToOneRelation("player", "teamId", "team", "id");

In this example, a one-to-one relationship is established between the "Player" and "Team" tables. The "player" table gets a foreign key column named "teamId" referencing the primary key "id" in the "team" table. This relationship implies that each player can be associated with one team, and each team can be associated with one player.

• One-To-Many

In a one-to-many relationship, each record in the primary table can be associated with multiple records in the related table, but each record in the related table is associated with only one record in the primary table. This is typically implemented by having a foreign key in the related table that refers to the primary key in the primary table.

Arguments

createOneToManyRelation("tableName", "key", "referenceTable", "referenceKey");

Where:

tableName: The name of the primary table where the one-to-many relationship is being created.

Example: "player"

key: The foreign key column to be added to the primary table tableName that references the related table's primary key.

Example: "teamId"

referenceTable: The name of the related table that forms the other side of the one-to-many relationship.

Example: "team"

referenceTable: The primary key column in the related table (referenceTable) that is being referenced by the foreign key in the primary table.

Example: "id"

import { createOneToManyRelation } from "bonorm"

const playerTable = new Model('Player');
// ...
const teamTable = new Model('Team');
// ...
createOneToManyRelation("player", "teamId", "team", "id");

In the following example, a one-to-many relationship is established between the "Player" and "Team" tables. The primary table, "player," gains a foreign key column named "teamId" which references the primary key "id" in the related table "team".

• Many-To-Many

In a many-to-many relationship, each record in the primary table can be associated with multiple records in the related table, and vice versa. This relationship is typically implemented using an intermediate table that contains foreign keys referencing both primary tables.

Arguments

createManyToManyRelation("tableName", "intermediateTableName", "referenceTable");

Where:

tableName: The name of the first primary table participating in the many-to-many relationship.

Example: "player"

intermediateTableName: The name of the intermediate table created to represent the many-to-many relationship.

Example: "playerTeam"

referenceTable: The name of the second primary table participating in the many-to-many relationship.

Example: "team"

import { createManyToManyRelation } from "bonorm"

const playerTable = new Model('Player');
// ...
const teamTable = new Model('Team');
// ...
createManyToManyRelation("player", "playerTeam", "team");

In this example, a many-to-many relationship is established between the "Player" and "Team" tables using the intermediate table "PlayerTeam" This allows each player to be associated with multiple teams, and each team to be associated with multiple players. The createManyToManyRelation function is used to create the "PlayerTeam" table, which acts as a bridge between the "Player" and "Team" tables, facilitating the many-to-many relationship.

Contributing

Contributions are what make the open source community such an amazing place to learn, inspire, and create. Any contributions you make are greatly appreciated.

If you have a suggestion that would make this better, please fork the repo and create a pull request. You can also simply open an issue with the tag "enhancement". Don't forget to give the project a star! Thanks again!

1. Fork the Project
2. Create your Feature Branch (git checkout -b feature/Features)
3. Commit your Changes (git commit -m 'Add some Features')
4. Push to the Branch (git push origin feature/Features)
5. Open a Pull Request

License

Distributed under the MIT License. See LICENSE.txt for more information.

Contact

Heorhii Huziuk - [email protected]

Why BonOrm?(it's like a word game with Bono and ORM)

Project link: https://github.com/hhuziuk/bonORM.git