tspace-mysql

v1.8.0

Published

4 days ago

Tspace MySQL is a promise-based ORM for Node.js, designed with modern TypeScript and providing type safety for schema databases.

Downloads

901

tspace-mysql

tspace-mysql is an Object-Relational Mapping (ORM) tool designed to run seamlessly in Node.js and is fully compatible with TypeScript. It consistently supports the latest features in both TypeScript and JavaScript, providing additional functionalities to enhance your development experience.

Feature

| Feature | Description | |-------------------------------|----------------------------------------------------------------------------------------------------------| | Query Builder | Create flexible queries like SELECT, INSERT, UPDATE, and DELETE. You can also use raw SQL. | | Join Clauses | Use INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN to combine data from multiple tables. | | Model | Provides a way to interact with database records as objects in code. You can perform create, read, update, and delete (CRUD) operations. Models also support soft deletes and relationship methods. | | Schema | Allows you to define and manage the structure of MySQL tables, including data types and relationships. Supports migrations and validation. | | Validation | Automatically checks data against defined rules before saving it to the database, ensuring data integrity and correctness. | | Sync | Synchronizes the model structure with the database, updating the schema to match the model definitions automatically. | | Soft Deletes | Marks records as deleted without removing them from the database. This allows for recovery and auditing later. | | Relationships | Set up connections between models, such as one-to-one, one-to-many, belongs-to, and many-to-many. Supports nested relationships and checks. | | Type Safety | Ensures that queries are safer by checking the types of statements like SELECT, ORDER BY, GROUP BY, and WHERE. | | Repository | Follows a pattern for managing database operations like SELECT, INSERT, UPDATE, and DELETE. It helps keep the code organized. | | Decorators | Use decorators to add extra functionality or information to model classes and methods, making the code easier to read. | | Caching | Improves performance by storing frequently requested data. Supports in-memory caching (like memory DB) and Redis for distributed caching. | | Migrations | Use CLI commands to create models, make migrations, and apply changes to the database structure. | | Blueprints | Create a clear layout of the database structure and how models and tables relate to each other. | | CLI | A Command Line Interface for managing models, running migrations, executing queries, and performing other tasks using commands (like make:model, migrate, and query). |

Install

Install with npm:

npm install tspace-mysql --save
npm install tspace-mysql -g

Basic Usage

Configuration

To establish a connection, the recommended method for creating your environment variables is by using a '.env' file. using the following:

DB_HOST = localhost;
DB_PORT = 3306;
DB_USERNAME = root;
DB_PASSWORD = password;
DB_DATABASE = database;

/**
 * @default
 *  DB_CONNECTION_LIMIT = 10
 *  DB_QUEUE_LIMIT      = 0
 *  DB_TIMEOUT          = 60000
 *  DB_DATE_STRINGS     = false
 */

You can also create a file named 'db.tspace' to configure the connection. using the following:

source db {
    host               = localhost
    port               = 3306
    database           = npm
    user               = root
    password           = database
    connectionLimit    = 10
    dateStrings        = true
    connectTimeout     = 60000
    waitForConnections = true
    queueLimit         = 0
    charset            = utf8mb4
}

Query Builder

How a database query builder works with a simple example using the following:

+-------------+--------------+----------------------------+
|                     table users                         |
+-------------+--------------+----------------------------+
| id          | username     | email                      |
|-------------|--------------|----------------------------|
| 1           | tspace       | [email protected]           |
| 2           | tspace2      | [email protected]          |
+-------------+--------------+----------------------------+


+-------------+--------------+----------------------------+
|                     table posts                         |
+-------------+--------------+----------------------------+
| id          | user_id      | title                      |
|-------------|--------------|----------------------------|
| 1           | 1            | posts tspace               |
| 2           | 2            | posts tspace2              |
+-------------+--------------+----------------------------+

Table Name & Alias Name


await new DB().from('users').find(1)
// SELECT * FROM `users` WHERE `users`.`id` = '1' LIMIT 1;

await new DB().table('users').find(1)
// SELECT * FROM `users` WHERE `users`.`id` = '1' LIMIT 1;

await new DB().table('users').alias('u').find(1)
// SELECT * FROM `users` AS `u` WHERE `u`.`id` = '1' LIMIT 1;

await new DB().fromRaw('u',new DB('users').select('*').limit(1).toString()).find(1)
// SELECT * FROM ( SELECT * FROM `users` LIMIT 1 ) AS `u` WHERE `u`.`id` = '1' LIMIT 1;

await new DB().alias('u',new DB('users').select('*').limit(1).toString()).find(1)
// SELECT * FROM ( SELECT * FROM `users` LIMIT 1 ) AS `u` WHERE `u`.`id` = '1' LIMIT 1;

Returning Results

const user = await new DB("users").find(1); // Object or null

const user = await new DB("users").findOne(); // Object or null

const user = await new DB("users").first(); // Object or null

const user = await new DB("users").firstOrError(message); // Object or error

const users = await new DB("users").findMany(); // Array-object of users

const users = await new DB("users").get(); // Array-object of users

const users = await new DB("users").toArray(); // Array of users

const users = await new DB("users").toJSON(); // JSON of users

const user = await new DB("users").exists(); // Boolean true if user exists otherwise false

const user = await new DB("users").count(); // Number of users counted

const user = await new DB("users").avg(); // Number of users avg

const user = await new DB("users").sum(); // Number of users sum

const user = await new DB("users").max(); // Number of users max

const user = await new DB("user").min(); // Number of users min

const users = await new DB("users").toString(); // sql query string

const users = await new DB("users").toSQL(); // sql query string

const users = await new DB("users").toRawSQL(); // sql query string

const users = await new DB("users").pagination(); // Object of pagination

const users = await new DB("users").makeSelectStatement() // query string for select statement

const users = await new DB("users").makeInsertStatement() // query string for insert statement

const users = await new DB("users").makeUpdateStatement() // query string for update statement

const users = await new DB("users").makeDeleteStatement() // query string for delete statement

const users = await new DB("users").makeCreateTableStatement() // query string for create table statement

Query Statements

const query = await DB.query(
  "SELECT * FROM users WHERE id = :id AND email IS :email AND name IN :username", {
  id : 1,
  email : null,
  username : ['name1','name2']
})
// SELECT * FROM users WHERE id = '1' AND email IS NULL AND username in ('name1','name2');

Select Statements

const select = await new DB("users").select("id", "username").findOne();
// SELECT `users`.`id`, `users`.`username` FROM `users` LIMIT 1;

const selectRaw = await new DB("users").selectRaw("COUNT(id)").findMany();
// SELECT COUNT(id) FROM `users`;
// You can also use the DB.raw() function
// const selectRaw = await new DB("users").selec(DB.raw("COUNT(id)")).findMany();

const selectObject = await new DB("posts")
  .join("posts.user_id", "users.id")
  .select("posts.*")
  .selectObject(
    { id: "users.id", name: "users.name", email: "users.email" },
    "user"
  )
  .findOne();

/** 
SELECT 
  posts.*, JSON_OBJECT('id' , `users`.`id` , 'name' , `users`.`name` , 'email' , `users`.`email`) AS `user`
FROM `posts` 
INNER JOIN `users` ON `posts`.`user_id` = `users`.`id` LIMIT 1;
*/

const selectArray = await new DB("users")
  .select('id','name','email')
  .join("users.id", "posts.user_id")
  .select("posts.*")
  .selectArray(
    { id: "posts.id", user_id: "posts.user_id", title: "posts.title" },
    "posts"
  )
  .findOne();
/** 
SELECT 
  `users`.`id`, `users`.`name`, `users`.`email`, 
  CASE WHEN COUNT(`posts`.`id`) = 0 THEN JSON_ARRAY() 
  ELSE JSON_ARRAYAGG(JSON_OBJECT('id' , `posts`.`id` , 'user_id' , `posts`.`user_id` , 'email' , `posts`.`title`)) 
  END AS `posts` 
FROM `users` 
INNER JOIN `posts` ON `users`.`id` = `posts`.`user_id` WHERE `users`.`deletedAt` IS NULL GROUP BY `users`.`id` LIMIT 1;
*/

await new DB("users").except("id").findOne();
// SELECT `users`.`email`, `users`.`username` FROM `users` LIMIT 1;

await new DB("users").distinct().select("id").findOne();
// SELECT DISTINCT `users`.`id` FROM `users` LIMIT 1;

Raw Expressions

const users = await new DB("users")
  .select(DB.raw("COUNT(`username`) as c"), "username")
  .groupBy("username")
  .having("c > 1")
  .findMany();
// SELECT COUNT(`username`) as c, `users`.`username` FROM `users` GROUP BY `username` HAVING c > 1;

const users = await new DB("users")
  .where(
    "id",
    DB.raw(new DB("users").select("id").where("id", "1").limit(1).toString())
  )
  .findMany();
// SELECT * FROM `users` WHERE `users`.`id` = (SELECT `users`.`id` FROM `users` WHERE `users`.`id` = '1' LIMIT 1);

const findFullName = await new User()
.select('name',`${DB.raw('CONCAT(firstName," ",lastName) as fullName')}`)
.whereRaw(`CONCAT(firstName," ",lastName) LIKE '%${search}%'`)
.findOne()     
//  SELECT `users`.`name`, CONCAT(firstName," ",lastName) as fullName FROM `users` WHERE CONCAT(firstName," ",lastName) LIKE '%search%' LIMIT 1;

Ordering, Grouping, Limit and Offset

Ordering

await new DB("users").orderBy("id", "asc").findOne();
// SELECT * FROM `users` ORDER BY `id` ASC LIMIT 1;

await new DB("users").orderBy("id", "desc").findOne();
// SELECT * FROM `users` ORDER BY `id` DESC LIMIT 1;

await new DB("users").oldest("id").findOne();
// SELECT * FROM `users` ORDER BY `id` ASC LIMIT 1;

await new DB("users").latest("id").findOne();
// SELECT * FROM `users` ORDER BY `id` DESC LIMIT 1;

await new DB("users").random().findMany();
// SELECT * FROM `users` ORDER BY RAND();

Grouping

await new DB("users").groupBy("id").findOne();
// SELECT * FROM `users` GROUP BY `id` LIMIT 1;

await new DB("users").groupBy("id", "username").findOne();
// SELECT * FROM `users` GROUP BY `id`, `username` LIMIT 1;

await new DB("users")
  .select(DB.raw("COUNT(username) as c"), "username")
  .groupBy("username")
  .having("c > 1")
  .findMany();
// SELECT COUNT(username) as c, `users`.`username` FROM `users` GROUP BY `username` HAVING c > 1;

Limit and Offset

await new DB("users").limit(5).findMany();
// SELECT * FROM `users` LIMIT 5;

await new DB("users").limit(-1).findMany();
// SELECT * FROM `users` LIMIT 2147483647; // int-32  2**31 - 1

await new DB("users").offset(1).findOne();
// SELECT * FROM `users` LIMIT 1 OFFSET 1;

Joins

Inner Join Clause

await new DB("posts").join("posts.user_id", "users.id").findMany();
// SELECT * FROM `posts` INNER JOIN `users` ON `posts`.`user_id` = `users`.`id`;

await new DB("posts")
.join((join) => {
  return join
  .on('posts.user_id','users.id')
  .on('users.id','post_user.user_id')
  .and('users.id','posts.user_id')
})
.findMany();

// SELECT * FROM `posts` 
// INNER JOIN `users` ON `posts`.`user_id` = `users`.`id` 
// INNER JOIN `post_user` ON `users`.`id` = `post_user`.`user_id` AND `users`.`id` = `posts`.`user_id`;

Left Join, Right Join Clause

await new DB("posts").leftJoin("posts.user_id", "users.id").findMany();
// SELECT * FROM `posts` LEFT JOIN `users` ON `posts`.`user_id` = `users`.`id`;

await new DB("posts").rightJoin("posts.user_id", "users.id").findMany();
// SELECT * FROM `posts` RIGHT JOIN `users` ON `posts`.`user_id` = `users`.`id`;

Cross Join Clause

await new DB("posts").crossJoin("posts.user_id", "users.id").findMany();
// SELECT * FROM `posts` CROSS JOIN `users` ON `posts`.`user_id` = `users`.`id`;

Basic Where Clauses

Where Clauses

const users = await new DB("users").where("id", 1).findMany();
// SELECT * FROM `users` WHERE `users`.`id` = '1'

const users = await new DB("users")
  .where("id", 1)
  .where("username", "try to find")
  .findMany();
// SELECT * FROM `users` WHERE `users`.`id` = '1' and `users`.`username` = 'try to find'

const users = await new DB("users").where("id", ">", 1).findMany();
// SELECT * FROM `users` WHERE `users`.`id` > '1';

const users = await new DB("users").where("id", "<>", 1).findMany();
// SELECT * FROM `users` WHERE `users`.`id` <> '1';

Or Where Clauses

const users = await new DB("users").where("id", 1).orWhere("id", 2).findMany();
// SELECT * FROM `users` WHERE `users`.`id` = '1' OR `users`.`id` = '2'

const users = await new DB("users")
  .where("id", 1)
  .whereQuery((query) => {
    return query
      .where("id", "<>", 2)
      .orWhere("username", "try to find")
      .orWhere("email", "[email protected]");
  })
  .findMany();
// SELECT * FROM `users` WHERE `users`.`id` = '1'
// AND
// ( `users`.`id` <> '2' OR `users`.`username` = 'try to find' OR `users`.`email` = '[email protected]');

Where Object Clauses

import { Operator } from 'tspace-mysql'

const whereObject = await new DB("users")
  .whereObject({
    id :  Operator.notEq(1),
    username :  Operator.in(['user1','user2']),
    name :  Operator.like('%value%')
  })
  .findMany();

// SELECT * FROM `users` WHERE `users`.`id` <> '1' AND `users`.`username` = 'user1' AND `users`.`name` LIKE '%value%';

JSON Where Clauses

const whereJSON = await new DB("users")
  .whereJSON("json", { key: "id", value: "1234" })
  .findMany();
// SELECT * FROM `users` WHERE `users`.`json`->>'$.id' = '1234';

Additional Where Clauses

const users = await new DB("users").whereIn("id", [1, 2]).findMany();
// SELECT * FROM `users` WHERE `users`.`id` IN ('1','2');

const users = await new DB("users").whereNotIn("id", [1, 2]).findMany();
// SELECT * FROM `users` WHERE `users`.`id` NOT IN ('1','2');

const users = await new DB("users").whereBetween("id", [1, 2]).findMany();
// SELECT * FROM `users` WHERE `users`.`id` BETWEEN '1' AND '2';

const users = await new DB("users").whereNotBetween("id", [1, 2]).findMany();
// SELECT * FROM `users` WHERE `users`.`id` NOT BETWEEN '1' AND '2';

const users = await new DB("users").whereNull("username").findMany();
// SELECT * FROM `users` WHERE `users`.`username` IS NULL;

const users = await new DB("users").whereNotNull("username").findMany();
// SELECT * FROM `users` WHERE `users`.`username` IS NOT NULL;

Logical Grouping

const users = await new DB("users")
  .whereQuery((query) => query.where("id", 1).where("username", "values"))
  .whereIn("id", [1, 2])
  .findOne();
// SELECT * FROM `users` WHERE ( `users`.`id` = '1' AND `users`.`username` = 'values') AND `users`.`id` IN ('1','2'') LIMIT 1;

const users = await new DB("users")
  .where("id", 1)
  .whereQuery((query) => {
    return query
      .where("id", "<>", 2)
      .where("username", "try to find")
      .where("email", "[email protected]");
  })
  .findMany();
// SELECT * FROM `users` WHERE `users`.`id` = '1'
// AND
// ( `users`.`id` <> '2' AND `users`.`username` = 'try to find' AND `users`.`email` = '[email protected]');

const users = await new DB("users")
  .whereAny(["name", "username", "email"], "like", `%v%`)
  .findMany();
// SELECT * FROM `users` WHERE ( `users`.`name` LIKE '%v%' OR `users`.`username` LIKE '%v%' OR `users`.`email` LIKE '%v%');

const users = await new DB("users")
  .whereAll(["name", "username", "email"], "like", `%v%`)
  .findMany();
// SELECT * FROM `users` WHERE ( `users`.`name` LIKE '%v%' AND `users`.`username` LIKE '%v%' AND `users`.`email` LIKE '%v%');

Advanced Where Clauses

Where Exists Clauses

const users = await new DB("users")
  .whereExists(new DB("users").select("id").where("id", 1).toString())
  .findMany();
// SELECT * FROM `users` WHERE EXISTS (SELECT `id` FROM `users` WHERE id = 1);

const users = await new DB("users")
  .wherNoteExists(new DB("users").select("id").where("id", 1).toString())
  .findMany();
// SELECT * FROM `users` WHERE NOT EXISTS (SELECT `id` FROM `users` WHERE id = 1);

Subquery Where Clauses

const users = await new DB("users")
  .whereSubQuery("id", "SELECT id FROM users")
  .findMany();
// SELECT * FROM `users` WHERE `users`.`id` IN (SELECT id FROM users);

const users = await new DB("users")
  .whereSubQuery("id", new DB("users").select("id").toString())
  .findMany();
// SELECT * FROM `users` WHERE `users`.`id` IN (SELECT id FROM users);

const users = await new DB("users")
  .whereSubQuery(
    "id",
    new DB("users")
      .select("id")
      .whereSubQuery("id", new DB("posts").select("user_id").toString())
      .toString()
  )
  .findMany();
/*
  SELECT * FROM `users` 
  WHERE `users`.`id`
    IN (
      SELECT `users`.`id` FROM `users` 
      WHERE `users`.`id` 
        IN (
          SELECT `posts`.`user_id` FROM `posts` 
        )
    );
*/

Conditional Where Clauses

const users = await new DB("users")
  .where("id", 1)
  .when(true, (query) => query.where("username", "when is actived"))
  .findMany();
// SELECT * FROM `users` WHERE `users`.`id` = '1' AND `users`.`username` = 'when is actived';

const users = await new DB("users")
  .where("id", 1)
  .when(false, (query) => query.where("username", "when is actived"))
  .findMany();
// SELECT * FROM `users` WHERE `users`.`id` = '1';

Paginating

const users = await new DB("users").paginate();
// SELECT * FROM `users` LIMIT 15 OFFSET 0;
// SELECT COUNT(*) AS total FROM `users`;

const pageTwoUsers = await new DB("users").paginate({ page: 2, limit: 5 });

/*
  SELECT * FROM `users` LIMIT 5 OFFSET 5;
  SELECT COUNT(*) AS total FROM `users`;
  
  the results are returned
  {
    meta: {
      total: n,
      limit: 5,
      total_page: 5,
      current_page: 2,
      last_page: n,
      next_page: 3,
      prev_page: 1
    },
    data: [...your data here]
  }

*/

Insert Statements

const user = await new DB("users")
  .create({
    name: "tspace3",
    email: "[email protected]",
  })
  .save();
/**
  INSERT INTO `users` 
    (`users`.`name`,`users`.`email`) 
  VALUES 
    ('tspace3','[email protected]');

  -- then return the result inserted --
  SELECT * FROM `users` WHERE `users`.`id` = ${INSERT ID};
*/

const users = await new DB("users")
  .createMultiple([
    {
      name: "tspace4",
      email: "[email protected]",
    },
    {
      name: "tspace5",
      email: "[email protected]",
    },
    {
      name: "tspace6",
      email: "[email protected]",
    },
  ])
  .save();

/**
  INSERT INTO `users` 
    (`users`.`name`,`users`.`email`)
  VALUES
    ('tspace4','[email protected]'), 
    ('tspace5','[email protected]'), 
    ('tspace6','[email protected]');
*/

const users = await new DB("users")
  .where("name", "tspace4")
  .where("email", "[email protected]")
  .createNotExists({
    name: "tspace4",
    email: "[email protected]",
  })
  .save();
/*
  -- if exists return null, if not exists created new data --
  SELECT EXISTS(
    SELECT 1 FROM `users` 
    WHERE `users`.`name` = 'tspace4' 
    AND `users`.`email` = '[email protected]' 
    LIMIT 1
  ) AS 'exists';

  INSERT INTO `users` (`users`.`name`,`users`.`email`) VALUES ('tspace4','[email protected]');
*/

const users = await new DB("users")
  .where("name", "tspace4")
  .where("email", "[email protected]")
  .createOrSelect({
    name: "tspace4",
    email: "[email protected]",
  })
  .save();
/**
  -- if has exists return data, if not exists created new data --
  SELECT EXISTS(
    SELECT 1 FROM `users` 
    WHERE `users`.`name` = 'tspace4' 
    AND `users`.`email` = '[email protected]' 
    LIMIT 1
  ) AS 'exists';

  INSERT INTO `users` (`users`.`name`,`users`.`email`) VALUES ('tspace4','[email protected]');

  SELECT * FROM `users` WHERE `users`.`id` = '4';
*/

Update Statements

const user = await new DB("users")
  .where("id", 1)
  .update({
    name: "tspace1**",
    email: "[email protected]",
  })
  .save();
/**

 UPDATE `users` SET 
  `users`.`name` = 'tspace1',
  `users`.`email` = '[email protected]' 
 WHERE `users`.`id` = '1' LIMIT 1;

 */

const user = await new DB("users")
  .where("id", 1)
  .updateMany({
    name: "tspace1",
    email: "[email protected]",
  })
  .save();
/**
 UPDATE `users` SET 
 `users`.`name` = 'tspace1',
 `users`.`email` = '[email protected]' 
 WHERE `users`.`id` = '1';
 */

const user = await new DB("users")
  .where("id", 1)
  .update(
    {
      name: "tspace1",
      email: "[email protected]",
    },
    ["name"]
  )
  .save();
/**
  UPDATE `users` SET 
    `name` = 
    CASE WHEN (`name` = '' OR `name` IS NULL) 
    THEN 'tspace1' ELSE `name` 
    END,
    `email` = 
    '[email protected]' 
    WHERE `users`.`id` = '1' LIMIT 1;
 */

const user = await new DB("users")
  .updateMultiple([
    {
      when: {
        id: 1,
        name: "name1",
      },
      columns: {
        name: "update row1",
        email: "[email protected]",
      },
    },
    {
      when: {
        id: 2,
      },
      columns: {
        name: "update row2",
        email: "[email protected]",
      },
    },
  ])
  .save();

/** 
 UPDATE `users` SET 
 `users`.`name` = ( 
  CASE WHEN `users`.`id` = '1' 
    AND `users`.`name` = 'name1' 
  THEN 'update row1' 
    WHEN `users`.`id` = '2' 
  THEN 'update row2' 
    ELSE `users`.`name` 
  END 
  ), 
  `users`.`email` = ( 
    CASE WHEN `users`.`id` = '1' 
      AND `users`.`name` = 'name1' 
    THEN '[email protected]' 
      WHEN `users`.`id` = '2' 
    THEN '[email protected]' 
      ELSE `users`.`email` 
    END 
  ) 
  WHERE `users`.`id` IN ('1','2') LIMIT 2;

 */

const user = await new DB("users")
  .where("id", 1)
  .updateOrCreate({
    name: "tspace1**",
    email: "[email protected]",
  })
  .save();
// if has exists return update, if not exists created new data
// UPDATE `users` SET `name` = 'tspace1**',`email` = '[email protected]' WHERE `users`.`id` = '1' LIMIT 1;
// INSERT INTO `users` (`name`,`email`) VALUES ('tspace1**','[email protected]');

Delete Statements

const deleted = await new DB("users").where("id", 1).delete();
// DELETE FROM `users` WHERE `users`.`id` = '1' LIMIT 1;

const deleted = await new DB("users").where("id", 1).deleteMany();
// DELETE FROM `users` WHERE `users`.`id` = '1' ;

Hook Statements

const hookImage = async (results) => {
  for(const result of results) {
    result.image = await ...getImage()
  }
};
const user = await new DB("users").where("id", 1).hook(hookResult).findMany();

Faker Statements

await new DB("users").faker(2);
/**
  INSERT INTO `users`  
    (`users`.`username`,`users`.`email`)
  VALUES
    ('ivsvtagyta86n571z9d81maz','fxcwkubccdi5ewos521uqexy'),
    ('rnr4esoki7fgekmdtarqewt','gv0mzb1m3rlbinsdyb6')
 */

// custom faker
await new DB("users").faker(5, (row, index) => {
  return {
    username: `username-${index + 1}`,
    email: `email-${index + 1}`,
  };
});

/**
  
INSERT INTO `users` 
  (`users`.`username`,`users`.`email`) 
VALUES 
  ('username-1','email-1'),
  ('username-2','email-2'),
  ('username-3','email-3'),
  ('username-4','email-4'),
  ('username-5','email-5');

 */

// fast to create
await new DB("users").faker(40_000);

Unset Statements


const userInstance = new User().where('email','[email protected]')
  
const exits = await userInstance.exists()
// SELECT EXISTS (SELECT 1 FROM `users` WHERE `users`.`email` = '[email protected]' LIMIT 1) AS `aggregate`;

const user = await userInstance.orderBy('id').findOne()
// SELECT * FROM `users` WHERE `users`.`email` = '[email protected]' ORDER BY `users`.`id` DESC LIMIT 1;

const users = await userInstance.select('id').unset({ limit : true }).findMany()
// SELECT `users`.`id` FROM `users` WHERE `users`.`email` = '[email protected]' ORDER BY `users`.`id` DESC;

const usersUnsetWhereStatement = await userInstance.unset({ select : true, where : true , orderBy : true }).findMany()
// SELECT * FROM `users` WHERE `users`.`deletedAt` IS NULL;

Common Table Expressions


const user = await new User()
.CTEs('z', (query) => {
  return query
  .from('posts')
})
.CTEs('x', (query) => {
  return query
  .from('post_user')
})
.select('users.*','x.*','z.*')
.join('users.id','x.user_id')
.join('users.id','z.user_id')
.findOne()

// WITH z AS (SELECT posts.* FROM `posts`), 
// x AS (SELECT * FROM `post_user`) 
// SELECT users.*, z.*, x.* FROM `users` INNER JOIN `x` ON `users`.`id` = `x`.`user_id` INNER JOIN `z` ON `users`.`id` = `z`.`user_id` WHERE `users`.`deleted_at` IS NULL LIMIT 1;

More Methods

where(column , operator , value)
whereSensitive(column , operator , value)
whereId(id)
whereUser(userId)
whereEmail(value)
whereIn(column , [])
whereNotIn(column , [])
whereNull(column)
whereNotNull(column)
whereBetween (column , [value1 , value2])
whereQuery(callback)
whereJson(column, { targetKey, value , operator })
whereRaw(sql)
whereExists(sql)
whereSubQuery(colmn , rawSQL)
whereNotSubQuery(colmn , rawSQL)
orWhere(column , operator , value)
orWhereRaw(sql)
orWhereIn(column , [])
orWhereSubQuery(colmn , rawSQL)
when(contition , callback)
select(column1 ,column2 ,...N)
distinct()
selectRaw(column1 ,column2 ,...N)
except(column1 ,column2 ,...N)
exceptTimestamp()
only(column1 ,column2 ,...N)
hidden(column1 ,column2 ,...N)
join(primary key , table.foreign key)
rightJoin (primary key , table.foreign key)
leftJoin (primary key , table.foreign key)
limit (limit)
having (condition)
havingRaw (condition)
orderBy (column ,'ASC' || 'DSCE')
orderByRaw(column ,'ASC' || 'DSCE')
latest (column)
latestRaw (column)
oldest (column)
oldestRaw (column)
groupBy (column)
groupByRaw (column)
create(objects)
createMultiple(array objects)
update (objects)
updateMany (objects)
updateMultiple(array objects)
createNotExists(objects)
updateOrCreate (objects)
onlyTrashed()
connection(options)
backup({ database , connection })
backupToFile({ filePath, database , connection })
hook((result) => ...) // callback result to function
sleep(seconds)

/**
 * registry relation in your models
 * @relationship
 */
hasOne({ name, model, localKey, foreignKey, freezeTable , as })
hasMany({ name, model, localKey, foreignKey, freezeTable , as })
belongsTo({ name, model, localKey, foreignKey, freezeTable , as })
belongsToMany({ name, model, localKey, foreignKey, freezeTable, as, pivot })
/**
 * @relation using registry in your models
 */
relations(name1 , name2,...nameN) // with(name1, name2,...nameN)
/**
 * @relation using registry in your models ignore soft delete
 */
relationsAll(name1 , name2,...nameN) // withAll(name1, name2,...nameN)
/**
 * @relation using registry in your models. if exists child data remove this data
 */
relationsExists(name1 , name2,...nameN) // withExists(name1, name2,...nameN)
/**
 * @relation using registry in your models return only in trash (soft delete)
 */
relationsTrashed(name1 , name2,...nameN) // withTrashed(name1, name2,...nameN)
/**
 * @relation call a name of relation in registry, callback query of data
 */
relationQuery(name, (callback) ) // withQuery(name1, (callback))


/**
 * queries statements
 * @execute data of statements
*/
findMany() // get()
findOne() // first()
find(id)
delelte()
delelteMany()
exists()
toString()
toJSON()
toArray(column)
count(column)
sum(column)
avg(column)
max(column)
min(column)
pagination({ limit , page })
save() /* for actions statements insert or update */
makeSelectStatement()
makeInsertStatement()
makeUpdateStatement()
makeDeleteStatement()
makeCreateTableStatement()

Database Transactions

Within a database transaction, you can utilize the following:

const connection = await new DB().beginTransaction();

try {
  /**
   *
   * @startTransaction start transaction in scopes function
   */
  await connection.startTransaction();

  const user = await new User()
    .create({
      name: `tspace`,
      email: "[email protected]",
    })
    /**
     *
     * bind method for make sure this connection has same transaction in connection
     * @params {Function} connection
     */
    .bind(connection)
    .save();

  const posts = await new Post()
    .createMultiple([
      {
        user_id: user.id,
        title: `tspace-post1`,
      },
      {
        user_id: user.id,
        title: `tspace-post2`,
      },
      {
        user_id: user.id,
        title: `tspace-post3`,
      },
    ])
    .bind(connection) // don't forget this
    .save();

  /**
   *
   * @commit commit transaction to database
   */
  await connection.commit();
  
} catch (err) {
  /**
   *
   * @rollback rollback transaction
   */
  await connection.rollback();
}

Connection

When establishing a connection, you can specify options as follows:

const connection = await new DB().getConnection({
    host: 'localhost',
    port : 3306,
    database: 'database'
    username: 'username',
    password: 'password',
})

const users = await new DB('users')
.bind(connection) // don't forget this
.findMany()

Backup

To backup a database, you can perform the following steps:

/**
 *
 * @param {string} database Database selected
 * @param {object | null} to defalut new current connection
 */
const backup = await new DB().backup({
    database: 'try-to-backup',  // clone current database to this database
    to ?: {
        host: 'localhost',
        port : 3306,
        username: 'username',
        password: 'password',
    }
})
/**
 *
 * @param {string} database Database selected
 * @param {string} filePath file path
 * @param {object | null} conection defalut current connection
 */
const backupToFile = await new DB().backupToFile({
    database: 'try-to-backup',
    filePath: 'backup.sql',
    connection ?: {
        host: 'localhost',
        port : 3306,
        database: 'database'
        username: 'username',
        password: 'password',
    }
})
// backupToFile => backup.sql

/**
 *
 * @param {string} database new db name
 */
await new DB().cloneDB('try-to-clone')

Injection

The 'tspace-mysql' library is configured to automatically escape SQL injection by default. Let's example a escape SQL injection and XSs injection:

const input = "admin' OR '1'='1";
DB.escape(input);
// "admin\' OR \'1\'=\'1"

//XSS
const input = "text hello!<script>alert('XSS attack');</script>";
DB.escapeXSS(input);
// "text hello!"

Generating Model Classes

To get started, install the 'tspace-mysql' package globally using the following npm command:

/**
 *
 * @install global command
 */
npm install tspace-mysql -g

/**
 *
 * @make Model
 */
tspace-mysql make:model <model name> --dir=< directory >

# tspace-mysql make:model User --dir=App/Models
# App/Models/User.ts

Model Conventions

Your database schema using models. These models represent tables in the database Let's example a basic model class:

import { Model } from "tspace-mysql";
// If you want to specify a global setting for the 'Model'
Model.global({
  uuid: true,
  softDelete: true,
  timestamp: true,
  logger: true,
});

class User extends Model {
  constructor() {
    super();
    /**
     *
     * Assign setting global in your model
     * @useMethod
     * this.usePattern('camelCase') // => default 'snake_case'
     * this.useCamelCase()
     * this.useSnakeCase()
     * this.useLogger()
     * this.useDebug()
     * this.usePrimaryKey('id')
     * this.useTimestamp({
     *    createdAt : 'created_at',
     *    updatedAt : 'updated_at'
     * }) // runing a timestamp when insert or update
     * this.useSoftDelete('deletedAt') // => default target to colmun deleted_at
     * this.useTable('users')
     * this.useTableSingular() // => 'user'
     * this.useTablePlural() // => 'users'
     * this.useUUID('uuid') // => runing a uuid (universally unique identifier) when insert new data
     * this.useRegistry() // => build-in functions registry
     * this.useLoadRelationsInRegistry() // => auto generated result from relationship to results
     * this.useBuiltInRelationFunctions() // => build-in functions relationships to results
     * this.useHooks([(r) => console.log(r)])
     * this.useObserver(Observe)
     * this.useSchema ({
     *     id          : Blueprint.int().notNull().primary().autoIncrement(),
     *     uuid        : Blueprint.varchar(50).null(),
     *     name        : Blueprint.varchar(191).notNull(),
     *     email       : Blueprint.varchar(191).notNull(),
     *     created_at  : Blueprint.timestamp().null(),
     *     updated_at  : Blueprint.timestamp().null(),
     *     deleted_at  : Blueprint.timestamp().null()
     *  }) // auto-generated table when table is not exists and auto-create column when column not exists
     *
     *  // validate input when create or update reference to the schema in 'this.useSchema'
     *  this.useValidateSchema({
     *   id : Number,
     *   uuid :  Number,
     *   name : {
     *       type : String,
     *       length : 191
     *       require : true
     *   },
     *   email : {
     *       type : String,
     *       require : true,
     *       length : 191,
     *       match: /^[a-zA-Z0-9._]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/,
     *       unique : true,
     *       fn : (email : string) => !/^[a-zA-Z0-9._]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/.test(email)
     *   },
     *   created_at : Date,
     *   updated_at : Date,
     *   deleted_at : Date
     *   })
     */

    /*
     * the "snake case", plural name of the class will be used as the table name
     *
     * @param {string} name The table associated with the model.
     */
    this.useTable("users");
  }
}
export { User };
export default User;

Basic Model Setup

Table Name

import { Model } from 'tspace-mysql'
class User extends Model {
    constructor() {
      super()
      // By default, the model knows that the table name for this User is 'users'

      this.useTable('fix_table') // fixtable
      this.useTablePlural() // users
      this.useTableSingular() // user
    }
}

Pattern


import { Model } from 'tspace-mysql'
class UserPhone extends Model {
    constructor() {
      super()
      // By default, the model is pattern snake_case
      // The table name is user_phones
      this.useSnakeCase()

      this.useCamelCase()
       // The table name is userPhones
    }
}

// set the pattern CamelCase for the model
const userPhone = await new UserPhone().where('user_id',1).findOne()
// covert 'user_id' to 'userId'
// SELECT * FROM `userPhones` WHERE `userPhones`.`userId` = '1' LIMIT 1;

// avoid the pattern CamelCase for the model
const userPhone = await new UserPhone().where(DB.freeze('user_id'),1).findOne()
// SELECT * FROM `userPhones` WHERE `userPhones`.`user_id` = '1' LIMIT 1;

UUID


import { Model } from 'tspace-mysql'
class User extends Model {
  constructor() {
    super()
    this.useUUID() // insert uuid when creating
  }
}

Timestamp


import { Model } from 'tspace-mysql'
class User extends Model {
  constructor() {
    super()
    // insert created_at and updated_at when creating
    // update updated_at when updating
    // 'created_at' and 'updated_at' still relate to pettern the model
    // this.useCamelCase() will covert 'created_at' to 'createdAt' and 'updated_at' to 'updatedAt'
    this.useTimestamp() 

    // custom the columns
    this.useTimestamp({
      createdAt : 'createdAtCustom',
      updatedAt : 'updatedAtCustom'
    })

  }
}

Debug


import { Model } from 'tspace-mysql'
class User extends Model {
    constructor() {
      super()
      this.useDebug() // show the query sql in console when executing
    }
}

Observer


class Observe {

  public selected(results) {
    console.log({ results , selected : true })
  }

  public created(results) {
      console.log({ results , created : true })
  }

  public updated(results) {
    console.log({ results , updated : true })
  }

  public deleted(results) {
    console.log({ results , deleted : true })
  }
}

import { Model } from 'tspace-mysql'
class User extends Model {
  constructor() {
    super()
    this.useObserver(Observe) // returning to the observers by statements
  }
}

Logger


import { Model } from 'tspace-mysql'
class User extends Model {
  constructor() {
    super()
    // keep logging everything except select  to the table '$loggers'
    // the table will automatically be created
    this.useLogger()

    // keep logging everything
    this.useLogger({
      selected : true,
      inserted : true,
      updated  : true,
      deleted  : true,
    })
  }
}

Hooks


import { Model } from 'tspace-mysql'
class User extends Model {
  constructor() {
    super()
      // when executed will returning the results to any hooks function
      this.useHooks([
        (results1) => console.log(results1),
        (results2) => console.log(results2),
        (results3) => console.log(results3)
      ])
  }
}

Global Scope


class User extends Model {
  constructor() {
    super()

    // Every query will have the global scope applied.
    this.globalScope((query : User) => {
      return query.select('id').where('id' , '>' , 10).orderBy('id')
    })
  }
}

const user = await new User().findMany()

// SELECT `users`.`id` FROM `users` WHERE `users`.`id` > '10' ORDER BY `users`.`id` ASC LIMIT 1

Joins Model

Inner Join Model Clause

await new User().joinModel(User, Post).findMany();
// SELECT `users`.`id`, `users`.`email`, `users`.`username` FROM `users` INNER JOIN `posts` ON `users`.`id` = `posts`.`user_id`;

// if the model use soft delete
await new User().joinModel(User, Post).findMany();
// SELECT `users`.`id`, `users`.`email`, `users`.`username` FROM `users` 
// INNER JOIN `posts` ON `users`.`id` = `posts`.`user_id` 
// WHERE `posts`.`deleted_at` IS NULL AND `users`.`deleted_at` IS NULL;

await new User().select(`${User.table}.*`,`${Post.table}.*`).joinModel(User, Post).findMany();
// SELECT users.*, posts.* FROM `users` 
// INNER JOIN `posts` ON `users`.`id` = `posts`.`user_id` 
// WHERE `posts`.`deleted_at` IS NULL AND `users`.`deleted_at` IS NULL;

await new User().select('u.*','p.*')
.joinModel({ model : User , key : 'id' , alias : 'u' }, { model : Post , key : 'user_id', alias : 'p'})
.findMany();
// SELECT u.*, p.* FROM `users` AS `u` 
// INNER JOIN `posts` AS `p` ON `u`.`id` = `p`.`user_id` 
// WHERE `p`.`deleted_at` IS NULL AND `u`.`deleted_at` IS NULL;

await new DB("posts")
.join((join) => {
  return join
  .on('posts.user_id','users.id')
  .on('users.id','post_user.user_id')
  .and('users.id','posts.user_id')
})
.findMany()
//  SELECT * FROM `posts` 
// INNER JOIN `users` ON `posts`.`user_id` = `users`.`id` 
// INNER JOIN `post_user` ON `users`.`id` = `post_user`.`user_id` AND `users`.`id` = `posts`.`user_id`;

Left Join, Right Join Model Clause

await new User().leftJoinModel(User, Post).findMany();
//  SELECT `users`.`id`, `users`.`email`, `users`.`username` FROM `users` LEFT JOIN `posts` ON `users`.`id` = `posts`.`user_id`;

await new User().rightJoinModel(User, Post).findMany();
//  SELECT `users`.`id`, `users`.`email`, `users`.`username` FROM `users` RIGHT JOIN `posts` ON `users`.`id` = `posts`.`user_id`;

Cross Join Model Clause

await new User().crossJoinModel(User, Post).findMany();
//  SELECT `users`.`id`, `users`.`email`, `users`.`username` FROM `users` CROSS JOIN `posts` ON `users`.`id` = `posts`.`user_id`;

Relationships

Relationships are defined as methods on your Model classes. Let's example a basic relationship:

One To One

A one-to-one relationship is used to define relationships where a single model is the parent to one child models

import { Model } from 'tspace-mysql'
import Phone  from '../Phone'
class User extends Model {
    constructor(){
        super()
        this.useTimestamp()
        /**
         *
         * @hasOne Get the phone associated with the user.
         * @relationship users.id -> phones.user_id
         */
        this.hasOne({ name : 'phone' , model : Phone })
    }
    /**
     * Mark a method for relationship
     * @hasOne Get the phone associated with the user. using function callback
     * @function
     */
    phone (callback) {
      return this.hasOneBuilder({ name : 'phone' , model : Phone } , callback)
    }
}
export default User

+--------------------------------------------------------------------------+

import User from '../User'
const user = await new User().relations('phone').findOne() // You can also use the method .with('roles').
// user?.phone => {...}
const userUsingFunction = await new User().phone().findOne()
// userUsingFunction?.phone => {...}

One To Many

A one-to-many relationship is used to define relationships where a single model is the parent to one or more child models.

import { Model } from 'tspace-mysql'
import Comment  from '../Comment'
class Post extends Model {
    constructor(){
        super()
        this.useTimestamp()
        /**
         *
         * @hasMany Get the comments for the post.
         * @relationship posts.id -> comments.post_id
         */
        this.hasMany({ name : 'comments' , model : Comment })
    }
    /**
     *
     * @hasManyQuery Get the comments for the post. using function callback
     * @function
     */
    comments (callback) {
        return  this.hasManyBuilder({ name : 'comments' , model : Comment } , callback)
    }
}
export default Post

+--------------------------------------------------------------------------+

import Post from '../Post'
const posts = await new Post().relations('comments').findOne()
// posts?.comments => [{...}]
const postsUsingFunction = await new Post().comments().findOne()
// postsUsingFunction?.comments => [{...}]

Belongs To

A belongsto relationship is used to define relationships where a single model is the child to parent models.

import { Model } from 'tspace-mysql'
import User  from '../User'
class Phone extends Model {
    constructor(){
        super()
        this.useTimestamp()
        /**
         *
         * @belongsTo Get the user that owns the phone.
         * @relationship phones.user_id -> users.id
         */
        this.belognsTo({ name : 'user' , model : User })
    }
    /**
     *
     * @belongsToBuilder Get the user that owns the phone.. using function callback
     * @function
     */
    user (callback) {
        return this.belongsToBuilder({ name : 'user' , model : User }, callback)
    }
}
export default Phone

+--------------------------------------------------------------------------+

import Phone from '../Phone'
const phone = await new Phone().relations('user').findOne()
// phone?.user => {...}
const phoneUsingFunction = await new Phone().user().findOne()
// phoneUsingFunction?.user => {...}

Many To Many

Many-to-many relations are slightly more complicated than hasOne and hasMany relationships.

import { Model } from 'tspace-mysql'
import Role from '../Role'
class User extends Model {
    constructor(){
        super()
        this.useTimestamp()
        /**
         *
         * @belongsToMany Get The roles that belong to the user.
         * @relationship users.id , roles.id => role_user.user_id , role_user.role_id
         */
        this.belognsToMany({ name : 'roles' , model : Role })
    }
    /**
     * @belongsToBuilder Get the user that owns the phone.. using function callback
     * @function
     */
    roles (callback) {
        return this.belognsToManyBuilder({ model : Role } , callback)
    }
}
export default User

+--------------------------------------------------------------------------+

import User from '../User'
const user = await new User().relations('roles').findOne()
// user?.roles => [{...}]
const userUsingFunction = await new User().roles().findOne()
// user?.roles => [{...}]

Relation

Relationships are connections between entities. Let's consider an example of a relationship:


+-------------+--------------+----------------------------+
|                     table users                         |                    
+-------------+--------------+----------------------------+
| id          | username     | email                      |
|-------------|--------------|----------------------------|
| 1           | tspace1      | [email protected]          |
| 2           | tspace2      | [email protected]          |
| 3           | tspace3      | [email protected]          |
+-------------+--------------+----------------------------+

+-------------+--------------+----------------------------+
|                     table posts                         |
+-------------+--------------+----------------------------+
| id          | user_id      | title                      |
|-------------|--------------|----------------------------|
| 1           | 1            | posts 1                    |
| 2           | 1            | posts 2                    |
| 3           | 3            | posts 3                    |
+-------------+--------------+----------------------------+

import { Model } from 'tspace-mysql'

class User extends Model {
    constructor(){
      super()
      this.hasMany({ name : 'posts' , model : Post })
    }
}

class Post extends Model {
    constructor(){
      super()
      this.belongsTo({ name : 'user' , model : User })
    }
}

await new User()
.relations('posts')
.findOne()
// SELECT * FROM `users` LIMIT 1;
// SELECT * FROM `posts` WHERE `posts`.`userId` IN (...);

/*
 * @returns 
 *  {
 *      id : 1,
 *      username:  "tspace1",
 *      email : "[email protected]",
 *      posts : [
 *        {
 *           id : 1 ,
 *           user_id : 1,
 *           title : "post 1"
 *        },
 *        {
 *           id : 2 ,
 *           user_id : 1,
 *           title : "post 2"
 *        }
 *      ]
 *  }
*/

Deeply Nested Relations

Relationships can involve deep connections. Let's consider an example of a deep relationship:

import { Model } from 'tspace-mysql'

class User extends Model {
    constructor(){
        super()
        this.hasMany({ name : 'posts' , model : Post })
    }
}
+--------------------------------------------------------------------------+
class Post extends Model {
    constructor(){
        super()
        this.hasMany({ name : 'comments' , model : Comment })
        this.belongsTo({ name : 'user' , model : User })
        this.belongsToMany({ name : 'users' , model : User , modelPivot : PostUser })
    }
}
+--------------------------------------------------------------------------+
class Comment extends Model {
    constructor(){
        super()
        this.hasMany({ name : 'users' , model : User })
        this.belongsTo({ name : 'post' , model : Post })
    }
}

class PostUser extends Model {}
+--------------------------------------------------------------------------+
// Deeply nested relations
await new User()
.relations('posts')
.relationQuery('posts', (query : Post) => {
    return query
    .relations('comments','user','users')
    .relationQuery('comments', (query : Comment) => {
        return query.relations('user','post')
    })
    .relationQuery('user', (query : User) => {
        return query.relations('posts').relationQuery('posts',(query : Post)=> {
            return query.relations('comments','user')
            // relation n, n, ...n
        })
    })
    .relationQuery('users', (query : User) => {
        return query
    })
    .relationQuery('users', (query : PostUser) => {
        return query
    }, { pivot : true })
})
.findMany()

// Select some columns in nested relations
await new User()
.relations('posts')
.relationQuery('posts', (query : Post) => query.select('id','user_id','title'))
.findMany()

// Where some columns in nested relations
await new User()
.relations('posts')
.relationQuery('posts', (query : Post) => query.whereIn('id',[1,3,5]))
.findMany()

// Sort data in  nested relations
await new User()
.relations('posts')
.relationQuery('posts', (query : Post) => query.latest('id'))
.findMany()

// Limit data in  nested relations
await new User()
.relations('posts')
.relationQuery('posts', (query : Post) => {
    return query
    .limit(1)
    .relations('comments')
    .relationQuery('comments', (query : Comment) => query.limit(1))
})
.findMany()

Relation Exists

Relationships can return results only if they are not empty in relations, considering soft deletes. Let's illustrate this with an example of an existence check in relations:

+-------------+--------------+----------------------------+--------------------+
|                     table users                         |                    |
+-------------+--------------+----------------------------+--------------------+
| id          | username     | email                      | deleted_at         |
|-------------|--------------|----------------------------|--------------------|
| 1           | tspace1      | [email protected]          |                    |
| 2           | tspace2      | [email protected]          |                    |
| 3           | tspace3      | [email protected]          |                    |
+-------------+--------------+----------------------------+--------------------+


+-------------+--------------+----------------------------+--------------------+
|                     table posts                         |                    |
+-------------+--------------+----------------------------+--------------------+
| id          | user_id      | title                      | deleted_at         |
|-------------|--------------|----------------------------|--------------------|
| 1           | 1            | posts 1                    |2020-07-15 00:00:00 |
| 2           | 2            | posts 2                    |                    |
| 3           | 3            | posts 3                    |2020-07-15 00:00:00 |
+-------------+--------------+----------------------------+--------------------+

import { Model } from 'tspace-mysql'

class User extends Model {
    constructor(){
        super()
        this.hasMany({ name : 'posts' , model : Post })
        this.useSoftDelete()
    }
}

+--------------------------------------------------------------------------+

class Post extends Model {
    constructor(){
        super()
        this.hasMany({ name : 'comments' , model : Comment })
        this.belongsTo({ name : 'user' , model : User })
        this.useSoftDelete()
    }
}
// normal relations
await new User().relations('posts').findMany()
// SELECT * FROM `users` WHERE `users`.`deleted_at`;
// SELECT * FROM `posts` WHERE `posts`.`userId` IN (...) AND `posts`.`deleted_at` IS NULL;

/*
 * @returns [
 *  {
 *      id : 1,
 *      username:  "tspace1",
 *      email : "[email protected]",
 *      posts : []
 *  },
 *  {
 *      id : 2,
 *      username:  "tspace2",
 *      email : "[email protected]",
 *      posts : [
 *       {
 *          id : 2,
 *          user_id :  2,
 *          title : "posts 2"
 *        }
 *      ]
 *  },
 *  {
 *      id : 3,
 *      username:  "tspace3",
 *      email : "[email protected]",
 *      posts : []
 *  }
 * ]
*/

await new User().relationsExists('posts').findMany()
// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL 
// AND EXISTS (SELECT 1 FROM `posts` WHERE `users`.`id` = `posts`.`user_id` AND `posts`.`deletedA_at` IS NULL);

// SELECT * FROM `posts` WHERE `posts`.`user_id` IN (...) AND `posts`.`deleted_at` IS NULL;

/*
 * @returns [
 *  {
 *      id : 2,
 *      username:  "tspace2",
 *      email : "[email protected]",
 *      posts : [
 *       {
 *          id : 2,
 *          user_id :  2,
 *          title : "posts 2"
 *        }
 *      ]
 *  }
 * ]
 * because posts id 1 and id 3 has been removed from database (using soft delete)
 */

Relation Count

Relationships will retrieving the count of related records without loading the data of related models Let's illustrate this with an example of an existence check in relations:


+-------------+--------------+----------------------------+
|                     table users                         |
+-------------+--------------+----------------------------+
| id          | username     | email                      |
|-------------|--------------|----------------------------|
| 1           | tspace1      | [email protected]          |
| 2           | tspace2      | [email protected]          |
+-------------+--------------+----------------------------+

+-------------+--------------+----------------------------+
|                     table posts                         |                    
+-------------+--------------+----------------------------+
| id          | user_id      | title                      |
|-------------|--------------|----------------------------|
| 1           | 1            | posts 1                    |
| 2           | 1            | posts 2                    |
| 3           | 2            | posts 3                    |
+-------------+--------------+----------------------------+

import { Model } from 'tspace-mysql'

class User extends Model {
  constructor(){
      super()
      this.hasMany({ name : 'posts' , model : Post })
      this.useSoftDelete()
  }
}

// you also use .withCount()
await new User().relationsCount('posts').findMany() 
// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL;

// SELECT `posts`.`user_id`, COUNT(`user_id`) AS `aggregate` FROM `posts` 
// WHERE `posts`.`user_id` IN ('1','2') AND `posts`.`deleted_at` IS NULL GROUP BY `posts`.`user_id`;

/*
 * @returns [
 *  {
 *      id : 1,
 *      username:  "tspace1",
 *      email : "[email protected]",
 *      posts : 2
 *  }
 *  {
 *      id : 2,
 *      username:  "tspace2",
 *      email : "[email protected]",
 *      posts : 1
 *  }
 * ]
 */

Relation Trashed

Relationships can return results only if they are deleted in table, considering soft deletes. Let's illustrate this with an example:


+-------------+--------------+----------------------------+--------------------+
|                     table users                         |                    |
+-------------+--------------+----------------------------+--------------------+
| id          | username     | email                      | deleted_at         |
|-------------|--------------|----------------------------|--------------------|
| 1           | tspace1      | [email protected]          |                    |
| 2           | tspace2      | [email protected]          |                    |
| 3           | tspace3      | [email protected]          |2020-07-15 00:00:00 |
+-------------+--------------+----------------------------+--------------------+

+-------------+--------------+----------------------------+--------------------+
|                     table posts                         |                    |
+-------------+--------------+----------------------------+--------------------+
| id          | user_id      | title                      | deleted_at         |
|-------------|--------------|----------------------------|--------------------|
| 1           | 1            | posts 1                    |2020-07-15 00:00:00 |
| 2           | 2            | posts 2                    |                    |
| 3           | 3            | posts 3                    |2020-07-15 00:00:00 |
+-------------+--------------+----------------------------+--------------------+

import { Model } from 'tspace-mysql'

class User extends Model {
  constructor(){
    super()
    this.hasMany({ name : 'posts' , model : Post })
    this.useSoftDelete()
  }
}

+--------------------------------------------------------------------------+

class Post extends Model {
    constructor(){
        super()
        this.hasMany({ name : 'comments' , model : Comment })
        this.belongsTo({ name : 'user' , model : User })
        this.useSoftDelete()
    }
}

// normal relations
await new User().relations('posts').findMany()
// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL;
// SELECT * FROM `posts` WHERE `posts`.`user_id` IN (...) AND `posts`.`deleted_at` IS NULL;

/*
 * @returns [
 *  {
 *      id : 1,
 *      username:  "tspace1",
 *      email : "[email protected]",
 *      posts : []
 *  }
 *  {
 *      id : 2,
 *      username:  "tspace2",
 *      email : "[email protected]",
 *      posts : [
 *        {
 *          id : 2,
 *          user_id :  2,
 *          title : "posts 2"
 *        }
 *    ]
 *  }
 * ]
 */

// relationsTrashed
await new User().relationsTrashed('posts').findMany()
// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL;
// SELECT * FROM `posts` WHERE `posts`.`user_id` IN (...) AND `posts`.`deleted_at` IS NOT NULL;

/*
 * @returns [
 *  {
 *      id : 1,
 *      username:  "tspace1",
 *      email : "[email protected]",
 *      posts : [
 *       {
 *          id : 1,
 *          user_id :  1,
 *          title : "posts 1"
 *        }
 *      ]
 *  }
 *  {
 *      id : 2,
 *      username:  "tspace2",
 *      email : "[email protected]",
 *      posts : []
 *  }
 * ]
 */

// relationsTrashed + trashed
await new User().relationsTrashed('posts').trashed().findMany()
// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NOT NULL;
// SELECT * FROM `posts` WHERE `posts`.`user_id` IN (...) AND `posts`.`deleted_at` IS NOT NULL;
/*
 * @returns [
 *  {
 *      id : 3,
 *      username:  "tspace3",
 *      email : "[email protected]",
 *      posts : [
 *        {
 *          id : 3,
 *          user_id :  3,
 *          title : "posts 3"
 *        }
 *      ]
 *  }
 * ]
 */

Built in Relation Functions

Certainly, let's illustrate the use of a built-in function in the results of relationships:

import { Model } from 'tspace-mysql'

class User extends Model {
    constructor(){
        super()
        this.hasMany({ name : 'posts

Published

Vulnerabilities

Links

Maintainers

Keywords

Readme

tspace-mysql

Feature

Install

Basic Usage

Configuration

Query Builder

Table Name & Alias Name

Returning Results

Query Statements

Select Statements

Raw Expressions

Ordering, Grouping, Limit and Offset

Ordering

Grouping

Limit and Offset

Joins

Inner Join Clause

Left Join, Right Join Clause

Cross Join Clause

Basic Where Clauses

Where Clauses

Or Where Clauses

Where Object Clauses

JSON Where Clauses

Additional Where Clauses

Logical Grouping

Advanced Where Clauses

Where Exists Clauses

Subquery Where Clauses

Conditional Where Clauses

Paginating

Insert Statements

Update Statements

Delete Statements

Hook Statements

Faker Statements

Unset Statements

Common Table Expressions

More Methods

Database Transactions

Connection

Backup

Injection

Generating Model Classes

Model Conventions

Basic Model Setup

Table Name

Pattern

UUID

Timestamp

Debug

Observer

Logger

Hooks

Global Scope

Joins Model

Inner Join Model Clause

Left Join, Right Join Model Clause

Cross Join Model Clause

Relationships

One To One

One To Many

Belongs To

Many To Many

Relation

Deeply Nested Relations

Relation Exists

Relation Count

Relation Trashed

Built in Relation Functions