tspace-sql

Query builder object relation mapping

Install

Install with npm:

npm install tspace-sql --save

/**
    support mysql & postgresql
*/
npm install mysql --save 
npm install pg --save

Basic Usage

/**
 * DB
 * 
 * @Usage DB
*/
import { DB } from 'tspace-sql'
(async () => {
    await new DB().raw('SELECT * FROM users')
    await new DB().table('users').where('active',true).findMany()
    await new DB().table('users').whereIn('id',[1,2,3]).where('active','!=',true).findOne()
    await new DB().table('users').where('active',true).findMany()
    // Case sensitive where statement
    await new DB().table('users').whereSensitive('username','Simple').findMany()
    
    await new DB()
       .table('users')
        .create({
            name : 'name',
            username: 'users'
        }).save()

    await new DB()
        .table('users')
        .createMultiple([{
            name : 'name',
            username: 'users'
        },
        {
            name : 'name2',
            username: 'users2'
        },
        {
            name : 'name3',
            username: 'users3'
        }]).save()

    await new DB()
        .table('users')
        .whereUser(1)
        .update({
            name: 'users12345'
        }).save()

    await new DB().where('id',1).delete()

    await new DB()
        .table('users')
        .where('id',1)
        .updateOrCreate({
            name: 'users12345'
        }).save()

    await new DB()
        .table('users')
        .whereId(1)
        .createNotExists({
            name: 'users12345'
        }).save()

        /**
         * transaction statement
         * 
        */
       const transaction = await new DB().beginTransaction()

        try { 

            const user = await new DB()
                .table('users')
                .create({
                    name: 'users12345'
                },transaction)
                .save()

            await new DB()
                .table('posts')
                .create({
                    user_id: user.id
                },transaction).save()

            // try to error     
            throw new Error('test transaction')

        } catch (err) {
            await transaction.rollback()
        }
})()

Model

support hasOne ,hasMany,belongsTo,belongsToMany

/**
 * Model
 *  
 * @Usage Model
*/
import { Model } from 'tspace-sql'
import Brand from '../Brand'
import Role from '../Role'
import Phone from '../Phone'
import Car from '../Car'

class User extends Model {
    constructor(){
        super()
        this.hasMany({name : 'phones', model: Phone })   
        // relation child * prefix with relation parent ex phones.brand
        this.hasOne({name : 'phones.brand', model: Brand ,child : true}) 
        this.belongsTo({name : 'car', model: Car })  
        this.belongsToMany({name : 'roles', model: Role }) 
    }
} 
export default User

import User from '../User'

(async () => {
    await new User().with('car','phones').withChild('phones.brand').findMany()
    await new User().with('roles').findOne()

    await new User().where('active',true).findMany()
    await new User().whereIn('id',[1,2,3]).where('active','!=',true).findOne()
    await new User().where('active',true).findMany()
    // Case sensitive where statement
    await new User().whereSensitive('username','Simple').findMany()
    
    await new User()
        .create({
            name : 'name',
            username: 'users'
        }).save()

    await new User()
        .createMultiple([{
            name : 'name',
            username: 'users'
        },
        {
            name : 'name2',
            username: 'users2'
        }]).save()

    await new User()
        .whereUser(1)
        .update({
            name: 'users12345'
        }).save()

    await new User().where('id',1).delete()

    await new User()
        .where('id',1)
        .updateOrCreate({
            name: 'users12345'
        }).save()

    await new User()
        .whereId(1)
        .createNotExists({
            name: 'users12345'
        }).save()

    const user =  await new User().callback(async(query:Model) => {
        // logic here
         const users = await query.get()
         const data = []
         return [...users,...data]
    })

})()

Method chaining

method chaining for query data

/**
 * Method
 * 
 * @Usage Method chaining
*/
where(column , operator , value)   
whereSensitive(column , operator , value) 
whereId(id)  
whereUser(userId)  
whereEmail(value)  
orWhere(column , operator , value)   
whereIn(column , [])
whereNotIn(column , [])
whereNull(column)
whereNotNull(column)
whereBetween (column , [value1 , value2])
whereSubQuery(colmn , rawSQL)

select(column1 ,column2 ,...N)
except(column1 ,column2 ,...N)
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)
orderBy (column ,'ASC' || 'DSCE')
having (condition)
latest (column)
oldest (column)
groupBy (column)
insert(objects)
create(objects)
createMultiple(array objects)
update (objects)
insertNotExists(objects)
createNotExists(objects)
updateOrInsert (objects)
updateOrCreate (objects)

/** 
 * relationship
 * 
 * @Relation setup name in model
*/
with(name1 , name2,...nameN)
withExists(name1 , name2,...nameN) 
withChild(nameParent.nameChild1 , nameParent.nameChild2, ...n)

/**
 * query statement
 * 
 *  @exec statement
*/
findMany()
findOne()
find(id)
first()
get()
all()
exists ()
onlyTrashed() // where soft delete
toSQL()
toJSON()
toString()
toArray(column)
count(column)
sum(column)
avg(column)
max(column)
min(column)
pagination({ limit , page })
callback(query => {
    // query statement
    return ...
})
save() /*for statement insert or update */

Cli

npm install tspace-sql -g

/**
 * 
 * 
 * @cli 
*/ 
- tspace-sql make:model <FOLDER/NAME> | tspace-sql make:model <FOLDER/NAME> --m  --f=... --name=....
    --m  /* created table for migrate in <FOLDER/migrations> */
    --f=FOLDER/... 
    /* created table for migrate in <CUSTOM FOLDER> default  <FOLDER/migrations> */ 
    --js /* extension .js default .ts */
    --name=NAME /* class name default <NAME> in <FOLDER/NAME> */

- tspace-sql make:table <FOLDER> --name=....
    --name=TABLENAME  /* created table for migrate in <FOLDER> */
    --js /* extension .js default .ts */

- tspace-sql migrate <FOLDER> | tspace-sql migrate <FOLDER> --js
    --js /* extension .js default .ts */
    
tspace-sql make:model App/Models/User --m

/*Ex folder 
- node_modules
- App
  - Models
      User.ts
*/

/* in App/Models/User.ts */
import { Model } from 'tspace-sql'
class User extends Model{
  constructor(){
    super()
    /**
     * 
     * 
     *  @Config Model
    */
    this.useDebug()  /* default false *debug raw sql */
    this.useTimestamp() /* default false * case created_at & updated_at [patern camelCase -> createdAt etc] when insert or update */
    this.useSoftDelete()  /*  default false * case where deleted_at is null  */
    this.useTable('Users') /*  default users   */
    this.usePattern('camelCase') /*  default snake_case  */
    this.useDefaultOrderBy('id',{ latest : true}) /*  default latest true *DESC  */
    this.useUUID()
    this.useRegistry
    this.useDefaultScope({
        where : {
        actived : true
        }
    })
  }
}
export default User

tspace-sql make:table App/Models/migrations --name=users
/* in App/Models/migrations/create_users_table.ts */
import { Schema , Blueprint , DB } from 'tspace-sql'
(async () => {
    await new Schema().table('users',{ 
        id :  new Blueprint().int().notNull().primary().autoIncrement(),
        name : new Blueprint().varchar(120).default('my name'),
        email : new Blueprint().varchar(255).unique(),
        email_verify : new Blueprint().tinyInt(),
        password : new Blueprint().varchar(255),
    })

    /*
        await new DB().table('users').insert({
            name : 'your name ...'
            email : '[email protected]',
            email_verify : false,
            password : 'hash password'
        }).save()
    */

})()
/* migrate all table in folder into database */
tspace-sql migrate App/Models/migrations

Setup

.env connection to database

NODE_ENV = development // production
// development
DB_DIALECT = mysql
DB_HOST = localhost
DB_PORT = 3306
DB_USERNAME = root
DB_PASSWORD = password
DB_DATABASE = database

// production
DB_DIALECT_PROD = pg
DB_HOST_PROD = localhost
DB_PORT_PROD = 5432
DB_USERNAME_PROD = root
DB_PASSWORD_PROD = password
DB_DATABASE_PROD = database