SailsJS中如何允许主键为负整数？相关报错的解决方案咨询

Hey there, I totally get this frustration—ORMs can have hidden validation rules that don’t always align with your database schema or use case. Since you’ve already confirmed the database allows negative integers and the docs don’t mention this restriction, let’s go through actionable alternatives to avoid rewriting all your related data:

1. Override the Model's Primary Key Validation Rules #

Waterline (Sails’ ORM) likely has a default implicit rule that treats primary keys as non-negative integers, even if your database doesn’t enforce it. You can explicitly define a custom validation for your primary key field to allow negatives:

// api/models/Org.js (adjust for your model name)
module.exports = {
  attributes: {
    id: {
      type: 'number',
      columnType: 'integer', // Match your database column type (e.g., INT instead of UNSIGNED INT)
      custom: function(value) {
        // Allow any integer, positive or negative
        return typeof value === 'number' && Number.isInteger(value);
      },
      required: true,
      unique: true,
      autoIncrement: false // Disable auto-increment if you’re manually setting negative IDs
    },
    // Add your other model fields here...
  }
};

If the negative ID is being used in an association (like the org field in your user model), you might also need to update the association’s validation in the related model:

// api/models/User.js
module.exports = {
  attributes: {
    org: {
      model: 'org',
      custom: function(value) {
        // Allow null or any integer (including negatives) for the org association
        return value === null || (typeof value === 'number' && Number.isInteger(value));
      }
    },
    // Other user fields...
  }
};

Pros: Clean, aligns with Sails’ recommended workflow, applies consistently to all operations using the model.
Cons: Requires updating each model where you need negative primary keys.

2. Bypass Waterline Entirely with Native SQL Queries #

If you need a quick fix or only have a few instances where you need to set a negative ID, you can skip Waterline’s validation by running raw SQL directly. Use Sails’ built-in sendNativeQuery method:

// Example in a controller or service
async function updateUserOrg(req, res) {
  try {
    // Update the user's org_id to -1
    await sails.sendNativeQuery(
      'UPDATE user SET org_id = $1 WHERE id = $2;',
      [-1, 1402] // Parameters: negative ID and user ID
    );
    return res.ok({ message: 'User association updated successfully' });
  } catch (err) {
    return res.serverError(err);
  }
}

Pros: Fast workaround, no model changes needed.
Cons: Bypasses ORM features like lifecycle callbacks, so you’ll lose any automatic logic (e.g., updating timestamps) tied to model operations.

3. Create a Custom Hook to Globally Allow Negative Primary Keys #

If you need this behavior across multiple models, a custom hook can override Waterline’s default validation rules once, instead of updating each model individually:

// api/hooks/allow-negative-pks/index.js
module.exports = function defineAllowNegativePksHook(sails) {
  return {
    initialize: async function() {
      // Wait for the ORM to load before modifying models
      sails.on('hook:orm:loaded', () => {
        // Iterate through all registered models
        Object.values(sails.models).forEach(model => {
          const primaryKey = model.primaryKey;
          const pkAttribute = model.attributes[primaryKey];
          
          // Only modify number-type primary keys
          if (pkAttribute?.type === 'number') {
            // Replace default validation with one that allows any integer
            pkAttribute.custom = (value) => typeof value === 'number' && Number.isInteger(value);
            // Remove any default max restriction that might block negatives
            delete pkAttribute.max;
          }
        });
      });
    }
  };
};

Don’t forget to enable the hook in your config/hooks.js file:

// config/hooks.js
module.exports = {
  'allow-negative-pks': true
};

Pros: Applies globally to all models, one-time setup.
Cons: More advanced, requires understanding Sails hooks; could potentially conflict with other custom validation rules if you have them.

As a side note: This restriction is likely a default safety measure in Waterline to prevent accidental negative IDs (since auto-incrementing keys are almost always positive), but it’s good that you’re pushing back against it for your specific use case.

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