Building Bookmark Manager using nodejs and mysql

This article is an introductory tutorial for Nodejs and MySQL explaining how to build a bookmark manager using nodejs and mysql. Building Bookmark Manager using nodejs and mysql.

you may be thinking, Oh No!!!! Here comes an another To-do app tutorial. you maybe right. but, i wanted to teach the nodejs and mysql concept in a different way.

First of all nodejs and mysql makes a good pair to develop applications. Here, we are going to use knex which is a SQL Query builder for bookmark manager application.

Why Knex.js

Mainly, knex query builder provider a layer of abstraction from the running the native SQL Queries in Nodejs Application. One of benefit of it is, it provides an option to run dynamic fields and values in the Query.

Enough of talking, let's see how to build an application using nodejs, knex and mysql.

Demo

Setup

Complete Source code can be found in this repo. before going further, let's see the dependencies for this application.

1npm init --yes
2npm install express body-parser express-handlebars knex mysql unfurl.js
  • Express - used to build a web application. it's basically for framework to build apps.
  • body-parser - if you want to parse the post request data in request body. body parser is a must have package.
  • express-handlebars - this package is used to render the view in nodejs express.
  • knex - SQL query builder used with mysql.
  • mysql - this is used to connect with mysql that it running in the machine.
  • unfurl.js - this library is used to unfurl the bookmarked url data.

we will be using babel for latest ES6 feature, if you are new to babel, checkout this article.

create a file app.js and add the following code,

1import express from "express"
2import bookmarkRoutes from "./routes/bookmarks"
3import * as BodyParser from "body-parser"
4const app = express()
5import hbs from "express-handlebars"
6
7app.use(BodyParser.json())
8app.use(BodyParser.urlencoded({ extended: true }))
9
10app.engine("hbs", hbs({ extname: "hbs" }))
11app.set("view engine", "hbs")
12app.use(express.urlencoded({ extended: true }))
13
14require("dotenv").config({})
15
16app.use("/bookmark", bookmarkRoutes)
17
18app.get("/", (req, res) => {
19 res.send("Hello Knex")
20})
21
22const PORT = process.env.PORT
23app.listen(PORT, () => {
24 console.log(`app is listening on PORT 3005`)
25})

Firstly, we import all the modules required for the application. After that, we are setting up the handlebar view engine.

1app.engine("hbs", hbs({ extname: "hbs" }))
2app.set("view engine", "hbs")
3app.use(express.urlencoded({ extended: true }))

Secondly, we are setting up the environment variables which is done through dotenv.

1require("dotenv").config({})

After that, we define the routes file which are about to see in few minutes.

1app.use("/bookmark", bookmarkRoutes)
2
3app.get("/", (req, res) => {
4 res.send("Hello Knex")
5})

finally, we starts the http server by listening to a specified port.

1const PORT = process.env.PORT
2app.listen(PORT, () => {
3 console.log(`app is listening on PORT 3005`)
4})

Now, it's time to setup db and knexfile for DB. create a file knexfile.js and add the following code,

1module.exports = {
2 development: {
3 client: "mysql",
4 connection: {
5 host: "localhost",
6 user: "root",
7 password: "password",
8 database: "bookmark_app",
9 },
10 },
11 staging: {
12 client: "mysql",
13 connection: {
14 host: "127.0.0.1",
15 user: "root",
16 password: "123456",
17 database: "bookmark_app",
18 },
19 },
20}

By defining this, you can setup different stages for application db connection. it can contain development stage, production stage and testing stage.

After that, create a file db.js and add the following code,

1require("dotenv").config({})
2const environment = process.env.NODE_ENV
3console.log("environment", environment)
4const config = require("./knexfile")[environment]
5
6const connection = require("knex")(config)
7
8module.exports = {
9 getBookMarks: getBookMarks,
10 getBookMarkById,
11 updateBookmark: updateBookmark,
12 insertBookMark: insertBookMark,
13}
14
15function getBookMarks(db = connection) {
16 return db("bookmarks").select()
17}
18
19function getBookMarkById(id, db = connection) {
20 return db("bookmarks").where({ id })
21}
22
23function insertBookMark(url, db = connection) {
24 return db("bookmarks").insert({ url: url })
25}
26
27function updateBookmark({ id, url }, db = connection) {
28 return db("bookmarks")
29 .where("id", id)
30 .update({ url: url })
31}

Here, we have different function to run the db queries.

  • getBookMarks - this function calls the db select and returns the total bookmarks in the database.
  • getBookMarkById - this returns the bookmark data for the particular id.
  • insertBookMark - As the name suggests, it inserts the data into the database.
  • updateBookmark - this function updates the data for the specified id in the database.

We are done with the db model and knexfile configuration. now, it's time to create migrations for mysql table.

what is migration ? and why ?

Mainly, migration is an efficient way to manage the changes or updates in the database which are basically adding or droping the tables.

it is easier to maintain the table in the database rather than changing it manually.

That is to say, create a folder migrations and file create_bookmark_table.js. add the following code,

1exports.up = knex => {
2 return knex.schema.createTable("bookmarks", t => {
3 t
4 .increments("id")
5 .primary()
6 .unsigned(),
7 t.string("url")
8 })
9}
10
11exports.down = knex => {
12 return knex.schema.dropTable("bookmarks")
13}

Now, it is time to run the migration using the command.

1npx knex migrate:latest

Screenshot 2020 02 15 at 1 30 21 AM

Further, create a folder routes and file bookmark.js to manage the routes

1import { Router } from "express"
2import { unfurl } from "unfurl.js"
3
4const router = Router()
5const db = require("../db")
6
7const unfurlUrl = ({ id, url }) => {
8 return new Promise(async (resolve, reject) => {
9 try {
10 const result = await unfurl(url)
11
12 resolve({ id, data: result })
13 } catch (e) {
14 reject(e)
15 }
16 })
17}
18
19router.get("/", (req, res) => {
20 db.getBookMarks()
21 .then(async bookmarks => {
22 console.log("bookmarks", bookmarks)
23 const data = await Promise.all(
24 bookmarks.map(bookmark =>
25 unfurlUrl({ id: bookmark.id, url: bookmark.url })
26 )
27 )
28
29 res.render("index", { bookmarks: data })
30 })
31 .catch(err => {
32 res.status(500).send("DATABASE ERROR: " + err.message)
33 })
34})
35
36router.post("/insert", (req, res) => {
37 const url = req.body.url
38 console.log("url", url)
39 db.insertBookMark(url)
40 .then(bookmark => {
41 db.getBookMarks().then(async bookmarks => {
42 const data = await Promise.all(
43 bookmarks.map(bookmark =>
44 unfurlUrl({ id: bookmark.id, url: bookmark.url })
45 )
46 )
47 res.render("index", { bookmarks: data })
48 })
49 })
50 .catch(err => {
51 res.status(500).send("DATABASE ERROR" + err.message)
52 })
53})
54
55router.get("/:id", (req, res) => {
56 const id = req.params.id
57 db.getBookMarkById(id)
58 .then(bookmark => {
59 console.log("bookmark", bookmark[0].url)
60 res.render("update", { id: bookmark[0].id, url: bookmark[0].url })
61 })
62 .catch(err => {
63 res.status(500).send("DATABASE ERROR" + err.message)
64 })
65})
66
67router.post("/:id", (req, res) => {
68 const id = req.body.id
69 const url = req.body.url
70 db.updateBookmark({ id, url })
71 .then(res.redirect("/bookmark"))
72 .catch(err => {
73 res.status(500).send("DATABASE ERROR" + err.message)
74 })
75})
76
77export default router

Let's breakdown it one by one,

  • GET method - GET method returns all the bookmarks in the database. on returning the data, it will call a function which returns the unfurled data for an url.
  • POST method - this takes the data and insert into the database and returns all the data that are available in the database.
  • GET method(id) - this method returns the data for the specified id.
  • POST method(update) - this method update the data for the specified id.

Finally, create a view to render the data in the express handlebars.

Screenshot 2020 02 15 at 2 23 12 AM

Summary

To sum up, we have seen how to build a simple bookmark manager application using nodejs and mysql. Take this application as a first step into learning nodejs and mysql and proceed further from here.

Complete Source Code

To Read More

Building a Piano with React Hooks

In this article, we will see how to build a piano with react hooks. Building a P...

TypeScript Basics - The Definitive ...

In this article, we will learn some basics of typescript which helps you to deve...

Here's why podman is more secured t...

In this article we will see about podman and why it is more secured way to run c...

Building a Production - Ready Node....

In this article, we will see how to build a Nodejs, TypeScript Application and d...

Nginx for Front-end Developers

This article is to explain Nginx for Front-end Developers in a much simpler way....

What is gRPC ? How to implement gRP...

Everyone talks about gRPC. Have you ever wonder how it works or how to implement...