Custom Node.js ORM

A custom Object-Relational Mapper (ORM) for Node.js, inspired by Laravel's Eloquent ORM. This ORM provides a fluent API for building and executing SQL queries, handling database interactions with ease.

Features

• Fluent API for building SQL queries
• Support for SELECT, INSERT, UPDATE, and DELETE operations
• Query methods including where, orWhere, orderBy, groupBy, having, join, innerJoin, leftJoin, rightJoin
• Pagination with limit and offset
• Support for relations and eager loading
• Method chaining
• Model class with Eloquent-like methods

Installation

npm install your-orm-package

Usage

importing the ORM

const Model = require("jcc-eloquent");

Model

The Model class extends BaseModel and provides additional methods for interacting with the database in an Eloquent-like manner.

Model.all() Retrieves all records from the database table associated with the current model.

const users = await User.all();
console.log(users);

Model.find(id) Retrieves a single record from the database table associated with the current model by its ID.

const user = await User.find(1);
console.log(user);

Model.create(data) Creates one or more records in the database table associated with the current model.

const user = await User.create({
  name: "John Doe",
  email: "[email protected]",
  age: 30,
});
console.log(user);

const users = await User.create([
  { name: "Jane Doe", email: "[email protected]", age: 28 },
  { name: "John Smith", email: "[email protected]", age: 35 },
]);
console.log(users);

save() Saves the current instance to the database. If the instance has an id, it performs an update; otherwise, it performs an insert.

 const new user = new User()
 user.name = 'abdou'
 await user.save()

static async all() Retrieves all records from the database table associated with the current model.

const users = await User.all();
console.log(users);

Relationship Definitions

hasOne(modelName, foreignKey = null, localKey = "id") Defines a one-to-one relationship between the current model and another model.

hasMany(model, foreignKey = null, localKey = "id") Defines a one-to-many relationship between the current model and another model.

belongsTo(modelName, foreignKey = null, localKey = "id") Defines a belongs-to relationship between the current model and another model.

Example Usage

const Model = require("your-orm-package/BaseModel");

class User extends Model {
  posts() {
    return this.hasMany("posts", "author", "id");
  }
}

class Post extends BaseModel {
  user() {
    return this.belongsTo("users", "posts", "id");
  }
}

Using Relationshps

const user = await User.with("posts").get();
const post  = await Post.with({author(query)=>query.where('status' , 'active')}).get()

Methods

select(...columns) Specifies the columns to select in a query.

distinct() Marks the query to return distinct results.

from(tableName) Specifies the table to query from.

where(column, operator, value) Adds a WHERE clause to the query.

orWhere(column, operator, value) Adds an OR WHERE clause to the query.

orderBy(column, direction) Specifies the column and direction for ordering the query results.

limit(count) Limits the number of results returned by the query.

offset(count) Specifies the offset for starting the query results.

groupBy(...columns) Specifies the columns to group the query results by.

having(column, operator, value) Adds a HAVING clause to the query.

join(table, firstColumn, operator, secondColumn) Adds a JOIN clause to the query.

innerJoin(table, firstColumn, operator, secondColumn) Adds an INNER JOIN clause to the query.

leftJoin(table, firstColumn, operator, secondColumn) Adds a LEFT JOIN clause to the query.

rightJoin(table, firstColumn, operator, secondColumn) Adds a RIGHT JOIN clause to the query.

insert(data) Inserts data into the database and retrieves the inserted record.

first() Fetches the first record from the database table.

update(data) Updates data in the database.

delete() Performs a delete operation.

getFinalQuery() Constructs and returns the SQL query based on the current query builder state.

get() Executes the constructed query and returns the results.

count() Executes a count query based on the current query builder state.

save() Saves the current instance to the database. If the instance has an id, it performs an update; otherwise, it performs an insert.

DB

It provides a fluent interface for building and executing SQL queries.

select

DB.select("users.name", "profiles.age")
  .from("users")
  .innerJoin("profiles", "users.id", "=", "profiles.user_id")
  .leftJoin("orders", "users.id", "=", "orders.user_id")
  .where("users.age", ">", 25)
  .orWhere("users.name", "LIKE", "%John%")
  .groupBy("users.id")
  .having("COUNT(orders.id)", ">", 5)
  .orderBy("users.name", "desc")
  .limit(10)
  .offset(5)
  .execute()
  .then((result) => console.log(result))
  .catch((error) => console.error(error));

insert Query

DB.from("users")
  .insert({
    name: "John Doe",
    email: "[email protected]",
    age: 30,
  })
  .then((result) => console.log(result))
  .catch((error) => console.error(error));

update Query

DB.from("users")
  .insert({
    name: "John Doe",
    email: "[email protected]",
    age: 30,
  })
  .then((result) => console.log(result))
  .catch((error) => console.error(error));

Delete Query

await DB.from("users")
  .where("id", 1)
  .delete()
  .then((result) => console.log(result))
  .catch((error) => console.error(error));

Distinct Select

await DB.table("users").distinct().select("name").get();

Where Clauses

await DB.table("users").where("id", 1).get();
await DB.table("users").where("age", ">", 18).get();

Or Where Clauses

await DB.table("users").where("age", "<", 18).orWhere("age", ">", 60).get();

Ordering Results

await DB.table("users").orderBy("name", "desc").get();

Limiting and Offsetting Results

await DB.table("users").limit(10).offset(5).get();

Grouping and Having

await DB.table("orders").groupBy("user_id").having("total", ">", 100).get();

Joins

await DB.table("users")
  .join("orders", "users.id", "=", "orders.user_id")
  .select("users.name", "orders.total")
  .get();

Inserting Data

await DB.table("users").insert({ name: "John Doe", email: "[email protected]" });

Updating Data

await DB.table("users").where("id", 1).update({ name: "Jane Doe" });

Deleting Data

await DB.table("users").where("id", 1).delete();

Raw Queries

await DB.raw("SELECT * FROM users WHERE age > 18");

Exists

const userExists = await DB.table("users")
  .where("email", "[email protected]")
  .exists();

Sum

const total = await DB.table("orders").sum("price");

Iterating Over Results

await DB.table("users").each((user) => {
  console.log(user.name);
});

Methods

• table(name): Sets the table name for the query.
• select(...columns): Specifies the columns to be selected.
• from(tableName): Sets the table name (used internally).
• where(column, operator, value): Adds a where clause to the query.
• orWhere(column, operator, value): Adds an or where clause to the query.
• distinct(): Selects distinct rows.
• orderBy(column, direction): Orders the results by a column.
• limit(count): Limits the number of rows returned.
• offset(count): Sets the offset for the rows returned.
• groupBy(...columns): Groups the results by columns.
• having(column, operator, value): Adds a having clause to the query.