In this project, we are going to make our first full CRUD back-end that uses a database.
In this step, we are going to create a bare-bones server.
- Run
npm init -y
. - Use npm to install and save
express
,body-parser
,dotenv
andmassive
. - Create a
.env
file. - Create a
.gitignore
to ignore thenode_modules
folder and the.env
file. - Create an
index.js
file. - Require all the packages that we installed and saved.
- Get your server listening on port
3000
.
Detailed Instructions
Let's begin by opening a new terminal window and cd
into the project. Let's create a package.json
file by running npm init -y
. Using the -y
flag, we'll get a package.json file with all the default values. Now that we have a package.json
file, we can use npm install --save
to install and save packages to it. Run npm install --save express body-parser massive dotenv
to get all the packages we'll need for this project.
After that is finished, we can see it created a node_modules
folder. We never want to include this folder on GitHub, so let's create a .gitignore
that will ignore node_modules
. Create a file and name it .env
. This file also needs to be included in the .gitignore
. After that, we're ready to start creating our server. Create an index.js
file and require
all the packages we install at the top.
const express = require('express');
const bodyParser = require('body-parser');
const massive = require('massive');
require('dotenv').config()
Now that our index.js
file has access to all our packages, let's create a basic server. We'll begin by saving express()
to a variable called app
.
const express = require('express');
const bodyParser = require('body-parser');
const massive = require('massive');
require('dotenv').config()
const app = express();
Then, we'll want to use our bodyParser
middleware.
const express = require('express');
const bodyParser = require('body-parser');
const massive = require('massive');
require('dotenv').config()
const app = express();
app.use( bodyParser.json() );
Finally, we'll want to tell the server to listen on port 3000
and use a console.log
to tell us when it is listening.
const express = require('express');
const bodyParser = require('body-parser');
const massive = require('massive');
require('dotenv').config()
const app = express();
app.use( bodyParser.json() );
const port = process.env.PORT || 3000;
app.listen( port, () => { console.log(`Server listening on port ${port}.`); } );
.gitignore
node_modules
.env
index.js
const express = require('express');
const bodyParser = require('body-parser');
const massive = require('massive');
require('dotenv').config()
const app = express();
app.use( bodyParser.json() );
const port = process.env.PORT || 3000;
app.listen( port, () => { console.log(`Server listening on port ${port}.`); } );
In this step, we are going to add massive to the server so we can connect to a database.
- Open the
.env
file and create a variable calledCONNECTION_STRING
that equals the URI connection string from your Heroku database.- Make sure to add
?ssl=true
at end of your connection string.
- Make sure to add
- Also add a port value to the
.env
with the value being 3000. - Use
massive
and theCONNECTION_STRING
to establish a connection. - In the
.then
callback frommassive
, setdb
on app to equal the database instance. - Make sure to add a
.catch()
with a callback function. - In the callback of the
.catch
,console.log
the error that would be received if the request was rejected.
Detailed Instructions
Now that we have a basic node server ready to go, let's modify it to connect to a postgres database. Open the .env
file and create a variable called CONNECTION_STRING
that equals the URI connection string from your Heroku database, it should look something like this postgres://username:password@host/dbname?ssl=true
.
Using the CONNECTION_STRING
, we can invoke massive
and pass it in as the first argument. This will return a promise
.
CONNECTION_STRING=postgres://username:password@host/dbname?ssl=true
massive( process.env.CONNECTION_STRING );
We'll want to execute some logic when the promise is fulfilled, so let's chain a .then
to it. Be sure to capture the database instance in the first parameter.
massive( process.env.CONNECTION_STRING ).then( dbInstance => {} );
Now that we have the dbInstance
, we can set it onto app
. Let's have our function return app.set('db', dbInstance)
.
massive( process.env.CONNECTION_STRING ).then( dbInstance => {
app.set('db', dbInstance)
});
Finally, we need to add a .catch
so that we can console.log
any error we might receive. Do so by chaining .catch
after the .then
. The .catch
takes a callback function. Name the callback function parameter err
. If .catch
is invoked, err
will be the error that was received. Add a console.log
to log the error.
massive( process.env.CONNECTION_STRING ).then( dbInstance => {
app.set('db', dbInstance)
}).catch( err => console.log(err) );
index.js
const express = require('express');
const bodyParser = require('body-parser');
const massive = require('massive');
require('dotenv').config()
const app = express();
app.use( bodyParser.json() );
massive( process.env.CONNECTION_STRING ).then( dbInstance => {
app.set('db', dbInstance)
}).catch( err => console.log(err) );
const port = process.env.PORT || 3000;
app.listen( port, () => { console.log(`Server listening on port ${port}.`); } );
In this step, we are going to create our table and the .sql
files we'll need to preform operations on our data. The schema for our table will look like:
- product_id - Serial Primary Key
- name - varchar(40)
- description - varchar(80)
- price - integer
- image_url - text
- Create a
product
table in your Heroku database. - Create a folder called
db
.- Create a
create_product.sql
file. - Create a
read_products.sql
file. - Create a
read_product.sql
file. - Create a
update_product.sql
file. - Create a
delete_product.sql
file.
- Create a
create_product.sql
:- The SQL should be able to add a new product to the
products
table. - The SQL should have four parameters ( name, description, price, image_url ).
- The SQL should be able to add a new product to the
read_products.sql
:- The SQL should be able to return all products from the
product
table.
- The SQL should be able to return all products from the
read_product.sql
:- The SQL should be able to return a specific product from the
product
table. - The SQL should use a parameter to find the product whose
product_id
matches.
- The SQL should be able to return a specific product from the
update_product.sql
:- The SQL should be able to update the description of a specific product from the
product
table. - The SQL should use a parameter to find the product whose
product_id
matches. - The SQL should use a parameter to update the value of the
description
.
- The SQL should be able to update the description of a specific product from the
delete_product.sql
:- The SQL should be able to delete a specific product from the
product
table. - The SQL should use a parameter to find the product whose
product_id
matches.
- The SQL should be able to delete a specific product from the
Detailed Instructions
Now that we have a method of connecting to our database and have an instance ready to go on app
, we are ready to start creating the sql
files that will interact with our database and a products
table. Let's begin by creating a products
table that follows the schema in the summary. The final syntax will look like:
CREATE TABLE product (
product_id SERIAL PRIMARY KEY NOT NULL,
name varchar(40) NOT NULL,
description varchar(80) NOT NULL,
price integer NOT NULL,
image_url text NOT NULL
);
Now that we have a product
table, we'll make five sql
files. One for creating
products; one for reading
all products; one for reading
a specific product; one for updating
products; and one for deleting
products. Let's create a folder called db
. This is where we'll store our sql
files that we can execute later using the database instance.
Inside the db
folder, let's create a file called create_product.sql
. This sql
file will be responsible for creating a new product using four parameters. The four parameters are name
, description
, price
, and image_url
. To add something to a database we use the following syntax: INSERT INTO Table ( column1, column2 ) VALUES ( value1, value2 );
The values we'll change are Table
, column
, and value
. Since we want to insert into the product
table, we'll change Table
to product
. Since we are updating the name
, description
, price
, and image_url
, we'll use those as the columns. And since we are using parameters for the values, we'll use $1
, $2
, $3
, and $4
as the values. The final syntax will look like:
INSERT INTO product ( name, description, price, image_url ) VALUES ( $1, $2, $3, $4 );
Now let's move on to read_products.sql
. Create a file in the db
folder called read_products.sql
. This sql
file will be responsible for reading all products from the database. To read all data from a database we use the following syntax: SELECT * FROM Table
. Since we are working with the product
table, we'll change Table
to product
. The final syntax will look like:
SELECT * FROM product;
Now let's move on to read_product.sql
. Create a file in the db
folder called read_product.sql
. This file will be very similar to read_products.sql
, however we need to add a where statement so we don't get all the products. We'll want to use a parameter so we can dynamically select a product by product_id
. We can use a where statement with the following syntax: WHERE column1 = value
. Since we are looking for a product by ID, we'll change column1
to product_id
. And since we are using a parameter for the ID, we'll change value
to $1
. The final syntax will look like:
SELECT * FROM product WHERE product_id = $1;
Now let's move on to update_product.sql
. Create a file in the db
folder called update_product.sql
. This sql
file will be responsible for updating the description of a product by ID. To update data from a database we use the following syntax: UPDATE Table SET column1 = value1 WHERE condition
. Since we are working with the product
table we'll change Table
to product
. Since we are updating the description dynamically we'll set column1
to description
and value1
to $2
. And since we are updating products by ID we'll set condition
to product_id = $1
. The order of $1
and $2
doesn't matter as long as you following the same order in the controller
file. I personally prefer (id, value)
instead of (value, id)
. The final syntax will look like:
UPDATE product SET description = $2 WHERE product_id = $1;
Now let's move on to delete_product.sql
. Create a file in the db
folder called delete_product.sql
. This sql
file will be responsible for deleting a specific product by ID. To delete data from a database we use the following syntax: DELETE FROM Table WHERE condition
. Since we are working with the product
table, we'll change Table
to product
. Since we are deleting by product ID, we'll change condition
to product_id = $1
.
The final syntax will look like:
DELETE FROM product WHERE product_id = $1;
CREATE TABLE product
CREATE TABLE product (
product_id SERIAL PRIMARY KEY NOT NULL,
name varchar(40) NOT NULL,
description varchar(80) NOT NULL,
price integer NOT NULL,
image_url text NOT NULL
);
SQL
create_product.sql
INSERT INTO product ( name, description, price, image_url ) VALUES ( $1, $2, $3, $4 );
read_products.sql
SELECT * FROM product;
read_product.sql
SELECT * FROM product WHERE product_id = $1;
update_product.sql
UPDATE product SET description = $2 WHERE product_id = $1;
delete_product.sql
DELETE FROM product WHERE product_id = $1;
In this step, we will create a products_controller.js
file that will handle the logic of interacting with the database.
- Create a
products_controller.js
file. - Use
module.exports
to export an object with five methods.create
,getOne
,getAll
,update
, anddelete
.
- Inside of each method, access the database instance.
- Inside of each method, use the correct SQL file.
create
->create_product.sql
.getOne
->read_product.sql
.getAll
->read_products.sql
.update
->update_product.sql
.delete
->delete_product.sql
.
- Don't worry about the parameter(s) in this step.
create
,update
, anddelete
should send status 200 on success and status 500 on failure with a message about how something went wrong.getOne
should send status 200 and the product on success and status 500 on failure with a message about how something went wrong (you can chose your own phrasing). Make sure to log the error to your console so you can read it if anything goes wrong.getAll
should send status 200 and the products on success and status 500 on failure with a message about how something went wrong (you can chose your own phrasing). Make sure to log the error to your console so you can read it if anything goes wrong.
Detailed Instructions
Now that we have all the sql
files we'll need to interact with our database, let's create a controller that will execute the sql
. Create a file called products_controller.js
. In this file, use module.exports
to export an object
with five methods. All methods should capture req
, res
, and next
and create a variable for the database instance off of req.app
.
module.exports = {
create: ( req, res, next ) => {
const dbInstance = req.app.get('db');
},
getOne: ( req, res, next ) => {
const dbInstance = req.app.get('db');
},
getAll: ( req, res, next ) => {
const dbInstance = req.app.get('db');
},
update: ( req, res, next ) => {
const dbInstance = req.app.get('db');
},
delete: ( req, res, next ) => {
const dbInstance = req.app.get('db');
}
};
Now that our methods have access to the dbInstance
we can execute our sql files by chaining on .file_name
. For example, if I wanted to execute read_product
I would use dbInstance.read_product()
. Knowing this we can execute our sql files in every method. Chain a .then
to use res
to sendStatus
200 and chain a .catch
to use res
to send status 500 with a message about how something went wrong (you can chose your own phrasing). Make sure to log the error to your console so you can read it if anything goes wrong. The getOne
and getAll
method should send product
and products
on success.
module.exports = {
create: ( req, res, next ) => {
const dbInstance = req.app.get('db');
dbInstance.create_product()
.then( () => res.sendStatus(200) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
getOne: ( req, res, next ) => {
const dbInstance = req.app.get('db');
dbInstance.read_product()
.then( product => res.status(200).send( product ) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
getAll: ( req, res, next ) => {
const dbInstance = req.app.get('db');
dbInstance.read_products()
.then( products => res.status(200).send( products ) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
update: ( req, res, next ) => {
const dbInstance = req.app.get('db');
dbInstance.update_product()
.then( () => res.sendStatus(200) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
delete: ( req, res, next ) => {
const dbInstance = req.app.get('db');
dbInstance.delete_product()
.then( () => res.sendStatus(200) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
}
};
We'll worry about how to use parameters after we configure our routes. For right now, this is all we need to do.
products_controller.js
module.exports = {
create: ( req, res, next ) => {
const dbInstance = req.app.get('db');
dbInstance.create_product()
.then( () => res.sendStatus(200) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
getOne: ( req, res, next ) => {
const dbInstance = req.app.get('db');
dbInstance.read_product()
.then( product => res.status(200).send( product ) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
getAll: ( req, res, next ) => {
const dbInstance = req.app.get('db');
dbInstance.read_products()
.then( products => res.status(200).send( products ) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
update: ( req, res, next ) => {
const dbInstance = req.app.get('db');
dbInstance.update_product()
.then( () => res.sendStatus(200) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
delete: ( req, res, next ) => {
const dbInstance = req.app.get('db');
dbInstance.delete_product()
.then( () => res.sendStatus(200) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
}
};
In this step, we will create endpoints that will call the methods on our controller. We will also require our controller in index.js
.
- Create the following endpoints: (
request method
,url
,controller method
)GET
-/api/products
-getAll
.GET
-/api/products/:id
-getOne
.PUT
-/api/products/:id?desc=...
-update
.POST
-/api/products
-create
.DELETE
-/api/products/:id
-delete
.
index.js
const express = require('express');
const bodyParser = require('body-parser');
const massive = require('massive');
require('dotenv').config()
const products_controller = require('./products_controller');
const app = express();
app.use( bodyParser.json() );
massive( process.env.CONNECTION_STRING ).then( dbInstance => {
app.set('db', dbInstance)
}).catch( err => console.log(err) );
app.post( '/api/products', products_controller.create );
app.get( '/api/products', products_controller.getAll );
app.get( '/api/products/:id', products_controller.getOne );
app.put( '/api/products/:id', products_controller.update );
app.delete( '/api/products/:id', products_controller.delete );
const port = process.env.PORT || 3000;
app.listen( port, () => { console.log(`Server listening on port ${port}.`); } );
In this step, we'll modify the controller to use parameters or the request body.
- Open
products_controller.js
. - Modify
update
to useid
fromreq.params
anddesc
fromreq.query
. - Modify
getOne
to useid
fromreq.params
. - Modify
delete
to useid
fromreq.params
. - Modify the
create
to usename
,description
,price
, andimage_url
from the request body. - Something to remember, you do not need to use an array if you have only one argument to pass to the massive method.
Detailed Instructions
Now that we know how our routes are configured, we can update our controller to reflect those changes. We'll modify update
to use id
from the request parameters and the desc
from the request query. We'll modify getOne
to use id
from the request parameters. We'll modify delete
to use id
from the request parameters. And we'll modify create
to use name
, description
, price
and image_url
from the request body. When adding parameters to sql, all you have to do is pass in an array as the first argument and then the element(s) in the array map to $1
, $2
, etc... For example: dbInstance.create_product([ name, description, price, image_url ])
, name is $1
, description is $2
, price is $3
, and image_url is $4
. Remember, if you have only one argument, you do not need to pass it in an array.
module.exports = {
create: ( req, res, next ) => {
const dbInstance = req.app.get('db');
const { name, description, price, image_url } = req.body;
dbInstance.create_product([ name, description, price, image_url ])
.then( () => res.sendStatus(200) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
getOne: ( req, res, next ) => {
const dbInstance = req.app.get('db');
const { params } = req;
dbInstance.read_product( params.id )
.then( product => res.status(200).send( product ) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
getAll: ( req, res, next ) => {
const dbInstance = req.app.get('db');
dbInstance.read_products()
.then( products => res.status(200).send( products ) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
update: ( req, res, next ) => {
const dbInstance = req.app.get('db');
const { params, query } = req;
dbInstance.update_product([ params.id, query.desc ])
.then( () => res.sendStatus(200) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
delete: ( req, res, next ) => {
const dbInstance = req.app.get('db');
const { params } = req;
dbInstance.delete_product( params.id )
.then( () => res.sendStatus(200) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
}
};
products_controller.js
module.exports = {
create: ( req, res, next ) => {
const dbInstance = req.app.get('db');
const { name, description, price, image_url } = req.body;
dbInstance.create_product([ name, description, price, image_url ])
.then( () => res.sendStatus(200) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
getOne: ( req, res, next ) => {
const dbInstance = req.app.get('db');
const { params } = req;
dbInstance.read_product([ params.id ])
.then( product => res.status(200).send( product ) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
getAll: ( req, res, next ) => {
const dbInstance = req.app.get('db');
dbInstance.read_products()
.then( products => res.status(200).send( products ) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
update: ( req, res, next ) => {
const dbInstance = req.app.get('db');
const { params, query } = req;
dbInstance.update_product([ params.id, query.desc ])
.then( () => res.sendStatus(200) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
},
delete: ( req, res, next ) => {
const dbInstance = req.app.get('db');
const { params } = req;
dbInstance.delete_product([ params.id ])
.then( () => res.sendStatus(200) )
.catch( err => {
res.status(500).send({errorMessage: "Oops! Something went wrong. Our engineers have been informed!"});
console.log(err)
} );
}
};
In this step, we'll test to make sure all the endpoint are working.
- Import the provided
postman_collection
into postman and make sure all the tests pass.
- Create a React front end to interact with your app.
- Use express static to serve up your React files from a build folder.
- Create a single view that can insert, read, update, and delete products.
- Create a second view that just reads the products and displays them in a pretty way (like Jane.com or amazon).
If you see a problem or a typo, please fork, make the necessary changes, and create a pull request so we can review your changes and merge them into the master repo and branch.
© DevMountain LLC, 2017. Unauthorized use and/or duplication of this material without express and written permission from DevMountain, LLC is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to DevMountain with appropriate and specific direction to the original content.