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.
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
}
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 |
+-------------+--------------+----------------------------+
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").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();
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").offset(1).findOne();
Joins
Inner Join Clause
await new DB("posts").join("posts.user_id", "users.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();
const users = await new DB("users")
.where("id", 1)
.orWhereQuery((query) => {
return query
.where("id", "<>", 2)
.where("username", "try to find")
.where("email", "find@example.com");
})
.findMany();
Where Object Clauses
import { Operator } from 'tspace-mysql'
const whereObject = await new DB("users")
.whereObject({
id : Operator.eq(1),
username : Operator.in(['user1','user2']),
name : Operator.like('%value%')
})
.findMany();
const where = await new DB("users")
.where({
id : 1,
username : 'username1',
name : Operator.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();
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",
},
},
])
.whereIn("id", [1, 2])
.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}`,
};
});
More Methods
where(column , operator , value)
whereSensitive(column , operator , value)
whereId(id)
whereUser(userId)
whereEmail(value)
whereIn(column , [])
whereNotIn(column , [])
whereNull(column)
whereNotNull(column)
whereBetween (column , [value1 , value2])
whereQuery(callback)
whereJson(column, { targetKey, value , operator })
whereRaw(sql)
whereExists(sql)
whereSubQuery(colmn , rawSQL)
whereNotSubQuery(colmn , rawSQL)
orWhere(column , operator , value)
orWhereRaw(sql)
orWhereIn(column , [])
orWhereSubQuery(colmn , rawSQL)
when(contition , callback)
select(column1 ,column2 ,...N)
distinct()
selectRaw(column1 ,column2 ,...N)
except(column1 ,column2 ,...N)
exceptTimestamp()
only(column1 ,column2 ,...N)
hidden(column1 ,column2 ,...N)
join(primary key , table.foreign key)
rightJoin (primary key , table.foreign key)
leftJoin (primary key , table.foreign key)
limit (limit)
having (condition)
havingRaw (condition)
orderBy (column ,'ASC' || 'DSCE')
orderByRaw(column ,'ASC' || 'DSCE')
latest (column)
latestRaw (column)
oldest (column)
oldestRaw (column)
groupBy (column)
groupByRaw (column)
create(objects)
createMultiple(array objects)
update (objects)
updateMany (objects)
updateMultiple(array objects)
createNotExists(objects)
updateOrCreate (objects)
onlyTrashed()
connection(options)
backup({ database , connection })
backupToFile({ filePath, database , connection })
hook((result) => ...)
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()
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)
])
}
}
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()
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()
Deeply Nested Relations
Relationships can involve deep connections.
Let's 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 })
}
}
+--------------------------------------------------------------------------+
class Comment extends Model {
constructor(){
super()
this.hasMany({ name : 'users' , model : User })
this.belongsTo({ name : 'post' , model : Post })
}
}
+--------------------------------------------------------------------------+
await new User()
.relations('posts')
.relationQuery('posts', (query : Post) => {
return query.relations('comments','user')
.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')
})
})
})
.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 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()
}
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(() => new Blueprint().int().notNull().primary().autoIncrement())
public id!: number
@Column(() => new Blueprint().varchar(50).null())
public uuid!: string
@Column(() => new 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(() => new Blueprint().varchar(50).null())
public name !: string
@Column(() => new Blueprint().varchar(50).null())
public username !: string
@Column(() => new Blueprint().varchar(50).null())
public password !: string
@Column(() => new Blueprint().timestamp().null())
public createdAt!: Date
@Column(() => new Blueprint().timestamp().null())
public updatedAt!: Date
@Column(() => new 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: new Blueprint().int().notNull().primary().autoIncrement(),
uuid: new Blueprint().varchar(50).null(),
name: new Blueprint().varchar(191).notNull(),
email: new Blueprint().varchar(191).notNull(),
createdAt: new Blueprint().timestamp().null().bindColumn('created_at'),
updatedAt: new Blueprint().timestamp().null().bindColumn('updated_at'),
deletedAt: new Blueprint().timestamp().null().bindColumn('deleted_at')
}
type TSchemaUser = TSchema<typeof Schema>
class User extends Model
<TSchemaUser>
{
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: new Blueprint().int().notNull().primary().autoIncrement(),
uuid: new Blueprint().varchar(50).null(),
name: new Blueprint().varchar(191).notNull(),
email: new Blueprint().varchar(191).notNull(),
createdAt: new Blueprint().timestamp().null(),
updatedAt: new Blueprint().timestamp().null(),
deletedAt: new 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.beforeCreatingTable(async () => {
return await new User()
.create({
...columns,
})
.void()
.save();
});
this.useSchema({
id: new Blueprint().int().notNull().primary().autoIncrement(),
uuid: new Blueprint().varchar(50).null(),
email: new Blueprint().int().notNull().unique(),
name: new Blueprint().varchar(255).null(),
created_at: new Blueprint().timestamp().null(),
updated_at: new Blueprint().timestamp().null(),
deleted_at: new 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: new Blueprint().int().notNull().primary().autoIncrement(),
uuid: new Blueprint().varchar(50).null(),
user_id: new Blueprint().int().notNull().foreign({
references: "id",
on: User,
onDelete: "CASCADE",
onUpdate: "CASCADE",
}),
title: new Blueprint().varchar(255).null(),
created_at: new Blueprint().timestamp().null(),
updated_at: new Blueprint().timestamp().null(),
deleted_at: new 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 });
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 } from 'tspace-mysql'
import Phone from '../Phone'
const schemaUser = {
id :new Blueprint().int().notNull().primary().autoIncrement(),
uuid :new Blueprint().varchar(50).null(),
email :new Blueprint().varchar(50).null(),
name :new Blueprint().varchar(255).null(),
username : new Blueprint().varchar(255).null(),
password : new Blueprint().varchar(255).null(),
createdAt :new Blueprint().timestamp().null(),
updatedAt :new Blueprint().timestamp().null()
}
type TSchemaUser = TSchema<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 } from 'tspace-mysql'
import { User } from './User.ts'
const schemaPhone = {
id :new Blueprint().int().notNull().primary().autoIncrement(),
uuid :new Blueprint().varchar(50).null(),
userId : new Blueprint().int().notNull(),
number :new Blueprint().varchar(50).notNull(),
createdAt :new Blueprint().timestamp().null(),
updatedAt :new Blueprint().timestamp().null()
}
type TSchemaPhone = TSchema<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 users = await new User().select('id','username').findMany() ✅
const users = await new User().select('idx','username').findMany() ❌
const users = await new User().except('id','username').findMany() ✅
const users = await new User().except('idx','username').findMany() ❌
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 , TRelation , TSchemaModel } from 'tspace-mysql'
import { Phone } from '../Phone'
const schemaUser = {
id :new Blueprint().int().notNull().primary().autoIncrement(),
uuid :new Blueprint().varchar(50).null(),
email :new Blueprint().varchar(50).null(),
name :new Blueprint().varchar(255).null(),
username : new Blueprint().varchar(255).null(),
password : new Blueprint().varchar(255).null(),
createdAt :new Blueprint().timestamp().null(),
updatedAt :new Blueprint().timestamp().null()
}
type TSchemaUser = TSchema<typeof schemaUser>
type TRelationUser = TRelation<{
phones : TSchemaModel<Phone>[]
phone : TSchemaModel<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 , TSchemaModel } from 'tspace-mysql'
import { User } from './User.ts'
const schemaPhone = {
id :new Blueprint().int().notNull().primary().autoIncrement(),
uuid :new Blueprint().varchar(50).null(),
userId : new Blueprint().int().notNull(),
number :new Blueprint().varchar(50).notNull(),
createdAt :new Blueprint().timestamp().null(),
updatedAt :new Blueprint().timestamp().null()
}
type TSchemaPhone = TSchema<typeof schemaPhone>
type TRelationPhone = TRelation<{
user : TSchemaModel<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 , Operator } from 'tspace-mysql'
import { User } from '../Models/User'
import { Phone } from '../Models/Phone'
const userRepository = Repository.bind(User)
const needPhone = true
const user = await userRepository.findOne({
select : ['id','name','username'],
where : {
id: 1
},
when : {
condition : `${needPhone}`,
callback: () => ({
relations : ['phone']
})
}
})
const users = await userRepository.findMany({
select : ['id','name','username'],
limit : 3,
orderBy : {
id : 'ASC',
name : 'DESC'
}
groupBy : ['id'],
where : {
id: Operator.in([1,2,3])
}
})
const userPaginate = await userRepository.pagination({
select : ['id','name','username'],
page : 1,
limit : 3,
where : {
id: Operator.in([1,2,3])
}
})
const findFullName = await userRepository.findOne({
select : ['name',`${DB.raw('CONCAT(firstName," ",lastName) as fullName')}`],
whereRaw : [
`CONCAT(firstName," ",lastName) LIKE '%${search}%'`
]
})
const userHasPhones = await userRepository.findOne({
select : ['*'],
where : {
id: 1
},
relations : ['post'],
relationQuery:{
name : 'post',
callback: () => ({
select: ['id', 'userId', 'name'],
relations : ['user']
}) as TRepository<Phone>
}
})
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()
}
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 : new Blueprint().int().notNull().primary().autoIncrement(),
uuid : new Blueprint().varchar(120).null()
name : new Blueprint().varchar(120).default('name'),
email : new Blueprint().varchar(255).unique().notNull(),
email_verify : new Blueprint().tinyInt(),
password : new Blueprint().varchar(255),
json : new Blueprint().json(),
created_at : new Blueprint().null().timestamp(),
updated_at : new Blueprint().null().timestamp(),
deleted_at : new 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} })
bindColumn('< real name column in table >')
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 "database" --values // backup with values in the tables
tspace-mysql dump:table "table" --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 --db=<db-name>
tspace-mysql migrations:db --dir=<path-to-migration> --push
tspace-mysql migrations:db --dir=migrations --generate --db=db-migratons
tspace-mysql migrations:db --dir=migrations --push