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;
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
}
SQL Like
import { sql , OP } from 'tspace-mysql'
await sql()
.select('id','name')
.from('users')
.where({
'name' : 'tspace'
'id' : OP.in([1,2,3])
})
.limit(3)
.orderBy('name')
await sql()
.insert('users')
.values({
email : 'tspace@example.com'
})
await sql()
.insert('users')
.values({
email : 'tspace@example.com'
})
.returning({
id : true,
email : true,
enum : true
})
await sql()
.update('users')
.where({
id : 1
})
.set({
email : 'tspace@example.com'
})
await sql()
.update('users')
.where({
id : 1
})
.set({
email : 'tspace@example.com'
})
.returning()
await sql()
.delete('users')
.where({
id : 1
})
Query Builder
How a database query builder works with a simple example using the following:
+-------------+--------------+----------------------------+
| table users |
+-------------+--------------+----------------------------+
| id | username | email |
|-------------|--------------|----------------------------|
| 1 | tspace | tspace@gmail.com |
| 2 | tspace2 | tspace2@gmail.com |
+-------------+--------------+----------------------------+
+-------------+--------------+----------------------------+
| table posts |
+-------------+--------------+----------------------------+
| id | user_id | title |
|-------------|--------------|----------------------------|
| 1 | 1 | posts tspace |
| 2 | 2 | posts tspace2 |
+-------------+--------------+----------------------------+
Table Name & Alias Name
import { DB } from 'tspace-mysql'
await new DB().from('users').find(1)
await new DB().table('users').find(1)
await new DB().table('users').alias('u').find(1)
await new DB().fromRaw('u',new DB('users').select('*').limit(1).toString()).find(1)
await new DB().alias('u',new DB('users').select('*').limit(1).toString()).find(1)
Returning Results
const user = await new DB("users").find(1);
const user = await new DB("users").findOne();
const user = await new DB("users").first();
const user = await new DB("users").firstOrError(message);
const users = await new DB("users").findMany();
const users = await new DB("users").get();
const users = await new DB("users").getGroupBy('name')
const users = await new DB("users").findGroupBy('name')
const users = await new DB("users").toArray();
const users = await new DB("users").toJSON();
const user = await new DB("users").exists();
const user = await new DB("users").count();
const user = await new DB("users").avg();
const user = await new DB("users").sum();
const user = await new DB("users").max();
const user = await new DB("user").min();
const users = await new DB("users").toString();
const users = await new DB("users").toSQL();
const users = await new DB("users").toRawSQL();
const users = await new DB("users").pagination();
const users = await new DB("users").makeSelectStatement()
const users = await new DB("users").makeInsertStatement()
const users = await new DB("users").makeUpdateStatement()
const users = await new DB("users").makeDeleteStatement()
const users = await new DB("users").makeCreateTableStatement()
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 Statements
const select = await new DB("users").select("id", "username").findOne();
const selectRaw = await new DB("users").selectRaw("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();
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();
await new DB("users").except("id").findOne();
await new DB("users").distinct().select("id").findOne();
Raw Expressions
const users = await new DB("users")
.select(DB.raw("COUNT(`username`) as c"), "username")
.groupBy("username")
.having("c > 1")
.findMany();
const users = await new DB("users")
.where(
"id",
DB.raw(new DB("users").select("id").where("id", "1").limit(1).toString())
)
.findMany();
const findFullName = await new User()
.select('name',`${DB.raw('CONCAT(firstName," ",lastName) as fullName')}`)
.whereRaw(`CONCAT(firstName," ",lastName) LIKE '%${search}%'`)
.findOne()
Ordering, Grouping, Limit and Offset
Ordering
await new DB("users").orderBy("id", "asc").findOne();
await new DB("users").orderBy("id", "desc").findOne();
await new DB("users").oldest("id").findOne();
await new DB("users").latest("id").findOne();
await new DB("users").random().findMany();
Grouping
await new DB("users").groupBy("id").findOne();
await new DB("users").groupBy("id", "username").findOne();
await new DB("users")
.select(DB.raw("COUNT(username) as c"), "username")
.groupBy("username")
.having("c > 1")
.findMany();
Limit and Offset
await new DB("users").limit(5).findMany();
await new DB("users").limit(-1).findMany();
await new DB("users").offset(1).findOne();
Joins
Inner Join Clause
await new DB("posts").join("posts.user_id", "users.id").findMany();
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();
Left Join, Right Join Clause
await new DB("posts").leftJoin("posts.user_id", "users.id").findMany();
await new DB("posts").rightJoin("posts.user_id", "users.id").findMany();
Cross Join Clause
await new DB("posts").crossJoin("posts.user_id", "users.id").findMany();
Basic Where Clauses
Where Clauses
const users = await new DB("users").where("id", 1).findMany();
const users = await new DB("users")
.where("id", 1)
.where("username", "try to find")
.findMany();
const users = await new DB("users").where("id", ">", 1).findMany();
const users = await new DB("users").where("id", "<>", 1).findMany();
Or Where Clauses
const users = await new DB("users").where("id", 1).orWhere("id", 2).findMany();
const users = await new DB("users")
.where("id", 1)
.whereQuery((query) => {
return query
.where("id", "<>", 2)
.orWhere("username", "try to find")
.orWhere("email", "find@example.com");
})
.findMany();
Where Object Clauses
import { OP } from 'tspace-mysql'
const whereObject = await new DB("users")
.whereObject({
id : OP.notEq(1),
username : OP.in(['user1','user2']),
name : OP.like('%value%')
})
.findMany();
JSON Where Clauses
const whereJSON = await new DB("users")
.whereJSON("json", { key: "id", value: "1234" })
.findMany();
Additional Where Clauses
const users = await new DB("users").whereIn("id", [1, 2]).findMany();
const users = await new DB("users").whereNotIn("id", [1, 2]).findMany();
const users = await new DB("users").whereBetween("id", [1, 2]).findMany();
const users = await new DB("users").whereNotBetween("id", [1, 2]).findMany();
const users = await new DB("users").whereNull("username").findMany();
const users = await new DB("users").whereNotNull("username").findMany();
Logical Grouping
const users = await new DB("users")
.whereQuery((query) => query.where("id", 1).where("username", "values"))
.whereIn("id", [1, 2])
.findOne();
const users = await new DB("users")
.where("id", 1)
.whereQuery((query) => {
return query
.where("id", "<>", 2)
.where("username", "try to find")
.where("email", "find@example.com");
})
.findMany();
const users = await new DB("users")
.whereAny(["name", "username", "email"], "like", `%v%`)
.findMany();
const users = await new DB("users")
.whereAll(["name", "username", "email"], "like", `%v%`)
.findMany();
Advanced Where Clauses
Where Exists Clauses
const users = await new DB("users")
.whereExists(new DB("users").select("id").where("id", 1).toString())
.findMany();
const users = await new DB("users")
.wherNoteExists(new DB("users").select("id").where("id", 1).toString())
.findMany();
Subquery Where Clauses
const users = await new DB("users")
.whereSubQuery("id", "SELECT id FROM users")
.findMany();
const users = await new DB("users")
.whereSubQuery("id", new DB("users").select("id").toString())
.findMany();
const users = await new DB("users")
.whereSubQuery(
"id",
new DB("users")
.select("id")
.whereSubQuery("id", new DB("posts").select("user_id").toString())
.toString()
)
.findMany();
Conditional Where Clauses
const users = await new DB("users")
.where("id", 1)
.when(true, (query) => query.where("username", "when is actived"))
.findMany();
const users = await new DB("users")
.where("id", 1)
.when(false, (query) => query.where("username", "when is actived"))
.findMany();
GetGroupBy
const data = await new DB("posts").getGroupBy('user_id')
const userHasPosts = data.get(1)
console.log(userHasPosts)
Paginating
const users = await new DB("users").paginate();
const pageTwoUsers = await new DB("users").paginate({ page: 2, limit: 5 });
Insert Statements
const user = await new DB("users")
.create({
name: "tspace3",
email: "tspace3@gmail.com",
})
.save();
const users = await new DB("users")
.createMultiple([
{
name: "tspace4",
email: "tspace4@gmail.com",
},
{
name: "tspace5",
email: "tspace5@gmail.com",
},
{
name: "tspace6",
email: "tspace6@gmail.com",
},
])
.save();
const users = await new DB("users")
.where("name", "tspace4")
.where("email", "tspace4@gmail.com")
.createNotExists({
name: "tspace4",
email: "tspace4@gmail.com",
})
.save();
const users = await new DB("users")
.where("name", "tspace4")
.where("email", "tspace4@gmail.com")
.createOrSelect({
name: "tspace4",
email: "tspace4@gmail.com",
})
.save();
Update Statements
const user = await new DB("users")
.where("id", 1)
.update({
name: "tspace1**",
email: "tspace1@gmail.com",
})
.save();
const user = await new DB("users")
.where("id", 1)
.updateMany({
name: "tspace1",
email: "tspace1@gmail.com",
})
.save();
const user = await new DB("users")
.where("id", 1)
.update(
{
name: "tspace1",
email: "tspace1@gmail.com",
},
["name"]
)
.save();
const user = await new DB("users")
.updateMultiple([
{
when: {
id: 1,
name: "name1",
},
columns: {
name: "update row1",
email: "row1@example.com",
},
},
{
when: {
id: 2,
},
columns: {
name: "update row2",
email: "row2@example.com",
},
},
])
.save();
const user = await new DB("users")
.where("id", 1)
.updateOrCreate({
name: "tspace1**",
email: "tspace1@gmail.com",
})
.save();
Delete Statements
const deleted = await new DB("users").where("id", 1).delete();
const deleted = await new DB("users").where("id", 1).deleteMany();
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);
await new DB("users").faker(5, (row, index) => {
return {
username: `username-${index + 1}`,
email: `email-${index + 1}`,
};
});
await new DB("users").faker(40_000);
Unset Statements
const userInstance = new User().where('email','test@gmail.com')
const exits = await userInstance.exists()
const user = await userInstance.orderBy('id').findOne()
const users = await userInstance.select('id').unset({ limit : true }).findMany()
const usersUnsetWhereStatement = await userInstance.unset({ select : true, where : true , orderBy : true }).findMany()
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()
More Methods
where(column , OP , value)
whereSensitive(column , OP , 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 , OP })
whereRaw(sql)
whereExists(sql)
whereSubQuery(colmn , rawSQL)
whereNotSubQuery(colmn , rawSQL)
orWhere(column , OP , 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) => ...)
sleep(seconds)
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 })
relations(name1 , name2,...nameN)
relationsAll(name1 , name2,...nameN)
relationsExists(name1 , name2,...nameN)
relationsTrashed(name1 , name2,...nameN)
relationQuery(name, (callback) )
findMany()
findOne()
find(id)
delelte()
delelteMany()
exists()
toString()
toJSON()
toArray(column)
count(column)
sum(column)
avg(column)
max(column)
min(column)
pagination({ limit , page })
save()
makeSelectStatement()
makeInsertStatement()
makeUpdateStatement()
makeDeleteStatement()
makeCreateTableStatement()
Database Transactions
Within a database transaction, you can utilize the following:
const connection = await new DB().beginTransaction();
try {
await connection.startTransaction();
const user = await new User()
.create({
name: `tspace`,
email: "tspace@example.com",
})
.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)
.save();
await connection.commit();
} catch (err) {
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)
.findMany()
Backup
To backup a database, you can perform the following steps:
const backup = await new DB().backup({
database: 'try-to-backup',
to ?: {
host: 'localhost',
port : 3306,
username: 'username',
password: 'password',
}
})
const backupToFile = await new DB().backupToFile({
database: 'try-to-backup',
filePath: 'backup.sql',
connection ?: {
host: 'localhost',
port : 3306,
database: 'database'
username: 'username',
password: 'password',
}
})
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);
const input = "text hello!<script>alert('XSS attack');</script>";
DB.escapeXSS(input);
Generating Model Classes
To get started, install the 'tspace-mysql' package globally using the following npm command:
npm install tspace-mysql -g
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";
Model.global({
uuid: true,
softDelete: true,
timestamp: true,
logger: true,
});
class User extends Model {
constructor() {
super();
this.useTable("users");
}
}
export { User };
export default User;
Basic Model Setup
Table Name
import { Model } from 'tspace-mysql'
class User extends Model {
constructor() {
super()
this.useTable('fix_table')
this.useTablePlural()
this.useTableSingular()
}
}
Pattern
import { Model } from 'tspace-mysql'
class UserPhone extends Model {
constructor() {
super()
this.useSnakeCase()
this.useCamelCase()
}
}
const userPhone = await new UserPhone().where('user_id',1).findOne()
const userPhone = await new UserPhone().where(DB.freeze('user_id'),1).findOne()
UUID
import { Model } from 'tspace-mysql'
class User extends Model {
constructor() {
super()
this.useUUID()
}
}
Timestamp
import { Model } from 'tspace-mysql'
class User extends Model {
constructor() {
super()
this.useTimestamp()
this.useTimestamp({
createdAt : 'createdAtCustom',
updatedAt : 'updatedAtCustom'
})
}
}
Debug
import { Model } from 'tspace-mysql'
class User extends Model {
constructor() {
super()
this.useDebug()
}
}
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)
}
}
Logger
import { Model } from 'tspace-mysql'
class User extends Model {
constructor() {
super()
this.useLogger()
this.useLogger({
selected : true,
inserted : true,
updated : true,
deleted : true,
})
}
}
Hooks
import { Model } from 'tspace-mysql'
class User extends Model {
constructor() {
super()
this.useHooks([
(results1) => console.log(results1),
(results2) => console.log(results2),
(results3) => console.log(results3)
])
}
}
Global Scope
class User extends Model {
constructor() {
super()
this.globalScope((query : User) => {
return query.select('id').where('id' , '>' , 10).orderBy('id')
})
}
}
const user = await new User().findMany()
Joins Model
Inner Join Model Clause
await new User().joinModel(User, Post).findMany();
await new User().joinModel(User, Post).findMany();
await new User().select(`${User.table}.*`,`${Post.table}.*`).joinModel(User, Post).findMany();
await new User().select('u.*','p.*')
.joinModel({ model : User , key : 'id' , alias : 'u' }, { model : Post , key : 'user_id', alias : 'p'})
.findMany();
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()
Left Join, Right Join Model Clause
await new User().leftJoinModel(User, Post).findMany();
await new User().rightJoinModel(User, Post).findMany();
Cross Join Model Clause
await new User().crossJoinModel(User, Post).findMany();
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()
this.hasOne({ name : 'phone' , model : Phone })
}
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()
const userUsingFunction = await new User().phone().findOne()
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()
this.hasMany({ name : 'comments' , model : Comment })
}
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()
const postsUsingFunction = await new Post().comments().findOne()
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()
this.belognsTo({ name : 'user' , model : User })
}
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()
const phoneUsingFunction = await new Phone().user().findOne()
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()
this.belognsToMany({ name : 'roles' , model : Role })
}
roles (callback) {
return this.belognsToManyBuilder({ model : Role } , callback)
}
}
export default User
+--------------------------------------------------------------------------+
import User from '../User'
const user = await new User().relations('roles').findOne()
const userUsingFunction = await new User().roles().findOne()
Relation
Relationships are connections between entities.
Let's consider an example of a relationship:
+-------------+--------------+----------------------------+
| table users |
+-------------+--------------+----------------------------+
| id | username | email |
|-------------|--------------|----------------------------|
| 1 | tspace1 | tspace1@gmail.com |
| 2 | tspace2 | tspace2@gmail.com |
| 3 | tspace3 | tspace3@gmail.com |
+-------------+--------------+----------------------------+
+-------------+--------------+----------------------------+
| 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()
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 {}
+--------------------------------------------------------------------------+
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')
})
})
.relationQuery('users', (query : User) => {
return query
})
.relationQuery('users', (query : PostUser) => {
return query
}, { pivot : true })
})
.findMany()
await new User()
.relations('posts')
.relationQuery('posts', (query : Post) => query.select('id','user_id','title'))
.findMany()
await new User()
.relations('posts')
.relationQuery('posts', (query : Post) => query.whereIn('id',[1,3,5]))
.findMany()
await new User()
.relations('posts')
.relationQuery('posts', (query : Post) => query.latest('id'))
.findMany()
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 | tspace1@gmail.com | |
| 2 | tspace2 | tspace2@gmail.com | |
| 3 | tspace3 | tspace3@gmail.com | |
+-------------+--------------+----------------------------+--------------------+
+-------------+--------------+----------------------------+--------------------+
| 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()
}
}
await new User().relations('posts').findMany()
await new User().relationsExists('posts').findMany()
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 | tspace1@gmail.com |
| 2 | tspace2 | tspace2@gmail.com |
+-------------+--------------+----------------------------+
+-------------+--------------+----------------------------+
| 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()
}
}
await new User().relationsCount('posts').findMany()
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 | tspace1@gmail.com | |
| 2 | tspace2 | tspace2@gmail.com | |
| 3 | tspace3 | tspace3@gmail.com |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()
}
}
await new User().relations('posts').findMany()
await new User().relationsTrashed('posts').findMany()
await new User().relationsTrashed('posts').trashed().findMany()
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' , model : Post })
this.useBuiltInRelationFunctions()
}
}
+--------------------------------------------------------------------------+
class Post extends Model {
constructor(){
super()
this.hasMany({ name : 'comments' , model : Comment })
this.belongsTo({ name : 'user' , model : User })
this.useBuiltInRelationFunctions()
}
}
+--------------------------------------------------------------------------+
class Comment extends Model {
constructor(){
super()
this.hasMany({ name : 'users' , model : User })
this.belongsTo({ name : 'post' , model : Post })
this.useBuiltInRelationFunctions()
}
}
+--------------------------------------------------------------------------+
const user = await new User().findOne()
const posts = await user.$posts()
for (const post of posts) {
const comments = await post.$comments()
}
Cache
Cache can be used in a Model.
Let's illustrate this with an example of a cache:
DB_CACHE = memory
DB_CACHE = db
DB_CACHE = redis:
const users = await new User()
.cache({
key : 'users',
expires : 1000 * 60
})
.sleep(5)
.findMany()
Decorator
Decorators can be used in a Model.
Let's illustrate this with an example of a decorators:
import {
Blueprint, Model ,
Table ,TableSingular, TablePlural,
UUID, SoftDelete, Timestamp,
Pattern, CamelCase , snakeCase ,
Column, Validate, Observer
} from 'tspace-mysql'
import { Post } from './Post'
import { PostUser } from './PostUser'
class UserObserve {
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 })
}
}
@Pattern('camelCase')
@Observer(UserObserve)
@UUID()
@SoftDelete()
@Timestamp()
@Table('users')
class User extends Model {
@Column(() => Blueprint.int().notNull().primary().autoIncrement())
public id!: number
@Column(() => Blueprint.varchar(50).null())
public uuid!: string
@Column(() => Blueprint.varchar(50).null())
@Validate({
type : String,
require : true,
length : 50,
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)
})
public email!: string
@Column(() => Blueprint.varchar(50).null())
public name !: string
@Column(() => Blueprint.varchar(50).null())
public username !: string
@Column(() => Blueprint.varchar(50).null())
public password !: string
@Column(() => Blueprint.timestamp().null())
public createdAt!: Date
@Column(() => Blueprint.timestamp().null())
public updatedAt!: Date
@Column(() => Blueprint.timestamp().null())
public deletedAt!: Date
}
export { User }
export default User
Schema
The schema refers to the structure of the database as it pertains to the objects and classes in the model.
using the following:
Schema Model
import { Model, Blueprint , TSchema } from "tspace-mysql";
const schema = {
id: Blueprint.int().notNull().primary().autoIncrement(),
uuid: Blueprint.varchar(50).null().index(),
name: Blueprint.varchar(191).notNull(),
email: Blueprint.varchar(191).notNull(),
createdAt: Blueprint.timestamp().null().bindColumn('created_at'),
updatedAt: Blueprint.timestamp().null().bindColumn('updated_at'),
deletedAt: Blueprint.timestamp().null().bindColumn('deleted_at')
}
type TS = TSchema<typeof Schema>
class User extends Model<TS>
{
constructor() {
super();
this.useCamelCase()
this.useSchema(schema)
}
}
Validation
Validate the schema of Model
let's example a validator model:
import { Model, Blueprint } from "tspace-mysql";
class User extends Model {
constructor() {
super();
this.useCamelCase();
this.useSchema({
id: Blueprint.int().notNull().primary().autoIncrement(),
uuid: Blueprint.varchar(50).null(),
name: Blueprint.varchar(191).notNull(),
email: Blueprint.varchar(191).notNull(),
createdAt: Blueprint.timestamp().null(),
updatedAt: Blueprint.timestamp().null(),
deletedAt: Blueprint.timestamp().null(),
});
this.useValidateSchema({
id: Number,
uuid: Number,
name: {
type: String,
length: 191,
require: true,
json: 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) => {
return /^[a-zA-Z0-9._]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/.test(email)
}
},
createdAt: Date,
updatedAt: Date,
deletedAt: Date,
});
}
}
Sync
Sync the schema with the "Models" setting in your directory.
This process will verify and update table columns and foreign keys as needed.
Ensure that the relationships are correctly established through the 'useSchema' method in your models.
Let's examine a basic sync class:
class User extends Model {
constructor() {
super();
this.hasMany({ name: "posts", model: Post });
this.whenCreatingTable(async () => {
return await new User()
.create({
...columns,
})
.void()
.save();
});
this.useSchema({
id: Blueprint.int().notNull().primary().autoIncrement(),
uuid: Blueprint.varchar(50).null(),
email: Blueprint.int().notNull().unique(),
name: Blueprint.varchar(255).null(),
created_at: Blueprint.timestamp().null(),
updated_at: Blueprint.timestamp().null(),
deleted_at: Blueprint.timestamp().null(),
});
}
}
import User from "./User";
class Post extends Model {
constructor() {
super();
this.hasMany({ name: "comments", model: Comment });
this.belongsTo({ name: "user", model: User });
this.useSchema({
id: Blueprint.int().notNull().primary().autoIncrement(),
uuid: Blueprint.varchar(50).null(),
user_id: Blueprint.int().notNull().foreign({
references: "id",
on: User,
onDelete: "CASCADE",
onUpdate: "CASCADE",
}),
title: Blueprint.varchar(255).null(),
created_at: Blueprint.timestamp().null(),
updated_at: Blueprint.timestamp().null(),
deleted_at: Blueprint.timestamp().null(),
});
}
}
await Schema.sync(`/src/Models`, {
force: true,
log: true,
foreign: true,
changed: true,
});
await new User().sync({ force: true, foreign: true, changed: true });
SoftDelete
import { Model } from 'tspace-mysql'
class User extends Model {
constructor() {
super()
this.useSoftDelete()
this.useSoftDelete('deletedAtCustom')
}
}
const user = await new User().where('user_id',1).findOne()
const user = await new User().trashed().findMany()
Type Safety
Type safety in TypeScript refers to the ability of the language to detect and prevent type errors during compile-time.
Type safety still works when you add additional types to your model, using the following:
import { Model , Blueprint , TSchema , TSchemaStatic } from 'tspace-mysql'
import Phone from '../Phone'
const schemaUser = {
id :Blueprint.int().notNull().primary().autoIncrement(),
uuid :Blueprint.varchar(50).null(),
email :Blueprint.varchar(50).null(),
name :Blueprint.varchar(255).null(),
username : Blueprint.varchar(255).null(),
password : Blueprint.varchar(255).null(),
createdAt :Blueprint.timestamp().null(),
updatedAt :Blueprint.timestamp().null()
}
type TSchemaUser = TSchemaStatic<typeof schemaUser>
class User extends Model<TSchemaUser> {
constructor() {
super()
this.useSchema(schemaUser)
this.hasOne({ model : Phone, name : 'phone' })
this.hasMany({ model : Phone, name : 'phones' })
}
}
export { User }
export default User
+--------------------------------------------------------------------------+
import { Model , Blueprint , TSchema , TSchemaStatic } from 'tspace-mysql'
import { User } from './User.ts'
const schemaPhone = {
id :Blueprint.int().notNull().primary().autoIncrement(),
uuid :Blueprint.varchar(50).null(),
userId : Blueprint.int().notNull(),
number :Blueprint.varchar(50).notNull(),
createdAt :Blueprint.timestamp().null(),
updatedAt :Blueprint.timestamp().null()
}
type TSchemaPhone = TSchemaStatic<typeof schemaPhone>
class Phone extends Model<TSchemaPhone> {
constructor() {
super()
this.useSchema(schemaPhone)
this.useBelongsTo({ model : User, name : 'user'})
}
}
export { Phone }
export default Phone
+--------------------------------------------------------------------------+
Safety Select
import { User } from './User.ts'
import { Phone } from './Phone.ts'
const user = await new User().select('id','username').findOne() ✅
const user = await new User().select('idx','username').findOne() ❌
const user = await new User().except('id','username').findOne() ✅
const user = await new User().except('idx','username').findOne() ❌
user.withoutSchema = 1 ✅
user.withoutSchema = 1 ❌
const user = await new User().except('idx','username').findOne<{ withoutSchema : number }>()
user.withoutSchema = 1 ✅
Safety OrderBy
import { User } from './User.ts'
import { Phone } from './Phone.ts'
const users = await new User().orderBy('id','DESC').findMany() ✅
const users = await new User().orderBy('idx','DESC').findMany() ❌
const users = await new User().latest('id').findMany() ✅
const users = await new User().latest('idx').findMany() ❌
const users = await new User().oldest('id').findMany() ✅
const users = await new User().oldest('idx').findMany() ❌
Safety GroupBy
import { User } from './User.ts'
import { Phone } from './Phone.ts'
const users = await new User().groupBy('id').findMany() ✅
const users = await new User().groupBy('idx').findMany() ❌
Safety Where
import { User } from './User.ts'
import { Phone } from './Phone.ts'
const users = await new User().where('id',1).findMany() ✅
const users = await new User().where('idxx',1).findMany() ❌
const users = await new User().where('id',1).orWhere('id',5).findMany() ✅
const users = await new User().where('id',1).orWhere('idxx',5).findMany() ❌
const users = await new User().whereIn('id',[1]).findMany() ✅
const users = await new User().whereIn('idx',[1]).findMany() ❌
const users = await new User().whereNull('id').findMany() ✅
const users = await new User().whereNull('idx').findMany() ❌
const users = await new User().whereNotNull('id').findMany()
const users = await new User().whereNotNull('idx').findMany()
const users = await new User().whereBetween('id',[1,2]).findMany() ✅
const users = await new User().whereBetween('idx',[1,2]).findMany() ❌
const users = await new User()
.whereSubQuery(
'id',
new User().select('id').toString()
).findMany() ✅
const users = await new User()
.whereSubQuery(
'idx',
new User().select('id').toString()
).findMany() ❌
Safety Insert
import { User } from './User.ts'
import { Phone } from './Phone.ts'
const users = await new User().create({ id : 10 }).save() ✅
const users = await new User().create({ id : "10" }).save() ❌
const users = await new User().create({ idx : 10 }).save() ❌
Safety Update
import { User } from './User.ts'
import { Phone } from './Phone.ts'
const users = await new User().update({ id : 10 }).where('id',1).save() ✅
const users = await new User().update({ id : 10 }).where('idx',1).save() ❌
const users = await new User().update({ id : "10" }).where('id',1).save() ❌
const users = await new User().update({ idx : 10 }).where('idx',1).save() ❌
Safety Delete
import { User } from './User.ts'
import { Phone } from './Phone.ts'
const users = await new User().where('id',1).delete() ✅
const users = await new User().where('idx',1).delete() ❌
Safety Relationships
import { TSchemaModel } from 'tspace-mysql'
import { User } from './User.ts'
import { Phone } from './Phone.ts'
const users = await new User()
.relations('phone','phones')
.findMany()
for(const user of users) {
user.phone ❌
user.phones ❌
}
const users = await new User()
.relations('phone','phones')
.findMany<{ phone : Record<string,any> , phones : any[]}>()
for(const user of users) {
user.phone ✅
user.phones ✅
user.phone.id ✅
user.phone.idx ✅💩💩💩
user.phones.map(phone => phone.id) ✅
user.phones.map(phone => phone.idx) ✅💩💩💩
}
const users = await new User()
.relations('phone','phones')
.findMany<{ phone : TSchemaModel<Phone> , phones : TSchemaModel<Phone>[] }>()
for(const user of users) {
user.phone ✅
user.phones ✅
user.phone?.id ✅
user.phone?.idx ❌
user.phones.map(phone => phone?.id) ✅
user.phones.map(phone => phone?.idx) ❌
}
+--------------------------------------------------------------------------+
const users = await new User()
.relations('phone','phones')
.relationQuery('phone' , (query : Phone) => query.relations('user'))
.relationQuery('phones' , (query : Phone) => query.relations('user'))
.findMany<{ phone : TSchemaModel<Phone> , phones : TSchemaModel<Phone>[] }>()
for(const user of users) {
user.phone.user ❌
user.phones.map(phone =>phone.user) ❌
}
const users = await new User()
.relations('phone','phones')
.relationQuery('phone' , (query : Phone) => query.relations('user'))
.relationQuery('phones' , (query : Phone) => query.relations('user'))
.findMany<{ phone : Record<string,any> , phones : Record<string,any>[] }>()
for(const user of users) {
user.phone.user ✅💩💩💩
user.phones.map(phone =>phone.user) ✅💩💩💩
user.phone.user.idx ✅💩💩💩
user.phones.map(phone =>phone.user.idx) ✅💩💩💩
}
const users = await new User()
.relations('phone','phones')
.relationQuery('phone' , (query : Phone) => query.relations('user'))
.relationQuery('phones' , (query : Phone) => query.relations('user'))
.findMany<{
phone : Partial<TSchemaModel<Phone>> & { user : TSchemaModel<User>};
phones : (Partial<TSchemaModel<Phone>> & { user : TSchemaModel<User>})[];
}>()
for(const user of users) {
user.phone.user ✅
user.phone.user.id ✅
user.phone.userx ❌
user.phone.user.idx ❌
user.phones.map(phone =>phone.user.id) ✅
user.phones.map(phone =>phone.user.idx) ❌
}
+--------------------------------------------------------------------------+
import { Model , Blueprint , TSchema , TSchemaStatic , TRelation } from 'tspace-mysql'
import { Phone } from '../Phone'
const schemaUser = {
id :Blueprint.int().notNull().primary().autoIncrement(),
uuid :Blueprint.varchar(50).null(),
email :Blueprint.varchar(50).null(),
name :Blueprint.varchar(255).null(),
username : Blueprint.varchar(255).null(),
password : Blueprint.varchar(255).null(),
createdAt :Blueprint.timestamp().null(),
updatedAt :Blueprint.timestamp().null()
}
type TSchemaUser = TSchemaStatic<typeof schemaUser>
type TRelationUser = TRelation<{
phones : Phone[]
phone : Phone
}>
class User extends Model< TSchemaUser, TRelationUser > {
constructor() {
super()
this.useSchema(schemaUser)
this.hasOne({ model : Phone, name : 'phonex' }) ❌
this.hasMany({ model : Phone, name : 'phonesx' }) ❌
this.hasOne({ model : Phone, name : 'phone' }) ✅
this.hasMany({ model : Phone, name : 'phones' }) ✅
}
}
export { User }
+--------------------------------------------------------------------------+
import { Model , Blueprint , TSchema , TRelation } from 'tspace-mysql'
import { User } from './User.ts'
const schemaPhone = {
id :Blueprint.int().notNull().primary().autoIncrement(),
uuid :Blueprint.varchar(50).null(),
userId : Blueprint.int().notNull(),
number :Blueprint.varchar(50).notNull(),
createdAt :Blueprint.timestamp().null(),
updatedAt :Blueprint.timestamp().null()
}
type TSchemaPhone = TSchema<typeof schemaPhone>
type TRelationPhone = TRelation<{
user : User[]
}>
class Phone extends Model<
TSchemaPhone,
TRelationPhone
> {
constructor() {
super()
this.useSchema(schemaPhone)
this.useBelongsTo({ model : User, name : 'userx'}) ❌
this.useBelongsTo({ model : User, name : 'user'}) ✅
}
}
export { Phone }
+--------------------------------------------------------------------------+
const users = await new User()
.relations('phonex','phonesx') ❌
.relationQuery('phonex' ❌ , (query : Phone) => query.relations('user')) ✅
.relationQuery('phonesx' ❌ , (query : Phone) => query.relations('user')) ✅
.findMany()
const users = await new User()
.relations('phone','phones') ✅
.relationQuery('phonex' ❌ , (query : Phone) => query.relations('user')) ✅
.relationQuery('phonesx' ❌ , (query : Phone) => query.relations('user')) ✅
.findMany()
const users = await new User()
.relations('phone','phones')
.relationQuery('phone' , (query : Phone) => query.relations('userx')) ❌
.relationQuery('phones' , (query : Phone) => query.relations('userx')) ❌
.findMany()
const users = await new User()
.relations('phone','phones') ✅
.relationQuery('phone' ✅ , (query : Phone) => query.relations('user')) ✅
.relationQuery('phones'✅ , (query : Phone) => query.relations('user')) ✅
.findMany()
for(const user of users) {
user.phone.user ❌
user.phone?.user ✅
user.phone?.user.id ✅
user.phone?.userx ❌
user.phone?.user.idx ❌
user.phones.map(phone =>phone?.user.id) ❌
user.phones?.map(phone =>phone?.user.id) ✅
user.phones?.map(phone =>phone?.user.idx) ❌
}
Repository
Repository is a mechanism that encapsulates all database operations related to a specific model.
It provides methods for querying, inserting, updating, and deleting records in the database associated with the model.
** The Repository check always type safety if model is used the type of schema
Repository Select Statements
import { Repository , TRepository , OP } from 'tspace-mysql'
import { User } from '../Models/User'
const userRepository = Repository.bind(User)
const needPhone = true
const user = await userRepository.findOne({
select : {
id : true,
name : true,
username : true,
phone : {
id : true,
name : true,
user_id : true,
}
},
where : {
id: 1
},
when : {
condition : needPhone,
query: () => ({
relations : {
phone : true
}
})
}
})
const users = await userRepository.findMany({
select : {
id : true,
name : true,
username : true,
},
limit : 3,
orderBy : {
id : 'ASC',
name : 'DESC'
}
groupBy : ['id'],
where : {
id: OP.in([1,2,3])
}
})
const userPaginate = await userRepository.pagination({
select : {
id : true,
name : true,
username : true,
},
page : 1,
limit : 3,
where : {
id: OP.in([1,2,3])
}
})
const findFullName = await userRepository.findOne({
select : {
name : true,
[`${DB.raw('CONCAT(firstName," ",lastName) as fullName')}`]: true
}
whereRaw : [
`CONCAT(firstName," ",lastName) LIKE '%${search}%'`
]
})
Repository Insert Statements
const userRepository = Repository.bind(User)
const created = await userRepository.create({
data : {
name : "repository-name",
}
})
const createdMultiple = await u.createMultiple({
data : [
{
name: "tspace4",
},
{
name: "tspace5",
},
{
name: "tspace6",
}
]
})
const createdNotExists = await userRepository.createNotExists({
data : {
name : "repository-name",
},
where : {
id : 1
}
})
const createdOrSelected = await userRepository.createOrSelect({
data : {
name : "repository-name",
},
where : {
id : 1
}
})
Repository Update Statements
const userRepository = Repository.bind(User)
const updated = await userRepository.update({
data : {
name : "repository-name",
},
where : {
id : 1
}
})
Repository Delete Statements
const userRepository = Repository.bind(User)
const deleted = await userRepository.delete({
where : {
id : 1
}
})
Repository Transactions
import { DB , Repository } from 'tspace-mysql'
import { User } from '../Models/User'
const userRepository = Repository.bind(User)
const transaction = await DB.beginTransaction()
try {
await transaction.startTransaction()
const created = await userRepository.create({
data : {
name : "repository-name",
},
transaction
})
const updated = await userRepository.update({
data : {
name : "repository-name",
},
where : {
id : created.id
},
transaction
})
await transaction.commit()
} catch (err) {
await transaction.rollback()
}
Repository Relations
import { Repository , TRepository , OP } from 'tspace-mysql'
import { User } from '../Models/User'
import { Phone } from '../Models/Phone'
const userRepository = Repository.bind(User)
const userHasPhones = await userRepository.findOne({
select : ['*'],
where : {
id: 1
},
relations : ['phone'],
relationQuery:{
name : 'phone',
callback: () : TRepository<Phone> => ({
select: ['id', 'userId', 'name'],
relations : ['user']
})
}
})
const phoneRepository = Repository.bind(Phone)
const phoneBelongUser = await phoneRepository.findOne({
select : ['*'],
where : {
id: 1
},
relations : ['user']
})
Blueprint
Blueprint is a tool used for defining database schemas programmatically.
It allows developers to describe the structure of their database tables using a simple and intuitive syntax rather than writing SQL queries directly., you may use the:
import { Schema , Blueprint , DB } from 'tspace-mysql'
(async () => {
await new Schema().table('users', {
id : Blueprint.int().notNull().primary().autoIncrement(),
uuid : Blueprint.varchar(120).null()
name : Blueprint.varchar(120).default('name'),
email : Blueprint.varchar(255).unique().notNull(),
email_verify : Blueprint.tinyInt(),
password : Blueprint.varchar(255),
json : Blueprint.json(),
created_at : Blueprint.null().timestamp(),
updated_at : Blueprint.null().timestamp(),
deleted_at : Blueprint.null().timestamp()
})
})()
int (number)
tinyInt (number)
bigInt (number)
double ()
float ()
json ()
varchar (number)
char (number)
longText()
mediumText()
tinyText()
text()
enum(...n)
date()
dateTime()
timestamp ()
unsigned()
unique()
null()
notNull()
primary()
default(string)
defaultTimestamp()
autoIncrement()
foreign({ references : ${COLUMN} , on : ${TABLE-NAME OR MODEL CLASSES} })
index()
Cli
To get started, let's install tspace-mysql
you may use a basic cli :
npm install tspace-mysql -g
Make Model
The command will be placed Model in the specific directory.
/**
*
* @make Model
* @options
* @arg --m => created scheme table for migrate. short cut migration table like Make Migration
* @arg --dir=directory => created model in directory. default root directory
* @arg --type=js // extension js. default ts
*/
tspace-mysql make:model <model name> --m --dir=.... --type=....
tspace-mysql make:model User --m --dir=app/Models
/**
*
* @Ex directory
*/
- node_modules
- app
- Models
User.ts
Make Migration
The command will be placed Migration in the specific directory.
/**
*
* @make Migration Table
* @options
* @arg --dir=directory => created scheme table in directory. default root directory
* @arg --type=js // extension js default ts
*/
tspace-mysql make:migration <table name> --type=... --dir=....
tspace-mysql make:migration users --dir=app/Models/Migrations
/**
*
* @Ex directory
*/
- node_modules
- app
- Models
- Migrations
create_users_table.ts
User.ts
Migrate
/**
*
* @run Migrate table
* @options
* @arg --dir=directory => find migrate in directory. default find in root folder
* @arg --type=js // extension js default ts
*/
tspace-mysql migrate <folder> --type=<type file js or ts> --dir=<directory for migrate>
tspace-mysql migrate --dir=app/Models/Migrations --type=js
/**
*
* @Ex directory
*/
- node_modules
- app
- Models
- Migrations
create_users_table.ts
create_posts_table.ts
User.ts
Post.ts
// => migrate all schemas in folder <Migrations>. created into database
Query
The command will execute a query.
tspace-mysql query "SELECT * FROM users"
Dump
The command will dump the database or table into a file.
tspace-mysql dump:db --dir=<folder for dump> --values // backup with values in the tables
tspace-mysql dump:table "table_name" --dir=<folder for dump> --values // backup with values in the table
Generate Models
The command will generate models from tables in the database.
tspace-mysql generate:models --dir=<folder for creating>
tspace-mysql generate:models --dir=app/Models --env=development --decorators
Migration Models
The command will generate migrations based on the schema in your models to a .sql file,
can also push the migration files to the database.
/**
*
* @arg --push will push the migration files to the database
* @arg --generate will generate the migration files
*/
tspace-mysql migrations:models --dir=<path-to-migration> --models=<path to your models> --generate
tspace-mysql migrations:models --dir=<path-to-migration> --push
tspace-mysql migrations:models --models=src/app/models --dir=migrations --generate
tspace-mysql migrations:models --dir=migrations --push
Migration DB
The command will generate migrations based on the schema in your database to a .sql file,
can also push the migration files to the database.
/**
*
* @arg --push will push the migration files to the database
* @arg --generate will generate the migration files
*/
tspace-mysql migrations:db --dir=<path-to-migration> --generate --env=<YOUR_ENV> -filename=<YOUR_FILENAME>
tspace-mysql migrations:db --dir=<path-to-migration> --push
tspace-mysql migrations:db --dir=migrations --generate --filename=dump.sql --env=development
tspace-mysql migrations:db --dir=migrations --push --filename=dump.sql --env=development