CRUD Operations

SQL CRUD Operation

ExpressWebJs makes it really easy to perform CRUD (Create, Read, Update and Delete) operations in our application.

This section of the documentation shows the basic operations you can perform on your SQL database. Kindly check out the Query Builder section for all the available methods.

Create

In ExpressWebJs, you can create new records to the database by first assigning values to the model instance and then calling the insert method.

Insert queries are created by chaining the insert method to the query method.

import User from "App/Model/User_model";

// Insert to the database
const userDetails = await User.query().insert({
  first_name: "Alex",
  last_name: "Igbokwe",
});

console.log(userDetails instanceof User); // --> true
console.log(userDetails.first_name); // --> 'Alex'
console.log(userDetails.last_name); // --> 'Igbokwe'

That’s great, but you know things in real life can be messy sometimes. Lets assume our last name does not allow null and the user last_name gets the value of null? Let’s see what happens.

import User from "App/Model/User_model";

//userdetails
const userDetails = {
  first_name: "Alex",
  last_name: null, // user last name is null 😭
};
// Insert to the database
await User.query().insert(userDetails);

Running this script will throw an error because the last_name column does not allow nulls.

You are mad now, but you remember the introduction to SQL course in high school and realize that all was your fault! If you want to avoid this kind of situations you need to use an SQL TRANSACTION!

Lets handle this with transactions

import User from "App/Model/User_model";

let userDetails = { first_name: "Alex", last_name: null };

try {
  const tx = await User.transaction((trx) => {
    const userRecord = await User.query(trx).insert(dataDetails);
    return userRecord;
  });
} catch (err) {
  // Here transaction has already been rolled back
  console.log(err);
}

Read

You can read records from your sql database by calling the any of the following methods.

findById()

Fetch an item by id:

import User from "App/Model/User_model";

const user = await User.query().findById(1);

console.log(user.first_name);
console.log(user instanceof User); // --> true

This translates to

 select "users".* from "users" where "users"."id" = 1

findByIds()

Finds a list of items. The order of the returned items is not guaranteed to be the same as the order of the inputs.

import User from "App/Model/User_model";

let ids = [1, 2, 3];

const user = await User.query().findByIds(ids);

findOne()

Shorthand for where(...whereArgs).first();

import User from "App/Model/User_model";

const user = await User.query().findOne(...whereArgs);

whereArgs is of type any which accepts anything the where method accepts.

Fetch all users from the database:

import User from "App/Model/User_model";

const user = await User.query();

console.log(user[0] instanceof User); // --> true
console.log("there are", user.length, "users in total");

This translates to

 select "users".* from "users"

select()

import User from "App/Model/User_model";

const user = await User.query().select("first_name", "last_name");

where()

import User from "App/Model/User_model";

const user = await User.query()
  .select("first_name", "last_name")
  .where("first_name", "=", "Alex");

select() with where()

import User from "App/Model/User_model";

const user = await User.query()
  .select("first_name", "last_name")
  .where("first_name", "=", "Alex");

whereNot()

import User from "App/Model/User_model";

const user = await User.query()
  .select("first_name", "last_name")
  .whereNot("id", 2);

whereIn()

import User from "App/Model/User_model";

const user = await User.query()
  .select("first_name", "last_name")
  .whereIn("id", [1, 2, 3]);

whereNotIn()

import User from "App/Model/User_model";

const user = await User.query()
  .select("first_name", "last_name")
  .whereNotIn("id", [1, 2, 3]);

whereNull()

import User from "App/Model/User_model";

const user = await User.query()
  .select("first_name", "last_name")
  .whereNull("updated_at");

whereNotNull()

import User from "App/Model/User_model";

const user = await User.query()
  .select("first_name", "last_name")
  .whereNotNull("created_at");

whereExists()

whereExists takes a builder or callback

import User from "App/Model/User_model";

const user = await User.query()
  .select("first_name", "last_name")
  .whereExists(() => {
    this.select("*")
      .from("accounts")
      .whereRaw("users.account_id = accounts.id");
  });

whereNotExists()

whereNotExists takes a builder or callback

import User from "App/Model/User_model";

const user = await User.query()
  .select("first_name", "last_name")
  .whereNotExists(() => {
    this.select("*")
      .from("accounts")
      .whereRaw("users.account_id = accounts.id");
  });

whereBetween()

whereBetween takes the column and range

import User from "App/Model/User_model";

const user = await User.query()
  .select("first_name", "last_name")
  .whereBetween("votes", [1, 100]);

whereNotBetween()

whereNotBetween takes the column and range

import User from "App/Model/User_model";

const user = await User.query()
  .select("first_name", "last_name")
  .whereNotBetween("votes", [1, 100]);

whereRaw()

whereRaw takes query and bindings

import User from "App/Model/User_model";

const user = await User.query()
  .select("first_name", "last_name")
  .whereRaw("id = ?", [1]);

Update

You can update records in your sql database by chaining the update or patch method.

patch and update method returns the number of updated rows. If you want the freshly updated item as a result you can use the helper method patchAndFetchById or updateAndFetchById. On postgresql you can simply chain .returning('')* or take a look at this recipe for more ideas. See update and patch API documentation for discussion about their differences.

Update an item by id:

import User from "App/Model/User_model";

const user = await User.query()
  .findById(1)
  .patch({ first_name: "Alex" });

This translates to

  update "users" set "first_name" = 'Alex' where "id" = 1

Update multiple items:

import User from "App/Model/User_model";

const userUpdated = await User.query()
  .patch({ status: "Hero" })
  .where("age", ">", 70);

console.log("all users over 70 years old are now heros");
console.log(userUpdated, "people were updated");

This translates to

 update "users" set "status" = 'Hero' where "age" > 60

Update and fetch an item:

import User from "App/Model/User_model";
const updatedUser = await User.query().patchAndFetchById(246, { last_Name: "Prince" });

console.log(updatedUser.last_name); // --> Updated.

This translates to

 update "users" set "last_name" = 'Prince' where "id" = 246
 select "users".* from "users" where "id" = 246

Delete

You can also delete records from your sql database by chaining the delete method to your query.

Delete an item by id:

import User from "App/Model/User_model";

const userDeleted = await User.query().deleteById(1);

This translates to

delete from "users" where id = 1

Delete multiple items:

import User from "App/Model/User_model";

const userDeleted = await User.query()
  .delete()
  .where(raw('lower("first_name")'), "like", "%ince%");

console.log(numDeleted, "user deleted");

This translates to

delete from "users" where lower("first_name") like '%ince%'

NOTE: The return value of the query will be the number of deleted rows. If you're using Postgres take a look at this recipe if you'd like the deleted rows to be returned as Model instances

Delete all users with last name Prince and return the deleted rows as Model instances in 1 query:

import User from "App/Model/User_model";

const deletedUsers = await User.query()
  .delete()
  .where({ last_name: "Prince" })
  .returning("*");

console.log(deletedUsers.length); // How many users there were

Persistent Repository

Implementing a data access layer of an application has been cumbersome for quite a while. Too much boilerplate code has to be written to execute simple queries as well as perform pagination, and sorting.

ExpressWebJS SQLPD_Repository and NOSQLPD_Repository aims to significantly improve the implementation of data access layers by reducing the effort to the amount that’s actually needed.

As a developer you write your repository classes, extending SQLPD_Repository for SQL Database or NOSQLPD_Repository for NOSQL Database, and ExpressWebJs will provide the powerfull methods needed to interact with your persistent data automatically, there by giving you one query languuge for quering both SQL and NOSQL database.

SQLPD_Repository

Let's implement SQLPD Repository to our UserRepository located at App/Repository/UserRepository.ts class and access our data via the data access layer

import User from "App/Model/User_model";
import SQLPD_Repository from "Elucidate/Repository/SQLPD_repository";

class UserRepository extends SQLPD_Repository {
  constructor() {
    super(User);
  }
}

export default UserRepository;

With this in place, we now have access to all the powerfull methods of ExpressWebJs Repository in our UserService in App/Service/UserService.ts file.

NOSQLPD_Repository

This section of the documentation shows the basic operations you can perform on your NOSQL database using NOSQLPD_Repository

import User from "App/Model/User_model";
import NOSQLPD_Repository from "Elucidate/Repository/NOSQLPD_repository";

class UserRepository extends NOSQLPD_Repository {
  constructor() {
    super(User);
  }
}

export default UserRepository;

With this in place, we now have access to all the powerfull methods of ExpressWebJs Repository in our UserService in App/Service/UserService.ts file.

Example: Get user by id

import userRepository from "App/Repository/UserRepository";

class UserService {
  public async getUser(id: number): Promise<object> {
    return await new userRepository()
      .findById(id)
      .then((user) => {
        return user;
      })
      .catch((error) => {
        return error;
      });
  }
}

export default UserService;