In this tutorial I will be demonstrating how to build a simple contacts management web application using Node.js, Express.js, Vue.js in conjunction with the sequelize.js object relational mapper (ORM) backed by a SQLite database.
However, the primary focus of this article will be how to use the sequelize.js library in conjuction with SQLite, which expands on my prior article A SQLite Tutorial with Node.js. You can find the completed code for this tutorial on my GitHub account.
Setup and Installation
To start off I will initialize a new project using good ole npm init and pressing enter to accept all defaults, except for using the entry point of server.js instead of index.js. As a side note, I am using Node.js version 8.10.
$ mkdir node-vue-sqlite-sequelize && cd node-vue-sqlite-sequelize
$ npm init
Next up I will install the dependencies I will be needing, which are express, body-parser, sequelize, sequelize-cli, sqlite3, and optionally nodemon to keep me from needing to restart Node.js all the time.
$ npm install --save express body-parser sequelize sequelize-cli sqlite3 nodemon
Building the Contacts Management UI
First I make a static directory in the same directory as the package.json file:
$ mkdir static
Inside the new static directory I will make an HTML file named index.html. For building rich and interactive web UI's (or even desktop apps with Electron) I rely on Vue.js for its elegance and simplicity, so again here in this tutorial I will be using Vue.js in the index.html file.
Inside index.html I will begin with a simple piece of HTML5 boiler-plate and source Vue.js along with Bulma and Font-Awesome to make things a little prettier, along with axios.js, which I will use for AJAX calls later on.
<!-- index.html -->
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Contact</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bulma/0.7.1/css/bulma.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/vue/2.5.17-beta.0/vue.js"></script>
<script src="https://unpkg.com/axios/dist/axios.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<style>
.section-container {
max-width: 800px;
margin: auto;
}
</style>
</head>
<body>
</body>
</html>
Ok, before I can delve into building out the UI I need to discuss functionality and get to thinking about the different ways in which I will need to pass data back and forth with the backend of this data-driven app.
Since the app manages contacts, I will obviously need some representation of a person, which I will refer to as a Contact object. As far as behaviors go I will probably need the following:
- View list of all contacts
- Add contacts
- View details of a contact
- Update details of a contact
- Delete a contact
Pretty simple CRUD functionality, right?
Furthermore, as I alluded to earlier I will be interacting with the backend via a AJAX REST API, so let me also layout the API endpoints I think I will need.
| Route | Method | Functionality |
|---|---|---|
/api/contacts |
GET | Retrive all contacts |
/api/contacts |
POST | Create contact |
/api/contacts/:id |
PUT | Update the details of a contact |
/api/contacts/:id |
DELETE | Delete a contact |
Now that I have established what the required functionalities are I can begin piecing together the components of the UI and implementing the JavaScript desired behaviors.
To begin, I will create a component named AddUpdateContact that I can use to add new contacts to the app or update existing ones. So, at the bottom of the HTML's body tag I add a script tag and the following Vue.js-based JavaScript code:
<script></script>
The AddUpdateContact component can be given two props: (1) a title and, (2) in the optional case where it will be used for updating an existing contact a contact object. The data members are initialized based off whether or not the contact object is passed in as a prop or not. The template section contains a form and input fields to either add new contact info or modify an existing one. Then the methods section contains a single save method that emits the information of the contact up to a parent component, which is consistent with the one-way data binding philosophy of Vue.js.
Next up I will create a Contact component within the script tag, which will look like so:
<script></script>
The Contact component will receive a contact object as a prop which contains the data to be displayed in its template. The Contact component is also going to utilize the AddUpdateContact component previously described to show the details of the contact, as well as give the user the ability to update it.
The update functionality is possible through the use of the event handler method onAddOrUpdateContact that is listening to the save-contact event of the AddUpdateContact component, which then propagates the event up the chain to the main Vue.js instance, which will be discussed shortly. Additionally, there is also a deleteContact method of the Contact component that also propagates the delete message up to the parent Vue.js instance.
Ok, to wrap up the frontend JavaScript code I need to instantiate the root Vue.js object and tell it to bind to a div element with id of "app" like so:
<script>
// omitting AddUpdateContant and Contact components ...
new Vue({
el: '#app',
components: { contact: Contact, 'add-update-contact': AddUpdateContact },
data: {
contacts: [],
apiURL: 'http://localhost:3000/api/contacts'
},
methods: {
onAddOrUpdateContact (contact) {
if (contact.id) {
this.updateContact(contact)
} else {
this.addContact(contact)
}
},
addContact (contact) {
return axios.post(this.apiURL, contact)
.then((response) => {
const copy = this.contacts.slice()
copy.push(response.data)
this.contacts = copy
})
},
updateContact (contact) {
return axios.put(`${this.apiURL}/${contact.id}`, contact)
.then((response) => {
const copy = this.contacts.slice()
const idx = copy.findIndex((c) => c.id === response.data.id)
copy[idx] = response.data
this.contacts = copy
})
},
deleteContact (contact) {
console.log('deleting', contact)
return axios.delete(`${this.apiURL}/${contact.id}`)
.then((response) => {
let copy = this.contacts.slice()
const idx = copy.findIndex((c) => c.id === response.data.id)
copy.splice(idx, 1)
this.contacts = copy
})
}
},
beforeMount () {
axios.get(this.apiURL)
.then((response) => {
this.contacts = response.data
})
}
})
</script>
The root Vue.js object registers the previously described components and defines a set of methods that will interact with the REST API via AJAX calls made to the soon-to-be-built Node.js / Express.js application server.
The last part of the UI to take care of is the HTML needed to render the Vue.js components, which is shown below in the entirety of the index.html file.
<!-- index.html -->
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Contacts</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bulma/0.7.1/css/bulma.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/vue/2.5.17-beta.0/vue.js"></script>
<script src="https://unpkg.com/axios/dist/axios.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<style>
.section-container {
max-width: 800px;
margin-right: auto;
margin-left: auto;
}
</style>
</head>
<body>
<div id="app" class="container">
<section class="section section-container" style="padding-top: 24px; padding-bottom: 5px;">
<h2 class="title">Contacts</h2>
<contact v-for="contact in contacts"
:key="contact.name"
:contact="contact"
@save-contact="onAddOrUpdateContact"
@delete-contact="deleteContact" />
</section>
<section class="section section-container" style="padding-bottom: 10px;">
<div class="box">
<add-update-contact title="Add Contact" @save-contact="onAddOrUpdateContact" />
</div>
</section>
</div>
<script>
const AddUpdateContact = {
props: ['contact', 'title'],
data () {
return {
id: this.contact ? this.contact.id : null,
firstName: this.contact ? this.contact.firstName : '',
lastName: this.contact ? this.contact.lastName : '',
phone: this.contact ? this.contact.phone : ''
}
},
methods: {
save() {
this.$emit('save-contact', { id: this.id, firstName: this.firstName, lastName: this.lastName, phone: this.phone })
if (!this.id) {
this.firstName = ''
this.lastName = ''
this.phone = ''
}
}
},
template: `
<form class="form" @submit.prevent="save">
<h3 class='subtitle'>{{ title }}</h3>
<div class="field">
<label>First Name</label>
<div class="control">
<input class="input" type="text" v-model="firstName">
</div>
</div>
<div class="field">
<label>Last Name</label>
<div class="control">
<input class="input" type="text" v-model="lastName">
</div>
</div>
<div class="field">
<label>Phone</label>
<div class="control">
<input class="input" type="text" v-model="phone">
</div>
</div>
<div class="field">
<div class="control">
<button class="button is-success">Save</button>
</div>
</div>
</form>
`
}
const Contact = {
props: ['contact'],
components: { 'add-update-contact': AddUpdateContact },
data () {
return {
showDetail: false
}
},
methods: {
onAddOrUpdateContact(contact) {
this.$emit('save-contact', contact)
},
deleteContact (contact) {
this.$emit('delete-contact', contact)
}
},
template: `
<div class="card">
<header class="card-header">
<p @click="showDetail = !showDetail" class="card-header-title">
{{ contact.firstName }} {{ contact.lastName }}
</p>
<a class="card-header-icon" @click.stop="deleteContact(contact)">
<span class="icon">
<i class="fa fa-trash"></i>
</span>
</a>
</header>
<div v-show="showDetail" class="card-content">
<add-update-contact title="Details" :contact="contact" @save-contact="onAddOrUpdateContact" />
</div>
</div>
`
}
new Vue({
el: '#app',
components: { contact: Contact, 'add-update-contact': AddUpdateContact },
data: {
contacts: [],
apiURL: 'http://localhost:3000/api/contacts'
},
methods: {
onAddOrUpdateContact (contact) {
if (contact.id) {
this.updateContact(contact)
} else {
this.addContact(contact)
}
},
addContact (contact) {
return axios.post(this.apiURL, contact)
.then((response) => {
const copy = this.contacts.slice()
copy.push(response.data)
this.contacts = copy
})
},
updateContact (contact) {
return axios.put(`${this.apiURL}/${contact.id}`, contact)
.then((response) => {
const copy = this.contacts.slice()
const idx = copy.findIndex((c) => c.id === response.data.id)
copy[idx] = response.data
this.contacts = copy
})
},
deleteContact (contact) {
console.log('deleting', contact)
return axios.delete(`${this.apiURL}/${contact.id}`)
.then((response) => {
let copy = this.contacts.slice()
const idx = copy.findIndex((c) => c.id === response.data.id)
copy.splice(idx, 1)
this.contacts = copy
})
}
},
beforeMount () {
axios.get(this.apiURL)
.then((response) => {
this.contacts = response.data
})
}
})
</script>
</body>
</html>
Scaffolding the REST API
Since I am utilizing Express.js for this app I will need to make a JavaScript file named server.js that will serve the app in the same directory as the package.json file and then open it up in my favorite development text editor (VS Code).
At the top of the file I pull in the Express.js framework via require and then instantiate an app instance. Next I tell the app to use the Express's static middleware to serve up static content (HTML, JS, CSS, et...) from the "static" directory. At the very bottom of the server.js script I tell the app server to bind (listen) to port 3000 and server requests.
In order to scaffold out the REST API endpoints previously defined in the table of the previous section I require body-parser and indicate to the express app that it is to be used for parsing HTTP body content. Then I stub out the API endpoints that serve up the requested content.
// server.js
const express = require('express');
const bodyParser = require('body-parser');
const app = express();
app.use(bodyParser.json());
app.use(express.static(__dirname + '/static'));
app.get('/api/contacts', (req, res) => {
// TODO: retreive contacts and send to requester
});
app.post('/api/contacts', (req, res) => {
const { firstName, lastName, phone } = req.body
// TODO: create contact
});
app.delete('/api/contacts/:id', (req, res) => {
const id = parseInt(req.params.id)
// TODO: find and delete contact by id
});
app.put('/api/contacts/:id', (req, res) => {
const id = parseInt(req.params.id)
const { firstName, lastName, phone } = req.body
// TODO: find and update contact by id
});
app.listen(3000, () => {
console.log('Server is up on port 3000');
});
Once the data models and subsequent SQLite database structure is defined I will come back and provide the functionality for the API route callbacks.
The Sequelize ORM and Sequelize CLI
Sequelize.js is a popular ORM for Node.js version 4 and above that can be used for many different database management systems (DBMS) such as MySQL, Postgres, SQLite, and others. There is a complementary utility library called sequelize-cli that helps to automate some of the mundane as well as somewhat non-trivial parts of database programming.
In this tutorial I will be using sequelize-cli to take care of generating the Data Definition Language (DDL) to create database tables, as well as generate data models that create and execute Data Manipulation Language (DML) to query the database, and even a migration system to help with version controlling the database's schema.
To begin I will use sequelize-cli to initialize the data access layer of the project like so:
$ node_modules/.bin/sequelize init
This command will create the directories config, migrations, models, and seeders resulting in my project structure looking like this:
.
├── config
│ └── config.json
├── migrations
├── models
│ └── index.js
├── package-lock.json
├── package.json
├── seeders
├── server.js
└── static
└── index.html
The purpose for the directories are as follows:
- config/index.js - this defines the connection parameters and sql dialet
- migrations - contains migration scripts to manage the versioning of the schema
- models - contains the data models you use to interact with the database within your application code
- seeders - contains scripts to populate your database with initial data
First off I need to edit the config/config.json file to let sequelize know that I am going to be working with a SQLite database. So I will change the file from this...
{
"development": {
"username": "root",
"password": null,
"database": "database_development",
"host": "127.0.0.1",
"dialect": "mysql"
},
"test": {
"username": "root",
"password": null,
"database": "database_test",
"host": "127.0.0.1",
"dialect": "mysql"
},
"production": {
"username": "root",
"password": null,
"database": "database_production",
"host": "127.0.0.1",
"dialect": "mysql"
}
}
to this ...
Check out our hands-on, practical guide to learning Git, with best-practices, industry-accepted standards, and included cheat sheet. Stop Googling Git commands and actually learn it!
{
"development": {
"dialect": "sqlite",
"storage": "./database.sqlite3"
},
"test": {
"dialect": "sqlite",
"storage": ":memory"
},
"production": {
"dialect": "sqlite",
"storage": "./database.sqlite3"
}
}
which will create and use a SQLite database file called database.sqlite3 in the root of the project.
I will now follow that command with another one but, this time I will be using the model:generate argument to define my Contact model and its attributes, as follows:
$ node_modules/.bin/sequelize model:generate --name Contact --attributes firstName:string,lastName:string,phone:string,email:string
The --name parameter is obviously the name of the model to be generated and the --attibutes parameter is followed by the object fields that define it along with their data types. The outputs of this command are two new files:
- models/contact.js - a data model to be used in the Node.js application logic code
- migrations/yyyymmddHHMMSS-create-contact.js - a migration script that will issue DDL SQL to create the contacts table in the database
In addition to the attributes specified in the model:generate command sequelize-cli will also generate an auto-incremented integer id field as well as as createdAt and updatedAt date-link fields.
Next up to do is to run the migration so that the SQLite database will contain the contacts table as follows:
$ node_modules/.bin/sequelize db:migrate
This command will indicate that the migration has been run successfully. I can now open up my newly generated database.sqlite3 file and view the schema like this:
$ sqlite3 database.sqlite3
SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);
CREATE TABLE `Contacts` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `firstName` VARCHAR(255), `lastName` VARCHAR(255), `phone` VARCHAR(255), `email` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);
CREATE TABLE sqlite_sequence(name,seq);
Note that there is also another table in there named SequelizeMeta. This is the table that Sequelize.js uses to keep tack of the order that migrations are ran in. For example, running this command will show the name of the migration script I just ran.
sqlite> .headers ON
sqlite> select * from SequelizeMeta;
name
20180726180039-create-contact.js
Now that I have the Contact model mapped to a database table I would like to generate a seeder script to pre-populate my database with some data to demonstrate how to interact with the ORM in my application code. Generating a seeder script is very similar to the prior commands.
$ node_modules/.bin/sequelize seed:generate --name seed-contact
The output is a new script in the seeders directory of the naming convention yyyymmddHHMMSS-seed-contact.js. Initially it's just a scaffold of a seeder interface like this:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
/*
Add altering commands here.
Return a promise to correctly handle asynchronicity.
Example:
return queryInterface.bulkInsert('Person', [{
name: 'John Doe',
isBetaMember: false
}], {});
*/
},
down: (queryInterface, Sequelize) => {
/*
Add reverting commands here.
Return a promise to correctly handle asynchronicity.
Example:
return queryInterface.bulkDelete('Person', null, {});
*/
}
};
I will edit it as follows to add a few contacts.
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
/*
Add altering commands here.
Return a promise to correctly handle asynchronicity.
Example:
return queryInterface.bulkInsert('Person', [{
name: 'John Doe',
isBetaMember: false
}], {});
*/
return queryInterface.bulkInsert('Contacts', [{
firstName: 'Snoop',
lastName: 'Dog',
phone: '111-222-3333',
email: '[email protected]',
createdAt: new Date().toDateString(),
updatedAt: new Date().toDateString()
}, {
firstName: 'Scooby',
lastName: 'Doo',
phone: '444-555-6666',
email: '[email protected]',
createdAt: new Date().toDateString(),
updatedAt: new Date().toDateString()
}, {
firstName: 'Herbie',
lastName: 'Husker',
phone: '402-437-0001',
email: '[email protected]',
createdAt: new Date().toDateString(),
updatedAt: new Date().toDateString()
}], {});
},
down: (queryInterface, Sequelize) => {
/*
Add reverting commands here.
Return a promise to correctly handle asynchronicity.
Example:
return queryInterface.bulkDelete('Person', null, {});
*/
return queryInterface.bulkDelete('Contacts', null, {});
}
};
Lastly, I need to run the seeder to populate the database with these initial contacts.
$ node_modules/.bin/sequelize db:seed:all
Which gives me output in the console letting me know that the database table was successfully seeded with data.
Ok, setup is finally done. Now I can move on to the more fun part of actually interacting with my database-backed Contact model and give those previously stubbed out API endpoints their required functionality.
However, before I can use my Contact model I need to let the Express.js application know that it exists. I do this by adding a require in server.js and assigning it to a const variable called db like so:
// server.js
const express = require('express');
const bodyParser = require('body-parser');
const db = require('./models'); // new require for db object
This db variable will contain my Contact model, which is accessible via db.Contact.
I start out with the simplest of the API endpoints, the GET /api/contacts endpoint. This endpoint simply needs to have all contacts returned from the database and serialized in a response to the calling requester. I can call the findAll method of the Contact object and once the thenable promise is returned I can fire the contacts off to the client using the familiar res.send(...) provided by the Express.js framework.
// server.js
// ommitting everything above ...
app.get('/api/contacts', (req, res) => {
return db.Contact.findAll()
.then((contacts) => res.send(contacts))
.catch((err) => {
console.log('There was an error querying contacts', JSON.stringify(err))
return res.send(err)
});
});
// omitting everything below...
I can now start up my Express server, point my browser to localhost:3000/index.html, and I will be greeted with my contacts UI.
$ nodemon
... or if not using nodemon:
$ npm start
Moving on I will now implement the create functionality for the POST /api/contacts endpoint. Creating new contact instances is super simple. Simply call the create(...) method of the db.Contact object, wait for the promise to resolve by chaining on a then(...) and I will then return the newly created contact to the client, like so:
// server.js
// ommitting everything above ...
app.post('/api/contacts', (req, res) => {
const { firstName, lastName, phone } = req.body
return db.Contact.create({ firstName, lastName, phone })
.then((contact) => res.send(contact))
.catch((err) => {
console.log('***There was an error creating a contact', JSON.stringify(contact))
return res.status(400).send(err)
})
});
// omitting everything below ...
Now if I enter another contact, say, Wonder Woman, but leave the phone number blank (she said she would call me instead of giving her number out), into the UI's "Add Contact" form and press "Save" my contact list will now have four members: Snoop Dog, Scooby Doo, Herbie Husker, and Wonder Woman.
Moving on I can now add the functionality to implement the update behavior for the PUT /api/contacts/:id endpoint. This is a two-part interaction from the database programming perspective.
First I begin with finding the contact matching the ID given in the API URL using the db.Contact.findById(...) method, then I modify the fields with new data, and end by using the update(...) method of the contact object sending the updated model back down to the database where those changes will be persisted.
// server.js
// ommitting everything above ...
app.put('/api/contacts/:id', (req, res) => {
const id = parseInt(req.params.id)
return db.Contact.findById(id)
.then((contact) => {
const { firstName, lastName, phone } = req.body
return contact.update({ firstName, lastName, phone })
.then(() => res.send(contact))
.catch((err) => {
console.log('***Error updating contact', JSON.stringify(err))
res.status(400).send(err)
})
})
});
// omitting everything below...
With this in place I can now update Wonder Woman's phone number because my good buddy Snoop Dog graciously gave it to me. To do this I click on Wonder Woman's name in the contacts list of the UI to expand the details / update form. After filling in her number 111-888-1213 and clicking Save my Wonder Woman contact is updated.
The final thing to do is to add the ability to delete a contact via the DELETE /api/contacts/:id endpoint. Deleting is very similar to the update functionality in that the model instance you would like to delete first needs to be retrieved from the database, and then you simply call the destroy() method of the model's instance.
// server.js
// ommitting everything above ...
app.delete('/api/contacts/:id', (req, res) => {
const id = parseInt(req.params.id)
return db.Contact.findById(id)
.then((contact) => contact.destroy())
.then(() => res.send({ id }))
.catch((err) => {
console.log('***Error deleting contact', JSON.stringify(err))
res.status(400).send(err)
})
});
// omitting everything below ...
Turns out its a good thing that contacts in my contact list can be deleted because Wonder Woman turned out to be a real drama queen and was picking fights with my wife, not cool Wonder Woman. You are about to be deleted.
From the UI I can delete Wonder Woman by clicking on the trash can to the right of her name.
The entirety of the server.js script is show below for completeness.
// server.js
// server.js
const express = require('express');
const bodyParser = require('body-parser');
const db = require('./models');
const app = express();
app.use(bodyParser.json());
app.use(express.static(__dirname + '/static'));
app.get('/api/contacts', (req, res) => {
return db.Contact.findAll()
.then((contacts) => res.send(contacts))
.catch((err) => {
console.log('There was an error querying contacts', JSON.stringify(err))
return res.send(err)
});
});
app.post('/api/contacts', (req, res) => {
const { firstName, lastName, phone } = req.body
return db.Contact.create({ firstName, lastName, phone })
.then((contact) => res.send(contact))
.catch((err) => {
console.log('***There was an error creating a contact', JSON.stringify(contact))
return res.status(400).send(err)
})
});
app.delete('/api/contacts/:id', (req, res) => {
const id = parseInt(req.params.id)
return db.Contact.findById(id)
.then((contact) => contact.destroy({ force: true }))
.then(() => res.send({ id }))
.catch((err) => {
console.log('***Error deleting contact', JSON.stringify(err))
res.status(400).send(err)
})
});
app.put('/api/contacts/:id', (req, res) => {
const id = parseInt(req.params.id)
return db.Contact.findById(id)
.then((contact) => {
const { firstName, lastName, phone } = req.body
return contact.update({ firstName, lastName, phone })
.then(() => res.send(contact))
.catch((err) => {
console.log('***Error updating contact', JSON.stringify(err))
res.status(400).send(err)
})
})
});
app.listen(3000, () => {
console.log('Server is up on port 3000');
});
Conclusion
In this tutorial I have demonstrated how to use the Sequelize.js ORM along with its Sequelize CLI to handle database programming in conjunction with SQLite. In doing so I provided a silly contacts list application that utilizes Node.js/Express.js for an application server along with a Vue.js based UI. Sequelize.js is a rather useful ORM that cuts away many of the details needed for database programming within Node.js-based applications and is quite popular among Node.js developers.
As always I thank you for reading and welcome comments and criticisms below.
