@saffellikhan/epic-sql

Epic Sql

A Powerful MySQL ORM. Epic SQL allows you to build applications by writing less code without a single line of SQL Query!

Usage

Epic SQL is very easy to use library. View the documentation below to start working with Epic SQL quickly!

Available DataTypes

Following are the available datatypes in the ORM.

1. SQLString
2. SQLNumber
3. SQLBoolean
4. SQLEnum
5. SQLContent
6. SQLObject
7. SQLMeta
8. SQLOneRelation
9. SQLManyRelation

Build a Schema

Following is the example of a Users Schema.

import { Schema, SQLBoolean, SQLEnum, SQLNumber, SQLString } from "epic-sql";

export const Users = new Schema("users", {
  userId: new SQLString({
    isSearchable: true, // Add Match Against Index
    isUnique: true,
  }),

  name: new SQLString({
    isSearchable: true, // Add Match Against Index
  }),

  isVerified: new SQLBoolean({
    defaultValue: false,
  }),

  status: new SQLEnum(["Active", "Blocked"], {
    isSearchable: true, // Add Match Against Index
    defaultValue: "Active",
  }),

  createdOn: new SQLNumber({
    defaultValue: Date.now(),
  }),

  modifiedOn: new SQLNumber({
    defaultValue: Date.now(),
    updatedValue: Date.now(), // Timestamp is updated if any record changes.
  }),
} as const);

Create a Connection

Follow the method below to create a Connection with MySql Server.

import { Connector, ConnectionManager } from "epic-sql";

// Import All Schemas
import { Users } from "./schema/users";
import { Profiles } from "./schema/profiles";
import { Orders } from "./schema/orders";

(async () => {
  // Create and Initialize a Connection Manager Instance.
  const Connection = await new ConnectionManager(
    // You need to pass a Connector Instance to Connection Manager.
    new Connector(
      {
        host: "localhost",
        user: "root",
        password: "",
        database: "mydatabase",
      },
      {
        /**
         * Schema will be synced to the MySql Server on each connection if sync is set to True.
         * Please don't forget to set it to False on the production.
         */
        sync: false,

        // Prints each exectued SQL Query in the Console.
        logs: true,
      }
    ),

    // Pass all Schemas to the Connection Manager.
    [Users, Profiles, Orders]
  ).init();

  // End the Connection
  Connection.getConnector().end();
})();

Insert Data

Follow the method below to insert data to Users Schema we created just now.

import { Users } from "./schema/users";

(async () => {
  // Create a Locked Users Schema by calling new() method on it.
  // It is required to Lock the Schema before doing any CRUD operation on it.
  const User = Users.new();

  // Insert Row to Users Schema
  // If there is a problem, the operation will throw an error.
  // You can insert one row passed as an object or multiple rows passed as an Array.
  const NewUser = await User.insert({
    userId: "john",
    name: "John Doe",
    isVerified: true,
    status: "Active",
  });

  // If the operation was successful, A new User object will be stored on NewUser variable.
  console.log(NewUser);

  /**
   * Will Print:
   *
   * {
   * 	_id: 1,
   * 	userId: "john",
   * 	name: "John Doe",
   * 	isVerified: true,
   * 	status: "Active",
   * 	createdOn: 356210359812,
   * 	modifiedOn: 356210359812,
   * }
   *
   */
})();

Fetch Data

Follow the method below to fetch data from Users Schema.

import { Users } from "./schema/users";

(async () => {
  // Example 1:
  // Select all Users from Users Schema.
  // Use select() method for selection.
  console.log(
    await Users.new()
      // You can pass an Array of column names that you want to select.
      .select(["userId", "name", "status"])
  );

  /**
   * Will Print:
   *
   * [
   *   {
   * 	 userId: "john",
   * 	 name: "John Doe",
   * 	 status: "Active",
   *   }
   * ]
   *
   */

  // Example 2:
  // Select One User where userId is "john"
  console.log(
    await Users.new()
      .where({
        userId: "john",
      })
      .selectOne()
  );

  /**
   * Will Print:
   *
   * {
   * 	_id: 1,
   * 	userId: "john",
   * 	name: "John Doe",
   * 	isVerified: true,
   * 	status: "Active",
   * 	createdOn: 356210359812,
   * 	modifiedOn: 356210359812,
   * }
   *
   */

  // Exmaple 3:
  // Advance Data Selection
  const SpecialUsers = await Users.new()
    .where({
      isVerified: true,
      status: "Active",
    })
    // Extra Conditions
    .having({
      _id: {
        // _id Ranges Between 1 to 100
        BT: [1, 100],
      },
    })
    // Set Selection Offset
    .offset(0)
    // Set Selection Limit
    .limit(10)
    // Set Selection Sorting ASC or DESC
    .sort("DESC")
    .select();

  // Example 4:
  // Search Data
  const SearchedUsers = await Users.new()
    // Search your data using Match Against feature
    // Make sure you have set isSearchable to True on the Schema for the columns you want to search.
    // In our case userId, name and status column is searchable.
    // You cannot use where() method with search() method.
    .search("my search string")
    .select();
})();

Update Data

Follow the method below to update data on Users Schema.

import { Users } from "./schema/users";

(async () => {
  // Example 1:
  // Update User Status
  // Use update() method to update User Data
  await Users.new().where({ userId: "john" }).update({
    status: "Blocked",
  });

  // Example 2:
  // Update User Status, use updateOrFail() method to throw error if nothing has been updated.
  await Users.new().where({ userId: "john" }).updateOrFail({
    status: "Blocked",
  });
})();

Delete Data

Follow the method below to delete data from Users Schema.

import { Users } from "./schema/users";

(async () => {
  // Example 1:
  // Delete a User
  // Use delete() method to delete User Data
  await Users.new().where({ userId: "john" }).delete();

  // Example 2:
  // Delete a User, use deleteOrFail() method to throw error if nothing has been deleted.
  await Users.new().where({ userId: "john" }).deleteOrFail({
    status: "Blocked",
  });
})();

I hope that the documentation is easy enough to understand the library. You will find it very easy to work with and will be able to explore more features while using this library.

We are still working on the documentation, there are allot of features missing in the documentation but available on the library. We will add them to the docs soon.

Good Luck!