SQLAlchemy— basic validations and constraints in models

Constraints

Constraints can be added directly to the column when it is defined in the column constructor.

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import UniqueConstraint
db = SQLAlchemy()

class Customer(db.Model):
    __tablename__ = 'customer'

    #UniqueConstraint table level contraint. Check that address column is unique
    __table_args__ = (db.UniqueConstraint ( 'address', name='uix_1'),)

    id = db.Column(db.Integer, primary_key=True)
    # column level constraint. Checks that email column is unique
    email = db.Column(db.String, unique = True)
    # column level constraint. Checks that name column is NOT nullable
    name = db.Column(db.String, nullable = False)
    phone = db.Column(db.Integer)
    address = db.Column(db.String)
    

unique (column constraint)

True — tells SQLAlchemy that the values in this column must be unique

False — is the default value

Unique constraints can also be specified using the UniqueConstraint table-level construct. It can be used to check uniqueness for a set of fields, for example: if you wanted to make sure an address-phone pair could only exist once, so if a user tried to sign up again with the same phone and address but a different email. https://www.theparsedweb.com/user-authentication-with-flask-and-react/

• in the example above there is a column-level constraint on the email column and a table-level constraint on the address column

Here is an example of a terminal error message when trying to create a new customer instance with a duplicate email (not unique)

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: authors.email

nullable (column constraint)

True — is the default value (except for a primary key column)

False — tells SQLAlchemy that this column cannot be Null

• is implied for a primary key column, but should be specified elsewhere
• explicitly declaring nullable columns also communicates intent to other programmers: “I intended for this field to be nullable (since all other non-nullable fields state so)’ vs ‘I may or may not have intended for this field to allow Null values ¯\_(ツ)_/¯ (since I am not in the practice of controlling for this constraint-)
• NOTE — that Null and an empty value (like an empty string ‘’) are not the same thing in Python. Thus in an integrated system, if a frontend application is passing an empty value to the database, the nullable constraint will not be triggered. This is a use case for also using validations, both in the backend (as with the validator below) and in the frontend.

Here is an example of a terminal error message when trying to create a new customer instance without a name (null):

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: authors.name

Validators

The validates() decorators can be added to methods in the model class definitions, which can prevent the data from being saved to the database if invalid.

‘An attribute validator can raise an exception, halting the process of mutating the attribute’s value, or can change the given value into something different.’

When integrating an SQLAlchemy app with a frontend (e.g. javascript), you can then capture these validations on the frontend and handle them accordingly.

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import validates
from sqlalchemy import UniqueConstraint

db = SQLAlchemy()

class Authors(db.Model):
    __tablename__ = 'authors'

    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String, unique = True)
    name = db.Column(db.String, nullable = False)
    phone = db.Column(db.Integer)
    address = db.Column(db.String)
    
    __table_args__ = (UniqueConstraint ('name', 'address', name='uix_1'),)

    #validates that the phone is 10 character long
    @validates('phone')
    def validate_phone(self, key, value):
        if not len(str(value)) ==10 :
            raise ValueError("Phone number must be 10 characters long")
        return value

    @validates('email')
    def validate_email(self, key, value):
        #validates that an email address is provided
        if not value:
            raise AssertionError('No email provided')
        #validates that the email provided has an '@' 
        if '@' not in value:
            raise AssertionError('Provided email is not an email address') 
        return value

    #validates that name and address are at least 5 chars long
    @validates('name','address')
    def validate_strings(self, key, value):
        if not len(value) >=5 :
            raise ValueError(f'{key} must be at least 5 characters long')
        return value

Continuing with our example, this validator is checking that the value provided for the phone field is 10 characters long. If not, a value error will be raised with the text provided:

ValueError: Phone number must be 10 characters long

And it is checking that the email value is present AND contains the ‘@’ character.

AssertionError: No email provided
AssertionError: Provided email is not an email address

Finally, a single validator can be used to validate more than one field by passing multiple column names to the decorator. The key can be used to interpolate the name into the returned message. for example, for the validate-strings method, the validator checks both the name and address fields, and the same method could return the following messages (interpolating the field name as key):

ValueError: name must be at least 5 characters long
ValueError: address must be at least 5 characters long

Sources:

1. https://docs.sqlalchemy.org/en/20/core/constraints.html
2. https://docs.sqlalchemy.org/en/20/core/metadata.html
3. https://docs.sqlalchemy.org/en/20/orm/mapped_attributes.html
4. https://www.w3schools.com/python/ref_keyword_none.asp