columnType

Summary

By default, when you define an attribute's type in a Sails model, Waterline automatically maps it to an appropriate database column type. Most of the time, this default mapping works well, but in some cases, you might need more control over the specific database type. This is where the columnType setting comes in. It allows you to explicitly define the exact database type for an attribute.

Using columnType gives you fine-grained control over how your data is stored, but it comes with risks: Sails will pass your specification directly to the database without any validation. Therefore, you should only use it if you are confident in your database knowledge.

Transcript

When you define an attribute's type in a Sails model, Sails automatically infers the corresponding database type. Most of the time, this works fine, but there may be situations where you need to enforce a specific database type for an attribute.

Let’s look at an example. Consider this age attribute in our User model:

age: {
  type: 'number'
}

Since age is a number, Sails will infer a suitable database type. If we inspect the database structure using Beekeeper, we might see that age is stored as float4 (a floating-point number). But what if we want age to be strictly an integer?

To enforce this at the database level, we can use the columnType property:

age: {
  type: 'number',
  columnType: 'int4'
}

This tells Waterline to explicitly use an int4 column in the database. However, since this is a database-level change, we need to run a migration to apply it. If we are in development, we can reset the database with:

sails migrate --drop

After running this, if we check the database schema again, we’ll see that age is now stored as int4 instead of float4.

Now, let's test our validation. If we attempt to create a user with a decimal age:

await User.create({ age: 18.6 }).fetch();

Sails will allow this at the application level, but when the database tries to store the value, it will throw an error because 18.6 is not a valid integer.

To prevent this error before it reaches the database, we should add an additional validation rule in our model:

age: {
  type: 'number',
  columnType: 'int4',
  isInteger: true
}

This ensures that only valid integers are accepted before they even reach the database.

Key Takeaways

• Default Behavior: Sails automatically maps attribute types to database column types.

• When to Use columnType: Use it when you need precise control over the database type.

• Risks: Sails does not validate columnType; it directly passes it to the database.

• Best Practice: Combine columnType with validation rules (isInteger, maxLength, etc.) to ensure data consistency at both the application and database levels.

Nine times out of ten, you won’t need columnType, but if you have a strong SQL background and specific business requirements, it’s a powerful tool.