Creating a Node.js SQL Library

Blaze Rowland — Tue, 08 Mar 2022 17:41:49 +0000

Backstory

This week I decided to take a step forward in my development path and create my first NPM Package.

I'm an Angular developer by trade. It's my preferred front-end framework. But I have found myself wanting to branch out the past couple of years to work on React projects. I've worked on a pretty large amount of side projects in Vue, so wanted to take a swing at the newest version of React. So, I found some UI inspiration online and wanted to get to it.

I knew I wanted a Node.JS backend using MySQL as the database, but I've never really enjoyed using any of the ORMs like Knex. I liked the syntax of something like Mongoose significantly more but have a small preference leaning towards relational databases. That's when I decided... Why not make my own library that has a similar syntax to Mongoose, but works with a relational database? Now I'm sure there are projects/packages out there that do what I'm wanting, but the challenge intrigued me.

Getting Started

Install the package from NPM

npm install @forty-boy/sql OR yarn add @forty-boy/sql

Configuring your .env File

The SQL Service that powers this package will look for these values to create a MySQL Pool for all of its queries.

FORTY_HOST=<YOUR_DB_HOST>
FORTY_DB=<YOUR_DB_NAME>
FORTY_USER=<YOUR_DB_USER>
FORTY_PASS=<YOUR_DB_PASS>

Create a Table in the Database

async createUsers(): Promise<void> {
    const sql = new SqlService('users');
    await sql.createTableQuery([
      {
        name: 'id',
        type: 'INT',
        size: 11,
        primaryKey: true,
        autoIncrement: true,
        nullable: false,
      },
      { name: 'fullName', type: 'VARCHAR', size: 255 },
      { name: 'createdAt', type: 'DATETIME' },
    ]);
}

Create a Relational Table

async createProducts(): Promise<void> {
    const sql = new SqlService('products');
    await sql.createTableQuery([
      {
        name: 'id',
        type: 'INT',
        size: 11,
        primaryKey: true,
        autoIncrement: true,
        nullable: false,
      },
      { name: 'name', type: 'VARCHAR', size: 255, default: 'Test Product' },
      { name: 'price', type: 'INT', size: 11 },
      { name: 'createdAt', type: 'DATETIME' },
      {
        name: 'createdBy',
        type: 'INT',
        nullable: false,
        foreignKey: {
          referenceId: 'id',
          referenceTable: 'users',
        },
      },
    ]);
}

Create the Table Class

*Note: All methods from the Table class return an RXJS Observable

class UserSchema {
  id?: number; // This is nullable for Create calls
  fullName: string;
  dateOfBirth: Date;

  constructor(id: number, fullName: string, dateOfBirth: Date) {
    this.id = id;
    this.fullName = fullName;
    this.dateOfBirth = dateOfBirth;
  }
}

type UserDateset = Array<UserSchema>;

export class UserTable extends Table<UserSchema> {
  constructor(tableName: string, users: UserDataset = []) {
    super(tableName, UserSchema, users);
  }
}

All Tables take in the name of the table as an argument. This allows for the use of a generic Schema for tables with the same properties, but different names if that use case ever occurs.

If this is not a use case you foresee, you can set a default value in the constructor, or negate the need to supply one at all and just pass it into the super call.

Tables can also include a set of default values; this can be used for testing or any use case you could find for this. The second argument is a list of default values to start the Table off with.

Create an instance of the newly created UserTable

const userTable = new UserTable('users')

Add Values to a Table

userTable.add({
  fullName: 'Blaze Rowland',
  dateOfBirth: new Date(1997, 11, 14),
});

Find Values from a Table

Find all values that match the condition. Returns Observable<Array<YOUR_SCHEMA>>

userTable
  .find({
    columns: ['id', 'fullName'],
    condition: { id: 1 },
  })
  .subscribe((users) => console.log(users));

Find one value that matches the condition. Returns Observable<YOUR_SCEHMA>

userTable
  .findOne({
    columns: ['id'],
    condition: {
      fullName: 'Blaze Rowland',
    },
  })
  .subscribe((user) => console.log(user));

Update Values

userTable
  .update({
    values: { fullName: 'Some New Name' },
    condition: { id: 1 },
  })
  .subscribe((res) => console.log(res));

Find and Update Values

userTable
  .findOne({
    columns: ['id'],
    condition: {
      id: 1,
    },
  })
  .subscribe({
    next: (user) =>
      userTable
        .update({
          values: { fullName: 'A Much Newer Name' },
          condition: { id: user.id },
        })
        .subscribe((res) => console.log(res)),
  });

Find and Add to a Relational Table

Find and Add to Relational Table
userTable
  .findOne({
    columns: ['id'],
    condition: {
      fullName: 'A Much Newer Name',
    },
  })
  .subscribe({
    next: (user) => {
      productTable
        .add({
          name: 'Television',
          price: 600,
          createdAt: new Date(),
          createdBy: user.id,
        })
        .subscribe((res) => console.log(res));
    },
  });

Delete from a Table

productTable.delete({ id: 1 });

Join Tables

  productTable
    .join({
      joinType: 'INNER JOIN',
      columnsToSelect: [
        { column: 'name' },
        { column: 'price' },
        { column: 'fullName', as: 'userName', table: userTable.tableName },
        { column: 'dateOfBirth', table: userTable.tableName },
      ],
      columnsOn: [
        {
          from: { column: 'id', table: userTable.tableName },
          to: { column: 'createdBy', table: productTable.tableName },
        },
      ],
    })
    .subscribe((res) => console.log(res));

  productTable
    .join({
      joinType: 'LEFT JOIN',
      columnsToSelect: [
        { column: 'name' },
        { column: 'price' },
        { column: 'fullName', as: 'userName', table: userTable.tableName },
        { column: 'dateOfBirth', table: userTable.tableName },
      ],
      columnsOn: [
        {
          from: { column: 'id', table: userTable.tableName },
          to: { column: 'createdBy', table: productTable.tableName },
        },
      ],
    })
    .subscribe((res) => console.log(res));

  productTable
    .join({
      joinType: 'RIGHT JOIN',
      columnsToSelect: [
        { column: 'name' },
        { column: 'price' },
        { column: 'fullName', as: 'userName', table: userTable.tableName },
        { column: 'dateOfBirth', table: userTable.tableName },
      ],
      columnsOn: [
        {
          from: { column: 'id', table: userTable.tableName },
          to: { column: 'createdBy', table: productTable.tableName },
        },
      ],
    })
    .subscribe((res) => console.log(res));

Union Tables

userTable
  .union({
    queries: [
      {
        columns: ['id', 'fullName'],
        tableName: 'users',
      },
      {
        columns: ['id', 'name'],
        tableName: 'products',
      },
    ],
    all: true, // Changes whether Union statement is UNION (false || not provided) or UNION ALL (true)
  })
  .subscribe((res) => console.log(res));

Things I'd like to implement in the future

A CLI Package for migrations
A Migration Manager
Pagination at its root.
- Pagination is important for any scalable project. I think having it built in from the ground up would be great. This project is still in infancy (released just two days ago).

This is more of a hobby project to get myself making blog posts, using my Github account, and branching out as a developer. Most my projects are private and that's not something I'm very happy about. If you want to contribute to this project I'd love to see Pull Requests or Open Issues that I can review or work on. Thanks for taking the time out of your day and I hope this package can simplify some work for you on your next project!

To test this package I've been working on this project. As I find bugs or features that should be implemented while using this as my only means to query the database I'll create issues that will make it into future versions.

DEV Community: Blaze Rowland