Building a REST-API with Expressjs, Tabel and Postgres | Part-2

By Satyam Yadav

May 2, 2016 - 3 minutes read - 545 words

Setting up database and orm. Tabel : A simple orm for PostgreSQL, built over knex.js, which works with simple javascript objects and arrays. More of a table gateway that can behave like an orm, and scale back down to a table-gateway when needed.

Install postgress

Set up postgres and redis on your system.

A repository for helping in instalations. Link Here

Postgres docs : Link

Redis docs: Link

Configure Tabel ORM

npm install config tabel pg

mkdir config

touch config/default.js

Copy config for orm from Tabel docs and update db name, user and password.

Migrations


mkdir migerations

touch migrate.js

/migrate.js

const migrate = require('tabel/lib/migrate');
const ormConfig = require('config');

migrate(ormConfig);

After adding above content to migrate.js run following migerate commonds from base path:

node migrate.js make CreateUsersTable
node migrate.js make CreatePostsTable

Now there should appear the migeration files in /migerations as <TIME_STAMP>_CreateUsersTable.js and <TIME_STAMP>__CreatePostsTable.js with content

function up(knex) {

}

function down(knex) {

}

module.exports = {up, down};

update these file contents then run

node migrate.js latest

now we can verify it in psql it should show blank tables created :

 api_dev=# \dt

List of relations

Name	Type	Owner
public	knex_migrations	table	dev
public	knex_migrations_lock	table	dev
public	posts	table	dev
public	users	table	dev

Table Definitions

Define Tables, a single file is sufficient to define tables but we can have a break down table specific files (modules) for definition as follow:

mkdir orm
mkdir orm/tables
touch orm/index.js
mkdir orm/tables
touch orm/tables/users.js
touch orm/tables/posts.js

Add definition as in full config section of tabel docs for posts and users both.

Example:

'use strict';

module.exports = function(orm) {
  orm.defineTable({
    name: 'users',
    props: {
      key: 'id',
      autoId: false,
      perPage: 25,
      timestamps: true
    },
    scopes: {},
    joints: {
      joinComments() {
        return this.joinPosts().join(
          'comments',
          'comments.post_id',
          '=',
          'posts.id'
        );
      },
      joinPosts() {
        return this.join('posts', 'users.id', '=', 'posts.author_id');
      }
    },
    relations: {
      posts: function() {
        return this.hasMany('posts', 'author_id');
      }
    }
  });
};

and require the two definitions in orm/tables/index.js

module.exports = function(orm) {
  require('./users')(orm);
  require('./posts')(orm);
};

now require the tables in orm/index.js

const Tabel = require('tabel');
const config = require('config');
const orm = new Tabel(config);

require('./tables')(orm);

module.exports = orm.exports;

Seeds

To seed some sample (fake data):

mkdir seeds
touch seeds/post.js
touch seeds/user.js

In seed files insert some data into table using tabel orm methods according to schema in migerations , I have used Faker.js.

npm install faker

Example Posts :

const faker = require('faker');
const { table } = require('../orm');

var seed = Promise.all(
  [1, 2, 3].map(function(n) {
    return table('posts').insert([
      {
        id: faker.random.uuid(),
        author_id: faker.random.uuid(),
        title: faker.lorem.sentence(),
        body: faker.lorem.sentence(),
        slug: faker.lorem.sentence().replace(/ /g, '-')
      }
    ]);
  })
);

seed.then(d => {
  console.log('seeded Posts', d);
});

Server

Now we make an app (express app) and define routes. Before that let’s verify if everything is working now by logging query results to console.

mkdir server
touch index.js

Adding a query and console log for the result in /server/index.js

const { table } = require('../orm');

const posts = table('posts').all();

posts.then(d => console.log(d));

Now run node sever from command from root of project. It should print the list of posts we have seeded in posts table.

Next we need to define an express app and add test for routes and then add routes and controllers for creating end points for REST API.

CODE: https://github.com/satyamyadav/postgres-express/tree/v0.1.0-1

Thank You !!