A MySQL Library for Node.js

Blaze Rowland

Last update: Mar 14, 2022

Related tags

Database forty-sql

Overview

@Forty-boy/SQL

A MySQL Library for Node.js

Currently creating this as a hobby project, but we'll see where it goes.

Installing the Project

npm install @forty-boy/sql OR yarn add @forty-boy/sql
const Forty = require('@forty-boy/sql')
OR
import Forty from '@forty-boy/sql'
OR
import { Table } from '@forty-boy/sql'
Create .env file at root with values for corresponding keys in .env-example found here

Cloning the Project

Clone the repository here
Run npm install OR yarn install
Create .env file at root with values for corresponding keys in .env-example
Run npm run dev OR yarn run dev

Changelog

Version 1.0.5

Version 1.0.4

Fixed issue with Join method

Version 1.0.3

Version 1.0.2

Can now run find() on a Table class with just the columns you want to receive with no condition arguments
- E.g. userTable.find({ columns: ['id', 'createdAt'] });

Version 1.0.1

Examples

Creating a Table Schema

For the rest of these examples we'll be using this user table

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
   
    ;

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

Create Table

async createProducts(): Promise
   
     {
    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',
        },
      },
    ]);
}

Add Values to Table

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

Find and Find one Value

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

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

Update Values

userTable
  .update({
    values: { fullName: 'Rylee Brown' },
    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: 'Forrest Rowland' },
          condition: { id: user.id },
        })
        .subscribe((res) => console.log(res)),
  });

Find and Add to Relational Table

userTable
  .findOne({
    columns: ['id'],
    condition: {
      fullName: 'Forrest Rowland',
    },
  })
  .subscribe({
    next: (user) => {
      productTable
        .add({
          name: 'Pacifier',
          price: 5,
          createdAt: new Date(),
          createdBy: user.id,
        })
        .subscribe((res) => console.log(res));
    },
  });

Delete from 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));

Alter Tables

Create an instance of the SQL Service

const sqlService = new SqlService('users')

Add Columns:

await sqlService.alterTableQuery({
  columnsToAdd: [
    {
      name: 'location',
      type: 'VARCHAR',
      size: 255,
    },
  ],
});

Alter Columns:

await sqlService.alterTableQuery({
  columnsToAlter: [
    {
      name: 'firstName',
      newName: 'fullName',
      type: 'VARCHAR',
      size: 255,
    },
  ],
});

Remove Columns:

await sqlService.alterTableQuery({
  columnsToRemove: [
    {
      name: 'lastName',
    },
  ],
});

columnsToAdd, columnsToAlter, and columnsToRemove can all be added to the alterAbleQuery like so:

await sqlService.alterTableQuery({
  columnsToAdd: [
    {
      name: 'location',
      type: 'VARCHAR',
      size: 255,
    },
  ],
  columnsToAlter: [
    {
      name: 'firstName',
      newName: 'fullName',
      type: 'VARCHAR',
      size: 255,
    },
  ],
  columnsToRemove: [
    {
      name: 'lastName',
    },
  ],
});

Drop Tables

Create an instance of the SQL Service

const sqlService = new SqlService('users')

sqlService.dropTable();

NodeJS,express and mysql starter project

Node-express-mysql-starterproject NodeJS,express and mysql starter project Start with cloning the repo & Run npm i to download all the dependecies Aft

Dec 11, 2022

A simple UI client for most SQL Engines written in Electron. It is compatible with Windows, Mac, Ubuntu / Debian and Redhat. It supports most dialects of RMBDs like MySQL, Microsoft SQL Server, Postgres, SQLite and has limited supports for Cassandra, MongoDB and Redis.

sqlui-native sqlui-native is a simple UI client for most SQL Engines written in Electron. It is compatible with most desktop OS's and support most dia

Jan 1, 2023

Couchbase Node.js Client Library (Official)

Couchbase Node.js Client The Node.js SDK library allows you to connect to a Couchbase cluster from Node.js. It is a native Node.js module and uses the

Nov 13, 2022

Nano: The official Apache CouchDB library for Node.js

Nano Offical Apache CouchDB library for Node.js. Features: Minimalistic - There is only a minimum of abstraction between you and CouchDB. Pipes - Prox

Dec 24, 2022

A database library stores JSON file for Node.js.

concisedb English | 简体中文 A database library stores JSON file for Node.js. Here is what updated every version if you want to know. API Document Usage B

Sep 4, 2022

A node.js locks library with support of Redis and MongoDB

locco A small and simple library to deal with race conditions in distributed systems by applying locks on resources. Currently, supports locking via R

Dec 13, 2022

A Node.js library for retrieving data from a PostgreSQL database with an interesting query language included.

RefQL A Node.js library for retrieving data from a PostgreSQL database with an interesting query language included. Introduction RefQL is about retrie

Nov 2, 2022

An easy-to-use multi SQL dialect ORM tool for Node.js

Sequelize Sequelize is a promise-based Node.js ORM tool for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction s

Jan 4, 2023