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:

1npm install tspace-mysql --save
2npm install tspace-mysql -g

Basic Usage

• Configuration
• SQL Like
• Query Builder
  • Table Name & Alias Name
  • Returning Results
  • Query Statement
  • 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
  • GetGroupBy
  • 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
  • Schema
    • Schema Model
    • Validation
    • Sync
  • SoftDelete
  • 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
  • Cache
  • Decorator
  • Type Safety
    • Safety Select
    • Safety OrderBy
    • Safety GroupBy
    • Safety Where
    • Safety Insert
    • Safety Update
    • Safety Delete
    • Safety Relationships
    • Safety Result
• Repository
  • Repository Select Statements
  • Repository Insert Statements
  • Repository Update Statements
  • Repository Delete Statements
  • Repository Transactions
  • Repository Relations
• Blueprint
• Cli
  • Make Model
  • Make Migration
  • Migrate
  • Query
  • Dump
  • Generate Models
  • Migration Models
  • Migration DB

Configuration

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

1DB_HOST = localhost;
2DB_PORT = 3306;
3DB_USERNAME = root;
4DB_PASSWORD = password;
5DB_DATABASE = database;
6
7/**
8 * @default
9 *  DB_CONNECTION_LIMIT = 10
10 *  DB_QUEUE_LIMIT      = 0
11 *  DB_TIMEOUT          = 60000
12 *  DB_DATE_STRINGS     = false
13 */

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

1source db {
2    host               = localhost
3    port               = 3306
4    database           = npm
5    user               = root
6    password           = database
7    connectionLimit    = 10
8    dateStrings        = true
9    connectTimeout     = 60000
10    waitForConnections = true
11    queueLimit         = 0
12    charset            = utf8mb4
13}
14

SQL Like

1import { sql , OP }  from 'tspace-mysql'
2
3// select
4await sql()
5  .select('id','name')
6  .from('users')
7  .where({
8    'name' : 'tspace'
9    'id' : OP.in([1,2,3])
10  })
11  .limit(3)
12  .orderBy('name')
13
14// insert
15await sql()
16.insert('users')
17.values({
18  email : 'tspace@example.com'
19})
20
21// insert return data
22await sql()
23.insert('users')
24.values({
25  email : 'tspace@example.com'
26})
27.returning({
28  id : true,
29  email : true,
30  enum : true
31})
32
33// update
34await sql()
35.update('users')
36.where({
37  id : 1
38})
39.set({
40  email : 'tspace@example.com'
41})
42
43// update return data
44await sql()
45.update('users')
46.where({
47  id : 1
48})
49.set({
50  email : 'tspace@example.com'
51})
52.returning()
53
54//delete
55await sql()
56.delete('users')
57.where({
58  id : 1
59})
60
61

Query Builder

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

1+-------------+--------------+----------------------------+
2|                     table users                         |
3+-------------+--------------+----------------------------+
4| id          | username     | email                      |
5|-------------|--------------|----------------------------|
6| 1           | tspace       | tspace@gmail.com           |
7| 2           | tspace2      | tspace2@gmail.com          |
8+-------------+--------------+----------------------------+
9
10
11+-------------+--------------+----------------------------+
12|                     table posts                         |
13+-------------+--------------+----------------------------+
14| id          | user_id      | title                      |
15|-------------|--------------|----------------------------|
16| 1           | 1            | posts tspace               |
17| 2           | 2            | posts tspace2              |
18+-------------+--------------+----------------------------+
19

Table Name & Alias Name

1import { DB }  from 'tspace-mysql'
2
3await new DB().from('users').find(1)
4// SELECT * FROM `users` WHERE `users`.`id` = '1' LIMIT 1;
5
6await new DB().table('users').find(1)
7// SELECT * FROM `users` WHERE `users`.`id` = '1' LIMIT 1;
8
9await new DB().table('users').alias('u').find(1)
10// SELECT * FROM `users` AS `u` WHERE `u`.`id` = '1' LIMIT 1;
11
12await new DB().fromRaw('u',new DB('users').select('*').limit(1).toString()).find(1)
13// SELECT * FROM ( SELECT * FROM `users` LIMIT 1 ) AS `u` WHERE `u`.`id` = '1' LIMIT 1;
14
15await new DB().alias('u',new DB('users').select('*').limit(1).toString()).find(1)
16// SELECT * FROM ( SELECT * FROM `users` LIMIT 1 ) AS `u` WHERE `u`.`id` = '1' LIMIT 1;
17

Returning Results

1const user = await new DB("users").find(1); // Object or null
2
3const user = await new DB("users").findOne(); // Object or null
4
5const user = await new DB("users").first(); // Object or null
6
7const user = await new DB("users").firstOrError(message); // Object or error
8
9const users = await new DB("users").findMany(); // Array-object of users
10
11const users = await new DB("users").get(); // Array-object of users
12
13const users = await new DB("users").getGroupBy('name') // Map
14
15const users = await new DB("users").findGroupBy('name') // Map
16
17const users = await new DB("users").toArray(); // Array of users
18
19const users = await new DB("users").toJSON(); // JSON of users
20
21const user = await new DB("users").exists(); // Boolean true if user exists otherwise false
22
23const user = await new DB("users").count(); // Number of users counted
24
25const user = await new DB("users").avg(); // Number of users avg
26
27const user = await new DB("users").sum(); // Number of users sum
28
29const user = await new DB("users").max(); // Number of users max
30
31const user = await new DB("user").min(); // Number of users min
32
33const users = await new DB("users").toString(); // sql query string
34
35const users = await new DB("users").toSQL(); // sql query string
36
37const users = await new DB("users").toRawSQL(); // sql query string
38
39const users = await new DB("users").pagination(); // Object of pagination
40
41const users = await new DB("users").makeSelectStatement() // query string for select statement
42
43const users = await new DB("users").makeInsertStatement() // query string for insert statement
44
45const users = await new DB("users").makeUpdateStatement() // query string for update statement
46
47const users = await new DB("users").makeDeleteStatement() // query string for delete statement
48
49const users = await new DB("users").makeCreateTableStatement() // query string for create table statement
50

Query Statements

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

Select Statements

1const select = await new DB("users").select("id", "username").findOne();
2// SELECT `users`.`id`, `users`.`username` FROM `users` LIMIT 1;
3
4const selectRaw = await new DB("users").selectRaw("COUNT(id)").findMany();
5// SELECT COUNT(id) FROM `users`;
6// You can also use the DB.raw() function
7// const selectRaw = await new DB("users").selec(DB.raw("COUNT(id)")).findMany();
8
9const selectObject = await new DB("posts")
10  .join("posts.user_id", "users.id")
11  .select("posts.*")
12  .selectObject(
13    { id: "users.id", name: "users.name", email: "users.email" },
14    "user"
15  )
16  .findOne();
17
18/** 
19SELECT 
20  posts.*, JSON_OBJECT('id' , `users`.`id` , 'name' , `users`.`name` , 'email' , `users`.`email`) AS `user`
21FROM `posts` 
22INNER JOIN `users` ON `posts`.`user_id` = `users`.`id` LIMIT 1;
23*/
24
25const selectArray = await new DB("users")
26  .select('id','name','email')
27  .join("users.id", "posts.user_id")
28  .select("posts.*")
29  .selectArray(
30    { id: "posts.id", user_id: "posts.user_id", title: "posts.title" },
31    "posts"
32  )
33  .findOne();
34/** 
35SELECT 
36  `users`.`id`, `users`.`name`, `users`.`email`, 
37  CASE WHEN COUNT(`posts`.`id`) = 0 THEN JSON_ARRAY() 
38  ELSE JSON_ARRAYAGG(JSON_OBJECT('id' , `posts`.`id` , 'user_id' , `posts`.`user_id` , 'email' , `posts`.`title`)) 
39  END AS `posts` 
40FROM `users` 
41INNER JOIN `posts` ON `users`.`id` = `posts`.`user_id` WHERE `users`.`deletedAt` IS NULL GROUP BY `users`.`id` LIMIT 1;
42*/
43
44await new DB("users").except("id").findOne();
45// SELECT `users`.`email`, `users`.`username` FROM `users` LIMIT 1;
46
47await new DB("users").distinct().select("id").findOne();
48// SELECT DISTINCT `users`.`id` FROM `users` LIMIT 1;

Raw Expressions

1const users = await new DB("users")
2  .select(DB.raw("COUNT(`username`) as c"), "username")
3  .groupBy("username")
4  .having("c > 1")
5  .findMany();
6// SELECT COUNT(`username`) as c, `users`.`username` FROM `users` GROUP BY `username` HAVING c > 1;
7
8const users = await new DB("users")
9  .where(
10    "id",
11    DB.raw(new DB("users").select("id").where("id", "1").limit(1).toString())
12  )
13  .findMany();
14// SELECT * FROM `users` WHERE `users`.`id` = (SELECT `users`.`id` FROM `users` WHERE `users`.`id` = '1' LIMIT 1);
15
16const findFullName = await new User()
17.select('name',`${DB.raw('CONCAT(firstName," ",lastName) as fullName')}`)
18.whereRaw(`CONCAT(firstName," ",lastName) LIKE '%${search}%'`)
19.findOne()     
20//  SELECT `users`.`name`, CONCAT(firstName," ",lastName) as fullName FROM `users` WHERE CONCAT(firstName," ",lastName) LIKE '%search%' LIMIT 1;
21

Ordering, Grouping, Limit and Offset

Ordering

1await new DB("users").orderBy("id", "asc").findOne();
2// SELECT * FROM `users` ORDER BY `id` ASC LIMIT 1;
3
4await new DB("users").orderBy("id", "desc").findOne();
5// SELECT * FROM `users` ORDER BY `id` DESC LIMIT 1;
6
7await new DB("users").oldest("id").findOne();
8// SELECT * FROM `users` ORDER BY `id` ASC LIMIT 1;
9
10await new DB("users").latest("id").findOne();
11// SELECT * FROM `users` ORDER BY `id` DESC LIMIT 1;
12
13await new DB("users").random().findMany();
14// SELECT * FROM `users` ORDER BY RAND();

Grouping

1await new DB("users").groupBy("id").findOne();
2// SELECT * FROM `users` GROUP BY `id` LIMIT 1;
3
4await new DB("users").groupBy("id", "username").findOne();
5// SELECT * FROM `users` GROUP BY `id`, `username` LIMIT 1;
6
7await new DB("users")
8  .select(DB.raw("COUNT(username) as c"), "username")
9  .groupBy("username")
10  .having("c > 1")
11  .findMany();
12// SELECT COUNT(username) as c, `users`.`username` FROM `users` GROUP BY `username` HAVING c > 1;

Limit and Offset

1await new DB("users").limit(5).findMany();
2// SELECT * FROM `users` LIMIT 5;
3
4await new DB("users").limit(-1).findMany();
5// SELECT * FROM `users` LIMIT 2147483647; // int-32  2**31 - 1
6
7await new DB("users").offset(1).findOne();
8// SELECT * FROM `users` LIMIT 1 OFFSET 1;

Joins

Inner Join Clause

1await new DB("posts").join("posts.user_id", "users.id").findMany();
2// SELECT * FROM `posts` INNER JOIN `users` ON `posts`.`user_id` = `users`.`id`;
3
4await new DB("posts")
5.join((join) => {
6  return join
7  .on('posts.user_id','users.id')
8  .on('users.id','post_user.user_id')
9  .and('users.id','posts.user_id')
10})
11.findMany();
12
13// SELECT * FROM `posts` 
14// INNER JOIN `users` ON `posts`.`user_id` = `users`.`id` 
15// INNER JOIN `post_user` ON `users`.`id` = `post_user`.`user_id` AND `users`.`id` = `posts`.`user_id`;

Left Join, Right Join Clause

1await new DB("posts").leftJoin("posts.user_id", "users.id").findMany();
2// SELECT * FROM `posts` LEFT JOIN `users` ON `posts`.`user_id` = `users`.`id`;
3
4await new DB("posts").rightJoin("posts.user_id", "users.id").findMany();
5// SELECT * FROM `posts` RIGHT JOIN `users` ON `posts`.`user_id` = `users`.`id`;

Cross Join Clause

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

Basic Where Clauses

Where Clauses

1const users = await new DB("users").where("id", 1).findMany();
2// SELECT * FROM `users` WHERE `users`.`id` = '1'
3
4const users = await new DB("users")
5  .where("id", 1)
6  .where("username", "try to find")
7  .findMany();
8// SELECT * FROM `users` WHERE `users`.`id` = '1' and `users`.`username` = 'try to find'
9
10const users = await new DB("users").where("id", ">", 1).findMany();
11// SELECT * FROM `users` WHERE `users`.`id` > '1';
12
13const users = await new DB("users").where("id", "<>", 1).findMany();
14// SELECT * FROM `users` WHERE `users`.`id` <> '1';

Or Where Clauses

1const users = await new DB("users").where("id", 1).orWhere("id", 2).findMany();
2// SELECT * FROM `users` WHERE `users`.`id` = '1' OR `users`.`id` = '2'
3
4const users = await new DB("users")
5  .where("id", 1)
6  .whereQuery((query) => {
7    return query
8      .where("id", "<>", 2)
9      .orWhere("username", "try to find")
10      .orWhere("email", "find@example.com");
11  })
12  .findMany();
13// SELECT * FROM `users` WHERE `users`.`id` = '1'
14// AND
15// ( `users`.`id` <> '2' OR `users`.`username` = 'try to find' OR `users`.`email` = 'find@example.com');
16

Where Object Clauses

1import { OP } from 'tspace-mysql'
2
3const whereObject = await new DB("users")
4  .whereObject({
5    id :  OP.notEq(1),
6    username :  OP.in(['user1','user2']),
7    name :  OP.like('%value%')
8  })
9  .findMany();
10
11// SELECT * FROM `users` WHERE `users`.`id` <> '1' AND `users`.`username` = 'user1' AND `users`.`name` LIKE '%value%';
12

JSON Where Clauses

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

Additional Where Clauses

1const users = await new DB("users").whereIn("id", [1, 2]).findMany();
2// SELECT * FROM `users` WHERE `users`.`id` IN ('1','2');
3
4const users = await new DB("users").whereNotIn("id", [1, 2]).findMany();
5// SELECT * FROM `users` WHERE `users`.`id` NOT IN ('1','2');
6
7const users = await new DB("users").whereBetween("id", [1, 2]).findMany();
8// SELECT * FROM `users` WHERE `users`.`id` BETWEEN '1' AND '2';
9
10const users = await new DB("users").whereNotBetween("id", [1, 2]).findMany();
11// SELECT * FROM `users` WHERE `users`.`id` NOT BETWEEN '1' AND '2';
12
13const users = await new DB("users").whereNull("username").findMany();
14// SELECT * FROM `users` WHERE `users`.`username` IS NULL;
15
16const users = await new DB("users").whereNotNull("username").findMany();
17// SELECT * FROM `users` WHERE `users`.`username` IS NOT NULL;

Logical Grouping

1const users = await new DB("users")
2  .whereQuery((query) => query.where("id", 1).where("username", "values"))
3  .whereIn("id", [1, 2])
4  .findOne();
5// SELECT * FROM `users` WHERE ( `users`.`id` = '1' AND `users`.`username` = 'values') AND `users`.`id` IN ('1','2'') LIMIT 1;
6
7const users = await new DB("users")
8  .where("id", 1)
9  .whereQuery((query) => {
10    return query
11      .where("id", "<>", 2)
12      .where("username", "try to find")
13      .where("email", "find@example.com");
14  })
15  .findMany();
16// SELECT * FROM `users` WHERE `users`.`id` = '1'
17// AND
18// ( `users`.`id` <> '2' AND `users`.`username` = 'try to find' AND `users`.`email` = 'find@example.com');
19
20const users = await new DB("users")
21  .whereAny(["name", "username", "email"], "like", `%v%`)
22  .findMany();
23// SELECT * FROM `users` WHERE ( `users`.`name` LIKE '%v%' OR `users`.`username` LIKE '%v%' OR `users`.`email` LIKE '%v%');
24
25const users = await new DB("users")
26  .whereAll(["name", "username", "email"], "like", `%v%`)
27  .findMany();
28// SELECT * FROM `users` WHERE ( `users`.`name` LIKE '%v%' AND `users`.`username` LIKE '%v%' AND `users`.`email` LIKE '%v%');

Advanced Where Clauses

Where Exists Clauses

1const users = await new DB("users")
2  .whereExists(new DB("users").select("id").where("id", 1).toString())
3  .findMany();
4// SELECT * FROM `users` WHERE EXISTS (SELECT `id` FROM `users` WHERE id = 1);
5
6const users = await new DB("users")
7  .wherNoteExists(new DB("users").select("id").where("id", 1).toString())
8  .findMany();
9// SELECT * FROM `users` WHERE NOT EXISTS (SELECT `id` FROM `users` WHERE id = 1);

Subquery Where Clauses

1const users = await new DB("users")
2  .whereSubQuery("id", "SELECT id FROM users")
3  .findMany();
4// SELECT * FROM `users` WHERE `users`.`id` IN (SELECT id FROM users);
5
6const users = await new DB("users")
7  .whereSubQuery("id", new DB("users").select("id").toString())
8  .findMany();
9// SELECT * FROM `users` WHERE `users`.`id` IN (SELECT id FROM users);
10
11const users = await new DB("users")
12  .whereSubQuery(
13    "id",
14    new DB("users")
15      .select("id")
16      .whereSubQuery("id", new DB("posts").select("user_id").toString())
17      .toString()
18  )
19  .findMany();
20/*
21  SELECT * FROM `users` 
22  WHERE `users`.`id`
23    IN (
24      SELECT `users`.`id` FROM `users` 
25      WHERE `users`.`id` 
26        IN (
27          SELECT `posts`.`user_id` FROM `posts` 
28        )
29    );
30*/

Conditional Where Clauses

1const users = await new DB("users")
2  .where("id", 1)
3  .when(true, (query) => query.where("username", "when is actived"))
4  .findMany();
5// SELECT * FROM `users` WHERE `users`.`id` = '1' AND `users`.`username` = 'when is actived';
6
7const users = await new DB("users")
8  .where("id", 1)
9  .when(false, (query) => query.where("username", "when is actived"))
10  .findMany();
11// SELECT * FROM `users` WHERE `users`.`id` = '1';

GetGroupBy

1const data = await new DB("posts").getGroupBy('user_id')
2
3// return new Map()
4// find posts by user id
5const userHasPosts = data.get(1)
6
7console.log(userHasPosts)
8

Paginating

1const users = await new DB("users").paginate();
2// SELECT * FROM `users` LIMIT 15 OFFSET 0;
3// SELECT COUNT(*) AS total FROM `users`;
4
5const pageTwoUsers = await new DB("users").paginate({ page: 2, limit: 5 });
6
7/*
8  SELECT * FROM `users` LIMIT 5 OFFSET 5;
9  SELECT COUNT(*) AS total FROM `users`;
10  
11  the results are returned
12  {
13    meta: {
14      total: n,
15      limit: 5,
16      total_page: 5,
17      current_page: 2,
18      last_page: n,
19      next_page: 3,
20      prev_page: 1
21    },
22    data: [...your data here]
23  }
24
25*/

Insert Statements

1const user = await new DB("users")
2  .create({
3    name: "tspace3",
4    email: "tspace3@gmail.com",
5  })
6  .save();
7/**
8  INSERT INTO `users` 
9    (`users`.`name`,`users`.`email`) 
10  VALUES 
11    ('tspace3','tspace3@gmail.com');
12
13  -- then return the result inserted --
14  SELECT * FROM `users` WHERE `users`.`id` = ${INSERT ID};
15*/
16
17const users = await new DB("users")
18  .createMultiple([
19    {
20      name: "tspace4",
21      email: "tspace4@gmail.com",
22    },
23    {
24      name: "tspace5",
25      email: "tspace5@gmail.com",
26    },
27    {
28      name: "tspace6",
29      email: "tspace6@gmail.com",
30    },
31  ])
32  .save();
33
34/**
35  INSERT INTO `users` 
36    (`users`.`name`,`users`.`email`)
37  VALUES
38    ('tspace4','tspace4@gmail.com'), 
39    ('tspace5','tspace5@gmail.com'), 
40    ('tspace6','tspace6@gmail.com');
41*/
42
43const users = await new DB("users")
44  .where("name", "tspace4")
45  .where("email", "tspace4@gmail.com")
46  .createNotExists({
47    name: "tspace4",
48    email: "tspace4@gmail.com",
49  })
50  .save();
51/*
52  -- if exists return null, if not exists created new data --
53  SELECT EXISTS(
54    SELECT 1 FROM `users` 
55    WHERE `users`.`name` = 'tspace4' 
56    AND `users`.`email` = 'tspace4@gmail.com' 
57    LIMIT 1
58  ) AS 'exists';
59
60  INSERT INTO `users` (`users`.`name`,`users`.`email`) VALUES ('tspace4','tspace4@gmail.com');
61*/
62
63const users = await new DB("users")
64  .where("name", "tspace4")
65  .where("email", "tspace4@gmail.com")
66  .createOrSelect({
67    name: "tspace4",
68    email: "tspace4@gmail.com",
69  })
70  .save();
71/**
72  -- if has exists return data, if not exists created new data --
73  SELECT EXISTS(
74    SELECT 1 FROM `users` 
75    WHERE `users`.`name` = 'tspace4' 
76    AND `users`.`email` = 'tspace4@gmail.com' 
77    LIMIT 1
78  ) AS 'exists';
79
80  INSERT INTO `users` (`users`.`name`,`users`.`email`) VALUES ('tspace4','tspace4@gmail.com');
81
82  SELECT * FROM `users` WHERE `users`.`id` = '4';
83*/

Update Statements

1const user = await new DB("users")
2  .where("id", 1)
3  .update({
4    name: "tspace1**",
5    email: "tspace1@gmail.com",
6  })
7  .save();
8/**
9
10 UPDATE `users` SET 
11  `users`.`name` = 'tspace1',
12  `users`.`email` = 'tspace1@gmail.com' 
13 WHERE `users`.`id` = '1' LIMIT 1;
14
15 */
16
17const user = await new DB("users")
18  .where("id", 1)
19  .updateMany({
20    name: "tspace1",
21    email: "tspace1@gmail.com",
22  })
23  .save();
24/**
25 UPDATE `users` SET 
26 `users`.`name` = 'tspace1',
27 `users`.`email` = 'tspace1@gmail.com' 
28 WHERE `users`.`id` = '1';
29 */
30
31const user = await new DB("users")
32  .where("id", 1)
33  .update(
34    {
35      name: "tspace1",
36      email: "tspace1@gmail.com",
37    },
38    ["name"]
39  )
40  .save();
41/**
42  UPDATE `users` SET 
43    `name` = 
44    CASE WHEN (`name` = '' OR `name` IS NULL) 
45    THEN 'tspace1' ELSE `name` 
46    END,
47    `email` = 
48    'tspace1@gmail.com' 
49    WHERE `users`.`id` = '1' LIMIT 1;
50 */
51
52const user = await new DB("users")
53  .updateMultiple([
54    {
55      when: {
56        id: 1,
57        name: "name1",
58      },
59      columns: {
60        name: "update row1",
61        email: "row1@example.com",
62      },
63    },
64    {
65      when: {
66        id: 2,
67      },
68      columns: {
69        name: "update row2",
70        email: "row2@example.com",
71      },
72    },
73  ])
74  .save();
75
76/** 
77 UPDATE `users` SET 
78 `users`.`name` = ( 
79  CASE WHEN `users`.`id` = '1' 
80    AND `users`.`name` = 'name1' 
81  THEN 'update row1' 
82    WHEN `users`.`id` = '2' 
83  THEN 'update row2' 
84    ELSE `users`.`name` 
85  END 
86  ), 
87  `users`.`email` = ( 
88    CASE WHEN `users`.`id` = '1' 
89      AND `users`.`name` = 'name1' 
90    THEN 'row1@example.com' 
91      WHEN `users`.`id` = '2' 
92    THEN 'row2@example.com' 
93      ELSE `users`.`email` 
94    END 
95  ) 
96  WHERE `users`.`id` IN ('1','2') LIMIT 2;
97
98 */
99
100const user = await new DB("users")
101  .where("id", 1)
102  .updateOrCreate({
103    name: "tspace1**",
104    email: "tspace1@gmail.com",
105  })
106  .save();
107// if has exists return update, if not exists created new data
108// UPDATE `users` SET `name` = 'tspace1**',`email` = 'tspace1@gmail.com' WHERE `users`.`id` = '1' LIMIT 1;
109// INSERT INTO `users` (`name`,`email`) VALUES ('tspace1**','tspace1@gmail.com');

Delete Statements

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

Hook Statements

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

Faker Statements

1await new DB("users").faker(2);
2/**
3  INSERT INTO `users`  
4    (`users`.`username`,`users`.`email`)
5  VALUES
6    ('ivsvtagyta86n571z9d81maz','fxcwkubccdi5ewos521uqexy'),
7    ('rnr4esoki7fgekmdtarqewt','gv0mzb1m3rlbinsdyb6')
8 */
9
10// custom faker
11await new DB("users").faker(5, (row, index) => {
12  return {
13    username: `username-${index + 1}`,
14    email: `email-${index + 1}`,
15  };
16});
17
18/**
19  
20INSERT INTO `users` 
21  (`users`.`username`,`users`.`email`) 
22VALUES 
23  ('username-1','email-1'),
24  ('username-2','email-2'),
25  ('username-3','email-3'),
26  ('username-4','email-4'),
27  ('username-5','email-5');
28
29 */
30
31// fast to create
32await new DB("users").faker(40_000);

Unset Statements

1
2const userInstance = new User().where('email','test@gmail.com')
3  
4const exits = await userInstance.exists()
5// SELECT EXISTS (SELECT 1 FROM `users` WHERE `users`.`email` = 'test@gmail.com' LIMIT 1) AS `aggregate`;
6
7const user = await userInstance.orderBy('id').findOne()
8// SELECT * FROM `users` WHERE `users`.`email` = 'test@gmail.com' ORDER BY `users`.`id` DESC LIMIT 1;
9
10const users = await userInstance.select('id').unset({ limit : true }).findMany()
11// SELECT `users`.`id` FROM `users` WHERE `users`.`email` = 'test@gmail.com' ORDER BY `users`.`id` DESC;
12
13const usersUnsetWhereStatement = await userInstance.unset({ select : true, where : true , orderBy : true }).findMany()
14// SELECT * FROM `users` WHERE `users`.`deletedAt` IS NULL;
15

Common Table Expressions

1
2const user = await new User()
3.CTEs('z', (query) => {
4  return query
5  .from('posts')
6})
7.CTEs('x', (query) => {
8  return query
9  .from('post_user')
10})
11.select('users.*','x.*','z.*')
12.join('users.id','x.user_id')
13.join('users.id','z.user_id')
14.findOne()
15
16// WITH z AS (SELECT posts.* FROM `posts`), 
17// x AS (SELECT * FROM `post_user`) 
18// 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;
19

More Methods

1where(column , OP , value)
2whereSensitive(column , OP , value)
3whereId(id)
4whereUser(userId)
5whereEmail(value)
6whereIn(column , [])
7whereNotIn(column , [])
8whereNull(column)
9whereNotNull(column)
10whereBetween (column , [value1 , value2])
11whereQuery(callback)
12whereJson(column, { targetKey, value , OP })
13whereRaw(sql)
14whereExists(sql)
15whereSubQuery(colmn , rawSQL)
16whereNotSubQuery(colmn , rawSQL)
17orWhere(column , OP , value)
18orWhereRaw(sql)
19orWhereIn(column , [])
20orWhereSubQuery(colmn , rawSQL)
21when(contition , callback)
22select(column1 ,column2 ,...N)
23distinct()
24selectRaw(column1 ,column2 ,...N)
25except(column1 ,column2 ,...N)
26exceptTimestamp()
27only(column1 ,column2 ,...N)
28hidden(column1 ,column2 ,...N)
29join(primary key , table.foreign key)
30rightJoin (primary key , table.foreign key)
31leftJoin (primary key , table.foreign key)
32limit (limit)
33having (condition)
34havingRaw (condition)
35orderBy (column ,'ASC' || 'DSCE')
36orderByRaw(column ,'ASC' || 'DSCE')
37latest (column)
38latestRaw (column)
39oldest (column)
40oldestRaw (column)
41groupBy (column)
42groupByRaw (column)
43create(objects)
44createMultiple(array objects)
45update (objects)
46updateMany (objects)
47updateMultiple(array objects)
48createNotExists(objects)
49updateOrCreate (objects)
50onlyTrashed()
51connection(options)
52backup({ database , connection })
53backupToFile({ filePath, database , connection })
54hook((result) => ...) // callback result to function
55sleep(seconds)
56
57/**
58 * registry relation in your models
59 * @relationship
60 */
61hasOne({ name, model, localKey, foreignKey, freezeTable , as })
62hasMany({ name, model, localKey, foreignKey, freezeTable , as })
63belongsTo({ name, model, localKey, foreignKey, freezeTable , as })
64belongsToMany({ name, model, localKey, foreignKey, freezeTable, as, pivot })
65/**
66 * @relation using registry in your models
67 */
68relations(name1 , name2,...nameN) // with(name1, name2,...nameN)
69/**
70 * @relation using registry in your models ignore soft delete
71 */
72relationsAll(name1 , name2,...nameN) // withAll(name1, name2,...nameN)
73/**
74 * @relation using registry in your models. if exists child data remove this data
75 */
76relationsExists(name1 , name2,...nameN) // withExists(name1, name2,...nameN)
77/**
78 * @relation using registry in your models return only in trash (soft delete)
79 */
80relationsTrashed(name1 , name2,...nameN) // withTrashed(name1, name2,...nameN)
81/**
82 * @relation call a name of relation in registry, callback query of data
83 */
84relationQuery(name, (callback) ) // withQuery(name1, (callback))
85
86
87/**
88 * queries statements
89 * @execute data of statements
90*/
91findMany() // get()
92findOne() // first()
93find(id)
94delelte()
95delelteMany()
96exists()
97toString()
98toJSON()
99toArray(column)
100count(column)
101sum(column)
102avg(column)
103max(column)
104min(column)
105pagination({ limit , page })
106save() /* for actions statements insert or update */
107makeSelectStatement()
108makeInsertStatement()
109makeUpdateStatement()
110makeDeleteStatement()
111makeCreateTableStatement()
112

Database Transactions

Within a database transaction, you can utilize the following:

1const connection = await new DB().beginTransaction();
2
3try {
4  /**
5   *
6   * @startTransaction start transaction in scopes function
7   */
8  await connection.startTransaction();
9
10  const user = await new User()
11    .create({
12      name: `tspace`,
13      email: "tspace@example.com",
14    })
15    /**
16     *
17     * bind method for make sure this connection has same transaction in connection
18     * @params {Function} connection
19     */
20    .bind(connection)
21    .save();
22
23  const posts = await new Post()
24    .createMultiple([
25      {
26        user_id: user.id,
27        title: `tspace-post1`,
28      },
29      {
30        user_id: user.id,
31        title: `tspace-post2`,
32      },
33      {
34        user_id: user.id,
35        title: `tspace-post3`,
36      },
37    ])
38    .bind(connection) // don't forget this
39    .save();
40
41  /**
42   *
43   * @commit commit transaction to database
44   */
45  // After your use commit if use same connection for actions this transction will auto commit
46  await connection.commit();
47
48  // If you need to start a new transaction again, just use wait connection.startTransaction();
49
50  const postsAfterCommited = await new Post()
51    .createMultiple([
52      {
53        user_id: user.id,
54        title: `tspace-post1`,
55      },
56      {
57        user_id: user.id,
58        title: `tspace-post2`,
59      },
60      {
61        user_id: user.id,
62        title: `tspace-post3`,
63      },
64    ])
65    // Using this connection now will auto-commit to the database.
66    .bind(connection) // If you need to perform additional operations, use await connection.startTransaction(); again.
67    .save();
68
69   
70    // Do not perform any operations with this connection.
71    // The transaction has already been committed, and the connection is closed.
72    // Just ensure everything is handled at the end of the transaction.
73    await connection.end();
74  
75} catch (err) {
76  /**
77   *
78   * @rollback rollback transaction
79   */
80  await connection.rollback();
81}

Connection

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

1const connection = await new DB().getConnection({
2    host: 'localhost',
3    port : 3306,
4    database: 'database'
5    username: 'username',
6    password: 'password',
7})
8
9const users = await new DB('users')
10.bind(connection) // don't forget this
11.findMany()

Backup

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

1/**
2 *
3 * @param {string} database Database selected
4 * @param {object | null} to defalut new current connection
5 */
6const backup = await new DB().backup({
7    database: 'try-to-backup',  // clone current database to this database
8    to ?: {
9        host: 'localhost',
10        port : 3306,
11        username: 'username',
12        password: 'password',
13    }
14})
15/**
16 *
17 * @param {string} database Database selected
18 * @param {string} filePath file path
19 * @param {object | null} conection defalut current connection
20 */
21const backupToFile = await new DB().backupToFile({
22    database: 'try-to-backup',
23    filePath: 'backup.sql',
24    connection ?: {
25        host: 'localhost',
26        port : 3306,
27        database: 'database'
28        username: 'username',
29        password: 'password',
30    }
31})
32// backupToFile => backup.sql
33
34/**
35 *
36 * @param {string} database new db name
37 */
38await new DB().cloneDB('try-to-clone')
39

Injection

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

1const input = "admin' OR '1'='1";
2DB.escape(input);
3// "admin\' OR \'1\'=\'1"
4
5//XSS
6const input = "text hello!<script>alert('XSS attack');</script>";
7DB.escapeXSS(input);
8// "text hello!"

Generating Model Classes

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

1/**
2 *
3 * @install global command
4 */
5npm install tspace-mysql -g
6
7/**
8 *
9 * @make Model
10 */
11tspace-mysql make:model <model name> --dir=< directory >
12
13# tspace-mysql make:model User --dir=App/Models
14# 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:

1import { Model } from "tspace-mysql";
2// If you want to specify a global setting for the 'Model'
3Model.global({
4  uuid: true,
5  softDelete: true,
6  timestamp: true,
7  logger: true,
8});
9
10class User extends Model {
11  constructor() {
12    super();
13    /**
14     *
15     * Assign setting global in your model
16     * @useMethod
17     * this.usePattern('camelCase') // => default 'snake_case'
18     * this.useCamelCase()
19     * this.useSnakeCase()
20     * this.useLogger()
21     * this.useDebug()
22     * this.usePrimaryKey('id')
23     * this.useTimestamp({
24     *    createdAt : 'created_at',
25     *    updatedAt : 'updated_at'
26     * }) // runing a timestamp when insert or update
27     * this.useSoftDelete('deletedAt') // => default target to colmun deleted_at
28     * this.useTable('users')
29     * this.useTableSingular() // => 'user'
30     * this.useTablePlural() // => 'users'
31     * this.useUUID('uuid') // => runing a uuid (universally unique identifier) when insert new data
32     * this.useRegistry() // => build-in functions registry
33     * this.useLoadRelationsInRegistry() // => auto generated result from relationship to results
34     * this.useBuiltInRelationFunctions() // => build-in functions relationships to results
35     * this.useHooks([(r) => console.log(r)])
36     * this.useObserver(Observe)
37     * this.useSchema ({
38     *     id          : Blueprint.int().notNull().primary().autoIncrement(),
39     *     uuid        : Blueprint.varchar(50).null(),
40     *     name        : Blueprint.varchar(191).notNull(),
41     *     email       : Blueprint.varchar(191).notNull(),
42     *     created_at  : Blueprint.timestamp().null(),
43     *     updated_at  : Blueprint.timestamp().null(),
44     *     deleted_at  : Blueprint.timestamp().null()
45     *  }) // auto-generated table when table is not exists and auto-create column when column not exists
46     *
47     *  // validate input when create or update reference to the schema in 'this.useSchema'
48     *  this.useValidateSchema({
49     *   id : Number,
50     *   uuid :  Number,
51     *   name : {
52     *       type : String,
53     *       length : 191
54     *       require : true
55     *   },
56     *   email : {
57     *       type : String,
58     *       require : true,
59     *       length : 191,
60     *       match: /^[a-zA-Z0-9._]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/,
61     *       unique : true,
62     *       fn : (email : string) => !/^[a-zA-Z0-9._]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/.test(email)
63     *   },
64     *   created_at : Date,
65     *   updated_at : Date,
66     *   deleted_at : Date
67     *   })
68     */
69
70    /*
71     * the "snake case", plural name of the class will be used as the table name
72     *
73     * @param {string} name The table associated with the model.
74     */
75    this.useTable("users");
76  }
77}
78export { User };
79export default User;

Basic Model Setup

Table Name

1import { Model } from 'tspace-mysql'
2class User extends Model {
3    constructor() {
4      super()
5      // By default, the model knows that the table name for this User is 'users'
6
7      this.useTable('fix_table') // fixtable
8      this.useTablePlural() // users
9      this.useTableSingular() // user
10    }
11}
12

Pattern

1
2import { Model } from 'tspace-mysql'
3class UserPhone extends Model {
4    constructor() {
5      super()
6      // By default, the model is pattern snake_case
7      // The table name is user_phones
8      this.useSnakeCase()
9
10      this.useCamelCase()
11       // The table name is userPhones
12    }
13}
14
15// set the pattern CamelCase for the model
16const userPhone = await new UserPhone().where('user_id',1).findOne()
17// covert 'user_id' to 'userId'
18// SELECT * FROM `userPhones` WHERE `userPhones`.`userId` = '1' LIMIT 1;
19
20// avoid the pattern CamelCase for the model
21const userPhone = await new UserPhone().where(DB.freeze('user_id'),1).findOne()
22// SELECT * FROM `userPhones` WHERE `userPhones`.`user_id` = '1' LIMIT 1;
23

UUID

1
2import { Model } from 'tspace-mysql'
3class User extends Model {
4  constructor() {
5    super()
6    this.useUUID() // insert uuid when creating
7  }
8}
9

Timestamp

1
2import { Model } from 'tspace-mysql'
3class User extends Model {
4  constructor() {
5    super()
6    // insert created_at and updated_at when creating
7    // update updated_at when updating
8    // 'created_at' and 'updated_at' still relate to pettern the model
9    // this.useCamelCase() will covert 'created_at' to 'createdAt' and 'updated_at' to 'updatedAt'
10    this.useTimestamp() 
11
12    // custom the columns
13    this.useTimestamp({
14      createdAt : 'createdAtCustom',
15      updatedAt : 'updatedAtCustom'
16    })
17
18  }
19}
20

Debug

1
2import { Model } from 'tspace-mysql'
3class User extends Model {
4    constructor() {
5      super()
6      this.useDebug() // show the query sql in console when executing
7    }
8}
9

Observer

1
2class Observe {
3
4  public selected(results) {
5    console.log({ results , selected : true })
6  }
7
8  public created(results) {
9      console.log({ results , created : true })
10  }
11
12  public updated(results) {
13    console.log({ results , updated : true })
14  }
15
16  public deleted(results) {
17    console.log({ results , deleted : true })
18  }
19}
20
21import { Model } from 'tspace-mysql'
22class User extends Model {
23  constructor() {
24    super()
25    this.useObserver(Observe) // returning to the observers by statements
26  }
27}
28

Logger

1
2import { Model } from 'tspace-mysql'
3class User extends Model {
4  constructor() {
5    super()
6    // keep logging everything except select  to the table '$loggers'
7    // the table will automatically be created
8    this.useLogger()
9
10    // keep logging everything
11    this.useLogger({
12      selected : true,
13      inserted : true,
14      updated  : true,
15      deleted  : true,
16    })
17  }
18}
19

Hooks

1
2import { Model } from 'tspace-mysql'
3class User extends Model {
4  constructor() {
5    super()
6      // when executed will returning the results to any hooks function
7      this.useHooks([
8        (results1) => console.log(results1),
9        (results2) => console.log(results2),
10        (results3) => console.log(results3)
11      ])
12  }
13}
14

Global Scope

1
2class User extends Model {
3  constructor() {
4    super()
5
6    // Every query will have the global scope applied.
7    this.globalScope((query : User) => {
8      return query.select('id').where('id' , '>' , 10).orderBy('id')
9    })
10  }
11}
12
13const user = await new User().findMany()
14
15// SELECT `users`.`id` FROM `users` WHERE `users`.`id` > '10' ORDER BY `users`.`id` ASC LIMIT 1
16

Joins Model

Inner Join Model Clause

1await new User().joinModel(User, Post).findMany();
2// SELECT `users`.`id`, `users`.`email`, `users`.`username` FROM `users` INNER JOIN `posts` ON `users`.`id` = `posts`.`user_id`;
3
4// if the model use soft delete
5await new User().joinModel(User, Post).findMany();
6// SELECT `users`.`id`, `users`.`email`, `users`.`username` FROM `users` 
7// INNER JOIN `posts` ON `users`.`id` = `posts`.`user_id` 
8// WHERE `posts`.`deleted_at` IS NULL AND `users`.`deleted_at` IS NULL;
9
10await new User().select(`${User.table}.*`,`${Post.table}.*`).joinModel(User, Post).findMany();
11// SELECT users.*, posts.* FROM `users` 
12// INNER JOIN `posts` ON `users`.`id` = `posts`.`user_id` 
13// WHERE `posts`.`deleted_at` IS NULL AND `users`.`deleted_at` IS NULL;
14
15await new User().select('u.*','p.*')
16.joinModel({ model : User , key : 'id' , alias : 'u' }, { model : Post , key : 'user_id', alias : 'p'})
17.findMany();
18// SELECT u.*, p.* FROM `users` AS `u` 
19// INNER JOIN `posts` AS `p` ON `u`.`id` = `p`.`user_id` 
20// WHERE `p`.`deleted_at` IS NULL AND `u`.`deleted_at` IS NULL;
21
22await new DB("posts")
23.join((join) => {
24  return join
25  .on('posts.user_id','users.id')
26  .on('users.id','post_user.user_id')
27  .and('users.id','posts.user_id')
28})
29.findMany()
30//  SELECT * FROM `posts` 
31// INNER JOIN `users` ON `posts`.`user_id` = `users`.`id` 
32// INNER JOIN `post_user` ON `users`.`id` = `post_user`.`user_id` AND `users`.`id` = `posts`.`user_id`;

Left Join, Right Join Model Clause

1await new User().leftJoinModel(User, Post).findMany();
2//  SELECT `users`.`id`, `users`.`email`, `users`.`username` FROM `users` LEFT JOIN `posts` ON `users`.`id` = `posts`.`user_id`;
3
4await new User().rightJoinModel(User, Post).findMany();
5//  SELECT `users`.`id`, `users`.`email`, `users`.`username` FROM `users` RIGHT JOIN `posts` ON `users`.`id` = `posts`.`user_id`;

Cross Join Model Clause

1await new User().crossJoinModel(User, Post).findMany();
2//  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

1import { Model } from 'tspace-mysql'
2import Phone  from '../Phone'
3class User extends Model {
4    constructor(){
5        super()
6        this.useTimestamp()
7        /**
8         *
9         * @hasOne Get the phone associated with the user.
10         * @relationship users.id -> phones.user_id
11         */
12        this.hasOne({ name : 'phone' , model : Phone })
13    }
14    /**
15     * Mark a method for relationship
16     * @hasOne Get the phone associated with the user. using function callback
17     * @function

18     */
19    phone (callback) {
20      return this.hasOneBuilder({ name : 'phone' , model : Phone } , callback)
21    }
22}
23export default User
24
25+--------------------------------------------------------------------------+
26
27import User from '../User'
28const user = await new User().relations('phone').findOne() // You can also use the method .with('roles').
29// user?.phone => {...}
30const userUsingFunction = await new User().phone().findOne()
31// 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.

1import { Model } from 'tspace-mysql'
2import Comment  from '../Comment'
3class Post extends Model {
4    constructor(){
5        super()
6        this.useTimestamp()
7        /**
8         *
9         * @hasMany Get the comments for the post.
10         * @relationship posts.id -> comments.post_id
11         */
12        this.hasMany({ name : 'comments' , model : Comment })
13    }
14    /**
15     *
16     * @hasManyQuery Get the comments for the post. using function callback
17     * @function

18     */
19    comments (callback) {
20        return  this.hasManyBuilder({ name : 'comments' , model : Comment } , callback)
21    }
22}
23export default Post
24
25+--------------------------------------------------------------------------+
26
27import Post from '../Post'
28const posts = await new Post().relations('comments').findOne()
29// posts?.comments => [{...}]
30const postsUsingFunction = await new Post().comments().findOne()
31// postsUsingFunction?.comments => [{...}]

Belongs To

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

1import { Model } from 'tspace-mysql'
2import User  from '../User'
3class Phone extends Model {
4    constructor(){
5        super()
6        this.useTimestamp()
7        /**
8         *
9         * @belongsTo Get the user that owns the phone.
10         * @relationship phones.user_id -> users.id
11         */
12        this.belognsTo({ name : 'user' , model : User })
13    }
14    /**
15     *
16     * @belongsToBuilder Get the user that owns the phone.. using function callback
17     * @function

18     */
19    user (callback) {
20        return this.belongsToBuilder({ name : 'user' , model : User }, callback)
21    }
22}
23export default Phone
24
25+--------------------------------------------------------------------------+
26
27import Phone from '../Phone'
28const phone = await new Phone().relations('user').findOne()
29// phone?.user => {...}
30const phoneUsingFunction = await new Phone().user().findOne()
31// phoneUsingFunction?.user => {...}

Many To Many

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

1import { Model } from 'tspace-mysql'
2import Role from '../Role'
3class User extends Model {
4    constructor(){
5        super()
6        this.useTimestamp()
7        /**
8         *
9         * @belongsToMany Get The roles that belong to the user.
10         * @relationship users.id , roles.id => role_user.user_id , role_user.role_id
11         */
12        this.belognsToMany({ name : 'roles' , model : Role })
13    }
14    /**
15     * @belongsToBuilder Get the user that owns the phone.. using function callback
16     * @function

17     */
18    roles (callback) {
19        return this.belognsToManyBuilder({ model : Role } , callback)
20    }
21}
22export default User
23
24+--------------------------------------------------------------------------+
25
26import User from '../User'
27const user = await new User().relations('roles').findOne()
28// user?.roles => [{...}]
29const userUsingFunction = await new User().roles().findOne()
30// user?.roles => [{...}]

Relation

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

1
2+-------------+--------------+----------------------------+
3|                     table users                         |                    
4+-------------+--------------+----------------------------+
5| id          | username     | email                      |
6|-------------|--------------|----------------------------|
7| 1           | tspace1      | tspace1@gmail.com          |
8| 2           | tspace2      | tspace2@gmail.com          |
9| 3           | tspace3      | tspace3@gmail.com          |
10+-------------+--------------+----------------------------+
11
12+-------------+--------------+----------------------------+
13|                     table posts                         |
14+-------------+--------------+----------------------------+
15| id          | user_id      | title                      |
16|-------------|--------------|----------------------------|
17| 1           | 1            | posts 1                    |
18| 2           | 1            | posts 2                    |
19| 3           | 3            | posts 3                    |
20+-------------+--------------+----------------------------+
21
22import { Model } from 'tspace-mysql'
23
24class User extends Model {
25    constructor(){
26      super()
27      this.hasMany({ name : 'posts' , model : Post })
28    }
29}
30
31class Post extends Model {
32    constructor(){
33      super()
34      this.belongsTo({ name : 'user' , model : User })
35    }
36}
37
38await new User()
39.relations('posts')
40.findOne()
41// SELECT * FROM `users` LIMIT 1;
42// SELECT * FROM `posts` WHERE `posts`.`userId` IN (...);
43
44/*
45 * @returns 
46 *  {
47 *      id : 1,
48 *      username:  "tspace1",
49 *      email : "tspace1@gmail.com",
50 *      posts : [
51 *        {
52 *           id : 1 ,
53 *           user_id : 1,
54 *           title : "post 1"
55 *        },
56 *        {
57 *           id : 2 ,
58 *           user_id : 1,
59 *           title : "post 2"
60 *        }
61 *      ]
62 *  }
63*/
64

Deeply Nested Relations

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

1import { Model } from 'tspace-mysql'
2
3class User extends Model {
4    constructor(){
5        super()
6        this.hasMany({ name : 'posts' , model : Post })
7    }
8}
9+--------------------------------------------------------------------------+
10class Post extends Model {
11    constructor(){
12        super()
13        this.hasMany({ name : 'comments' , model : Comment })
14        this.belongsTo({ name : 'user' , model : User })
15        this.belongsToMany({ name : 'users' , model : User , modelPivot : PostUser })
16    }
17}
18+--------------------------------------------------------------------------+
19class Comment extends Model {
20    constructor(){
21        super()
22        this.hasMany({ name : 'users' , model : User })
23        this.belongsTo({ name : 'post' , model : Post })
24    }
25}
26
27class PostUser extends Model {}
28+--------------------------------------------------------------------------+
29// Deeply nested relations
30await new User()
31.relations('posts')
32.relationQuery('posts', (query : Post) => {
33    return query
34    .relations('comments','user','users')
35    .relationQuery('comments', (query : Comment) => {
36        return query.relations('user','post')
37    })
38    .relationQuery('user', (query : User) => {
39        return query.relations('posts').relationQuery('posts',(query : Post)=> {
40            return query.relations('comments','user')
41            // relation n, n, ...n
42        })
43    })
44    .relationQuery('users', (query : User) => {
45        return query
46    })
47    .relationQuery('users', (query : PostUser) => {
48        return query
49    }, { pivot : true })
50})
51.findMany()
52
53// Select some columns in nested relations
54await new User()
55.relations('posts')
56.relationQuery('posts', (query : Post) => query.select('id','user_id','title'))
57.findMany()
58
59// Where some columns in nested relations
60await new User()
61.relations('posts')
62.relationQuery('posts', (query : Post) => query.whereIn('id',[1,3,5]))
63.findMany()
64
65// Sort data in  nested relations
66await new User()
67.relations('posts')
68.relationQuery('posts', (query : Post) => query.latest('id'))
69.findMany()
70
71// Limit data in  nested relations
72await new User()
73.relations('posts')
74.relationQuery('posts', (query : Post) => {
75    return query
76    .limit(1)
77    .relations('comments')
78    .relationQuery('comments', (query : Comment) => query.limit(1))
79})
80.findMany()
81

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:

1+-------------+--------------+----------------------------+--------------------+
2|                     table users                         |                    |
3+-------------+--------------+----------------------------+--------------------+
4| id          | username     | email                      | deleted_at         |
5|-------------|--------------|----------------------------|--------------------|
6| 1           | tspace1      | tspace1@gmail.com          |                    |
7| 2           | tspace2      | tspace2@gmail.com          |                    |
8| 3           | tspace3      | tspace3@gmail.com          |                    |
9+-------------+--------------+----------------------------+--------------------+
10
11
12+-------------+--------------+----------------------------+--------------------+
13|                     table posts                         |                    |
14+-------------+--------------+----------------------------+--------------------+
15| id          | user_id      | title                      | deleted_at         |
16|-------------|--------------|----------------------------|--------------------|
17| 1           | 1            | posts 1                    |2020-07-15 00:00:00 |
18| 2           | 2            | posts 2                    |                    |
19| 3           | 3            | posts 3                    |2020-07-15 00:00:00 |
20+-------------+--------------+----------------------------+--------------------+
21
22import { Model } from 'tspace-mysql'
23
24class User extends Model {
25    constructor(){
26        super()
27        this.hasMany({ name : 'posts' , model : Post })
28        this.useSoftDelete()
29    }
30}
31
32+--------------------------------------------------------------------------+
33
34class Post extends Model {
35    constructor(){
36        super()
37        this.hasMany({ name : 'comments' , model : Comment })
38        this.belongsTo({ name : 'user' , model : User })
39        this.useSoftDelete()
40    }
41}
42// normal relations
43await new User().relations('posts').findMany()
44// SELECT * FROM `users` WHERE `users`.`deleted_at`;
45// SELECT * FROM `posts` WHERE `posts`.`userId` IN (...) AND `posts`.`deleted_at` IS NULL;
46
47/*
48 * @returns [
49 *  {
50 *      id : 1,
51 *      username:  "tspace1",
52 *      email : "tspace1@gmail.com",
53 *      posts : []
54 *  },
55 *  {
56 *      id : 2,
57 *      username:  "tspace2",
58 *      email : "tspace2@gmail.com",
59 *      posts : [
60 *       {
61 *          id : 2,
62 *          user_id :  2,
63 *          title : "posts 2"
64 *        }
65 *      ]
66 *  },
67 *  {
68 *      id : 3,
69 *      username:  "tspace3",
70 *      email : "tspace3@gmail.com",
71 *      posts : []
72 *  }
73 * ]
74*/
75
76await new User().relationsExists('posts').findMany()
77// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL 
78// AND EXISTS (SELECT 1 FROM `posts` WHERE `users`.`id` = `posts`.`user_id` AND `posts`.`deletedA_at` IS NULL);
79
80// SELECT * FROM `posts` WHERE `posts`.`user_id` IN (...) AND `posts`.`deleted_at` IS NULL;
81
82/*
83 * @returns [
84 *  {
85 *      id : 2,
86 *      username:  "tspace2",
87 *      email : "tspace2@gmail.com",
88 *      posts : [
89 *       {
90 *          id : 2,
91 *          user_id :  2,
92 *          title : "posts 2"
93 *        }
94 *      ]
95 *  }
96 * ]
97 * because posts id 1 and id 3 has been removed from database (using soft delete)
98 */
99

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:

1
2+-------------+--------------+----------------------------+
3|                     table users                         |
4+-------------+--------------+----------------------------+
5| id          | username     | email                      |
6|-------------|--------------|----------------------------|
7| 1           | tspace1      | tspace1@gmail.com          |
8| 2           | tspace2      | tspace2@gmail.com          |
9+-------------+--------------+----------------------------+
10
11+-------------+--------------+----------------------------+
12|                     table posts                         |                    
13+-------------+--------------+----------------------------+
14| id          | user_id      | title                      |
15|-------------|--------------|----------------------------|
16| 1           | 1            | posts 1                    |
17| 2           | 1            | posts 2                    |
18| 3           | 2            | posts 3                    |
19+-------------+--------------+----------------------------+
20
21import { Model } from 'tspace-mysql'
22
23class User extends Model {
24  constructor(){
25      super()
26      this.hasMany({ name : 'posts' , model : Post })
27      this.useSoftDelete()
28  }
29}
30
31// you also use .withCount()
32await new User().relationsCount('posts').findMany() 
33// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL;
34
35// SELECT `posts`.`user_id`, COUNT(`user_id`) AS `aggregate` FROM `posts` 
36// WHERE `posts`.`user_id` IN ('1','2') AND `posts`.`deleted_at` IS NULL GROUP BY `posts`.`user_id`;
37
38/*
39 * @returns [
40 *  {
41 *      id : 1,
42 *      username:  "tspace1",
43 *      email : "tspace1@gmail.com",
44 *      posts : 2
45 *  }
46 *  {
47 *      id : 2,
48 *      username:  "tspace2",
49 *      email : "tspace2@gmail.com",
50 *      posts : 1
51 *  }
52 * ]
53 */
54

Relation Trashed

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

1
2+-------------+--------------+----------------------------+--------------------+
3|                     table users                         |                    |
4+-------------+--------------+----------------------------+--------------------+
5| id          | username     | email                      | deleted_at         |
6|-------------|--------------|----------------------------|--------------------|
7| 1           | tspace1      | tspace1@gmail.com          |                    |
8| 2           | tspace2      | tspace2@gmail.com          |                    |
9| 3           | tspace3      | tspace3@gmail.com          |2020-07-15 00:00:00 |
10+-------------+--------------+----------------------------+--------------------+
11
12+-------------+--------------+----------------------------+--------------------+
13|                     table posts                         |                    |
14+-------------+--------------+----------------------------+--------------------+
15| id          | user_id      | title                      | deleted_at         |
16|-------------|--------------|----------------------------|--------------------|
17| 1           | 1            | posts 1                    |2020-07-15 00:00:00 |
18| 2           | 2            | posts 2                    |                    |
19| 3           | 3            | posts 3                    |2020-07-15 00:00:00 |
20+-------------+--------------+----------------------------+--------------------+
21
22import { Model } from 'tspace-mysql'
23
24class User extends Model {
25  constructor(){
26    super()
27    this.hasMany({ name : 'posts' , model : Post })
28    this.useSoftDelete()
29  }
30}
31
32+--------------------------------------------------------------------------+
33
34class Post extends Model {
35    constructor(){
36        super()
37        th