NilFactor SQL DB

NF-SQL-DB is a framework for dealing with postgresql databases in NodeJS. Allows for easy creation of modules for dealing with interfacing with postgres. Built using pg node package.

This is just a simply lightweight tool with limited frills. If feature requests are made I may add them. I wrote this for simple uses and didn't need joins and all that. I just wanted a simply way to create objects that would save with out a bunch of work.

Updated: 2018-06-16

• Fixed issue with updating entries

Configuration - Set Environment Variables

$ PGUSER=dbuser \
  PGHOST=database.server.com \
  PGPASSWORD=secretpassword \
  PGDATABASE=mydb \
  PGPORT=3211 \
  node script.js

Example - Module

// Example Module
const db = require('nf-sql-db');

module.exports = () => {
    var table = {
        tableName: 'users',
        primaryKey: 'user_id',
        columns: [
            'user_id',
            'username',
            'password',
            'email',
            'first_name',
            'last_name',
            'active',
            'last_login',
            'date_created',
            'reset_hash',
            'reset_hash_experation'
        ],
        type: {
            'user_id': 'integer',
            'username': 'text',
            'password': 'hash',
            'email': 'text',
            'first_name': 'text',
            'last_name': 'text',
            'active': 'boolean',
            'last_login': 'timestamp',
            'date_created': 'timestamp',
            'reset_hash': 'hash',
            'reset_hash_experation': 'timestamp'
        },
        delete: () => {
            return db.delete(table);
        },
        save: (object) => {
            if (typeof object == 'undefined') {
                return db.save(table);
            } else {
                return db.save(object);
            }
        },
        findById: (id) => {
            return db.findById(table, id);
        },
        find: (params, options) => {
            return db.find(table, params, options);
        },
        original: {
            user_id: null,
            username: null,
            password: null,
            email: null,
            first_name: null,
            last_name: null,
            active: null,
            last_login: null,
            date_created: null,
            reset_hash: null,
            reset_hash_experation: null,
        },
        user_id: null,
        username: null,
        password: null,
        email: null,
        first_name: null,
        last_name: null,
        active: null,
        last_login: null,
        date_created: null,
        reset_hash: null,
        reset_hash_experation: null,
    };

    return table;
};

Example - Using Module

var user = require('./example_table');

user.connect();
user.username = 'richard';
user.password = 'test';
user.email = '[email protected]';
user.first_name = 'Richard';
user.last_name = 'Williamson';
user.active = true;
user.last_login = null;
user.date_created = new Date();

// save your user
user.save()
    .then((user) => {
        console.log(user);
    })
    .catch((error) => {
        console.log("ERROR: ", error);
        user.close();
    });

Functions to Note

1. connect() - connects to the postgres db
2. close() - closes connection to postgres db
3. save(object) - inserts/updates object given, where object is a module like example above
4. findByID(object, id) - finds the given object type in the database by primary key id, where object is a module like example above
5. find(object, params, options (optional)) - find object(s) based on given params and options where object is a module like example above params are like so {active: true, "email IS NOT NULL": null} options are optional and can be like the example below
6. delete(object) - deletes given object from database

options to pass in for find

1. array columns - array of strings with column names to select
2. array sort - array of strings with column names to sort on

var options = {
    columns: ['user_id', 'user_name', 'email'],
    sort: ['user_name', 'email']
}