Build a Recipe App With Node.js & PostgreSQL - Introduction Part 1

Posted 23. January 2022. 5 min read.

You will learn how to build a recipe app using Node.js & PostgreSQL. After completing this series you will be more familiar with how PostgreSQL and Node.js work together.

Parts

How the final app will look like

Database model

recipe

id unique primary key
name name of the recipe for example carrot cake
description short descriptive text about the recipe
image image of the recipe
instructions step by step on how to make it
created_at date when the recipe was inserted into the database

ingredient

id unique primary key
name name of the ingredient for example butter

measure

id unique primary key
name name of the measure for example cup

recipe_ingredient

recipe_id reference to a recipe
ingredient_id reference to an ingredient
measure_id reference to a measure
amount how much of the ingredient should be added

The first three tables are quite straightforward, we have recipe, ingredient, and measure.

We want a recipe to have multiple ingredients and every ingredient is connected to a measurement. To be able to achieve this we need to have a fourth table that stores a reference between those tables.

Setup

Make sure PostgreSQL is installed on your system before you continue. https://www.postgresql.org/download/

Clone this repo to your computer starter code.

This repo contains some starter images, package.json with all dependencies we gonna use, and db.sql file to generate all tables with some starter data.

After you have cloned the repo run the following commands.

git checkout starter-code

npm install

Dependency list

express A minimalist web framework for Node.js
express-handlebars Template engine handlebars for express
handlebars-helpers Additional helper methods for handlebars
method-override Lets you use HTTP verbs such as PUT or DELETE in places where the client doesn't support it.
multer Middleware to handle file upload
pg PostgreSQL client for Node.js.
nodemon A tool to automatically restart node.js application when changes are detected.

Create tables and add initial data

On your computer create a database called cookbook.

Now you need to execute the content in db.sql or the SQL code below. Copy and paste it to your favorite Postgres GUI tool or use the terminal. I will be using Tableplus.

create table recipe (
    id SERIAL PRIMARY KEY, 
	name VARCHAR(250), 
	description VARCHAR(250),
    image VARCHAR(250), 
	instructions text,
	created_at timestamp NOT NULL DEFAULT NOW());

create table ingredient (
    id SERIAL PRIMARY KEY, 
	name VARCHAR(100) UNIQUE); 

create table measure (
    id SERIAL PRIMARY KEY, 
	name VARCHAR(100) UNIQUE); 

create table recipe_ingredient (
    recipe_id INT NOT NULL, 
	ingredient_id INT NOT NULL, 
	measure_id INT, 
	amount INT, 
	CONSTRAINT fk_recipe FOREIGN KEY(recipe_id) REFERENCES Recipe(id) ON DELETE CASCADE, 
	CONSTRAINT fk_ingredient FOREIGN KEY(ingredient_id) REFERENCES Ingredient(id),
	CONSTRAINT fk_measure FOREIGN KEY(measure_id) REFERENCES Measure(id));



INSERT INTO measure (name) VALUES('cup'), ('teaspoon'), ('tablespoon'), ('grams');

INSERT INTO ingredient (name) VALUES('egg'), ('canola oil'), ('salt'), ('sugar'), ('chocolate'), ('vanilla extract'), ('flour');

INSERT INTO recipe (name, description, instructions, image) VALUES('Carrot cake', 'Delicious and suitable for all occasions.', 
'Set the oven to 175 degrees.

Whisk eggs and powdered sugar. Add the oil and whisk together.

Mix wheat flour, bicarbonate, baking powder, ground cinnamon, ground ginger and salt in a separate bowl.

Mix the dry ingredients into the egg batter. Finish by turning down the grated carrots and grated coconut.

Pour the batter into the mold and bake the cake for about 45-50 minutes until a toothpick comes out clean. Allow the cake to cool completely before spreading the frosting.

Whisk butter until the frosting is really creamy, add cream cheese and whisk together well, about 3-5 min. Sift over the icing sugar and whisk together until a creamy frosting.

Spread the frosting over the carrot cake.
', 
'carrot-cake.jpg');

INSERT INTO recipe (name, description, instructions, image) VALUES('Cheese pizza', 'Yummy pizza with extra cheese', 'Add eggs, flour, to a pan. Bake at 200 for 1 hour', 'pizza.jpg');

INSERT INTO recipe (name, description, instructions, image) VALUES('Thai green curry', 'Hot and spicy', 'Stir chicken and vegetables in a pan.', 'green-curry.jpg');

INSERT INTO recipe_ingredient (recipe_id, ingredient_id, measure_id, amount) VALUES (1, 1, NULL, 3);
INSERT INTO recipe_ingredient (recipe_id, ingredient_id, measure_id, amount) VALUES (1, 2, 1, 1);
INSERT INTO recipe_ingredient (recipe_id, ingredient_id, measure_id, amount) VALUES (1, 4, 4, 200);

INSERT INTO recipe_ingredient (recipe_id, ingredient_id, measure_id, amount) VALUES (2, 1, NULL, 3);
INSERT INTO recipe_ingredient (recipe_id, ingredient_id, measure_id, amount) VALUES(2, 3, 1, 2);

This SQL code will create all the tables. It also seeds the database with some test data. To make sure we can not have duplicate values of ingredient and measure we set name to be unique. We also set up foreign keys between the tables to define our relationships.

This is how it would look in Tableplus.

If everything went right you should now have a database called cookbook with all the tables and some initial data.

Connecting PostgreSQL with express

In the root folder of the repo you cloned create a new file called db.js with the following content

const { Pool } = require('pg');

const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'cookbook',
  password: '',
  port: 5432,
});

module.exports = {
  query: (text, params, callback) => pool.query(text, params, callback),
};

Here you need to put in the information of your database. If you are not using the same credentials as me make sure to update this file.

(Remember to not put sensitive data in here and commit to Github, a better alternative would be environment variables.)

We export 1 method called query which will be executing database queries, by doing this we don't need to repeat the code for creating a connection every time we wanna query the database.

In the root create a file called app.js with the following content.

const express = require('express');
const db = require('./db');

const app = express();
const port = 3000;

app.get('/', async (req, res) => {
  const queryResult = await db.query('select * from recipe');
  res.send(queryResult.rows);
});

app.listen(port, () => {
  console.log(`Example app listening at http://localhost:${port}`);
});

Here we set up express with 1 route to call the query method we created before in db.js

To start the app you can now run npm run dev in the terminal. This command is defined under scripts in package.json "dev": "nodemon app.js"

nodemon will restart your app every time you save a file for a better developer experience.

You should be able to visit http://localhost:3000 and if everything is set up right you should get back a list with recipes.

(Your JSON might not be formatted as nice, this is from a plugin to google chrome)

Conclusion

So far we have set up the database with some initial data and connected it to express. In the next part we will build the view for listing all recipes and searching.