DEV Community

Cover image for Creating a Node.js SQL Library
Blaze Rowland
Blaze Rowland

Posted on • Updated on

Creating a Node.js SQL Library

#node #javascript #typescript #sql

Project on Github

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>
Enter fullscreen mode Exit fullscreen mode

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' },
    ]);
}
Enter fullscreen mode Exit fullscreen mode

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',
        },
      },
    ]);
}
Enter fullscreen mode Exit fullscreen mode

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);
  }
}
Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

Add Values to a Table 

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

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));
Enter fullscreen mode Exit fullscreen mode

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

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

Update Values 

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

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)),
  });
Enter fullscreen mode Exit fullscreen mode

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));
    },
  });
Enter fullscreen mode Exit fullscreen mode

Delete from a Table 

productTable.delete({ id: 1 });
Enter fullscreen mode Exit fullscreen mode

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));
Enter fullscreen mode Exit fullscreen mode 
  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));
Enter fullscreen mode Exit fullscreen mode 
  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));
Enter fullscreen mode Exit fullscreen mode

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));
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (2)

Collapse
 
thereis profile image
Lucas Reis •

Don’t get me wrong but this looks like an ORM

Collapse
 
blazerowland profile image
Blaze Rowland •

Technically yeah, you’re not writing any SQL but I’d never suggest it to someone who’s looking for an ORM since they’d probably be needing migrations and a few other features, which aren’t included… Yet :)

Read next

lebbe profile image

GlobalErrorHandler: Catch the errors that falls through ErrorBoundary's fingers!

Lars-Erik Bruce -

notarena profile image

Create Toggle Button in HTML CSS & JavaScript

Notarena -

sunnyss profile image

Java Programming

sunny -

sonaykara profile image

Why Virtual DOM: Render and Performance

Sonay Kara -