Connecting Our Library to a Database

Create a Postgres database with Render

Navigate to the Render website and create a new Postgres database.

The Entity Relationship Diagram below shows the relationship between tables. You can learn more about ERDs here - you don't have to create an ERD now!

Create Environment variables

Create a .env file at the root of your project. It should contain the following name/value pairs: (Be sure to insert your actual values in this file!)

PORT=
DB_CONNECTION_STRING=

Your DB_CONNECTION_STRING will be provided by Render. You'll find it in the "Connections" section labelled "External Database URL".

The API won't have a hardcoded PORT like you've seen before in previous weeks. Study the subtle difference on line 26 in app.js.

Using dot env

You can load environment variables using the dotenv package. It's already been installed for you and should appear in your package.json file as a dev dependency.

Note: With the recent release of Node.js version 20.6.0, there is no longer a need to use the dotenv package, but we'll use it in this repo as you'll come across both ways to do it in the future. Read More

The following scripts have been added to your package.json file: (Notice the start script does not use dotenv, so your environment variables will not be loaded - unlike the dev script.)

"start": "node app.js",
"dev": "nodemon -r dotenv/config app.js",
"reset-database": "node -r dotenv/config db/scripts/reset-database.js"

Now, whenever you enter npm run dev the dotenv package will load your environment variables during runtime, and you'll be able to access them via process.env.

When you enter npm run reset-database the script should reset your database, inspect the code in db/scripts/reset-database.js for more details.

The .env file and .gitignore

.env has been added to your .gitignore file. You don't want those sensitive database credentials being pushed up to GitHub!

node_modules
.env

Using the pg package:

You can connect to the PostgreSQL database using the pg package. It's already been added to your package.json file as a dependency.

The job of the db/index.js file is to export a Pool from the pg package enabling you to use the pool.query() method so you to execute SQL queries in other files.

Always use parameterized queries with the pool.query() method to help protect against SQL injection attacks - this is important!

Existing Routes

The route handlers are already set up and functioning. Take a look inside app.js.

Method	Path	Request Body	Result	Status code
GET	/books		get all books	200
GET	/books/:id		get a book by id	200
POST	/books	A book object	create a new book	201
PATCH	/books/:id	A partial book object	update a book	200
DELETE	/books/:id		delete a book	200

Method	Path	Request Body	Result	Status code
GET	/authors		get all authors	200
GET	/authors/:id		get an author by id	200
POST	/authors	An author object	create a new author	201
PATCH	/authors/:id	A partial author object	update an author	200
DELETE	/authors/:id		delete an author	200

Code the helper functions

Previously, you've used the filesystem to read and write data to JSON files.

Now, you're going to use an SQL database, so there's no need to use the fs module anymore.

Complete the code for each function inside books.js and authors.js.

Write your queries using the pool.query() method using the pool imported at the top of each file.

The route handlers are already set up, so once you think you've completed each helper function, test the API with Postman.

Remember, go step by step, make a plan and break each problem down!