使用 Node.js 插入数据

Inserting Data with Node.js

提问by Onder OZCAN

I 'am trying to insert some data with Node.js. I installed mysql support with npm . I just checked arround some source code, I've wrote following code , I can follow sql output in console.log and SQL output is correct. But It does not affect on any rows in mySQL database.

我正在尝试使用 Node.js 插入一些数据。我用 npm 安装了 mysql 支持。我刚刚检查了一些源代码，我写了以下代码，我可以在 console.log 中跟踪 sql 输出并且 SQL 输出是正确的。但它不会影响 mySQL 数据库中的任何行。

Here is my code :

这是我的代码：

var mysql      = require('mysql');

var connection = mysql.createConnection({
  host     : 'cccc.net',
  user     : 'username',
  password : 'password',
});

var post  = {srcUserID: userSrcID, destUserID: msg.userid, messageContent: msg.txt, messageSendDate:sendDate };

connection.query('INSERT INTO messages VALUES ?', post, function(err, result) {

});

采纳答案by SheetJS

You have to select a DB before performing a query. The easiest way is to add it to the object in the createConnection call:

您必须在执行查询之前选择一个数据库。最简单的方法是在 createConnection 调用中将其添加到对象中：

var connection = mysql.createConnection({
  host     : 'cccc.net',
  user     : 'xxxxx_usr',
  password : 'xxxxxxx',
  database : 'database_name'
});

回答by user254153

Its to late but If this can help other.

为时已晚，但如果这可以帮助其他人。

 var post  = {id: 1, title: 'Hello MySQL'};
 var query = connection.query('INSERT INTO posts SET ?', post, function(err, result) {
   // Neat!
 });
 console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'

Notice that they use SET instead of VALUES. INSERT INTO ... SET x = y is a valid MySQL query, while INSET INTO ... VALUES x = y is not.

请注意，他们使用 SET 而不是 VALUES。INSERT INTO ... SET x = y 是一个有效的 MySQL 查询，而 INSET INTO ... VALUES x = y 不是。

回答by Golo Roden

As you've pointed out from your comments, you had no database selected:

正如您在评论中指出的那样，您没有选择数据库：

ER_NO_DB_ERROR: No database selected

Hence, you need to select a database first, and then it works as expected. What you need to add is the databaseproperty to your call to createConnection, so your code should look like the following:

因此，您需要先选择一个数据库，然后它才能按预期工作。您需要添加的是database调用的属性createConnection，因此您的代码应如下所示：

var mysql = require('mysql');

var connection = mysql.createConnection({
  host: 'cccc.net',
  user: 'xxxxx_usr',
  password: 'xxxxxxx',
  database: 'name of your database goes here …'
});

var post  = {
  srcUserID: userSrcID,
  destUserID: msg.userid,
  messageContent: msg.txt,
  messageSendDate:sendDate
};

connection.query('INSERT INTO messages VALUES ?', post, function (err, result) {
  // ...
});

回答by seme

   const pgp = require('pg-promise')()

// const connection = { host: 'localhost', port: 5432, db: 'users' }
const connection = process.env.NODE_ENV === 'test'
  ? 'postgres:///users_test'
  : 'postgres:///users'
const db = pgp(connection) 

const addUser = (userName, jobName) => {
      return db.one(`
            SELECT j.job_id
            FROM jobs AS s
            WHERE j.job_name = `
            , [jobName])
            .then((jobs) => {
        return db.one(`
            INSERT INTO users
            (name, job_id)
            VALUES (, )
            RETURNING user_id`,
          [userName, jobs.job_id])
    })
    }


     addUser('Micheal', 'teacher')
      .then(console.log)
      .catch(console.error)




      const addUserToCompany = (userName, companyName) => {
        let userId
        let companyId
        return db.one(`
          SELECT user_id
          FROM users
          WHERE name=`, [userName])
          .then((user) => {
            userId = user.user_id
            return db.one(`
            SELECT company_id
            FROM companies
            WHERE name=`, [companyName])
          })
          .then((company) => {
            ownerId = company.company_id
            return db.one(`
              INSERT INTO companyUsers
                (user_id, company_id)
              VALUES
                (, )
              RETURNING *`,
              [userId, companyId])
          })
      }
      addUserToCompany('Micheal', 'Code Academy')
        .then(console.log)
        .catch(console.error)



        const updateUserName = (userId, newName) => {
          db.oneOrNone('UPDATE users SET name= WHERE user_id= RETURNING user_id', [newName, userId])
            .then((returnedId) => {
              if (returnedId) return { success: true, message: '' }
              return { success: false, message: `Could not find userId ${userId}` }
            })
            .catch(err => Object({ success: false, message: err.message }))
    }
    updateUserName(17, 'Micheal-Moore')

module.exports = { addUser, addUserToCompany , updateUserName }