postgrexpress v1.0.5

PostgrExpress

Description

This package generates an Rest API for PostgreSQL.

Table of Contents

• Required Dependencies
• Usage
• API
  • Table Schema
  • View Schema
  • Command Schemas
    • Select
    • Insert
    • Update
    • Delete
  • Field Schema
  • Pagination
• Example
  • Insert Tables To Database
  • Define Schemas In Typescript
  • Result
• Testing

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

Testing

Writed tests for body handling and key checks.