Node.js in Action Chapter 3 What is a Node Web Program 3.3 Adding a Database

Node.js in action

Chapter 3 What is a Node Web Application

3.3 Add database

As far as adding a database to a Node program is concerned, there are no mandatory rules, but generally the following steps are involved.

  1. Decide which database system you want to use.
  2. Check out popular modules on npm that implement database drivers or object-relational mapping (ORM).
  3. Add the module to the project with npm --save.
  4. Create models that encapsulate database access API s.
  5. Add these models to the Express routes.

Before adding the database, let's add the routing processing code of step 5 in Express.

The HTTP route handler in the program makes a simple call to the model. Here is an example:

app.get('/articles', (req, res, err) => { //get all articles
    Article.all(err , articles) => {
        if(err) return next(err);
    }
    res.send(articles);
});

This HTTP route is used to fetch all articles, so the corresponding model method should be similar to Article.all.

It depends on the database API. Generally speaking, it should be Article.find({}, cb) and Article.fetchAll().then(cb), where cb is the abbreviation of callback (callback).

[Which database to choose]

In this project, we are going to use SQLite, and the popular sqlite3 module. SQLite is an in-process database, so it's convenient: you don't need to install a database running in the background on your system. All the added data will be written into a file, that is to say, the data is still there when the program is stopped and restarted, so it is very suitable for introductory learning. [Switch to MySQL later]

3.3.1 Make your own model API

Articles should be able to be created, retrieved, and deleted, so the model class Article should provide the following methods:

  • Article.all(cb) - returns all articles;
  • Article.find(id, cb) ——Given the ID, find the corresponding article;
  • Article.create({ title,content }, cb) - create an article with title and content;
  • Article.delete(id, cb) – delete an article by ID.

These can all be implemented with the sqlite3 module. With this module, we can use db.all to get multiple rows of data, and db.get to get one row of data.

But first there must be a database connection.

Create a new file module db.js

[Model Article]

install sqlite3

npm install --save sqlite3

Write code:

const sqlite3 = require('sqlite3').verbose();
const dbName = 'later.sqlite';
const db = new sqlite3.Database(dbName); //connect to a database file

db.serialize(() => {
    const sql = `create table if not exists articles(id integer primary key,title,content TEXT)`;

    db.run(sql);  //Create an article table if not already
});

class Article{
    static all(cb){
        db.all('select * from articles' , cb); //get all articles
    }

    static find(id, cb){
        db.get('select * from articles where id = ?',id ,cb); //Select a specific article
    }

    static create(data , cb){
        const sql = 'insert into articles(title , content) values(?,?)'; //Question marks indicate parameters
        db.run(sql,data.title,data.content,cb); 
    }

    static delete(id, cb){
        if(!id){
            return cb(new Error('Please provide an id'));
        }
        db.run('delete from articles where id = ?' , id, cb);
    }
}

module.exports = db;
module.exports.Article = Article;

This example creates an object named Article that can create, retrieve, and delete data using standard SQL and the sqlite3 module.

First open a database file with sqlite3.Database, and then create the table articles. The SQL syntax IF NOT EXISTS is used here to prevent the previous table from being deleted and re-created when the code is accidentally re-run.

After the database and tables are prepared, the program can perform queries. All articles can be obtained by using the all method of sqlite3. The specified article can be obtained by providing a specific value to the query syntax with a question mark, and sqlite3 will insert the ID into the query statement. Finally, data can be inserted and deleted with the run method.

The basic database functionality is something like this,

[Add the Article module to the HTTP route]

const express = require('express');
const bodyParser = require('body-parser');
const Article = require('./db').Article; //load database module

const app = express();

const articles = [{ title: 'Example' }];

app.set('port', process.env.PORT || 3000);
app.use(bodyParser.json());  //Support encoding as JSON as message request body
app.use(bodyParser.urlencoded({ extended: true })); //Support for request message bodies encoded as forms


app.get('/articles', (req, res, next) => { //get all articles
    Article.all((err, articles) => {
        if (err) return next(err);
        res.send(articles);
    });
});
    

app.post('/articles', (req, res, next) => { //create an article
    const article = { title: req.body.title };
    articles.push(article);
    res.send(article);
});

app.get('/articles/:id', (req, res, next) => { //Get the specified article
    const id = req.params.id;
    Article.find(id , (err,article) => {
        if(err) return next(err);
        res.send(article);
    });
});

app.delete('/articles/:id', (req, res, next) => { //Delete specified article
    const id = req.params.id;

    Article.delete(id, (err) =>{
        if(err) return next(err);
        res.send({message: 'Deleted'});
    });
});

app.listen(app.get('port'), () => {
    console.log('App started on port', app.get('port'));
});

module.exports = app;

The last thing is to implement the function of creating articles. So the articles need to be downloaded and processed with the magic readability algorithm.

We need a module from npm.

3.3.2 Make the article readable and save it

The RESTful API has been built, and the data can be persisted to the database. Next, it is time to write code to convert the webpage into a simplified version of the "reading view".

But we don't have to implement it ourselves, because there are already such modules in npm.

Try node-readability

npm install node-readability --save install

This module provides an asynchronous function that downloads the page at the specified URL and converts the HTML into a simplified version.

With this module, combined with the database, use the Article.create method to save the article

read(url, (err, result) => {
  Article.create(
    { title: result.title, content: result.content },
    (err, article) => {
      // save the article to the database
    }
  );
});

Open index.js, add a new app.post routing processor, and use the above method to realize the function of downloading and saving articles. Combining all the knowledge we have learned above, namely about HTTP POST and message body parser in Express, we can come up with the following code.

[Generate readable articles and save them]

const express = require('express');
const bodyParser = require('body-parser');
const Article = require('./db').Article; //load database module
const read = require('node-readability');

const app = express();

const articles = [{ title: 'Example' }];

app.set('port', process.env.PORT || 3000);
app.use(bodyParser.json());  //Support encoding as JSON as message request body
app.use(bodyParser.urlencoded({ extended: true })); //Support for request message bodies encoded as forms


app.get('/articles', (req, res, next) => { //get all articles
    Article.all((err, articles) => {
        if (err) return next(err);
        res.send(articles);
    });
});


app.post('/articles', (req, res, next) => { //create an article

    const url = req.body.url;   //Get URL from POST message body
    read(url, (err, result) => { //Use the readability module to get the page pointed to by this URL
        if (err || !result) res.status(500).send('Error downloading article');

        Article.create({ title: result.title, content: result.content }, (err, article) => {
            if (err) return next(err);

            res.send('Ok');  //After the article is saved successfully, send a response with status code 200
        });
    });
});

app.get('/articles/:id', (req, res, next) => { //Get the specified article
    const id = req.params.id;
    Article.find(id, (err, article) => {
        if (err) return next(err);
        res.send(article);
    });
});

app.delete('/articles/:id', (req, res, next) => { //Delete specified article
    const id = req.params.id;

    Article.delete(id, (err) => {
        if (err) return next(err);
        res.send({ message: 'Deleted' });
    });
});

app.listen(app.get('port'), () => {
    console.log('App started on port', app.get('port'));
});

module.exports = app;

Start the service and test:

curl --data "url=http://manning.com/cantelon2/" http://localhost:3000/articles

View all data currently available

nothing wrong.

Our program can now save articles as well as retrieve them. [good]

Tags: node.js Front-end Database

Posted by garygay on Tue, 27 Dec 2022 03:38:58 +0300