pg-accessor

pg-accessor is a handy Node.js CLI package for PostgreSQL databases that makes creating Promise based getter and setter functions for the tables a breeze.

The getter and setter functions are built using the amazing pg-promise package for async data management. Please refer to its documentation if you want to know more about the pg-promise functions generated by this package.

Table of Contents

• Installation
• Usage
• The config.js file
  • 1. getter {..}
  • 2. setter {..}
  • 3. Parameterized Values
  • 4. Custom Accessors
• Examples
• Contributing
• Contact
• License

Installation

# For easiest use, install in global:
npm install -g pg-accessor 
# Usage: accessor <command>

# Local installation (relative path required):
npm install pg-accessor 
# Usage: ./node_modules/.bin/accessor <command>

Usage

The usage is pretty simple, and involves running 2 important commands.

• Make sure the postgresql server is running.
• Export the DATABASE_URL to the environment by using the export command or adding it to a ".env" file.
• First run accessor init. This will create config.js file in the ./db directory.
  • The config.js file consists of an object with various specifications that are easily customizable.
  • Refer here to learn how.
• (Optional) You can run accessor status to know which functions will and will not be generated.
• Once you're done customizing the config.js file, run accessor build.
  • This will generate getter and setter files with the specifications defined in config.js.

One can always run accessor --help to see the list of available options and commands.

Notes :

1. Existing accessor files with the same name are protected from being overwritten. Rebuilding can be forced by passing the overwrite flag (-o, --overwrite).
2. It is recommended you use snake case for table names to output table names inside the accessors as camel case. Eg: my_table becomes myTable, order_id becomes orderId, etc.

The config.js file

config.js consists a list of all the database tables further broken down into getter and setter properties. These properties are further divided into required and optional properties. If one of the required child property is left empty, the parent property i.e. the getter or setter for that table will not be built.

To prevent the building of both—getter and setter—for certain tables, just delete the entire table object from the config.js file.Similarly, to prevent the building of only one—either the getter or the setter—of a certain table, just delete the getter/setter objects of that table. Refer to the examples section to learn more.

Note : The table object names are only arbitrary identifiers to make it easy for user to identify which accessors are for which tables. Their names don't effect the creation of the accessor functions in any way.

Below are the general specificiations of various properties of a single table object, and how you can use them.

1. getter {..}

| Property | Type | Required | Description | |:--------:|:------:|:--------:|:-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------:| | name | String | Yes | The name of the getter function.Autogenerated, but can be changed as desired. | | select | Array | Yes | The columns to be pulled from database.By default contains all the columns in the table, but can delete the ones that are not required.If all columns are required, just put an asterisk (*). | | from | String | Yes | The name of the table you want to obtain the data from.Autogenerated, but can be altered to use more complex tables like in case of joins. | | where | Object | No | Conditions specified in form of key value pairs.Eg: {"name":"John", "id":123} |

2. setter {..}

| Property | Type | Required | Description | |:--------:|:------:|:--------:|:------------------------------------------------------------------------------------------------------------------------------------------------------------------------------:| | name | String | Yes | The name of the setter function.Autogenerated, but can be changed as desired. | | base | String | Yes | The name of the table you want to update the data in. | | set | Object | Yes | The new values in the form of key-value pairs.The keys are essentially the column names, and the values are the new data you want to put in that column.Eg: {"age":18} | | where | Object | No | Conditions specified in form of key value pairs.Eg: {"name":"John", "id":123} |

3. Parameterized Values

It is possible to create field values of the getter and setter function to be parameterized. These values will not be predefined, but will be sent as arguments to the getter or setter function during the time of execution of your program

To create a parameterized field, just use a dollar sign ($) followed by the variable name. For example, if you want to get username as an argument to the WHERE clause of a getter function, just add the following to the "where" object: "username": "$username". Refer to examples section for better examples.

4. Custom Accessors

It is also possible to create additional custom accessors. This may be needed when more than one getter/setter is required for the same table.

To do so, you just need to create another table object that matches the general structure. Refer to this example to learn more.

Examples

Below are the examples of customizing the config.js file to fit the needs of user.

1. Generate getter and setter for USERS table.

The following config.js file will return all columns and rows for the getter; and will will update the age of a particular user using the user parameter to setter.

// config.js
module.exports = {
  "USERS": {              // Table object
    "getter": {
      "name": "getUsers", // Autogenerated
      "select": ["*"],    // Was autogenerated with as ["id","user","age","city"], but changed it to ["*"].
      "from": "Users",    // Autogenerated
      "where": {}         // No condition clause
    },
    "setter": {
      "name": "setAge",   // Was autogenerated as setUsers, but changed it to setAge
      "base": "Users",    // Autogenerated
      "set": {
        "age": "$age"     // Creating a parameter for age, that is to be sent to the setter function.
      },
      "where": {
        "user": "$user"   // Like above creating a parameter for user
      }
    }
  }
}

The resulting output files will look like:

// getUsers.js
const db = require('./index');

const QUERY = `SELECT * FROM Users`;

const getUsers = () => {
  return db.any(QUERY, [])
    .then((data) => {
      return data;
    }).catch((err) => {
      console.error(err);
      process.exit(1);
    });
}
module.exports = getUsers;

// setAge.js
const db = require('./index');

const QUERY = `UPDATE Users SET age=$1 WHERE user=$2`;

const setAge = (age,user) => {
  return db.query(QUERY, [age,user])
    .catch((err) => {
      console.error(err);
      process.exit(1);
    });
}
module.exports = setAge;

2. Generating a getter with a complex FROM clause.

The generated getter function will return the user's name and salary of the ones living in San Francisco, and of the age as passed as a parameter to the getter function.

// config.js
module.exports = {
  "USERS": {
    "getter": {
      "name": "getSalary",          // Was autogenerated to "getUsers", but changed to "getSalary".
      "select": ["user", "salary"], // Selecting user and salary columns
      "from": "users INNER JOIN employees ON users.id = employees.id", // Complex FROM clause
      "where": {
        "age": "$age",              // age parameter
        "city": "San Francisco"
      }
    } // Setter was autogenerated after this, but deleted it because was not required.
  }
}

3. Creating additional accessors

// config.js
module.exports = {
  .  //
  .  // Previously autogenerated table objects
  .  //
  }, //
  "MyCustomAccessor": { // Manually built table object. Name does not matter
    "getter": {
      "name": "getCity",
      "select": ["user","id"],
      "from": "users"
      "where": {
        "user": "$name"
      }
    }, 
    "setter": {
      "name": "setCity",
      "base": "users"
      "set": {
        "city": "$city"
      },
      "where": {
        "user": "$name"
      } 
    }
  }
}

Contributing

If you are a developer trying to contribute to this project, please follow these steps:

1. Fork and Clone the repository.
2. Run npm install.
3. Export the DATABASE_URL to the environment using export or adding it to a ".env" file.
4. Run npm start <command> or ./index.js <command> to see if it runs without errors.
5. Tests can be performed by running npm test

Please refer Contribution Guidelines for more information.

Contact

Email : [email protected] Telegram : @uzair_inamdar

License

MIT