Node, Express and PostgreSQL

Overview

This is an easy, basic and raw example of HOW to implement an API with Node, Express and PostgreSQL (with Sequelize ORM).

Requirements

• Node 12+
• NPM
• PostgreSQL
• Sequelize ORM
• Optional: ElephantSQL account

Install dependencies

npm install

Git hooks are installed automatically by the prepare script (husky).

Installs use legacy-peer-deps (set in .npmrc) because the pinned 2021 dev tooling predates npm 7's stricter peer-dependency resolution.

Environment variables

The app loads src/config/<ENVIRONMENT>.env, so create dev.env for npm run dev and prod.env for npm start. Start from the sample:

cp src/config/.env.example src/config/dev.env

DB

Create database

createdb users

Populate data

psql users

Add data to users table

COPY users(id, firstname, lastname, age, gender, username, company, email, phone, address, created_at, updated_at)
FROM'/Users/your-user/data/node-express-postgresql/users.csv'
DELIMITER ','
CSV HEADER;

Dump data from local DB to external

pg_dump postgres://your-user:your-password@127.0.0.1/users | psql postgres://your-user:your-password@your-endpoint.db.elephantsql.com/your-database-name

Running the server

Development

npm run dev

Production

npm run build
npm start

API endpoints

GET /api/users

• Returns an object with the key data containing an array of objects with 40 records.
• Supports query string:
  • ?limit=integer
  • ?offset=integer

Request:

curl http://127.0.0.1:3333/api/users

Sample response:

{
"data": [
{
"id": 1,
"firstname": "Christian",
"lastname": "Deackes",
"age": 36,
"gender": "Genderqueer",
"username": "cdeackes0",
"company": "Eayo",
"email": "cdeackes0@mit.edu",
"phone": "602-240-5463",
"address": "53 Lakewood Plaza",
"createdAt": "2020-11-30T08:00:00.000Z",
"updatedAt": "2021-03-28T07:00:00.000Z"
},
{
"id": 2,
"firstname": "Staford",
"lastname": "Noice",
"age": 27,
"gender": "Female",
"username": "snoice1",
"company": "Oyoloo",
"email": "snoice1@si.edu",
"phone": "951-811-1800",
"address": "18298 Crest Line Road",
"createdAt": "2021-06-30T07:00:00.000Z",
"updatedAt": "2021-07-14T07:00:00.000Z"
}
]
}

Query string

GET /api/users?limit=1

• Returns n record(s) where n is the value (type: Number) of the limit key.

Request:

curl http://127.0.0.1:3333/api/users?limit=1

Response:

{
"data": [
{
"id": 1,
"firstname": "Christian",
"lastname": "Deackes",
"age": 36,
"gender": "Genderqueer",
"username": "cdeackes0",
"company": "Eayo",
"email": "cdeackes0@mit.edu",
"phone": "602-240-5463",
"address": "53 Lakewood Plaza",
"createdAt": "2020-11-30T08:00:00.000Z",
"updatedAt": "2021-03-28T07:00:00.000Z"
},
]
}

A non-numeric value falls back to the default (limit 40, offset 0). Example: users?limit=%27Hello%27 returns the first 40 users.

GET /api/users?offset=10

• Returns from n (PRIMARY KEY) where n is the value (type: Number) of the offset key.

Request:

curl http://127.0.0.1:3333/api/users?offset=10

Response:

{
"data": [
{
"id": 11,
"firstname": "Goldie",
"lastname": "Dany",
"age": 88,
"gender": "Female",
"username": "gdanya",
"company": "Devcast",
"email": "gdanya@berkeley.edu",
"phone": "954-161-7922",
"address": "68 Drewry Plaza",
"createdAt": "2021-03-28T07:00:00.000Z",
"updatedAt": "2021-03-19T07:00:00.000Z"
},
{
"id": 12,
"firstname": "Kial",
"lastname": "Hamberstone",
"age": 53,
"gender": "Male",
"username": "khamberstoneb",
"company": "Skipfire",
"email": "khamberstoneb@yellowpages.com",
"phone": "896-244-3662",
"address": "68425 Buell Point",
"createdAt": "2020-10-11T07:00:00.000Z",
"updatedAt": "2021-06-02T07:00:00.000Z"
}
]
}

GET /latency

• Returns an object with a delay of 1 second (default)
• Supports query string:
  • ?delay=integer (milliseconds, min 1000, max 4000)

Request:

curl http://127.0.0.1:3333/latency

Response:

{
"data": "Thanks for waiting 1 second"
}

Query string

GET /latency?delay=2000

• Increases latency (delay) to n milliseconds where, min:1000 and max:4000. Default value: 1000ms.

Wrong type for n value will produce a default delay of 1000ms.

Request:

curl http://127.0.0.1:3333/latency?delay=2000

Response:

{
"data": "Thanks for waiting 2 seconds"
}

GET everything else

• Any other endpoint will retrieve an object

Request:

curl http://127.0.0.1:3333/

Response:

{
"message": "Node.js, Express, and PostgreSQL API!"
}