In this article I show how to configure SQLAlchemy in a Flask backend. I also show how to create entities which reflect some tables in a database. And show how some relationships are managed with SQLAlchemy as one-to-one, one-to-many and many-to-many.
Content
- Flask-SQLAlchemy
- Database Entities
- One-to-many
- One-to-one
- Many-to-many
- Initialise the Database
- Querying the Database
More details can be found in this video.
All the code is available in the following repository.
Flask-SQLAlchemy
The first thing to do is adding the dependency with Poetry. As I am in a Flask application, I will use the Flask-SQLAlchemy library, which will fit perfectly in my Flask application.
> poetry add Flask-SQLAlchemy
Now what I need to do is instantiate the SQLAlchemy connector, load it into my Flask application and have the URI already inside my Flask application.
db = SQLAlchemy()
app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://sergio:my-password@localhost:5432/backenddb"
db.app = app
db.init_app(app)
If I start my application now, I will have it successfully connected to the database. Nevertheless, the database is empty and I have no models to map with. Let’s create some entities which will reflect the tables.
Database Entities
I will start by the country table which will contain only its ID, a code and a name.
class Country(db.Model):
__tablename__ = country_table_name
id = db.Column(db.Integer, primary_key=True)
code = db.Column(db.String(2), unique=True, nullable=False)
name = db.Column(db.String(50), unique=True, nullable=False)
I prefer to specify the table name by myself instead of letting SQLAlchemy infer it with the model name. Because now I will create the user model. But the “user” table is an already existing table for the connection in the database.
One-to-many
class User(db.Model):
__tablename__ = user_table_name
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False)
password = db.Column(db.Text, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
country_id = db.Column(db.Integer, db.ForeignKey(Country.id))
country = relationship(Country.__name__)
Here, I’ve specified the one-to-many relationship. I specify only the field in the user’s model because I don’t want the list in the country model. I have the complete object Country inside the user’s model.
Let’s create a new class, the messages, where I will have the entities both in the parent and in the children.
class Message(db.Model):
__tablename__ = message_table_name
id = db.Column(db.Integer, primary_key=True)
content = db.Column(db.Text, nullable=False)
created = db.Column(db.DateTime(timezone=True), default=db.func.now())
user_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=False)
user = relationship(User.__name__, backref="messages", cascade="all")
I have the User object in the Message model. And with the back_ref property, I indicate that a new field, messages, will be created inside the User model automatically. I don’t need to specify it. I also have the created field which reflect the creation date of the item. It will be filled automatically with the default function.
Another example. What if i want to declare my field, the entity of the relationship, in both models? To see that, let’s create a one-to-one relationship with a Profile model.
One-to-one
class User(db.Model):
...
profile = relationship("Profile", uselist=False, back_populates="user")
class Profile(db.Model):
__tablename__ = profile_table_name
id = db.Column(db.Integer, primary_key=True)
birth_date = db.Column(db.DateTime)
job = db.Column(db.String(100))
user_id = db.Column(db.Integer, db.ForeignKey(User.id))
user = relationship(User.__name__, uselist=False, back_populates="profile")
I’ve added the profile field in the User’s model and the users field in the Profile model. This time I’ve used the property back_populate instead of back_ref. back_populate injects the value in an existing field. On the other side, back_ref creates a new field with the given name. This time, I want a one-to-one relationship, I’ve used uselist to false to avoid SQLAlchemy creating a list of elements but only a single element.
And now, the last relationship: the many-to-many. I will create a Group model and each user can belong to multiple groups.
Many-to-many
users_to_groups_assocation = db.Table(
"users_to_groups_assocation",
db.Column("user_id", db.Integer, db.ForeignKey(User.id), primary_key=True),
db.Column("group_id", db.Integer, db.ForeignKey(f"{group_table_name}.id"), primary_key=True)
)
class Group(db.Model):
__tablename__ = group_table_name
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20), unique=True, nullable=False)
users = relationship(User.__name__, secondary=users_to_groups_assocation, backref="groups")
For the many-to-many relationships, I need an intermediary table. And in the users field, I specify the secondary table to use. And the back_ref to create a field in the User’s model automatically.
Initialise the database
Now that I have the models created, I need the associated tables to be created in the database. This should be done manually or with a database migration tool. Nevertheless, I will use SQLAlchemy to create the tables upon the model’s definitions. This approach must only be done in local or for unit tests.
db.create_all()
Querying the database
And now, let’s edit my endpoints to read from the database and return the existing data. I have the old style where i can use the models, the entities, to create the queries like that.
groups = Group.query.all()
But I also have the new style where I create more complex queries.
db.session.scalars(select(Group)).all()
This new style allows me to create dynamically the SQL query, the SQL statements, from the model object. Instead of executing the statement, I call scalars to return me directly the result instead of the rows metadata. Let’s see another example to insert a new users in both styles.
u = User()
u.username = d["username"]
u.email = d["email"]
u.password = generate_password_hash(d["password"])
db.session.add(u)
db.session.commit()
And with the new style.
db.session.execute(
insert(User).values(
username=d["username"],
email=d["email"],
password=generate_password_hash(d["password"])
))
db.session.commit()
This time, I can use execute as i don’t care about the returned value. And when all the operations are done, commit the transaction before leaving the endpoint to persist the changes in the database. One last example: filtering the data.
u = User.query.filter(User.id == user_id).one()
And with the new style.
u = db.session.scalars(select(User).where(User.id == user_id)).one()
Conclusion
- I’ve added the Flask-SQLAlchemy dependency
- I’ve instantiated the SQLAlchemy connector
- I’ve added the database URI to the Flask application
- And finally, I’ve loaded the connector inside flask. Then the models can be created with all the column properties as you want, as done directly in the database.
The Dev World - Sergio Lema 
Leave a comment