postgrexpress

v1.0.5

Published

4 years ago

This package generates an Rest API for PostgreSQL.

Downloads

0High
0Medium
0Low

phibersoft

pg postgresql express rest api postgres

PostgrExpress

Description

This package generates an Rest API for PostgreSQL.

Required Dependencies

Node v14+
Express (https://www.npmjs.com/package/express) // Server
Chalk (https://www.npmjs.com/package/chalk) // Better debugging on console
Pg (https://www.npmjs.com/package/pg) // PostgreSQL Official module
Express-Async-Wrapper (https://www.npmjs.com/package/express-async-wrapper) // Wrapping errors to errorHandler

Usage

  // Import packages
  import * as PostgrExpress from "postgrexpress";
  import * as pg from "pg";
  import * as express from "express";
  
  const app = express();
  
  /*
   ... Your express server configurations ...
   */
  
  // Connect to your database via "pg" package
  const poolConfig = {
    host: '...',
    ...
  };
  
  const db = new pg.Pool(poolConfig);
  
  // Define your table's schema.
  const tables = [
      {
          tableName: "users",
          routeName: "users",
          primaryKey: "user_id",
          update: {
              fields: [
                  {
                      name: "username",
                      required: true,
                  },
              ],
          },
          insert: {
              fields: [
                  {
                    name: "username",
                    required: true,
                  }
             ],
          },
      },
  ];
  
  // You can define your views too. Reference API.
  
  // Define your PostgrExpress config.
  const conf = {
    database: {
        tables: schema,
        views: []
    }  
  };
  
  // Call PostgrExpress. 
  const pge = new PostgrExpress(db);
  app.use(`/api`, pge.PrepareRoute(conf));
  
  // Done.

API

Table Schema

View Schema

Command Schemas (Select, Insert, Update, Delete)

Select

Update

Insert

Delete

Field

Pagination

Select all route uses pagination default. Here are the querystring schema.

Example

Insert tables to database

    CREATE TABLE users(
       user_id integer SERIAL PRIMARY KEY,
       username varchar(100)
    );
    
    CREATE TABLE comments(
       comment_id integer SERIAL PRIMARY KEY,
       user_id integer,
       content varchar(500),
       data json
    );

Define schemas in typescript

  // Define fields for insert/update operations.
import PostgrExpress from "./PostgrExpress";

const userFields = [
    {
        name: 'username',
        required: true,
    }
];
const commentFields = [
    {
        name: 'content',
        required: true,
    },
    {
        name: 'user_id',
        required: true,
    }
];

// Define database config for PostgrExpress.
const schema = {
    tables: [
        {
            tableName: 'users',
            primaryKey: 'user_id',
            update: {
                fields: userFields,
                async after(updatedRows) {
                    // You can do any stuff with updatedRows.
                    console.log(`Updated row count: ${updatedRows}`);
                },
                async before(body) {
                    // You can do any editing on body.
                    // If you dont return edited body, this program will give error.
                    return body;
                }
            },
            insert: {
                fields: userFields
            }
        },
        {
            tableName: 'comments',
            routeName: 'custom_comments', // If you dont want to use /comments route.
            update: {
                fields: commentFields,
            },
            insert: {
                fields: commentFields
            }
        }
    ],
    views: [
        {
            routeName: "user_comments",
            primaryKey: "u.user_id",
            query: `SELECT * FROM users u INNER JOIN comments c ON u.user_id = c.user_id `,
            args: [],
        },
    ]
};

const pge = new PostgrExpress(db) // db is your Pg POOL!
const route = pge.PrepareRoute({
    database: schema,
}); // route is express.Route now.

// You can use route as any if you want.
server.use(`/api`, route);

// Done.

Result

| Method | Route | Description | | :---: | :---: | :---: | | GET | /api/users | Gets all users with pagination | | GET | /api/users/1 | Gets user where "user_id" = 1 | | POST | /api/users (content-type: application/json) | Inserts user with required key "username" | POST | /api/users/1 (content-type: application/json) | Updates user where "user_id" = 1 | DELETE | /api/users/1 | Deletes user where "user_id" = 1 | |||| | GET | /api/custom_comments | Gets all comments with pagination | | GET | /api/custom_comments/1 | Gets comment where "comment_id" = 1 | | POST | /api/custom_comments (content-type: application/json) | Inserts comment with required keys "user_id", "content" | POST | /api/custom_comments/1 (content-type: application/json) | Updates comment where "comment_id" = 1 | | DELETE | /api/custom_comments/1 | Deletes comment where "comment_id" = 1 |||| | GET | /api/view/user_comments | Gets user-comment join data | | GET | /api/view/user_comments/1 | Gets user-comment join data where "user_id" = 1 |

Testing

Writed tests for body handling and key checks.