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
To avoid issues with husky, first enable git hooks (and add our hook):
npx husky install
npx husky add .husky/pre-commitThen, install the dependencies as usual:
npm installDB
Create database
createdb usersPopulate data
psql usersAdd 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:[email protected]/agency | psql postgres://your-user:[email protected]/your-database-nameRunning the server
Development
npm run devProduction
npm run build
npm startAPI 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/usersSample response:
{
"data": [
{
"id": 1,
"firstname": "Christian",
"lastname": "Deackes",
"age": 36,
"gender": "Genderqueer",
"username": "cdeackes0",
"company": "Eayo",
"email": "[email protected]",
"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": "[email protected]",
"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
nrecord(s) wherenis the value (type: Number) of thelimitkey.
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": "[email protected]",
"phone": "602-240-5463",
"address": "53 Lakewood Plaza",
"createdAt": "2020-11-30T08:00:00.000Z",
"updatedAt": "2021-03-28T07:00:00.000Z"
},
]
}Wrong type for n value will return all the users.
Example: users?limit=%27Hello%27
GET /api/users?offset=10
- Returns from
n(PRIMARY KEY) wherenis the value (type: Number) of theoffsetkey.
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": "[email protected]",
"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": "[email protected]",
"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:
- ?limit=integer
- ?offset=integer
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
nmilliseconds 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!"
}