A little experiment in defining models in Joi and creating PostgreSQL Tables

abase-db is a hapi plugin that provides an easy way to set up postgres database tables and perform CRUD operations by declaring a schema object which is heavily influenced by joi.

It can be used alone but is most powerful when used as part of abase or with your select few abase plugins.

Note if you are totally new to Hapi.js see: https://github.com/dwyl/learn-hapi And/or if you are new to postgres check out: https://github.com/dwyl/learn-postgresql

From a joi schema we should be able to infer many things about the fields in a database. abase-db provides the mapping between a config (inspired by joi schema) to commands that can create tables with the correct fields.

We also want a "plug and play" access and easy to use handlers to perform CRUD operations and abase-db offers this without having to worry about any postgres querying.

For more understanding of why see the parent module abase as this provides just the db part.

Why PostgreSQL?

While there is a lot of hype surrounding NoSQL Databases like MongoDB & Redis, we found we were having to write a lot of code to do useful queries. And while de-normalising data might "make sense" for "scalability" in theory, what we found in practice is that even with 100 Billion Records (way more users than 99.99% of companies/startups!) a well-managed PostgreSQL cluster copes very well.

Make up your own mind: https://www.postgresql.org/about If you're still Curious or Worried about scaling PostgreSQL? see: https://www.citusdata.com Want to model the network of people as a graph? https://github.com/cayleygraph/cayley

1. Install npm install abase-db --save
2. Write a schema for your tables like so:

  var schema = {
    tableName: 'users',
    fields: {
      name: { type: 'string' }
    }
  }

3. Run a database remotely or locally (see here for how) and acquire db url or connection object.
4. Create options object of the form:

  var options = {
    dbConnection: process.env.DATABASE_URL,
    schema: dbSchema
  };

5. Plugin

server.register([
    { register: require('abase-db'), options: options }
], function () {
    server.route(routes);
    server.start();
});

6. Play

handler: function (request, reply) {
    return request.abase.db.insert(
      { tableName: 'users', fields: request.payload },
      function () { return reply('OK') }
    );
}

7. Play without hapi. See API section below.

When registered with Hapi takes options of the form:

  { dbConnection, schema }

Either provide a database url and we'll do the rest or an object that can used to configure a pooled connection with node-pg.

The schema is in align with the requirements made by abase and as stated before is inspired by joi and will try to provide a one to one mapping.

The schema must be an object (or an array of objects for multiple tables) of the form: { tableName, fields }.

fields is of the form { [fieldName]: { type, rest: optional }

Table and field names must be valid postgres table and column names. (non empty, alphanumeric, no leading number, less than 64)

Each field must be given a type prop. Data/joi types we support:

More information can be inferred from lib/config_validator.js

Each field can also take more properties most of which will be used by other abase modules and have no effect but the ones we care about right now are.

With given database and schema, on initialisation of plugin, we will create all necessary tables if they don't already exist.

This will only therefore happen if starting server for the first time, or if a new table is added to the schema.

Unfortunately if you want to modify a tables schema you will have to drop the whole table to have the database reconfigured on start up. We look to find a nice process for this in the future if you want to update your tables with new columns.

####### Handlers

Each request will have the db handlers insert, select, update, delete, query. They all have clients attached and ready to go.

They can be accessed like so: request.abase.db.insert.

They are all of the form function(options, callback = optional) and return promises if no callback given.

The options object must contain tableName for crud operation if performing innerJoin not necessary, i.e. the table you want to operate on. Below are more details for properties of options.

The query handler takes options object of the form {raw: 'SELECT * FROM your_raw_psql' }.

####### Pool

You can also get the pool that the client uses by db.pool. You will be responsible for connecting and releasing clients. See details from pg.

The hapi server will be given a method endAbaseDb of the form function (callback) which can be called to closed the pool connection.

Helper that you can use to check your schema outside of hapi. Takes in a schema object and will throw if it fails.

Helper of the form function(dbConnection) to create a single node-pg client that is configured in the same way as how you provide your dbConnection above.

Object with methods insert, select, update, delete, init, flush.

They all have form function(client, schema, options, cb) so you will have to bind your own client.

Crud operation documented above.

Used at plugin registration takes same schema and doesn't use options arg.

Used to drop tables easily. If given options arg will delete on a table by table basis but if left out will delete all tables in schema. options takes the form {tableName}.

For examples and tests you will need a config.env file at the root of your project with a test database url like so:

TEST_DATABASE_URL=psql://localhost:5432/testdb

Note: this database must be running and before tests are run the tables may be removed from the database so don't keep anything important there.

To see a simple example in action type npm run example into your command line.

We hope you find this module useful!

If you need something cleared up, have any requests or want to offer any improvements then please create an issue or better yet a PR!

Note We are aware that not all postgres features may be supported yet. This module will need a few iterations so please suggest missing features to be implemented as you use it and we can hopefully work together to solve it.