A Node JS Restful API made with Express, Knex and PostgreSQL

This is a app backend that made to learn Javascript. I used express, knex and PostgreSQL. I come from an HTML and CSS background on the web but do most of my work in R for my job as a wildlife biologist. I've long wanted to combine my R work with Databases and my HTML and CSS skills. R ins't approprate for doing backend work, so I figured JS would be a good place to get to know CRUD operations.

This repo is an Restful API with a many to many relationship. I'm wanted to get better at javascript so I wanted to play around with a SQL style (table based) database to practice manipulating data. This repo contains my experiments.

This is a rough todo to track how I did everything.

The Master TODO

Set up

• use express gerator to generate app with no views.
• in app.js delete router information auto generated by express generator.
• in app.js update error handling to return json instead of html.
• install knex, pg, and nodemon.
• Run Nodemon and make route to '/' to make sure that everything runs.

Create Database and Schema

• Create PostgreSQL database in terminal createdb man-to-many
• Add knex connection to PostgreSQL with /db/knex.js file and knexfile.js.
  • Add knexfile.js with connection to database for now we will just have a development connection.
  • add /db/knex.js file with connection details to db.
• Make migration knex migrate:make notes_and_tags (if knex isn't install run npm install knex -g):
  • Add schema for Notes, Tags, and NotesTag (join table) tables.
    • Notes - id, title and body.
    • Tags - id and name.
    • notesTag will have id, note_id and tag_id
  • Run Migration knex migrate:latest

Create database POST queries and routes for Many to Many relationship

• create queries to create note, tag and notestag.
• create api folder with notesTags.js for all router info for notesTag. Bring in router and express.
• add notes api routes to app in app.js.
• make route to create note.
  • in note route make query to tags table to add tags.
  • get returned note id and returned tag ids and .map to create array of note_id and tag_id to add to notes_tags table.
  • add query to tags database to query tags table and see if the tags have already been created. If they have, return their ids.
  • create if else to separate if there are new tags or if there are just old tags.
    • if there are old and new tags, take the returned ids from the tags check query, post the new tags, get the responded ids from the post, combine the old and new ids and add to object with note_id and all tag_ids.
    • if there are just old tags take the returned ids from the tags check and make object with note_id and all tag_ids.
    • post object with node_id and tag_ids to notes_tag table.

Create GetOne Route for Many to Many relationship.

• make .get route on /:id.
• get note based on route param id.
• Use note ID to get table with id.
• Use joined notes_tag returned fields to return all tags.

Update Note Query and Route

• make .put route on /:id
• Use async function.
• get a new Date() to update updated_at field, this isn't updated automatically.
• update the note based on param.id and request (title, body, updated_at).
• use response to get joining table based on note_id.
• get tags using tag_id from joining table.
• compare tags that have been requested to the tags in the DB.
• create list of tags to delete from joining table.
• create list of tags to add to tags table and to add to joining table.
• combine the delete list with add list to output with response.
• if there are tags to delete delete the tag entry from joining table (do not delete from tags table because the tag might be used for another note)
• if there are tags that are not yet in the database add those tags and return them so that they can be added to the joining table with the note_id.
• get the tags for the note with a new knex query. I made a function to do this from the createNote function so I didn't have to write the code out again. The function getTagsForNote() just needs to be passed a note ID and it will return all the tags for that note.
• return the updated note, new list of tags from getTagsForNote and the tagTodo which kept track of what notes were added and removed.

Get All Notes and Tags

• Make route on / to get all notes.
• Make async query function.
• get all notes.
• use response to get tags for each note.id.
  • use map to loop over array of notes. If map is inside of an async function, the map function must be an async function inside Promise.all().
  • for each note get tags using joining table.
  • return note and tags for each note.
• return note and tags from Promise.all(...map...) function.

Delete Note query and route

• create delete route on /:id.
• delete note based on ID.
• delete joining table entries based on note ID. Don't delete actual tags.