Knex.js整数值错误及Express中Knex多查询Promise问题求助

Hey there, let's break down your problems step by step!

First off, that error tells you exactly what's wrong: you're trying to assign a JavaScript object to the user_id column, which expects an integer.

Looking at your code snippet, the issue is almost certainly in the follow-up users table query. Here's what's likely happening:

• When you query for the user with knex('users').where({ google_id: profile_id }), you're getting back a user object (or array of objects) instead of just the id value.
• Later, when you try to link this user to the new unit (probably in a junction table like user_units), you're passing the entire user object to user_id instead of extracting user.id.

Quick Fix Example #

Adjust your chain to extract the user's ID properly:

this.addUnit = function(unit_prefixV, unit_nameV, unit_descriptionV, profile_id) {
  return knex.insert({
    'unit_prefix': unit_prefixV,
    'unit_name': unit_nameV,
    'unit_description': unit_descriptionV
  }).into('units')
  .then(function(unitIds) {
    const newUnitId = unitIds[0]; // Knex returns inserted IDs as an array
    // Use .first() to get a single user object instead of an array
    return knex('users').where({ 'google_id': profile_id }).first()
      .then(function(user) {
        // Now pass the integer user.id, not the whole user object
        return knex('user_units').insert({
          user_id: user.id,
          unit_id: newUnitId
        });
      });
  })
  .catch(err => {
    console.error('Query error:', err);
    throw err;
  });
};

Also double-check that profile_id itself isn't an object—if it's coming from a request (like req.body.profile_id), make sure it's parsed as a string/integer, not a nested object.

Since you're learning Promises and multi-query workflows, here are three common, practical approaches:

Approach 1: Chained Promises (Great for Learning Promise Flow) #

Your existing code uses this pattern—each .then() returns the next Promise, ensuring queries run in sequence:

this.addUnit = function(unit_prefixV, unit_nameV, unit_descriptionV, profile_id) {
  // Step 1: Insert new unit
  return knex.insert({
    unit_prefix: unit_prefixV,
    unit_name: unit_nameV,
    unit_description: unit_descriptionV
  }).into('units')
  // Step 2: Fetch the associated user
  .then(unitIds => knex('users').where({ google_id: profile_id }).first())
  // Step 3: Link user to unit (adjust table/columns to match your schema)
  .then(user => knex('user_units').insert({ user_id: user.id, unit_id: unitIds[0] }))
  // Handle any errors across the entire chain
  .catch(err => {
    console.error('Multi-query failed:', err);
    throw err;
  });
};

Key tips:

• Knex's insert() returns an array of inserted IDs (hence unitIds[0]).
• Use .first() to get a single record instead of an array when querying for one user.

Approach 2: Async/Await (Cleaner, More Readable) #

Once you grasp Promises, async/await simplifies the code drastically by eliminating .then() chains:

this.addUnit = async function(unit_prefixV, unit_nameV, unit_descriptionV, profile_id) {
  try {
    // Insert unit and get its ID
    const [newUnitId] = await knex.insert({
      unit_prefix: unit_prefixV,
      unit_name: unit_nameV,
      unit_description: unit_descriptionV
    }).into('units');

    // Fetch the user (fail early if user doesn't exist)
    const user = await knex('users').where({ google_id: profile_id }).first();
    if (!user) throw new Error(`No user found with google_id: ${profile_id}`);

    // Link user to unit
    await knex('user_units').insert({ user_id: user.id, unit_id: newUnitId });

    // Return a meaningful result to the caller
    return { unitId: newUnitId, userId: user.id };
  } catch (err) {
    console.error('Error adding unit:', err);
    throw err; // Let the caller handle the error if needed
  }
};

Approach 3: Transactions (For Atomic Operations) #

If your multi-queries need to either all succeed or all fail (to avoid partial data), use Knex transactions:

this.addUnit = async function(unit_prefixV, unit_nameV, unit_descriptionV, profile_id) {
  // Wrap all queries in a transaction
  return knex.transaction(async (trx) => {
    const [newUnitId] = await trx.insert({
      unit_prefix: unit_prefixV,
      unit_name: unit_nameV,
      unit_description: unit_descriptionV
    }).into('units');

    const user = await trx('users').where({ google_id: profile_id }).first();
    if (!user) throw new Error('User not found');

    await trx('user_units').insert({ user_id: user.id, unit_id: newUnitId });

    return { unitId: newUnitId, userId: user.id };
  });
};

If any step fails, Knex automatically rolls back all previous changes in the transaction—critical for maintaining data consistency.

内容的提问来源于stack exchange，提问作者Matt Kent