User Tools

Site Tools


Sample CRUD model and controller

Router

router.js
'use sctrict'
...
const users = require('users-controller').new(knex)
const express = require('express')
const app = express()
 
app.get('/api/users/list', users.list.bind(users))
app.post('/api/users/add', users.add.bind(users))
app.post('/api/users/update', users.update.bind(users))
app.post('/api/users/drop', users.drop.bind(users))

Model

user-model.js
'use strict'
 
class Users {
    constructor(knex) {
        this.knex = knex
    }
    login(params) {
        return this.knex
            .select([
                'users.*',
                'domains.name as domain_name',
                'roles.description as role_description',
                'roles.name as role_name',
            ])
            .from('users')
            .leftJoin('domains as domains', 'domains.id', 'users.domain_id')
            .leftJoin('roles as roles', 'roles.id', 'users.role_id')
            .where({
                'users.name': params.userName,
                'users.password': params.password,
                'domains.name': params.domainName
            })
    }
    list() {
        return this.knex
            .select([
                'users.*',
                'domains.name as domain_name',
                'roles.description as role_description',
                'roles.name as role_name',
            ])
            .from('users')
            .leftJoin('domains as domains', 'domains.id', 'users.domain_id')
            .leftJoin('roles as roles', 'roles.id', 'users.role_id')
            .orderBy('users.name')
    }
 
    listId(params) {
        return this.knex
            .select([
                'users.*',
                'domains.name as domain_name',
                'roles.description as role_description',
                'roles.name as role_name',
            ])
            .from('users')
            .leftJoin('domains as domains', 'domains.id', 'users.domain_id')
            .leftJoin('roles as roles', 'roles.id', 'users.role_id')
            .where({
                'users.id': params.id,
            })
            .orderBy('users.name')
    }
    add(params) {
        return this.knex
            .insert({
                gecos: params.gecos,
                name: params.name,
                password: params.password,
                domain_id: params.domain_id,
                role_id: params.role_id
            })
            .into('users')
    }
    update(params) {
        return this.knex
            .update({
                gecos: params.gecos,
                name: params.name,
                password: params.password,
                domain_id: params.domain_id,
                role_id: params.role_id
            })
            .from('users')
            .where({
                id: params.id
            })
    }
    drop(params) {
        return this.knex
            .del()
            .from('users')
            .where({
                id: params.id
            })
    }
    static new(knex) {
        return new Users(knex)
    }
}
 
module.exports = Users

Controler

user-controller.js
'use strict'
 
const usersModel = require('models/users-model')
 
class usersController {
    constructor(knex) {
        this.knex = knex
    }
    login(req, res) {
        var params = req.body.params
        var userName = params.login.split('@')[0]
        var domainName = params.login.split('@')[1]
        usersModel
            .new(this.knex)
            .login({
                userName: userName,
                domainName: domainName,
                password: params.password
            })
            .then(function(result) {
                if (result.length) {
                    req.session.userId = result[0].id
                    req.session.user = result[0]
                    res.send({
                        result: true
                    })
                } else
                    res.send({
                        result: false
                    })
 
            })
            .catch(function(error) {
                console.log(error)
                res.send({
                    result: false
                })
            })
    }
    logout(req, res) {
        req.session.destroy(function(err) {
            res.redirect('/')
        })
 
    }
    list(req, res) {
            usersModel
                .new(this.knex)
                .list()
                .then(function(result) {
                    res.send({
                        result: result
                    })
                })
                .catch(function(error) {
                    console.log(error)
                    res.send({
                        result: false
                    })
                })
    }
    add(req, res) {
        var params = req.body.params
        console.log(params)
        usersModel
            .new(this.knex)
            .add(params)
            .then(function(result) {
                res.send({
                    result: true
                })
            })
            .catch(function(error) {
                console.log(error)
                res.send({
                    result: false
                })
            })
    }
    update(req, res) {
        var params = req.body.params
        usersModel
            .new(this.knex)
            .update(params)
            .then(function(result) {
                res.send({
                    result: true
                })
            })
            .catch(function(error) {
                console.log(error)
                res.send({
                    result: false
                })
            })
    }
    drop(req, res) {
        var params = req.body.params
        usersModel
            .new(this.knex)
            .drop(params)
            .then(function(result) {
                res.send({
                    result: true
                })
            })
            .catch(function(error) {
                console.log(error)
                res.send({
                    result: false
                })
            })
    }
    static new(knex) {
        return new usersController(knex)
    }
}
 
module.exports = usersController

Output

output.txt
$ curl -v 'http://127.0.0.1:3100/api/users/list'
*   Trying 127.0.0.1...
* Connected to 127.0.0.1 (127.0.0.1) port 3100 (#0)
> GET /api/users/list HTTP/1.1
> Host: 127.0.0.1:3100
> User-Agent: curl/7.48.0
> Accept: */*
> 
< HTTP/1.1 200 OK
< X-Powered-By: Express
< Access-Control-Allow-Origin: *
< Access-Control-Allow-Headers: Origin, X-Requested-With, Content-Type, Accept
< Content-Type: application/json; charset=utf-8
< Content-Length: 932
< ETag: W/"3a4-nBO5wjkg1xdzhMVOuqc3Ng00ST0"
< set-cookie: session=s%3Aj6m7SqFww8qXTfhjJi7NIXisShLhaN8i.Hu%2BsAFg9nuVHnVQ7V5xUFoFUujaudXNk9%2BoBfsKo%2FOw; Path=/; Expires=Fri, 09 Nov 2018 11:17:26 GMT
< X-Response-Time: 70.716ms
< Vary: Accept-Encoding
< Date: Fri, 09 Nov 2018 11:07:26 GMT
< Connection: keep-alive
< 
* Connection #0 to host 127.0.0.1 left intact
 
{
    "result": [{
        "id": 9,
        "name": "qwerty",
        "password": "12345",
        "gecos": null,
        "domain_id": 2,
        "role_id": 1,
        "created_at": "2018-10-24 20:14:20",
        "updated_at": "2018-10-24 20:14:20",
        "domain_name": "sample.org",
        "role_description": "Superuser",
        "role_name": "superuser"
    }, {
        "id": 1,
        "name": "user",
        "password": "12345",
        "gecos": null,
        "domain_id": 2,
        "role_id": 2,
        "created_at": "2018-10-24 19:46:09",
        "updated_at": "2018-10-24 19:46:09",
        "domain_name": "sample.org",
        "role_description": "User",
        "role_name": "user"
    }, {
        "id": 11,
        "name": "user",
        "password": "12345",
        "gecos": null,
        "domain_id": 1,
        "role_id": 2,
        "created_at": "2018-10-24 22:15:23",
        "updated_at": "2018-10-24 22:15:23",
        "domain_name": "sample.com",
        "role_description": "User",
        "role_name": "user"
    }, {
        "id": 10,
        "name": "zairtant",
        "password": "12345",
        "gecos": null,
        "domain_id": 2,
        "role_id": 1,
        "created_at": "2018-10-24 20:15:07",
        "updated_at": "2018-10-24 20:15:07",
        "domain_name": "sample.org",
        "role_description": "Superuser",
        "role_name": "superuser"
    }]

Schema

migration.js
exports.up = function(knex, Promise) {
    return knex.schema
        .createTable('domains', function(table) {
            table.increments('id').primary().unique()
            table.string('name').unique()
            table.timestamp('created_at').defaultTo(knex.fn.now())
            table.timestamp('updated_at').defaultTo(knex.fn.now())
        })
        .createTable('roles', function(table) {
            table.increments('id').primary().unique()
            table.string('name')
            table.string('description')
            table.timestamp('created_at').defaultTo(knex.fn.now())
            table.timestamp('updated_at').defaultTo(knex.fn.now())
        })
        .createTable('users', function(table) {
            table.increments('id').primary().unique()
            table.string('name')
            table.string('password')
            table.string('gecos')
            table.integer('domain_id').references('domains.id')
            table.integer('role_id').references('roles.id')
            table.timestamp('created_at').defaultTo(knex.fn.now())
            table.timestamp('updated_at').defaultTo(knex.fn.now())
        })
}
 
exports.down = function(knex, Promise) {
    return knex.schema
        .dropTable('domains')
        .dropTable('roles')
        .dropTable('users')
}