JSON Postman 正文不适用于 PostgreSQL 插入

Question

我的 Node.js 代码有问题。我正在使用 PostgreSQL 来管理我的数据库端。但是当我想插入东西并使用 Postman 进行测试时，我遇到了约束冲突。事实上，我要插入的所有值都是 NULL。

我以为我的 Postman JSON 正文不起作用。但我有点不知道为什么 :/

代码如下:

var express = require('express');
var router = express.Router();
const { Pool, Client } = require('pg');
const bodyParser = require('body-parser');

const client = new Client({
  user: process.env.DB_DBUSER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASS,
  port: process.env.DB_PORT,
})

const pool = new Pool({
  user: process.env.DB_DBUSER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASS,
  port: process.env.DB_PORT,
})

pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err)
  process.exit(-1)
})

//Third-party middelware
router.use(bodyParser.json());
router.use(bodyParser.urlencoded({
  extended: true
}));

router.post('/create_account', function (req, res, next) {

  // Grab data from http request
  const data = {
    username: req.body.username, name: req.body.user,
    firstname: req.body.firstname, email: req.body.email,
    location: req.body.location
  };

  const values = [data.username, data.name,
  data.firstname, data.email, data.location];

  pool.connect((err, client, done) => {
    if (err) throw err
    client.query('INSERT INTO Users(username, name,'
      + 'firstname, email, location) values($1, $2, $3, $4, $5)', values, (err, res) => {
        done();
        if (err) {
          throw err
        }
        console.log('user:', res.rows[0])
        pool.end()
      })
  });
})

module.exports = router;

Oc，在我的 postman 体内，我有这个:

{
    "username" : "Username",
    "name" : "Bob",
    "firstname" : "Paul",
    "email" : "spfqqds@gmail.com",
    "location" : "Here"
}

还有我的依赖:

  "dependencies": {
    "body-parser": "^1.18.3",
    "cookie-parser": "~1.4.3",
    "debug": "~2.6.9",
    "dotenv": "^6.0.0",
    "express": "~4.16.0",
    "http-errors": "~1.6.2",
    "morgan": "~1.9.0",
    "pg": "^6.1.0",
    "pg-hstore": "^2.3.2"
  },
  "devDependencies": {
    "chai": "^4.1.2",
    "mocha": "^5.2.0"
  }

SQL:

const queryUser = client.query(
  'CREATE TABLE IF NOT EXISTS Users('
  + 'user_id SERIAL,'
  + 'username VARCHAR(40) NOT NULL UNIQUE,'
  + 'name VARCHAR(40) NOT NULL,'
  + 'firstname VARCHAR(40) NOT NULL,'
  + 'email VARCHAR(60) UNIQUE,'
  + 'location Varchar(60) NOT NULL,'
  + 'CONSTRAINT pk_user PRIMARY KEY (user_id))',

  (err, res) => {
    if (err) throw err
    console.log("TABLE USERS : " + res)
  });

这是我的错误:

error: une valeur NULL viole la contrainte NOT NULL de la colonne « username »
    at Connection.parseE (C:\Users\Amaris\Desktop\NodeJS_Projects\basefugees-dev-backend\node_modules\pg\lib\connection.js:554:11)
    at Connection.parseMessage (C:\Users\Amaris\Desktop\NodeJS_Projects\basefugees-dev-backend\node_modules\pg\lib\connection.js:381:17)
    at Socket.<anonymous> (C:\Users\Amaris\Desktop\NodeJS_Projects\basefugees-dev-backend\node_modules\pg\lib\connection.js:117:22)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
    at Socket.Readable.push (_stream_readable.js:208:10)
    at TCP.onread (net.js:597:20)

Answer 1

我试过你的代码，稍作修改，它成功了。

这是SQL

CREATE TABLE public.users
(
  username character varying,
  name character varying,
  firstname character varying,
  email character varying,
  location character varying,
  id integer NOT NULL DEFAULT nextval('users_id_seq'::regclass),
  CONSTRAINT users_pkey PRIMARY KEY (id)
)

和 Node.js

const express = require('express');
const app = express();
const router = express.Router();
const {
    Pool,
    Client
} = require('pg');
const bodyParser = require('body-parser');

// const client = new Client({
//   user: process.env.DB_DBUSER,
//   host: process.env.DB_HOST,
//   database: process.env.DB_NAME,
//   password: process.env.DB_PASS,
//   port: process.env.DB_PORT,
// })

const pool = new Pool({
    user: 'postgres',
    host: 'localhost',
    database: 'postgres',
    password: 1,
    port: 5432,
});

pool.on('error', (err, client) => {
    console.error('Unexpected error on idle client', err);
    process.exit(-1)
});

//Third-party middelware
router.use(bodyParser.json());
router.use(bodyParser.urlencoded({
    extended: true
}));

router.post('/create_account', function(req, response, next) {

    // Grab data from http request
    const data = {
        username: req.body.username,
        name: req.body.user,
        firstname: req.body.firstname,
        email: req.body.email,
        location: req.body.location
    };

    const values = [
        data.username,
        data.name,
        data.firstname,
        data.email,
        data.location
    ];

    pool.connect((err, client, done) => {
        if (err)
            throw err;
        client.query('INSERT INTO users(username, name, firstname, email, location) values($1, $2, $3, $4, $5)', values, (err, res) => {
            done();
            if (err) {
                next(err);
                throw err
            }
            response.send('ok');
            console.log('user:', res.rows[0]);
            pool.end()
        })
    });

});
app.use(router);
app.listen(3000, () => {
    console.log('SERVER STARTED ON PORT 3000');
});

最后是我的 postman 电话

希望对您有所帮助!))