Creating a Node.js SQL Library
Tags: node, javascript, typescript, sql
Published: 2022-03-08 17:41:49

[Project on Github](https://github.com/blaze-rowland/forty-sql)

## 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](https://github.com/blaze-rowland/shopping-cart). 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.