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.
Top comments (2)
Don’t get me wrong but this looks like an ORM
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 :)