Italy's daily coronavirus death toll and new cases fall

on
0

PostgreSQL and NodeJS

on
by Michael Herman
Today we're going to build a CRUD todo single page application with Node, Express, Angular, and PostgreSQL.

Project Setup

Start by installing the Express generator if you don't already have it:
1


$ npm install -g express-generator@4
Then create a new project and install the dependencies:
1
2

$ express node-postgres-todo


$ cd node-postgres-todo && npm install
Add Supervisor to watch for code changes:
1

$ npm install supervisor -g
Update the ‘start’ script in the package.json file:
1
2
3

"scripts": {


 "start": "supervisor ./bin/www"


},
Run the app:
1

$ npm start
Then navigate to http://localhost:3000/ in your browser. You should see the "Welcome to Express" text.

Postgres Setup

Need to setup Postgres? On a Mac? Check out Postgres.app.
With your Postgres server up and listening on port 5432, making a database connection is easy with the pg library:
1

$ npm install pg --save
Now let’s set up a simple table creation script:
1
2
3
4
5
6
7

var pg = require('pg');


var connectionString = process.env.DATABASE_URL || 



'postgres://localhost:5432/todo'

;




var client = new pg.Client(connectionString);


client.connect();


var query = client.query(



'CREATE TABLE items(id SERIAL PRIMARY KEY, text VARCHAR(40) not null, complete BOOLEAN)'

);



query.on('end', function() { client.end(); });
Save this as database.js in a new folder called "models".
Here we create a new instance of Client to interact with the database and then establish communication with it via the connect() method. We then set run a SQL query via the query() method. Communication is closed via the end() method. Be sure to check out the documentation for more info.
Make sure you have a database called "todo" setup, and then run the script to setup the table and subsequent fields:
1

$ node models/database.js
Verify the table/schema creation in psql:
1
2
3
4
5
6
7
8
9
10
11

michaelherman=# \c todo


You are now connected to database "todo" as user "michaelherman".


todo=# \d+ items


 Table "public.items"




 Column | Type | Modifiers | Storage | Stats target | Description




----------+-----------------------+----------------------------------------------------+----------+--------------+-------------




 id | integer | not null default nextval('items_id_seq'::regclass) | plain | |




 text | character varying(40) | not null | extended | |




 complete | boolean | | plain | |


Indexes:


 "items_pkey" PRIMARY KEY, btree (id)
With the database connection setup along with the "items" table, we can now configure the CRUD portion of our app.

Server-Side: Routes

Let’s keep it simple by adding all endpoints to the index.js file within the "routes" folder. Make sure to update the imports:
1
2
3
4


var express = require('express');




var router = express.Router();


var pg = require('pg');


var connectionString = process.env.DATABASE_URL || 



'postgres://localhost:5432/todo'

;
Now, let’s add each endpoint.
Function URL Action
CREATE /api/v1/todos Create a single todo
READ /api/v1/todos Get all todos
UPDATE /api/v1/todos/:todo_id Update a single todo
DELETE /api/v1/todos/:todo_id Delete a single todo
Follow along with the inline comments below for an explanation of what’s happening. Also, be sure to check out the pg documentation to learn about connection pooling. How does that differ from pg.Client?

Create

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34


router.post('/api/v1/todos', function(req, res) {



 var results = [];



 // Grab data from http request




 var data = {text: req.body.text, complete: false};





 



// Get a Postgres client from the connection pool



 pg.connect(connectionString, function(err, client, done) {





 // SQL Query > Insert Data




 client.query(



"INSERT INTO items(text, complete) values($1, $2)"

, [data.text, data.complete]);




 // SQL Query > Select Data




 var query = client.query(



"SELECT * FROM items ORDER BY id ASC"

);




 



// Stream results back one row at a time



 query.on('row', function(row) {




 results.push(row);


 });



 



// After all data is returned, close connection and return results



 query.on('end', function() {


 client.end();


 return res.json(results);


 });

 // Handle Errors
 if(err) {
 console.log(err);
 }

 });
});
Test this out via Curl in your terminal:
1


$ curl --data "text=test&complete=false" http://127.0.0.1:3000/api/v1/todos
Then confirm that the data was INSERT’ed correctly into the database via psql:
1
2
3
4
5


todo=



# SELECT * FROM items ORDER BY id ASC;

 id | text | complete
----+-------+----------
 1 | test | f
(1 row)

Read

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


router.get('/api/v1/todos', function(req, res) {



 var results = [];



 



// Get a Postgres client from the connection pool



 pg.connect(connectionString, function(err, client, done) {





 // SQL Query > Select Data




 var query = client.query(



"SELECT * FROM items ORDER BY id ASC;"

);




 



// Stream results back one row at a time



 query.on('row', function(row) {




 results.push(row);


 });



 



// After all data is returned, close connection and return results



 query.on('end', function() {


 client.end();


 return res.json(results);


 });

 // Handle Errors
 if(err) {
 console.log(err);
 }

 });

});
Add a few more rows of data via Curl, and then test the endpoint out in your browser at http://localhost:3000/api/v1/todos. You should see an array of JSON objects:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

[
 {
 id: 1,
 text: "test",
 complete: false
 },
 {
 id: 2,
 text: "test2",
 complete: false
 },
 {
 id: 3,
 text: "test3",
 complete: false
 }
]

Update

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38


router.put('/api/v1/todos/:todo_id', function(req, res) {



 var results = [];



 



// Grab data from the URL parameters



 var id = req.params.todo_id;





 // Grab data from http request




 var data = {text: req.body.text, complete: req.body.complete};





 



// Get a Postgres client from the connection pool



 pg.connect(connectionString, function(err, client, done) {





 // SQL Query > Update Data




 client.query(



"UPDATE items SET text=($1), complete=($2) WHERE id=($3)"

, [data.text, data.complete, id]);




 // SQL Query > Select Data




 var query = client.query(



"SELECT * FROM items ORDER BY id ASC"

);




 



// Stream results back one row at a time



 query.on('row', function(row) {




 results.push(row);


 });



 



// After all data is returned, close connection and return results



 query.on('end', function() {


 client.end();


 return res.json(results);


 });

 // Handle Errors
 if(err) {
 console.log(err);
 }

 });

});
Again, test via Curl:
1


$ curl -X PUT --data "text=test&complete=true" http://127.0.0.1:3000/api/v1/todos/1
Navigate to http://localhost:3000/api/v1/todos to make sure the data has been updated correctly.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

[
 {
 id: 1,
 text: "test",
 complete: true
 },
 {
 id: 2,
 text: "test2",
 complete: false
 },
 {
 id: 3,
 text: "test3",
 complete: false
 }
]

Delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36


router.delete('/api/v1/todos/:todo_id', function(req, res) {



 var results = [];



 



// Grab data from the URL parameters



 var id = req.params.todo_id;






 



// Get a Postgres client from the connection pool



 pg.connect(connectionString, function(err, client, done) {





 // SQL Query > Delete Data




 client.query(



"DELETE FROM items WHERE id=($1)"

, [id]);




 // SQL Query > Select Data




 var query = client.query(



"SELECT * FROM items ORDER BY id ASC"

);




 



// Stream results back one row at a time



 query.on('row', function(row) {




 results.push(row);


 });



 



// After all data is returned, close connection and return results



 query.on('end', function() {


 client.end();


 return res.json(results);


 });

 // Handle Errors
 if(err) {
 console.log(err);
 }

 });

});
Final Curl test:
1


$ curl -X DELETE http://127.0.0.1:3000/api/v1/todos/3
And you should now have:
1
2
3
4
5
6
7
8
9
10
11
12

[
 {
 id: 1,
 text: "test",
 complete: true
 },
 {
 id: 2,
 text: "test2",
 complete: false
 }
]

Refactoring

Before we jump to the client-side to add Angular, be aware that our code should be refactored to address a few issues. We’ll handle this later on in this tutorial, but this is an excellent opportunity to refactor the code on your own. Good luck!

Client-Side: Angular

Let’s dive right in to Angular.
Keep in mind that this is not meant to be an exhaustive tutorial. If you’re new to Angular I suggest following my "AngularJS by Example" tutorial - Building a Bitcoin Investment Calculator.

Module

Create a file called app.js in the "public/javascripts" folder. This file will house our Angular module and controller:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17


angular.module('nodeTodo', [])





.controller('mainController', function($scope, $http) {



 $scope.formData = {};
 $scope.todoData = {};

 // Get all todos


 $http.get('/api/v1/todos')




 .success(function(data) {




 $scope.todoData = data;




 console.log(data);


 })


 .error(function(error) {




 console.log('Error: ' + error);


 });
});
Here we define our module as well as the controller. Within the controller we are using the $http service to make an AJAX request to the '/api/v1/todos' endpoint and then updating the scope accordingly.
What else is going on?
Well, we’re injecting the $scope and $http services. Also, we’re defining and updating $scope to handle binding.

Update / Route

Let’s update the main route in index.js within the "routes" folder:
1
2
3


router.get('/', function(req, res, next) {




 res.sendFile(path.join(__dirname, '../views', 'index.html'));


});
So when the end user hits the main endpoint, we send the index.html file. This file will contain our HTML and Angular templates.
Make sure to add the following dependency as well:
1

var path = require('path');

View

Now, let’s add our basic Angular view within index.html:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19


 ng-app="nodeTodo">
 


 </span>Todo App - with Node + Express + Angular + PostgreSQL<span class="nt">




  name="viewport" content=



"width=device-width, initial-scale=1.0"

>



  href=



"http://netdna.bootstrapcdn.com/bootstrap/3.3.1/css/bootstrap.min.css"

rel="stylesheet" media="screen">
ng-controller="mainController">
class="container">
    ng-repeat="todo in todoData">
  • This should all be straightforward. We bootstrap Angular - ng-app="nodeTodo", define the scope of the controller - ng-controller="mainController" - and then use ng-repeat to loop through the todoData object, adding each individual todo to the page.

    Module (round two)

    Next, let’s update the module to handle the Create and Delete functions:
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

    // Create a new todo

    
$http.post('/api/v1/todos', $scope.formData)

    
 .success(function(data) {
 $scope.formData = {};

    
 $scope.todoData = data;

    
 console.log(data);
 })
 .error(function(error) {

    
 console.log('Error: ' + error);

    
 });

// Delete a todo

    
$http.delete('/api/v1/todos/' + todoID)

    
 .success(function(data) {

    
 $scope.todoData = data;

    
 console.log(data);
 })
 .error(function(data) {

    
 console.log('Error: ' + data);

    
 });
    Now, let’s update our view…

    View (round two)

    Simply update each list item like so:
    1

    


  •  type="checkbox" ng-click="deleteTodo(todo.id)"> {{ todo.text }}
    •
    This uses the ng-click directive to call the deleteTodo() function - which we still need to define - that takes a unique id associated with each todo as an argument.

    Module (round three)

    Update the controller:
    1
2
3
4
5
6
7
8
9
10
11

    // Delete a todo

    
$scope.deleteTodo = function(todoID) {

    

    
 $http.delete('/api/v1/todos/' + todoID)

    

    
 .success(function(data) {

    

    
 $scope.todoData = data;

    

    
 console.log(data);

    
 })

    
 .error(function(data) {

    

    
 console.log('Error: ' + data);

    
 });
};
    We simply wrapped the delete functionality in the deleteTodo() function. Test this out. Make sure that when you click a check box the todo is removed.

    View (round three)

    To handle the creation of a new todo, we need to add an HTML form:
    1
2
3
4
5
6
7
8
9
10
11
12
13
14

    
 class="container">

    
    
 
    

    
    
     
    
 class="form-group">

    

    
  type="text" class="form-control input-lg" placeholder="Add a todo..." ng-model="formData.text">
      ng-repeat="todo in todoData">
  • type="checkbox" ng-click="deleteTodo(todo.id)"> {{ todo.text }}
    •
    Again, we use ng-click to call a function in the controller.

    Module (round four)

    1
2
3
4
5
6
7
8
9
10
11
12

    // Create a new todo

    
$scope.createTodo = function(todoID) {

    

    
 $http.post('/api/v1/todos', $scope.formData)

    

    
 .success(function(data) {

    

    
 $scope.formData = {};

    

    
 $scope.todoData = data;

    

    
 console.log(data);

    
 })

    
 .error(function(error) {

    

    
 console.log('Error: ' + error);

    
 });
};
    Test this out!

    View (round four)

    With the main functionality done, let’s update the front-end to make it look, well, presentable.
    HTML:
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

    
 ng-app="nodeTodo">
 

    
 </span>Todo App - with Node + Express + Angular + PostgreSQL<span class="nt">

    

    
  name="viewport" content=
    

    
"width=device-width, initial-scale=1.0"
    
>

 

    
  href=
    

    
"http://netdna.bootstrapcdn.com/bootstrap/3.3.1/css/bootstrap.min.css"
    
rel="stylesheet" media="screen">


    
  href="stylesheets/style.css" rel="stylesheet" media="screen">
    ng-controller="mainController">
    class="container">
    class="header">

    Todo App

    class="lead">Node + Express + Angular + PostgreSQL
    class="todo-form">
    class="form-group">
    type="text" class="form-control input-lg" placeholder="Enter text..." ng-model="formData.text">
    "btn btn-primary btn-lg btn-block"
    ng-click="createTodo()">Add Todo
    class="todo-list">
      ng-repeat="todo in todoData">

  • class="lead" type="checkbox" ng-click="deleteTodo(todo.id)"> {{ todo.text }}

    • CSS:
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

    body {
 padding: 50px;

    
 font: 14px "Lucida Grande", Helvetica, Arial, sans-serif;

    
}

a {
 color: #00B7FF;
}

ul {
 list-style-type: none;
 padding-left: 10px;
}

.container {
 max-width: 400px;
 background-color: #eeeeee;
 border: 1px solid black;
}

.header {
 text-align: center;
}
    How’s that? Not up to par? Continue working on it on your end.

    Refactoring (for real)

    Now that we added the front-end functionality, let’s update our application’s structure and refactor parts of the code.

    Structure

    Since our application is logically split between the client and server, let’s do the same for our project structure. So, make the following changes to your folder structure:
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

    ├── app.js
├── bin
│ └── www
├── client
│ ├── public
│ │ ├── javascripts
│ │ │ └── app.js
│ │ └── stylesheets
│ │ └── style.css
│ └── views
│ └── index.html
├── config.js
├── package.json
└── server
 ├── models
 │ └── database.js
 └── routes
 └── index.js
    Now, we need to make a few updates to the code:
    server/routes/index.js:
    1

    
res.sendFile(path.join(__dirname, '../', '../', 'client', 'views', 'index.html'));
    app.js:
    1

    
var routes = require('./server/routes/index');
    app.js:
    1

    
app.use(express.static(path.join(__dirname, './client', 'public')));

    Configuration

    Next, let’s move the connectionString variable - which specifies the database URI (process.env.DATABASE_URL || 'postgres://localhost:5432/todo';) - to a configuration file since we are reusing the same same connection throughout our application.
    Create a file called config.js in the root directory, and then add the following code to it:
    1
2
3

    
var connectionString = process.env.DATABASE_URL || 
    

    
'postgres://localhost:5432/todo'
    
;



    
module.exports = connectionString;
    Then update the connectionString variable in both server/models/database.js and server/routes/index.js:
    1

    
var connectionString = require(path.join(__dirname, '../', '../', 'config'));
    And make sure to add var path = require('path'); to the former file as well.

    Utility Function

    Did you notice in our routes that we are reusing the same code in each of the CRUD functions:
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

    // SQL Query > Select Data

    
var query = client.query(
    

    
"SELECT * FROM items ORDER BY id ASC"
    
);



    
// Stream results back one row at a time
    

    
query.on('row', function(row) {

    
 results.push(row);
});


    
// After all data is returned, close connection and return results
    
query.on('end', function() {
 client.end();
 return res.json(results);
});

// Handle Errors
if(err) {
 console.log(err);
}
    We should abstract that out into a utility function so we're not duplicating code. Do this on your own, and then post a link to your code in the comments for review.

    Conclusion and next steps

    That's it! Now, since there's a number of moving pieces here, please review how each piece fits into the overall process and whether each is part of the client or server-side. Comment below with questions. Grab the code from the repo.

    Finally, this app is far from finished. What else do we need to do?
    What else? Comment below.

    Comments

    Post a Comment