tspace-sql

Query builder object relation mapping

Install

Install with npm:

1npm install tspace-sql --save
2
3/**
4    support mysql & postgresql
5*/
6npm install mysql --save 
7npm install pg --save

Basic Usage

1/**
2 * DB
3 * 
4 * @Usage DB
5*/
6import { DB } from 'tspace-sql'
7(async () => {
8    await new DB().raw('SELECT * FROM users')
9    await new DB().table('users').where('active',true).findMany()
10    await new DB().table('users').whereIn('id',[1,2,3]).where('active','!=',true).findOne()
11    await new DB().table('users').where('active',true).findMany()
12    // Case sensitive where statement
13    await new DB().table('users').whereSensitive('username','Simple').findMany()
14    
15    await new DB()
16       .table('users')
17        .create({
18            name : 'name',
19            username: 'users'
20        }).save()
21
22    await new DB()
23        .table('users')
24        .createMultiple([{
25            name : 'name',
26            username: 'users'
27        },
28        {
29            name : 'name2',
30            username: 'users2'
31        },
32        {
33            name : 'name3',
34            username: 'users3'
35        }]).save()
36
37    await new DB()
38        .table('users')
39        .whereUser(1)
40        .update({
41            name: 'users12345'
42        }).save()
43
44    await new DB().where('id',1).delete()
45
46    await new DB()
47        .table('users')
48        .where('id',1)
49        .updateOrCreate({
50            name: 'users12345'
51        }).save()
52
53    await new DB()
54        .table('users')
55        .whereId(1)
56        .createNotExists({
57            name: 'users12345'
58        }).save()
59
60        /**
61         * transaction statement
62         * 
63        */
64       const transaction = await new DB().beginTransaction()
65
66        try { 
67
68            const user = await new DB()
69                .table('users')
70                .create({
71                    name: 'users12345'
72                },transaction)
73                .save()
74
75            await new DB()
76                .table('posts')
77                .create({
78                    user_id: user.id
79                },transaction).save()
80
81            // try to error     
82            throw new Error('test transaction')
83
84        } catch (err) {
85            await transaction.rollback()
86        }
87})()

Model

support hasOne ,hasMany,belongsTo,belongsToMany

1/**
2 * Model
3 *  
4 * @Usage Model
5*/
6import { Model } from 'tspace-sql'
7import Brand from '../Brand'
8import Role from '../Role'
9import Phone from '../Phone'
10import Car from '../Car'
11
12class User extends Model {
13    constructor(){
14        super()
15        this.hasMany({name : 'phones', model: Phone })   
16        // relation child * prefix with relation parent ex phones.brand
17        this.hasOne({name : 'phones.brand', model: Brand ,child : true}) 
18        this.belongsTo({name : 'car', model: Car })  
19        this.belongsToMany({name : 'roles', model: Role }) 
20    }
21} 
22export default User
23
24import User from '../User'
25
26(async () => {
27    await new User().with('car','phones').withChild('phones.brand').findMany()
28    await new User().with('roles').findOne()
29
30    await new User().where('active',true).findMany()
31    await new User().whereIn('id',[1,2,3]).where('active','!=',true).findOne()
32    await new User().where('active',true).findMany()
33    // Case sensitive where statement
34    await new User().whereSensitive('username','Simple').findMany()
35    
36    await new User()
37        .create({
38            name : 'name',
39            username: 'users'
40        }).save()
41
42    await new User()
43        .createMultiple([{
44            name : 'name',
45            username: 'users'
46        },
47        {
48            name : 'name2',
49            username: 'users2'
50        }]).save()
51
52    await new User()
53        .whereUser(1)
54        .update({
55            name: 'users12345'
56        }).save()
57
58    await new User().where('id',1).delete()
59
60    await new User()
61        .where('id',1)
62        .updateOrCreate({
63            name: 'users12345'
64        }).save()
65
66    await new User()
67        .whereId(1)
68        .createNotExists({
69            name: 'users12345'
70        }).save()
71
72    const user =  await new User().callback(async(query:Model) => {
73        // logic here
74         const users = await query.get()
75         const data = []
76         return [...users,...data]
77    })
78
79})()

Method chaining

method chaining for query data

1/**
2 * Method
3 * 
4 * @Usage Method chaining
5*/
6where(column , operator , value)   
7whereSensitive(column , operator , value) 
8whereId(id)  
9whereUser(userId)  
10whereEmail(value)  
11orWhere(column , operator , value)   
12whereIn(column , [])
13whereNotIn(column , [])
14whereNull(column)
15whereNotNull(column)
16whereBetween (column , [value1 , value2])
17whereSubQuery(colmn , rawSQL)
18
19select(column1 ,column2 ,...N)
20except(column1 ,column2 ,...N)
21only(column1 ,column2 ,...N)
22hidden(column1 ,column2 ,...N)
23join (primary key , table.foreign key) 
24rightJoin (primary key , table.foreign key) 
25leftJoin (primary key , table.foreign key) 
26limit (limit)
27orderBy (column ,'ASC' || 'DSCE')
28having (condition)
29latest (column)
30oldest (column)
31groupBy (column)
32insert(objects)
33create(objects)
34createMultiple(array objects)
35update (objects)
36insertNotExists(objects)
37createNotExists(objects)
38updateOrInsert (objects)
39updateOrCreate (objects)
40
41/** 
42 * relationship
43 * 
44 * @Relation setup name in model
45*/
46with(name1 , name2,...nameN)
47withExists(name1 , name2,...nameN) 
48withChild(nameParent.nameChild1 , nameParent.nameChild2, ...n)
49
50/**
51 * query statement
52 * 
53 *  @exec statement
54*/
55findMany()
56findOne()
57find(id)
58first()
59get()
60all()
61exists ()
62onlyTrashed() // where soft delete
63toSQL()
64toJSON()
65toString()
66toArray(column)
67count(column)
68sum(column)
69avg(column)
70max(column)
71min(column)
72pagination({ limit , page })
73callback(query => {
74    // query statement
75    return ...
76})
77save() /*for statement insert or update */

Cli

npm install tspace-sql -g

1/**
2 * 
3 * 
4 * @cli 
5*/ 
6- tspace-sql make:model <FOLDER/NAME> | tspace-sql make:model <FOLDER/NAME> --m  --f=... --name=....
7    --m  /* created table for migrate in <FOLDER/migrations> */
8    --f=FOLDER/... 
9    /* created table for migrate in <CUSTOM FOLDER> default  <FOLDER/migrations> */ 
10    --js /* extension .js default .ts */
11    --name=NAME /* class name default <NAME> in <FOLDER/NAME> */
12
13- tspace-sql make:table <FOLDER> --name=....
14    --name=TABLENAME  /* created table for migrate in <FOLDER> */
15    --js /* extension .js default .ts */
16
17- tspace-sql migrate <FOLDER> | tspace-sql migrate <FOLDER> --js
18    --js /* extension .js default .ts */
19    
20tspace-sql make:model App/Models/User --m
21
22/*Ex folder 
23- node_modules
24- App
25  - Models
26      User.ts
27*/
28
29/* in App/Models/User.ts */
30import { Model } from 'tspace-sql'
31class User extends Model{
32  constructor(){
33    super()
34    /**
35     * 
36     * 
37     *  @Config Model
38    */
39    this.useDebug()  /* default false *debug raw sql */
40    this.useTimestamp() /* default false * case created_at & updated_at [patern camelCase -> createdAt etc] when insert or update */
41    this.useSoftDelete()  /*  default false * case where deleted_at is null  */
42    this.useTable('Users') /*  default users   */
43    this.usePattern('camelCase') /*  default snake_case  */
44    this.useDefaultOrderBy('id',{ latest : true}) /*  default latest true *DESC  */
45    this.useUUID()
46    this.useRegistry
47    this.useDefaultScope({
48        where : {
49        actived : true
50        }
51    })
52  }
53}
54export default User
55
56tspace-sql make:table App/Models/migrations --name=users
57/* in App/Models/migrations/create_users_table.ts */
58import { Schema , Blueprint , DB } from 'tspace-sql'
59(async () => {
60    await new Schema().table('users',{ 
61        id :  new Blueprint().int().notNull().primary().autoIncrement(),
62        name : new Blueprint().varchar(120).default('my name'),
63        email : new Blueprint().varchar(255).unique(),
64        email_verify : new Blueprint().tinyInt(),
65        password : new Blueprint().varchar(255),
66    })
67
68    /*
69        await new DB().table('users').insert({
70            name : 'your name ...'
71            email : 'email@gmail.com',
72            email_verify : false,
73            password : 'hash password'
74        }).save()
75    */
76
77})()
78/* migrate all table in folder into database */
79tspace-sql migrate App/Models/migrations

Setup

.env connection to database

1NODE_ENV = development // production
2// development
3DB_DIALECT = mysql
4DB_HOST = localhost
5DB_PORT = 3306
6DB_USERNAME = root
7DB_PASSWORD = password
8DB_DATABASE = database
9
10// production
11DB_DIALECT_PROD = pg
12DB_HOST_PROD = localhost
13DB_PORT_PROD = 5432
14DB_USERNAME_PROD = root
15DB_PASSWORD_PROD = password
16DB_DATABASE_PROD = database