vue+node链接数据库_使用Vue和Node构建迷你发票应用程序：数据库和API

vue+node链接数据库

To get paid for goods and services provided, companies/freelancers need to send invoices to their customers informing them of the services that they will be charged for. Back then, people had paper invoices which they gave to the customers when they contact them for their services. Right now, with the advent and advancement of technology, people are now able to send electronic invoices to their customers.

为了从所提供的商品和服务中获得报酬，公司/自由职业者需要向客户发送发票，以告知他们要付费的服务。 当时，人们有纸发票，当他们与他们联系以获取服务时便给了他们。 现在，随着技术的出现和进步，人们现在能够将电子发票发送给他们的客户。

In this article, we are going to look at how to build a mini invoicing application using Vue and NodeJS. This application will perform simple function such as - creating an invoice, sending an invoice, editing and deleting an invoice.

在本文中，我们将研究如何使用Vue和NodeJS构建小型发票应用程序。 该应用程序将执行简单的功能，例如-创建发票，发送发票，编辑和删除发票。

要求 ( Requirements )

To follow through the article adequately, you’ll need the following:

要充分阅读本文，您需要满足以下条件：

• Node installed on your machine
  安装在您机器上的节点
• Node Package Manager (NPM) installed on your machine
  您计算机上安装的节点软件包管理器（NPM）

To verify your installation, run the following :

要验证您的安装，请运行以下命令：

node --versionnpm --version

If you get version numbers as results then you’re good to go.

如果您得到版本号作为结果，那您就很好了。

设置服务器 ( Setting Up Server )

Now that we have the requirements all set, the next thing to do is to create the backend server for the application. The backend server will simply:

现在我们已经满足了所有要求，接下来要做的就是为应用程序创建后端服务器。 后端服务器将简单地：

• Maintain the database connection
  维护数据库连接

Now, let’s get to creating the server. Create a folder to house the new project and initialize it as a node project:

现在，让我们开始创建服务器。 创建一个文件夹来容纳新项目并将其初始化为节点项目：

mkdir invoicing-app
    cd invoicing-app && npm init

For the server to function appropriately, there are some node packages that need to be installed, you can install them by running the command :

为了使服务器正常运行，需要安装一些节点程序包，您可以通过运行以下命令来安装它们：

npm install --save express body-parser connect-multiparty sqlite3 bluebird path umzug bcrypt

• bcrypt to hash user passwords
  bcrypt哈希用户密码
• express to power our web application
  express为我们的Web应用程序提供动力
• sqlite3 to create and maintain the database
  sqlite3创建和维护数据库
• path to resolve file paths within our application
  path到我们的应用程序内解决文件路径
• bluebird to use Promises when writing migrations
  bluebird在编写迁移时使用Promises
• umzug as a task runner to run our database migrations
  umzug作为任务运行器来运行我们的数据库迁移
• body-parser and connect-multiparty to handle incoming form requests
  body-parser和connect-multiparty处理传入的表单请求

Now, go ahead to create a server.js file that will house the application logic:

现在，继续创建一个将包含应用程序逻辑的server.js文件：

touch server.js

In the server.js, Import the necessary modules and create an express app:

在server.js ，导入必要的模块并创建一个快速应用程序：

// server.js

    const express = require('express')
    const bodyParser = require('body-parser');
    const sqlite3 = require('sqlite3').verbose();
    const PORT = process.env.PORT || 3128;

    const app = express();
    app.use(bodyParser.urlencoded({extended: false}));
    app.use(bodyParser.json());

    [...]

then create a simple / route to test our server works:

然后创建一个简单的/路由来测试我们的服务器是否正常工作：

// server.js
    [...]

    app.get('/', function(req,res){
        res.send("Welcome to Invoicing App");
    });

    app.listen(PORT, function(){
        console.log(`App running on localhost:${PORT}`);
    });

app.listen() tells the server the port to listen to for incoming routes.

app.listen()告诉服务器端口监听入站路由。

To start the server, run the following in your project directory:

要启动服务器，请在项目目录中运行以下命令：

node server

Your application begins to listen to incoming requests.

您的应用程序开始侦听传入的请求。

使用SQLite创建并连接到数据库 ( Creating And Connecting To Database Using SQLite )

For an invoicing application, a database is needed to store the existing invoices. SQLite is going to be our database client of choice for this application.

对于开票应用程序，需要一个数据库来存储现有的发票。 SQLite将成为我们选择该应用程序的数据库客户端。

To create a database folder, create a file for your database in the folder:

要创建数据库文件夹，请在该文件夹中为数据库创建一个文件：

mkdir database
    cd database && touch InvoicingApp.db

In the database directory, run the sqlite3 client and then open InvoicingApp.db database

在数据库目录中，运行sqlite3客户端，然后打开InvoicingApp.db数据库

$ invoicing-app/database/   sqlite3> .open InvoicingApp.db

Now that the database to be used has been selected, next thing is to create the needed tables.

既然已经选择了要使用的数据库，下一步就是创建所需的表。

Database Structure For this application, there are 3 tables needed:

数据库结构对于此应用程序，需要3个表：

• Users - This will contain the user data (id, name, email, company_name, password)
  用户 -这将包含用户数据（ID，名称，电子邮件，公司名称，密码）
• Invoices - Store data for an invoice (id, name, paid, user_id)
  发票 -存储发票数据（ID，名称，已付款，user_id）
• Transactions - Singular transactions that come together to make an invoice (name, price, invoice_id)
  交易 -一起构成发票的单一交易（名称，价格，invoice_id）

Since the necessary tables have been identified, the next step is to run the queries to create the tables.

由于已经确定了必要的表，因此下一步是运行查询以创建表。

Writing Migrations Migrations are used to keep track of changes in a database as the application grows. To do this, create a migrations folder in the database directory.

编写迁移迁移用于随着应用程序的增长跟踪数据库中的更改。 为此，请在database目录中创建一个migrations文件夹。

mkdir migrations

This will house all the migration files.

这将容纳所有迁移文件。

Now, create a 1.0.js file in the migrations folder. This naming convention is to keep track of the newest changes.

现在，在migrations文件夹中创建一个1.0.js文件。 此命名约定是为了跟踪最新的更改。

cd migations && touch 1.0.js

In the 1.0.js file, you first import the node modules:

在1.0.js文件中，首先导入节点模块：

// database/migrations 1.0.js
    "use strict";
    const Promise = require("bluebird");
    const sqlite3 = require("sqlite3");
    const path = require('path');

    [...]

Then, the idea now is to export an up function that will be executed when the migration file is run and a down function to reverse the changes to the database.

然后，现在的想法是导出将在运行迁移文件时执行的up函数和一个down函数以将更改撤消到数据库。

// database/migrations/1.0.js

    [...]
    module.exports = {
      up: function() {
        return new Promise(function(resolve, reject) {
          /* Here we write our migration function */
          let db = new sqlite3.Database('./database/InvoicingApp.db');
          //   enabling foreign key constraints on sqlite db
          db.run(`PRAGMA foreign_keys = ON`);

          [...]

In the up function, connection is first made to the database and then, foreign keys are enabled on the sqlite database

在up功能中，首先建立与数据库的连接，然后在sqlite数据库上启用外键

In SQLite, foreign keys are disabled by default to allow for backwards compatibility so, the foreign keys have to be enabled on every connection

在SQLite中，默认情况下禁用外键以允许向后兼容，因此必须在每个连接上启用外键

After successful connection, the queries to create the tables are then specified

成功连接后，然后指定创建表的查询

// database/migrations/1.0.js
          [...]
          db.serialize(function() {
            db.run(`CREATE TABLE users (
              id INTEGER PRIMARY KEY,
              name TEXT,
              email TEXT,
              company_name TEXT,
              password TEXT
            )`);

            db.run(`CREATE TABLE invoices (
              id INTEGER PRIMARY KEY,
              name TEXT,
              user_id INTEGER,
              paid NUMERIC,
              FOREIGN KEY(user_id) REFERENCES users(id)
            )`);

            db.run(`CREATE TABLE transactions (
              id INTEGER PRIMARY KEY,
              name TEXT,
              price INTEGER,
              invoice_id INTEGER,
              FOREIGN KEY(invoice_id) REFERENCES invoices(id)
            )`);
          });
          db.close();
        });
      },
      [...]

The serialize() is used to specify that the queries should be run sequentially and not simultaneously

serialize()用于指定查询应按顺序而不是同时运行

Afterwards, the queries to reverse the changes are also specified in the down() function below

之后，还可以在下面的down()函数中指定用于撤消更改的查询

// database/migrations/1.0.js
      [...]

      down: function() {
        return new Promise(function(resolve, reject) {
          /* This runs if we decide to rollback. In that case we must revert the `up` function and bring our database to it's initial state */
          let db = new sqlite3.Database("./database/InvoicingApp.db");
          db.serialize(function() {
            db.run(`DROP TABLE transactions`);
            db.run(`DROP TABLE invoices`);
            db.run(`DROP TABLE users`);
          });
          db.close();
        });
      }
    };

Running Migrations Once the migration files have been created, the next step is running them to effect the changes in the database. To do this, create a scripts folder from the root of your application and then create a file called migrate.js.

运行迁移创建迁移文件后，下一步就是运行它们以影响数据库中的更改。 为此，请从应用程序的根目录创建一个scripts文件夹，然后创建一个名为migrate.js的文件。

mkdir scripts
    cd scripts && touch migrate.js

The migrate.js file will look like this:

migrate.js文件将如下所示：

// scripts/migrate.js

    const path = require("path");
    const Umzug = require("umzug");

    let umzug = new Umzug({
      logging: function() {
        console.log.apply(null, arguments);
      },
      migrations: {
        path: "./database/migrations",
        pattern: /\.js$/
      },
      upName: "up",
      downName: "down"
    });

    [...]

First, the needed node modules are imported and then, a new umzug object is created with the following configurations. The path and pattern of the migrations scripts are also specified. To learn more about the configurations, head over here

首先，导入所需的节点模块，然后使用以下配置创建一个新的umzug对象。 还指定了迁移脚本的路径和模式。 要了解有关配置的更多信息，请转到此处

To also give some verbose feedback, create a function to log events as shown below and then finally execute the up function to run the database queries specified in the migrations folder.

为了提供一些详细的反馈，请创建一个记录事件的函数，如下所示，然后最终执行up函数以运行migrations文件夹中指定的数据库查询。

// scripts/migrate.js
    [...]

    function logUmzugEvent(eventName) {
      return function(name, migration) {
        console.log(`${name} ${eventName}`);
      };
    }

    // using event listeners to log events
    umzug.on("migrating", logUmzugEvent("migrating"));
    umzug.on("migrated", logUmzugEvent("migrated"));
    umzug.on("reverting", logUmzugEvent("reverting"));
    umzug.on("reverted", logUmzugEvent("reverted"));

    // this will run your migrations
    umzug.up().then(console.log("all migrations done"));

Now, to execute the script, go to your terminal and in the root directory of your application, run:

现在，要执行脚本，请转到终端并在应用程序的根目录中运行：

> ~/invoicing-app node scripts/migrate.js up
    all migrations done
    == 1.0: migrating =======
    1.0 migrating

创建申请路线 ( Creating Application Routes )

Now that the database is adequately set up, the next thing is to go back to the server.js file and create the application routes. For this application, the following routes will be made available:

现在已经正确设置了数据库，接下来要做的是回到server.js文件并创建应用程序路由。 对于此应用程序，将提供以下路由：

URL	METHOD	FUNCTION
/register	POST	To register a new user
/login	POST	To log in an existing user
/invoice	POST	To create a new invoice
/invoice/user/{user_id}	GET	To fetch all the invoices for a user
/invoice/user/{user_id}/{invoice_id}	GET	To fetch a certain invoice
/invoice/send	POST	To send invoice to client

网址	方法	功能
/register	POST	注册新用户
/login	POST	登录现有用户
/invoice	POST	创建新发票
/invoice/user/{user_id}	GET	提取用户的所有发票
/invoice/user/{user_id}/{invoice_id}	GET	提取特定发票
/invoice/send	POST	向客户发送发票

Registering a New User To register a new user, a post request will be made to the /register route of our server. This route will look like this :

注册新用户要注册新用户，将向我们服务器的/register路由发出发布请求。 这条路线看起来像这样：

// server.js
    [...]
    const bcrypt = require('bcrypt')
    const saltRounds = 10;
    [...]

    app.post('/register', function(req, res){
        // check to make sure none of the fields are empty
        if( isEmpty(req.body.name)  || isEmpty(req.body.email) || isEmpty(req.body.company_name) || isEmpty(req.body.password) ){
            return res.json({
                'status' : false,
                'message' : 'All fields are required'
            });
        }
        // any other intendend checks

        [...]

A check is made to see if any of the fields are empty. If no fields are empty and if the data sent matches all the specification. If an error occurs, an error message is sent to the user as a response. If not, the password is hashed and the data is then stored in the database and a response is sent to the user informing them that they are registered.

进行检查以查看是否有任何字段为空。 如果没有字段为空，并且发送的数据与所有规范匹配。 如果发生错误，则会将错误消息发送给用户作为响应。 如果不是，则对密码进行哈希处理，然后将数据存储在数据库中，并将响应发送给用户，通知他们已注册。

// server.js 
        bcrypt.hash(req.body.password, saltRounds, function(err, hash) {
        let db = new sqlite3.Database("./database/InvoicingApp.db");
        let sql = `INSERT INTO users(name,email,company_name,password) VALUES('${
          req.body.name
        }','${req.body.email}','${req.body.company_name}','${hash}')`;
        db.run(sql, function(err) {
          if (err) {
            throw err;
          } else {
            return res.json({
              status: true,
              message: "User Created"
            });
          }
        });
        db.close();
      });
    });

When a sample request is made from Postman, the result below is obtained :

当邮递员提出样品请求时，将获得以下结果：

Logging in a New User Now, if an existing user tries to log in to the system using the /login route, they need to provide their email address and password. Once they do that, the route handles the request as follows :

立即登录新用户 ，如果现有用户尝试使用/login路由/login到系统，则需要提供其电子邮件地址和密码。 一旦他们这样做，路由将按以下方式处理请求：

// server.js
    [...]

    app.post("/login", function(req, res) {
      let db = new sqlite3.Database("./database/InvoicingApp.db");
      let sql = `SELECT * from users where email='${req.body.email}'`;
      db.all(sql, [], (err, rows) => {
        if (err) {
          throw err;
        }
        db.close();
        if (rows.length == 0) {
          return res.json({
            status: false,
            message: "Sorry, wrong email"
          });
        }

      [...]

A query is made to the database to fetch the record of the user with a particular email. If the result returns an empty array, then it means that the user doesn’t exist and a response is sent informing the user of the error.

对数据库进行查询，以获取具有特定电子邮件的用户记录。 如果结果返回一个空数组，则意味着该用户不存在，并且发送了一个响应，通知用户该错误。

If the database query returned user data, further check is made to see if the password entered matches that password in the database. If it does, then a response is sent with the user data.

如果数据库查询返回了用户数据，请进一步检查以查看输入的密码是否与数据库中的密码匹配。 如果是这样，则将与用户数据一起发送响应。

// server.js
      [...]
        let user = rows[0];
        let authenticated = bcrypt.compareSync(req.body.password, user.password);
        delete user.password;
        if (authenticated) {
          return res.json({
            status: true,
            user: user
          });
        }
        return res.json({
          status: false,
          message: "Wrong Password, please retry"
        });
      });
    });

    [...]

When the route is tested from Postman, you get this result:

从邮递员测试路线后，您会得到以下结果：

Creating a new Invoice The /invoice route handles the creation of an invoice. Data passed to the route will include the following:

创建新发票 /invoice路由处理/invoice的创建。 传递到路线的数据将包括以下内容：

• User ID, Name of the invoice and invoice status.
  用户ID，发票名称和发票状态。
• Singular Transactions to make up the invoice.
  单一交易构成发票。

The server handles the request as follows:

服务器按以下方式处理请求：

// server.js
    [...]
    app.post("/invoice", multipartMiddleware, function(req, res) {
      // validate data
      if (isEmpty(req.body.name)) {
        return res.json({
          status: false,
          message: "Invoice needs a name"
        });
      }
      // perform other checks

      [...]

First, the data sent to the server is validated and then, a connection is made to the database for the subsequent queries.

首先，验证发送到服务器的数据，然后为后续查询建立与数据库的连接。

// server.js
      [...]
      // create invoice
      let db = new sqlite3.Database("./database/InvoicingApp.db");
      let sql = `INSERT INTO invoices(name,user_id,paid) VALUES(
        '${req.body.name}',
        '${req.body.user_id}',
        0
      )`;
      [...]

The insert query needed to create the invoice is written and then, it is executed. Afterwards, the singular transactions are inserted into the transactions table with the invoice_id as foreign key to reference them.

编写创建发票所需的插入查询，然后执行它。 然后，将单个交易插入到具有invoice_id作为外键的transactions表中以引用它们。

// server.js
      [...]
      db.serialize(function() {
        db.run(sql, function(err) {
          if (err) {
            throw err;
          }
          let invoice_id = this.lastID;
          for (let i = 0; i < req.body.txn_names.length; i++) {
            let query = `INSERT INTO transactions(name,price,invoice_id) VALUES(
                '${req.body.txn_names[i]}',
                '${req.body.txn_prices[i]}',
                '${invoice_id}'
            )`;
            db.run(query);
          }
          return res.json({
            status: true,
            message: "Invoice created"
          });
        });
      });
    [...]

Once this is executed, the invoice is successfully created:

一旦执行，便成功创建了发票：

On checking the SQLite database, the following result is obtained:

在检查SQLite数据库时，获得以下结果：

sqlite> select * from invoices;
    1|Test Invoice New|2|0
    sqlite> select * from transactions;
    1|iPhone|600|1
    2|Macbook|1700|1

Fetching All Invoices

提取所有发票

Now, when a user wants to see all the created invoices, the client will make a GET request to the /invoice/user/:id route. The user id is passed as a route parameter. The request is handled as follows:

现在，当用户希望查看所有创建的发票时，客户端将向/invoice/user/:id路由发出GET请求。 用户ID作为路由参数传递。 该请求的处理方式如下：

// index.js
      [...]
    app.get("/invoice/user/:user_id", multipartMiddleware, function(req, res) {
      let db = new sqlite3.Database("./database/InvoicingApp.db");
      let sql = `SELECT * FROM invoices LEFT JOIN transactions ON invoices.id=transactions.invoice_id WHERE user_id='${req.params.user_id}'`;
      db.all(sql, [], (err, rows) => {
        if (err) {
          throw err;
        }
        return res.json({
          status: true,
          transactions: rows
        });
      });
    });

    [...]

A query is run to fetch all the invoices and the transactions related to the invoice belonging to a particular user.

运行查询以获取所有发票和与属于特定用户的发票有关的交易。

Fetching Single Invoice To fetch a specific invoice, the a GET request is with the user_id and invoice_id to the /invoice/user/{user_id}/{invoice_id} route. The request is handles as follows

提取单个发票要获取特定的发票， GET请求与到/invoice/user/{user_id}/{invoice_id}路由的user_id和invoice_id一起使用。 请求是如下处理

// index.js
    [...] 

    app.get("/invoice/user/:user_id/:invoice_id", multipartMiddleware, function(req, res) {
      let db = new sqlite3.Database("./database/InvoicingApp.db");
      let sql = `SELECT * FROM invoices LEFT JOIN transactions ON invoices.id=transactions.invoice_id WHERE user_id='${
        req.params.user_id
      }' AND invoice_id='${req.params.invoice_id}'`;
      db.all(sql, [], (err, rows) => {
        if (err) {
          throw err;
        }
        return res.json({
          status: true,
          transactions: rows
        });
      });
    });

    // set application port
    [...]

A query is run to fetch a single invoice and the transactions related to the invoice belonging to the user.

运行查询以获取单个发票以及与属于用户的发票相关的交易。

Running the request on Postman will give you the result below :

在Postman上运行请求将为您提供以下结果：

结论 ( Conclusion )

In this part of the series, we walked though how to get up your server with all the needed routes for the mini invoicing application. In the next part of this series, we will look at how to Create the Interface for the invoicing application using Vue. Here’s a link to the full Github repository. Feel free to leave a comment below.

在本系列的这一部分中，我们介绍了如何使用小型发票应用程序的所有必需路径来启动服务器。 在本系列的下一部分中，我们将研究如何使用Vue为发票应用程序创建接口。 这是完整Github存储库的链接。 随时在下面发表评论。

翻译自: https://scotch.io/tutorials/building-a-mini-invoicing-app-with-vue-and-node-database-and-api

vue+node链接数据库