for more of our great content, visit the Coding Duck Blog at: ccstechme.com/coding-duck-blog

SQLAlchemy is the go-to ORM (Object Relational Model) system for working with databases in Python. An ORM is basically a middle-man between Python and SQL, allowing Python developers to create database tables as objects that can be manipulated directly from Python.

This example will be using the Flask framework's flavor of SQLAlchemy and assumes that you have a basic Flask setup in place. Create a new file and name it models.py:

from flask_sqlalchemy import SQLAlchemy
from werkzeug.security import generate_password_hash, check_password_hash

db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(120), index=True, unique=True)
    phone_number = db.Column(db.String(16), index=True)
    password_hash = db.Column(db.String(128))

Here we're importing the SQLAlchemy class to handle database interactions. From there, we define a user class (table) that has id, email, and phone_number fields. As is typical, the id is the primary_key for use with any Foreign Key relationships later on. Email is a String type with a maximum size of 120 characters, and phone_number is also a string with a 16 character limit (in case you plan to process international numbers). The final field is the password_hash, which will be a 128 character hash.

The great thing about SQLAlchemy tables is that they are also objects and therefore, can have other attributes and functions attached to them that are exclusive to that table. In this case, we're going to add two functions for use in creating a password hash using werkzueg (another excellent library included with Flask) and checking a password input against that hash.

Below the User table information, let's add our functions. Be sure to keep these functions within the User class so that only the User database table will have access to generating and checking hashes. Again, SQLAlchemy tables are actually objects as far as python is concerned, so it will treat all attributes and functions within that class as it would any other class. SQLAlchemy will do the necessary translation to SQL and store it in the corresponding DB table.

class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(120), index=True, unique=True)
    phone_number = db.Column(db.String(16), index=True)
    company_name = db.Column(db.String(128), index=True)
    first_name = db.Column(db.String(32), index=True)
    last_name = db.Column(db.String(32), index=True)
    password_hash = db.Column(db.String(128))

    #below our user model, we will create our hashing functions

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

It's that easy! Now, we can go to our Python terminal and type the following:

from app import db
from app.models import user

u = User(email="test@examplesite.com", phone_number="1111111111")

This process creates a new User object and assigns it to the variable u, essentially creating a new entry in the User table. The new user's email is "test@examplesite.com" and their phone number is "1111111111". I'm guessing they will be fairly difficult to reach at that number, so I would try the email if you need to get in touch 😄.

But they don't have a password hash! you say? No. they do not. That is because if we assign a password directly to the database entry, then create a hash of it after, the database will have seen the user's password and stored it before it was hashed. This would be a big security risk. Ultimately, the less the DB knows about Auth information, the better. We don't want to give it any information that can be used maliciously or have value if stolen if we can help it. Luckily, we created the password hash generators in the User class:

u.set_password('P@ssw0rd')
db.session.add(u)
db.session.commit()

See how simple that is? Essentially, set_password is a function that is inherited by any User object. This takes the characters called with the function and makes a 128-character hash of them without ever storing the password in the database. We then tell SQLAlchemy to add the new user to the table and commit the changes. From here, we can load this user object and check if the password was saved:

u = User.query.filter_by(email="test@examplesite.com").first()
u.check_password('P@ssw0rd')
True
u.check_password('notherightpassword')
False

In the closing, back-end development involves a lot of work with the database and system architecture. To be proficient at it, we must learn how to speak fluently to the database. Would performing this same task with raw SQL be faster? Almost certainly. But that is the choice of the developer to make and depends entirely on the needs of the project. Are database calls going to be coming in by the tens, hundres, thousands, or greater? How quickly do you want to develop this product and ship the MVP? What kind of other overhead do you have? And are there "bigger fish to fry" than porting your database interactions to raw SQL? Considering these factors will help you decide if an ORM is for you. And if you decide it is, now you know how to perform password hashing and basic table setup!

Thank you so much for reading! We are all in this together to build the best projects we can. This community we have built together is a beautiful thing because it allows experts to freely exchange ideas, theories, and working projects that prove a better mouse trap can always be built. Few other industries shun the greedy protection of their breakthroughs like software development. It is really rare to see so many people so excited to tell their brilliant, sometimes potentially lucrative ideas with so many other developers. And even rarer still that those other devs are more intersted in helping build this product, than stealing it for themselves. The world needs us. Needs you. So get building and happy coding!