Database Constraints vs. Model Validations

When constructing a full-stack application, it’s incredibly important to validate data before it gets entered into your database. Invalid data in a database has the potential to cause a slew of problems when that data needs to be used and/or accessed.

In order to prevent invalid data from being entered into a database, Flask-SQLAlchemy offers constraints and validations as ways in which to validate data in applications / deal with invalid data.

Constraints
SQLAlchemy constraints allow for inputs to be controlled at a database level. To do so, they exist within models.

Constraints that can be added directly to the column as it’s defined:

• Unique (unique=True): This constraint can be added when database values need to be unique: name = db.Column(db.String, primary_key=True, unique=True)
• Nullable (nullable=False): This constraint can be added when an input value is mandatory: name = db.Column(db.String, primary_key=True, nullable=False)

In the example below, pet instances must include a name, and that name must be unique (i.e. does not already exist in the database’s “name” column).

class Pet(db.Model):
    __tablename__ = pets
    name = db.Column(db.String, primary_key=True, nullable=False, unique=True)
    age = db.Column(db.Integer)
    animal_type = db.Column(db.String)  
    owner_email = db.Column(db.String) 

Constraints that can be created for columns and/or tables:

• CheckConstraints: Can use any valid SQL and numeric comparisons to validate input.

In the example below, pet instances include an age, and that age must be greater than 2.

class Pet(db.Model):
    __tablename__ = pets
    name = db.Column(db.String, primary_key=True)
    age = db.Column(db.Integer, db.CheckConstraint('age > 2'))
    animal_type = db.Column(db.String) 
    owner_email = db.Column(db.String)

Validations
SQLAlchemy also offers validations: special method calls that go at the top of class definitions and prevent new instances of that class from being saved to the database if their data isn’t what is expected. While both constraints and validations seek to prevent invalid data from entering a database, constraints control input at a database level whereas validations control input at a Python ORM level.

In the example below, pet instances include an owner email, and that email address must contain a “@”, indicating that it is a valid email address.

class Pet(db.Model):
    __tablename__ = pets
    name = db.Column(db.String, primary_key=True)
    age = db.Column(db.Integer)
    animal_type = db.Column(db.String)
    owner_email = db.Column(db.String) 

@validates('owner_email')
   def validate_owner_email(self, key, owner_email):
       if '@' not in owner_email:
            raise ValueError("Failed email validation")
       return owner_email

Comparing Database Constraints and Model Validations
Constraints and validations have the same goal: To make sure data entering the database is valid. While they have a shared goal, it is important to understand how their functionality differs. Database constraints will always be checked when adding or updating data in the database. On the other hand, SQLAlchemy validations will only be checked when adding or updating data through the SQLAlchemy ORM. If data is added or changed some other way (i.e. SQL code via the command line), these modifications will not pass through model validations.

Conclusion
While constraints and validations seek to achieve the same goal, constraints come with an added level of security. To ensure invalid data doesn’t slip through the cracks, some folks write database constraints so that they mirror the model validations they’ve written. TLDR: If there is any chance that data can be added or updated via a method other than through the SQLAlchemy ORM, using database constraints is your best bet.

Resources
"SQLAlchemy Constraints." Canvas - Flatiron School, Accessed 31 May 2023.
"Flask-SQLAlchemy Validations." Canvas - Flatiron School, Accessed 31 May 2023.
coderSloth. (2020, December 22). Database Constraints, But Why? https://codersloth.medium.com/database-constraints-but-why-c283761ec7c6
Long, N. (2017, February). Guaranteed Consistency: The Case for Database Constraints. Big Nerd Ranch. https://bignerdranch.com/blog/guaranteed-consistency-the-case-for-database-constraints/
Defining Constraints and Indexes. (n.d.). SQLAlchemy. Retrieved May 31, 2023, from https://docs.sqlalchemy.org/en/20/core/constraints.html