Restful Api Express Postgres数据库 [英] Restful Api express postgres database

问题描述

我正在开发带有node和exrpess的其余完整api，我的数据库是postgresql，我需要使用postgres软件包pg-promise。

I´m developing a rest full api with node and exrpess, my database is postgresql, I need to use the postgres package pg-promise.

我知道我需要将我的应用程序与app.js文件中的数据库连接，但是我的问题是，我应该如何在端点中使用此连接。

I know that I need to connect my app with the database in the app.js file, but my question is, How I should use this connection in my endpoints.

我有路由和我正在使用控制器。

I have routes and I am using controllers.

例如

app.js

//in this file, suppously I have to to the connection 
const db = pgp('postgres://john:pass123@localhost:5432/products');

app.use('/products', productsRoute);

products.js（路线）

products.js (route)

 router.get('/', ProductsController.get_all_products);

products.js（控制器）

products.js (controller)

    exports.get_all_products = (req, res, next ) => {
        // Here i want to use de database connection to do the query to find all
        //products in the database
    }

我如何访问连接以执行类似的操作

How do I get access to the connection to do something like

db.any('SELECT * FROM products WHERE active = $1', [true])
    .then(function(data) {
        // success;
    })
    .catch(function(error) {
        // error;
    });

来自控制器。

更新

好，我现在使用的是node-prostgres，pg。我看到了更好，谢谢大家的建议。

Ok, I´m using now node-prostgres, pg. I saw is better, Thanks for the advice people.

我想创建一个time db实例，并在任何地方调用它，特别是在控制器中

I want to create one time de db instance, and call it anywhere, in specific in the controllers

我可以使用app.local保存我的客户端吗？连接，执行查询然后关闭它。

Could I use app.local to save my client?, connect, do a query and then close it. Do this anywhere

推荐答案

我还没有使用 pg-promise 。

如果有帮助，您可以使用 PostgreSQL客户端用于Node.js 。您还可以将 async / await 与它一起使用。

If it helps, you can use PostgreSQL client for Node.js. You can also use async/await with it.

代替路由器，可以使用Express中间件

Instead of a router, you can use Express middle-ware straightaway as follows.

//app.js:

const express = require('express')
const bodyParser = require('body-parser')
const app = express()
const port = 1234

const db = require('./dbconnector')

//...omitted for brevity`
// 'db' is exported from a file such as 
// dbconnector.js.
app.get('/products', db.getProducts) 


//In dbconnector.js:
const Pool = require('pg').Pool
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'mydb',
  password: 'mypwd',
  port: 5432,
})

const getProducts = (request, response) => {
    pool.query('SELECT * FROM products ORDER BY id 
ASC', (error, results) => {
      if (error) {
        throw error
      }
      response.status(200).json(results.rows)
    })
  }

// ...omitted for brevity

module.exports = {
 getProducts 

}

对于模块化设计，请使用单独的文件（而不是 app.js / index.js / server.js ）作为数据库连接，这是最佳做法，而要求在您的主 app.js 中。

For modular design, please use a separate file (not app.js/index.js/server.js) for db connections as best practice and require that in your main app.js.

这里是 pg 模块上的href = https://node-postgres.com/features/connecting rel = nofollow noreferrer>帮助。

Here is help on pg module.

这篇关于Restful Api Express Postgres数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！