javascript - 在 node.js Web 应用程序中全局定义 PostgreSQL 数据库连接变量的最佳实践？

Question

我正在创建一个 Node 并使用 PostgreSQL 数据库表达 REST 应用程序。

我的问题是如何在极简主义的快速应用程序中全局定义连接变量(对于 Hello World 示例)？

我有以下文件结构，其中包含以下关键文件。

• {PROJECT_ROOT}\bin\www
• {PROJECT_ROOT}\app.js
• {PROJECT_ROOT}\routes\index.js
• {PROJECT_ROOT}\db\db.js
• {PROJECT_ROOT}\db\location.js
• {PROJECT_ROOT}\其他文件

db.js 应该包含全局连接到 PostgreSQL 数据库的变量定义。此变量应在必要时由其他模块共享，以避免重复连接。

db.js

var promise = require('bluebird');

/**
 *Use dotenv to read .env vars into Node
 */
require('dotenv').config();

const  options = {
  // Initialization Options
  promiseLib: promise,
  connect(client, dc, useCount) {
    const cp = client.connectionParameters;
    console.log('Connected to database:', cp.database);
  }
};

const  pgp = require('pg-promise')(options);
const  connectionString = process.env.PG_CONN_STR;
const  db = pgp(connectionString);

module.exports = {
    pgp, db
};

location.js 定义了操作 gcur_point_location 表的业务逻辑。

var db_global = require('./db');
var db = db_global.db;

// add query functions

module.exports = {
  getAllLocations: getAllLocations,
  getLocation: getLocation,
  createLocation: createLocation,
  updateLocation: updateLocation,
  removeLocation: removeLocation
};

function getAllLocations(req, res, next) {
  db.any('select * from gcur_point_location')
    .then(function (data) {
      res.status(200)
        .json({
          status: 'success',
          data: data,
          message: 'Retrieved ALL GCUR Point Locations'
        });
    })
    .catch(function (err) {
      return next(err);
    });
}

function getLocation(req, res, next) {
  var locationId = parseInt(req.params.id);
  db.one('select * from gcur_point_location where locationid = $1', locationId)
    .then(function (data) {
      res.status(200)
        .json({
          status: 'success',
          data: data,
          message: 'Retrieved ONE Location by Id'
        });
    })
    .catch(function (err) {
      return next(err);
    });
}

function createLocation(req, res, next) {
  req.body.age = parseInt(req.body.age);
  db.none('insert into gcur_point_location(locationname, locationstatus, lng, lat)' +
      'values(${locationname}, ${locationstatus}, ${lng}, ${lat})',
    req.body)
    .then(function () {
      res.status(200)
        .json({
          status: 'success',
          message: 'Inserted one Location'
        });
    })
    .catch(function (err) {
      return next(err);
    });
}

function updateLocation(req, res, next) {
  db.none('update gcur_point_location set locationname=$1, locationstatus=$2, lng=$3, lat=$4 where locationid=$5',
    [req.body.locationname, req.body.locationstatus, parseFloat(req.body.lng),
      parseFloat(req.body.lat), parseInt(req.params.id)])
    .then(function () {
      res.status(200)
        .json({
          status: 'success',
          message: 'Updated Location'
        });
    })
    .catch(function (err) {
      return next(err);
    });
}

function removeLocation(req, res, next) {
  var locationId = parseInt(req.params.id);
  db.result('delete from gcur_point_location where locationid=$1', locationId)
    .then(function (result) {
      /* jshint ignore:start */
      res.status(200)
        .json({
          status: 'success',
          message: `Removed ${result.rowCount} Location`
        });
      /* jshint ignore:end */
    })
    .catch(function (err) {
      return next(err);
    });
}

同样，不同表的操作将在单独的 js 文件中定义。所有这些都需要 db.js。

routes/index.js

var express = require('express');
var router = express.Router();

var db = require('../db/location');

/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('index', { title: 'Express' });
});

router.get('/api/locations', db.getAllLocations);
router.get('/api/location/:id', db.getLocation);
router.post('/api/location', db.createLocation);
router.put('/api/location/:id', db.updateLocation);
router.delete('/api/location/:id', db.removeLocation);

module.exports = router;

app.js

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', indexRouter);
app.use('/users', usersRouter);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

module.exports = app;

我想知道上面的代码是好的还是坏的做法或任何潜在的失败？

Answer 1

虽然我会实现您自己的 ORM 和模型层，但大多数代码对我来说都有意义，因此您可以删除一些 PSQL 查询代码并遵循 MVC 设计模式。如果您要构建的只是一个 express api 服务器，那么您不需要 View 部分。

我通常有一个名为 ORM 的文件，其中包含类似以下内容:

var orm = {
  all: function(tableInput, cb) {
    var queryString = "SELECT * FROM " + tableInput + ";";
    connection.query(queryString, function(err, result) {
      if (err) {
        throw err;
      }
      cb(result);
    });
  },
  create: function(table, cols, vals, cb) {
    var queryString = "INSERT INTO " + table;

    queryString += " (";
    queryString += cols.toString();
    queryString += ") ";
    queryString += "VALUES (";
    queryString += printQuestionMarks(vals.length);
    queryString += ") ";

    console.log(queryString);

    connection.query(queryString, vals, function(err, result) {
      if (err) {
        throw err;
      }

      cb(result);
    });
  },
  // An example of objColVals would be {name: panther, sleepy: true}
  update: function(table, objColVals, condition, cb) {
    var queryString = "UPDATE " + table;

    queryString += " SET ";
    queryString += objToSql(objColVals);
    queryString += " WHERE ";
    queryString += condition;

    console.log(queryString);
    connection.query(queryString, function(err, result) {
      if (err) {
        throw err;
      }

      cb(result);
    });
  }
};

// Export the orm object for the model (cat.js).
module.exports = orm;

然后我为您在 psql 中的每个表定义一个模型文件，如下所示:

// Import the ORM to create functions that will interact with the database.
var orm = require("../config/orm.js");

var cat = {
  all: function(cb) {
    orm.all("cats", function(res) {
      cb(res);
    });
  },
  // The variables cols and vals are arrays.
  create: function(cols, vals, cb) {
    orm.create("cats", cols, vals, function(res) {
      cb(res);
    });
  },
  update: function(objColVals, condition, cb) {
    orm.update("cats", objColVals, condition, function(res) {
      cb(res);
    });
  }
};

// Export the database functions for the controller (catsController.js).
module.exports = cat;

Controller :

var express = require("express");

var router = express.Router();

// Import the model (cat.js) to use its database functions.
var cat = require("../models/cat.js");

// Create all our routes and set up logic within those routes where required.
router.get("/", function(req, res) {
  cat.all(function(data) {
    var hbsObject = {
      cats: data
    };
    console.log(hbsObject);
    res.render("index", hbsObject);
  });
});

router.post("/api/cats", function(req, res) {
  cat.create(["name", "sleepy"], [req.body.name, req.body.sleepy], function(result) {
    // Send back the ID of the new quote
    res.json({ id: result.insertId });
  });
});

router.put("/api/cats/:id", function(req, res) {
  var condition = "id = " + req.params.id;

  console.log("condition", condition);

  cat.update(
    {
      sleepy: req.body.sleepy
    },
    condition,
    function(result) {
      if (result.changedRows === 0) {
        // If no rows were changed, then the ID must not exist, so 404
        return res.status(404).end();
      }
      res.status(200).end();

    }
  );
});

// Export routes for server.js to use.
module.exports = router;

这遵循 MVC 设计模式，非常容易阅读和理解。所以我的整个文件夹结构看起来像这样: