From SQL to SQLModel: A Cleaner Way to Work with Databases in Python

SQLModel is a library that lets you interact with databases through Python code with Python objects and type annotations instead of writing direct SQL queries.

Created by the same author of the extremely popular framework FastAPI, it aims to make interacting with SQL DBs in Python easier and elegant, with data validation and IDE support, without the need to learn SQL.

It’s an ORM (Object-Relational Mapper), meaning: it translates between classes/objects and SQL.

In this article, I will cover why you would use SQLModel over plain SQL queries, what benefits it brings to the table and the basics of using it in Python projects.

I’ll assume that you’re comfortable with Python (functions, classes, attributes). I’m not assuming any prior knowledge of SQL though. The article should be approachable to anyone with basic Python experience.

To keep the article manageable, I’ve intentionally left out things like grouping operations in functions, error handling, code execution output and performance optimization. Otherwise, this would span multiple parts.

Why use SQLModel?

The main reason you would want to use SQLModel is to avoid writing SQL in your Python code. Mixing code from another language (especially SQL queries) with your Python code can get messy. It take away from your code clarity and readability, it adds a maintenance cost and it’s not always secure.

Although there are other solutions that allow you to avoid writing SQL in Python, SQLModel comes with additional, very important features for free, most notably: data validation.

Here are the main features included in SQLModel:

Type annotation by default: SQLModel is built on Pydantic. An SQLModel model works exactly the same way a Pydantic model works. You get Pydantic data validation, serialization and documentation.
Built on the most popular DB library in Python: SQLAlchemy. When the simplicity and defaults of SQLModel aren’t enough for your use case, you can use SQLAlchemy directly.
IDE support: code completion/suggestions and inline errors.
easier to test. Writing tests that includes SQLModel code is fairly easy with expected results as compared to writing test for SQL queries.

SQLModel creator says that there was a lot of research and effort dedicated to make SQLModel model both a Pydantic & an SQLAlchemy model.

Another reason might be that you don’t (or don’t want to) know SQL. Which is fine although it will greatly help if you learn the very basics even if you choose to use SQLModel.

How to use SQLModel?

To keep things simple, I will use a user info table as an example throughout the post.
Here is how the ‘user’ table in the DB may look like:

id	name	email	date_joined	is_admin

The SQL-in-my-Python way:

Lets start with the non-SQLModel way of interacting with DBs from Python.
When your Python app needs to save/retrieve/update/delete data from/to an SQL DB, we write SQL queries directly in Python code like this:

First, we connect to the DB (creating it if it doesn’t exist):

import sqlite3
from datetime import datetime
con = sqlite3.connect("user_db.db")

Then, we get a hold of a cursor to be able to execute SQL statements:

cur = con.cursor()

Then, we create the ‘user’ table with the necessary fields:

cur.execute("""CREATE TABLE IF NOT EXISTS user (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL,
            date_joined TEXT,
            is_admin INTEGER DEFAULT 0
            );
            """)

The string passed to .execute() is the SQL statement that we want to to execute on the DB.

Finally, we can perform CRUD operations (create, read, update, delete). Suppose we have this user data:

# id omitted. sqlite will auto create it and autoincrement it.
name = "John Doe"
date_joined = datetime.now()
email = 'john@example.com'
is_admin = False

Using same connection and cursor we’ve just created above, we can execute SQL queries like so:

insert (create):

cur.execute(
    "INSERT INTO user (name, email, date_joined, is_admin) VALUES (?, ?, ?, ?)",
    (name, email, date_joined, int(is_admin)),
)
con.commit()

select (read):

user_id = 1
user1 = cur.execute("SELECT * FROM user WHERE id = ?", (user_id,)).fetchone()
print("User1: ", user1)

update:

user_id = 1
new_name = "Jane Doe"
cur.execute("UPDATE user SET name=? WHERE id = ?", (new_name, user_id))
con.commit()

delete:

user_id = 1
cur.execute("DELETE FROM user WHERE id = ?", (user_id,))
con.commit()

You can already see how this is not the most efficient way and not the cleanest code. It’s a mix of Python and SQL with long strings and placeholders for values. In addition, you need to know at least basic SQL to write these queries.

I’m sure, some developers know and love SQL and prefer to use it directly in their Python code, however, in my opinion, there are some downsides to this approach:

It’s not clean.
less readable.
prone to errors.
harder to maintain.
could open code to SQL injection attacks especially if you use string formatting instead of value placeholders.
no data validation or type annotation (at least not by default).
no IDE/editor support. No completion or suggestions. No inline error warnings. Query strings errors might not be detected by your IDE.
makes writing tests a tedious task. You have to also write SQL queries (maybe complicated ones) in tests especially for edge cases.

Now let’s see what SQLModel has to offer to improve the situation.

The SQLModel way:

SQLModel offers a better, safer, and more elegant way to interact with SQL DBs from Python code. It allows us to work with DB records (rows) as regular Python objects.

Let’s see how we can use it instead.

1- Install SQLModel:

After creating and activating you virtual environment, use your favorite package manager to install SQLModel. Here, I’m using uv:

uv add sqlmodel

2- Create a model for your data:

Before using SQLModel to interact with the DB, we need first to create an SQLModel model. It’s exactly the same as a Pydantic model.
Using our ‘user’ example above, here is how our model would look:

from sqlmodel import SQLModel, Field
from datetime import datetime

class User(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    email: str
    date_joined: datetime = Field(default_factory=datetime.now)
    is_admin: bool = False

Here, in the class User, we’re sub-classing SQLModel from the sqlmodel library and telling it to create a corresponding SQL table in the database if it doesn’t exist, indicated by table=True.

Then we defined the fields of the class just like any other Python class with type annotations (it’s a Pydantic model).

We’re using the special Field function from SQLModel to set arguments for the id and date_joined fields.

The date_joined filed uses a default_factory to store the current timestamp.

The model itself represents a table in the database and each field represents a column in that table.

Now, we can create “user” instances from this SQLModel “User” model. Every instance we create will represent a row in the table.

3- Create DB and Table:

We’re now ready to create the DB, connect to it and create necessary tables.

For this task, we need to create an SQLAlchemy engine (remember, SQLModel uses SQLAlchemy under the hood).

The engine is an object that handles the communication with the DB.

We can create one using create_engine() from SQLModel. First we need to add it to our imports:

from sqlmodel import SQLModel, Field, create_engine

Then, we add the engine creation code below the model class as order here matters:

...
# below the code defining User model
sqlite_file_name = "users_db.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)

SQLModel.metadata.create_all(engine)

Here, we’re using SQLite and setting the DB URL. You can use any DB that is supported by SQLAlchemy.

You would typically load this DB URL from the environment, see our article: How to handle environment variables in Python

Then we’re calling create_engine() passing it the URL and telling it to log what it’s doing to the terminal where we run the Python code with echo=True. This is very handy not only for debugging but also for learning.

Lastly, we call create_all() passing the engine to tell SQLModel to create everything: the DB and the table(s).

The create_all() takes an engine and creates a table for each model that inherits from SQLModel with table=True configured. Our model meets these conditions, so its table gets created.

One important thing to remember is that calling create_all() needs to happen after the code that defines the model class. Otherwise the table will not be created.

You can verify that the DB has indeed been created by simply checking for a new .db file with the same name as sqlite_file_name. If it’s there, you’re good to continue.

4- Create instances of the SQLModel to represent individual rows:

Now that we have our DB and table created, we can start creating instances of our User.

It’s straightforward. Just like instantiating any Python class, we do it like so:

user1 = User(name="ahmed", email="ahmed@example.com", is_admin=True)

NOTE: We don’t need to include the id field when creating or updating records in the DB (you can use it to find the record to update but not update id itself). It will be automatically created by SQLite and auto incremented for us. The same goes for the date_joined field as it uses a default_factory to store the current timestamp automatically when creating a new instance of User.

So far, our user (user1) lives only in memory, we need to persist it to the DB so that we can later retrieve/update/delete it.

Here is where SQLModel really shines. Forget all those messy SQL queries and value placeholders. SQLModel allows us to use Python code to cleanly and elegantly interact with the DB.

5- Create a session and perform DB operations:

The engine we created earlier handles the communication with the DB for the whole program. On top of the engine, we need a session.
A session uses the engine to perform operations on the DB. We need a new session for each group of operations that belong together.

Let’s create a session to add a row in the DB table for the user we just created.

First, we need to add Session to our imports:

from sqlmodel import SQLModel, Field, Session, create_engine

Next, we create the session and tell it to add the User instance to the DB. We’re using a with block to cleanly close the session after each operation even if there was an exception in the block:

...
# below the code that creates the DB & table above:
with Session(engine) as session:
    session.add(user1)

So far, the user instance is only added to the session (in memory) and still not sent to the DB.

This is where we see the benefit of using the session. It holds in memory the objects we need to save to the DB until we’re ready to commit, at which point, we call commit() which will use the engine to save all changes to the DB.

This allows for batch operations, instead of sending changes individually to the DB which may be expensive.

Now we can commit:

    session.commit()

You can use a DB browsing tool (like DB Browser for SQLite) to verify that a row has been created for the user instance.

And here is the complete creation code:

user1 = User(name="ahmed", email="ahmed@example.com", is_admin=True)

with Session(engine) as session:
    session.add(user1)
    session.commit()

To make things easier for us for the rest of the post and to have some data to work with in the DB, we’ll now create multiple users. Use the following code to create 3 more users and add them to the DB:

user2 = User(name="john", email="john@example.com", is_admin=False)
user3 = User(name="bob", email="bob@example.com", is_admin=True)
user4 = User(name="kate", email="kate@example.com", is_admin=False)

with Session(engine) as session:
    session.add(user2)
    session.add(user3)
    session.add(user4)

    session.commit()

How to do CRUD operations with SQLModel?

Now, that we know how to create a session to perform operations on the DB, it’s a matter of knowing how to do each CRUD operation using SQLModel.

Here is a simple table for each operation, SQL command and the corresponding SQLModel function or session method:

CRUD op.	SQL cmnd.	SQLModel fn./mthd.
create	INSERT	.add()
read	SELECT	select()
update	UPDATE	select() & .add()
delete	DELETE	delete() or .delete()

We’ve already seen how to do the first one. We’ll explore the rest next.

Select (read/retrieve) multiple rows from DB:

“Where does SELECT come from?” you may ask. In SQL, we use SELECT to read rows from the DB. An SQL SELECT statement looks like this:

SELECT * FROM user

This means: select all columns (i.e. id, name, email, etc.) from table ‘user’. Not specifying any filtering conditions means: return all rows (records, user instances) you find in the DB.

Similarly, in SQLModel we use its select()function and pass it the name of the model (which represent the DB table) we want to read from.

We can chain filtering and ordering preference to select() but we’re not going to do that just yet. Now, we want all rows from the table.

First we need to add select to our imports:

from sqlmodel import SQLModel, Field, Session, create_engine, select

Then, to read all rows from the ‘user’ table in the DB, we use the following pattern:

with Session(engine) as session:
    statement = select(User)
    results = session.exec(statement)
    users = results.all()
    for user in users:
        print(user)

NOTE: We’re still using the same engine from earlier but with a new session for each set of related DB operations.

Here, we’re storing the query statement in a variable then passing it to exec() to execute it. This will return a results object. We call its .all() method to return all User objects.

We can make the previous code more Pythonic:

with Session(engine) as session:
    users = session.exec(select(User)).all()
    for user in users:
        print(user)

However, for readability, especially if the statement is a long one (like the code block where we chain filtering, ordering and limiting), it may be better to store the statement in a variable first and then pass it to the exec() function.

Filtering, ordering and limiting the results:

As mentioned earlier, we can optionally chain filtering, ordering preference and/or limiting to select() using the methods .where(), .order_by() and .limit() respectively. We can use all of them, or mix and mach to suit our needs.

For example, we can select only the users that are admins:

with Session(engine) as session:
    statement = select(User).where(User.is_admin)
    users = session.exec(statement).all()
    for user in users:
        print(user)

One important thing to remember is that you want to pass a Python expression to .where() like:

select(User).where(User.name=="ahmed")

not a keyword argument like:

select(User).where(name="ahmed")

With the latter, you won’t get IDE auto-completion/suggestions nor inline errors. So, if you pass a non-existent attribute or miss-spell an existing one and it returned unexpected results, it would be hard to discover/debug.

We can also order the results by further chaining .order_by() passing it the field we want to order by, and calling either .asc() for ascending or .desc() for descending:

with Session(engine) as session:
    statement = (
        select(User)
        .where(User.is_admin)
        .order_by(User.date_joined.asc())
    )
    users = session.exec(statement).all()
    for user in users:
        print(user)

Here, we’re ordering by users by the date they joined. The first joined first.

And finally we can chain one more method to limit the results instead of returning the full list of rows which may be slow especially for large tables. We just pass the number of rows we want to .limit() the result to:

with Session(engine) as session:
    statement = (
        select(User)
        .where(User.is_admin)
        .order_by(User.date_joined.asc())
        .limit(2)
    )
    users = session.exec(statement).all()
    for user in users:
        print(user)

Select only one row from the DB:

So far, we have been selecting (reading) multiple rows from the DB. The select() statement unmodified returns an object that is an iterable. We’ve seen how we can use its method .all() to get a list of rows/records instead of the iterable object.

There might be times though where we may want only the first row. We can do that by calling .first() instead of .all(). This will return the first row if there was any:

...
    user = session.exec(statement).first()
    print(user)

If we’re absolutely sure that our query should return one (and only one) row, we can use the .one() method on the exec() result. For example, the id field is the primary key and it must be unique across the table. If we look a user up by id the query should return only one row, or None if no user was found with that id.

    with Session(engine) as session:
        statement = select(User).where(User.id==1)
        user = session.exec(statement).one()
        print(user)

This also helps in testing cases where we want to insure that only one row is returned. If multiple rows were returned, an exception is raised and the test fails.

NOTE: Knowing how to use select(), especially combined with .one() is very important as we will need them when updating and/or deleting to find the row(s) we want to update/delete.

Update rows

Updating rows using SQLModel is a three-step process:

first, we select the row we want to update,
then, we update the row,
finally, we add the row back to the DB and commit.

Think of it like this: you pull the item out of the DB ‘box’, change some of its attributes and then put it back in the DB box.

(Optional): We can refresh the Python object/instance linked with that DB row so that it reflects the new changes.

It’s easier than it sounds, so let’s see how. Assume that we want to assign the user with id #4 administrator privileges.

with Session(engine) as session:
    statement = select(User).where(User.id == 4)
    results = session.exec(statement)
    user_to_update = results.one()

    user_to_update.is_admin = True
    session.add(user_to_update)
    session.commit()
    session.refresh(user_to_update)
    print("Updated user: ", user_to_update)

The code is almost self-explanatory. We find the user using select() filtering the rows with .where(). We then call the one() method of the results object to get only one user, and store that user’s instance in user_to_update.

We then change the is_admin attribute of the user instance to True, add it back to the session and commit the change to the DB.
Finally we refresh the user instance and print it to verify that the new is_admin value has indeed been updated and the user with id #4 is now an admin.

NOTE: Refreshing after updating is optional because SQLModel (via SQLAlchemy) will automatically refresh the object when you access one of its attributes, for example user.name. This lazy refresh ensures you’re seeing the latest data from the database.

However, if you don’t access any attributes after committing, the object might not refresh on its own. That’s why we explicitly call session.refresh(user) to make sure we’re working with the latest data even if we don’t immediately read from it.

That’s it. As you can probably see, it’s fairly easy to update a row with SQLModel.
Next, we’ll see how to perform the last CRUD operation: delete.

Delete specific rows:

Deleting rows from DB is a straightforward business. You select() the row(s) you want to delete, delete them and commit the session.

Here is how we would delete the user with id #3:

with Session(engine) as session:
    statement = select(User).where(User.id == 3)
    results = session.exec(statement)
    user_to_delete = results.one()

    session.delete(user_to_delete)
    session.commit()

We ‘select’ the user to be deleted, the one with the id #3, using select() and filter for the id with .where(). We then call .one() on the results to get the only row returned. After we’ve got the row, we tell the session to delete it and commit the deletion to the DB.

Delete all rows:

To delete all rows in the table, we use the delete() function from sqlmodel, not the .delete() method of the session. After importing the function, we execute it as a statement passing it the name of the model (‘User’ in our example).

WARNING: This would delete all user rows from the ‘user’ table in the DB.

from sqlmodel import SQLModel, Field, Session, create_engine, select, delete
with Session(engine) as session:
    statement = delete(User)
    session.exec(statement)
    session.commit()

That concludes our exploration of CRUD operations and, therefore, our discussion of SQLModel as a nice alternative to using SQL directly in Python code.

Conclusion

It’s been a journey! If you have made it so far, congrats on learning this cool library and adding a new tool to your toolbox.

I think SQLModel is one of the best SQL libraries for Python and is worth learning.

To continue learning, or if you need more technical details, I recommend the documentation. It’s well written, well presented and very approachable.

Thanks for your time. See you in another Pybites blog post.