tspace-sqlorm

tspace-sqlorm

Query builder object relation mapping

Install

Install with npm:

npm install tspace-sqlorm --save

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

Usage

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

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

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

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

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

Model for relationship support hasOne ,hasMany,belongsTo,belongsToMany

/**
 * Model
 * 
 * @Usage Model
*/
import { Model } from 'tspace-sqlorm'
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 in relation parent required name parent . name child
            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').withHas('phones.brand').get()
})()

Method chaining

method chaining for query data

/**
 * Method
 * 
 * @Usage Method chaining
*/
where(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 ,...columnn)
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)
hidden (...columns)
insert(objects)
create(objects)
update (objects)
insertNotExists(objects)
createNotExists(objects)
updateOrInsert (objects)
updateOrCreate (objects)

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

/**
 * query statement
 * 
 *  @exec statement
*/
all()
get()
first()
find(id)
exists ()
toSQL()
toJSON()
toArray(column)
count(column)
sum(column)
avg(column)
max(column)
min(column)
pagination({ limit , page })
save() /*for statement insert or update */

Cli

npm install tspace-sqlorm -g

/**
 * 
 * 
 * @cli 
*/ 
- tspace-sqlorm make:model <FOLDER/NAME> | tspace-sqlorm 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-sqlorm make:table <FOLDER> --name=....
    --name=TABLENAME  /* created table for migrate in <FOLDER> */
    --js /* extension .js default .ts */

- tspace-sqlorm migrate <FOLDER> | tspace-sqlorm migrate <FOLDER> --js
    --js /* extension .js default .ts */

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

tspace-sqlorm make:table App/Models/migrations --name=users
/* in App/Models/migrations/create_users_table.ts */
import { Schema , Blueprint } from 'tspace-sqlorm'
(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),
    })
})()
/* migrate all table in folder into database */
tspace-sqlorm 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