ais-db-service v1.4.0-pg1

ais-db-service

This service intends to provide API to connect master database which should not be directly accessible by other services. To use this service you need to make http call on master-db.STAGE.peak.ai

Why ais-db-service ?

ais-db-service works as a database abstraction layer for all other micro-services to access master database. A single db service for other services to perform operations using API's. It protects the master database from direct manipulation over db. It also helps to monitor database when all the queries are executing on a single service.

Prerequisites

• Docker

Service Setup

• Clone this repo
• Copy .npmrc file to the root directory of this repo
• Create an .env file with required values (sample in .envSample)
  • Note that if you're running the Docker Compose + Postgres project (i.e. yarn start-with-postgres/docker-compose -f docker-compose-with-postgres.yml up), PG_HOST must be set to postgres
• yarn build
• yarn install

Then you can run one of:

• yarn start: runs the service
• yarn start-with-postgres: runs the service with an accompanying local Postgres container, which is migrated and seeded on startup

Go to browser and hit port 8888 at localhost.

API's

1. query - API to run single query with following parameter

{
  query: 'select 1+1 as sum'
}

• Method - post
• Endpoint - /execute

2. transaction - API to run multiple queries as transaction with following parameters

{
  tx: ['select 1+1 as sum'], // array of stringified queries
  concurrent: false         // concurrent true if you want to run queries in parallel
}

• Method - post
• Endpoint - /transaction

How to create migrations ?

This service is based on sequelize ORM, so to create a new migration follow below steps. 1. Run following command to create a migration of your requirement like:

npx sequelize migration:create --name add_table_column

2. After creating new migration you can put your changes in migration file 20191007135218-add_table_column

module.exports = {
  up: (queryInterface, Sequelize) => queryInterface.createTable('table_name'),
  // logic for transforming into the new state

  down: (queryInterface, Sequelize) => queryInterface.dropTable('table_name'),
  // logic for reverting the changes
};

Note: If you are going to create a new table then it is better to create a model for that table.

How to create a model ?

To create a model for your table with basic attributes run following command

npx sequelize model:generate --name User --attributes firstName:string,lastName:string,email:string

Note: A migration will be created automatically if you create a model for your table.

How to create a seed ?

To create a seed follow below steps 1. Run npx sequelize seed:generate --name insert_data 2. Now change the created seed file 20191007143836-insert_data according to your requirement.

Run migrations and seeds locally

Note that running the Docker Compose + Postgres project will apply all migrations and seeds during startup.

• yarn migrate - run migrations
• yarn undo-migrate: rollback recent migrations,
• yarn undo-migrate-all: rollback all migrations,
• yarn seed - run all seeds
• yarn undo-seed: rollback recent seeds,
• yarn undo-seed-all: rollback all seeds,

Infrastructure

ais-db-service is running on beanstalk environment which provides scalability and availability. All the migration runs on the code build if the migration build is selected in pipeline. Running migration on the service level gives problems like blocking other requests, so a different codebuild to run these migration required. After running migrations, Beanstalk starts the application using configuration from config/config.js which has basic settings like user, password, database, seederStorage or tableName and pooling etc. Currently we are using pooling of max 5 connections.

Documentation

• Database Service
• Database Service Best Practices
• Postgres POC