Express Database

In this workshop we're going to look at how to use express with a postgres database.

Learning Objectives

• Explain how REST API methods interact with a relational database
• Implement a REST API backed by a database using express and postgres

Setup

1. Fork this repository
2. Clone the forked repository onto your local machines
3. In the root directory, type npm install, which installs dependencies for the project

For this exercise we will also need to configure our database:

4. Create a postgres database either locally or via a cloud provider like Neon

5. Connect to your postgres database instance in a tool like TablePlus

6. Copy the SQL from the files in the sql/ directory and run them in TablePlus

• create-books.sql
• create-pets.sql
• insert-books.sql
• insert-pets.sql

7. Copy the URL of your instance

8. Create a file .env in the root directory of your project. It should be right next to the .env.example file. It should contain a single line, which contains the environment variable used to specify the url from the instance created above. See the example file for reference.

9. Type npm start, which starts a development server that will reload whenever you make any changes to source files.

All being well, you will have a terminal window that looks like the following:

Figure 2: The terminal window where the express server is running successfully

Interacting with the Database

To interact with the database we will use the node-postgres library. We will use the query method to send SQL queries to the database sever and receive responses. The db/index.js file establishes the connection to the database. Your instructor will walk through this with you.

Demo

Your instructor will demonstrate implementing some of the books API, now using a real database. You will complete the API spec implementation

Instructions

• Implement the API spec

Tests

Run the following commands from your project directory to run the test suites:

$ npm test # standard criteria
$ npm run test-extensions # extension criteria

You can also focus on one test at a time - use the jest docs to help filter which tests to run. We recommend you run tests manually with the option --forceExit.

For example, for the following test:

it("will list all books", async () => {
  const response = await supertest(app).get("/books")

  expect(response.status).toEqual(200)
  expect(response.body.books).not.toEqual(undefined)
  expect(response.body.books.length).toEqual(2)
  const expectedBooks = [book1, book2]
  response.body.books.forEach((retrievedBook, index) => {
    expect(retrievedBook.title).toEqual(expectedBooks[index].title)
  })
})

Here are two ways to run it.

$ npx jest -t "will list all books" --forceExit
$ npx jest test/api/routes/books.spec.js --forceExit # remember to add the 'f' before it()

Extension 1

• Implement the extension API spec
• This API spec has some of the same endpoints as the Standard Criteria API spec, but they are in addition to / build on top of that one.

Extension 2

So far we've been including all our database code directly in our route handlers. In a real application, this is considered bad practice. It would become difficult to maintain as the code base grows, and we are also mixing concerns. We have routing code, request/response handling and database access all in a single function. This leads to tight coupling and low cohesion.

It is better practice to split your code into different layers. This helps keep our code decoupled. Rather than your route handler implementing all your logic, you can introduce controller functions that handle your routes as well as specific functions for calling the database. There is no single "correct" approach, but here are some examples:

• MDN Express controllers and routes
• Express REST API structure

There is also an example boolean repository that provides a suggested structure:

• API Express Architecture

Update your implementation to match the structure of the above repo. Controllers functions should handle your requests and responses, and repository functions should handle your database access.