Controlling Data with Flask-SQLAlchemy: Validations vs Constraints

Why this topic?

In the world of coding, one of the many core skills of a programmer consists of abilities in managing databases. In a previous post about data structures with Python, it was discussed the importance of one's understanding of code and how to correctly organize, manage, and store code to properly create the building blocks of software systems. To reiterate, database management refers to the actions one takes to manipulate and control data to meet necessary conditions throughout the entire data lifecycle. Database management requires one to have strong skills with data structures and algorithims. Understanding how to use this knowledge is critical, because it is what can lead to building software that is efficient and optimized.

When creating databases, validating the data that goes in is a critical step in ensuring a smooth workflow. Without validators or constraints for the data that is being taken in, especially at the beginning of the process, it can create inconsistences and may influence the final results making them inaccurate. In order to prevent inaccuracies, one must check quality of the data before it is processed. Validating data is essential in creating data that is consistent, accurate and complete, preventing loss of data and errors. Data validation can be easily overlooked but it is good practice to constantly check oneself with what data exactly is being inputted. Flask-SQLAlchemy offers two ways, constraints and validations, to validate the data that is being taken into the database.

Constraints

SQL are used to specify rules for the data in a table. In other words, limit the type of data that can go into a table. If there is any violation between the constraint and the data action, the action gets aborted and the data does not go into the table. There are multiple constraints in SQL and each have their own special rules.

NULLABLE

By default, a column in a table can hold null values. The constraint NULLABLE enforces a column to NOT accept NULL values if set to false. It ensures that the field will always have a value, which means no change can be made with this field unless a value has been added.

NULLABLE is useful for creating data for objects that cannot exist without having a value in their fields. An example could be an object with a name attached.

class Person(db.Model):
    __tablename__ = 'persons'

    id = db.Column(db.Integer, primary_key=true)
    first_name = db.Column(db.Integer, nullable=false)
    last_name = db.Column(db.Integer, nullable=false)

In the example above, an instance of Person must always have a value in the fields of first_name and last_name. This is because nullable is set to false, which means that when creating a person, it cannot exist unless there is a first name and a last name value. Logically speaking, a person could not (or at least should not) exist unless the person has a first and last name. NULLABLE is a good way to ensure that in creating instances, that data is not lost or incomplete.

UNIQUE

When inputting data into databases, values can be often repeated when creating multiple instances of a class. However, with the UNIQUE constraint, it ensures that all values in a column are different or rather reflective of the constraint's name, unique.

UNIQUE constraints can be useful when creating data that has an attribute that seperates it from the rest of the instances. UNIQUE constraints can come in the form of names, or ID numbers.

class Mountain(db.Model)
    __tablename__ == 'mountains'

    id = db.Column(db.Integer, primary_key=true)
    name = db.Column(db.String, unique=True)
    country_of_origin = db.Column(db.String)

In the example above, an instance of Mountain will have the fields of id, name, and country of origin. However, when creating an instance of Mountain, unique is set to true in the field of name. What that means is that in every instance of Mountain, name must be unique from each other and that no instance of Mountain can have name be repeated (the same for id). This logically makes sense because globally known mountains would have unique names that seperate it's identity, outside of it's id (or identifcation number) from each other. Country of origin does not have a unique constraint because multiple mountains can have the same country of origin. UNIQUE is a good constraint to make sure that certain fields do not contain the same data or have its data repeated.

DEFAULT

There may be circumstances when inputting data, that certain fields may not have a specified value therefore would need a default in case. The DEFAULT constraint would be used to set a default for a value in a column.

The DEFAULT constraint can be used in scenarios where fields of data can be expected to have a certain value unless specified otherwise.

class Worker(db.Model)
    __tablename__ = 'workers'

    id = db.Column(db.Integer, primary_key=true)
    name = db.Column(db.String)
    salary = db.Column(db.Integer, default = 50000)

In the example above, an instance of Worker would have the fields of id, name, and salary. Salary has a DEFAULT constraint in which the value would always be an integer of 50,000 unless there is an input stating otherwise. Logically, this would be okay because this is assuming that every entry level worker would have the same base salary coming into a company. The DEFAULT constraint can help avoid repetition of inputs when knowing a field would in most case scenarios have a repeating value.

Validators

Validators is an alternate way of checking the integrity and security of the data that is being input server-side. SQLAlchemy has a built in helped method for using validations, validates().
Validates() is set up as function, that can check data being input through fields via if statements.

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String)
    password = db.Column(db.String)

@validates('username')
def validate_username(self, key, value):
    if not value:
        raise ValueError("Must have a username")
    return value

In the example above, a class of User has an id , username and password attached to it. Here @validates is checking the value of username, hence why the function is labeled as validate_username. The if statement reads that if there no value in the field of username, then a error message will pop up alerting the user on the server-side that the field of username must have a value. In the event that there is a value for username, then the instance of User will return the value that was input for username. Validators are a valuable method of checking if whether or not data is being input at all, and if so, does it meet the conditions to best fit the field.

In Conclusion

Having been introduced to validators and constraints, try to manipulate the data being input to your database to your liking and properly match up the data up to what the fields are expected to have. A developer must always check the integrity and security of the data of not just from the front-end but from the back-end as well. Inconsistencies can be the downfall of a database, and the workflow can be greatly hindered when not being able to managae data properly. Thus, one must always be in control of their data inputs so that the workflow of a project can be clean, concise and accurately produce the results that one would hope to achieve. By strengthing the handle over your databases, the stronger of handle you have over your code and projects. By staying in control, the more power you can demonstrate with your applications and ultimately achieve the purposes you set for your projects!

Resources
https://www.sigmoid.com/blogs/data-validation/#:~:text=Data%20validation%20provides%20accuracy%2C%20cleanness,as%20Excel%20creates%20better%20results.
https://www.w3schools.com/sql/sql_constraints.asp