sqlmodel Documentation

File: docs/advanced/decimal.md Copy Chapter

Decimal Numbers

In some cases you might need to be able to store decimal numbers with guarantees about the precision.

This is particularly important if you are storing things like currencies, prices, accounts, and others, as you would want to know that you wouldn't have rounding errors.

As an example, if you open Python and sum 1.1 + 2.2 you would expect to see 3.3, but you will actually get 3.3000000000000003:

>>> 1.1 + 2.2
3.3000000000000003

This is because of the way numbers are stored in "ones and zeros" (binary). But Python has a module and some types to have strict decimal values. You can read more about it in the official Python docs for Decimal.

Because databases store data in the same ways as computers (in binary), they would have the same types of issues. And because of that, they also have a special decimal type.

In most cases this would probably not be a problem, for example measuring views in a video, or the life bar in a videogame. But as you can imagine, this is particularly important when dealing with money and finances.

Decimal Types Copy Chapter

Pydantic has special support for Decimal types.

When you use Decimal you can specify the number of digits and decimal places to support in the Field() function. They will be validated by Pydantic (for example when using FastAPI) and the same information will also be used for the database columns.

/// note

For the database, SQLModel will use SQLAlchemy's DECIMAL type.

///

Decimals in SQLModel Copy Chapter

Let's say that each hero in the database will have an amount of money. We can define that field as a Decimal and configure the maximum number of digits and decimal places using the parameters of Field():

{* ./docs_src/advanced/decimal/tutorial001_py310.py ln[1:11] hl[11] *}

Here we are saying that money can have at most 5 digits with max_digits, this includes the integers (to the left of the decimal dot) and the decimals (to the right of the decimal dot).

We are also saying that the number of decimal places (to the right of the decimal dot) is 3, so we can have 3 decimal digits for these numbers in the money field. This means that we will have 2 digits for the integer part and 3 digits for the decimal part.

✅ So, for example, these are all valid numbers for the money field:

• 12.345
• 12.3
• 12
• 1.2
• 0.123
• 0

🚫 But these are all invalid numbers for that money field:

• 1.2345
  • This number has more than 3 decimal places.
• 123.234
  • This number has more than 5 digits in total (integer and decimal part).
• 123
  • Even though this number doesn't have any decimals, we still have 3 places saved for them, which means that we can only use 2 places for the integer part, and this number has 3 integer digits. So, the allowed number of integer digits is max_digits - decimal_places = 2.

/// tip

Make sure you adjust the number of digits and decimal places for your own needs, in your own application. 🤓

///

Create models with Decimals Copy Chapter

When creating new models you can actually pass normal (float) numbers, Pydantic will automatically convert them to Decimal types, and SQLModel will store them as Decimal types in the database (using SQLAlchemy).

{* ./docs_src/advanced/decimal/tutorial001_py310.py ln[24:34] hl[25:27] *}

Select Decimal data Copy Chapter

Then, when working with Decimal types, you can confirm that they indeed avoid those rounding errors from floats:

{* ./docs_src/advanced/decimal/tutorial001_py310.py ln[37:50] hl[49:50] *}

Review the results Copy Chapter

Now if you run this, instead of printing the unexpected number 3.3000000000000003, it prints 3.300:

$ uv run python app.py

// Some boilerplate and previous output omitted 😉

// The type of money is Decimal('1.100')
Hero 1: id=1 secret_name='Dive Wilson' age=None name='Deadpond' money=Decimal('1.100')

// More output omitted here 🤓

// The type of money is Decimal('1.100')
Hero 2: id=3 secret_name='Tommy Sharp' age=48 name='Rusty-Man' money=Decimal('2.200')

// No rounding errors, just 3.3! 🎉
Total money: 3.300

/// warning

Although Decimal types are supported and used on the Python side, not all databases support them. In particular, SQLite doesn't support decimals, so it will convert them to the same floating NUMERIC type it supports.

But decimals are supported by most of the other SQL databases. 🎉

///

File: docs/advanced/index.md Copy Chapter

Advanced User Guide

The Advanced User Guide is gradually growing, you can already read about some advanced topics.

At some point it will include:

• How to use async and await with the async session.
• How to run migrations.
• How to combine SQLModel models with SQLAlchemy.
• ...and more. 🤓

File: docs/advanced/uuid.md Copy Chapter

UUID (Universally Unique Identifiers)

We have discussed some data types like str, int, etc.

There's another data type called UUID (Universally Unique Identifier).

You might have seen UUIDs, for example in URLs. They look something like this:

4ff2dab7-bffe-414d-88a5-1826b9fea8df

UUIDs can be particularly useful as an alternative to auto-incrementing integers for primary keys.

/// note

Official support for UUIDs was added in SQLModel version 0.0.20.

///

About UUIDs Copy Chapter

UUIDs are numbers with 128 bits, that is, 16 bytes.

They are normally seen as 32 hexadecimal characters separated by dashes.

There are several versions of UUID, some versions include the current time in the bytes, but UUIDs version 4 are mainly random, the way they are generated makes them virtually unique.

Distributed UUIDs Copy Section

You could generate one UUID on one computer, and someone else could generate another UUID on another computer, and it would be almost impossible for both UUIDs to be the same.

This means that you don't have to wait for the DB to generate the ID for you, you can generate it in code before sending it to the database, because you can be quite certain it will be unique.

/// note | Technical Details

Because the number of possible UUIDs is so large (2^128), the probability of generating the same UUID version 4 (the random ones) twice is very low.

If you had 103 trillion version 4 UUIDs stored in the database, the probability of generating a duplicated new one is one in a billion. 🤓

///

For the same reason, if you decided to migrate your database, combine it with another database and mix records, etc. you would most probably be able to just use the same UUIDs you had originally.

/// warning

There's still a chance you could have a collision, but it's very low. In most cases you could assume you wouldn't have it, but it would be good to be prepared for it.

///

UUIDs Prevent Information Leakage Copy Section

Because UUIDs version 4 are random, you could give these IDs to the application users or to other systems, without exposing information about your application.

When using auto-incremented integers for primary keys, you could implicitly expose information about your system. For example, someone could create a new hero, and by getting the hero ID 20 they would know that you have 20 heroes in your system (or even less, if some heroes were already deleted).

UUID Storage Copy Section

Because UUIDs are 16 bytes, they would consume more space in the database than a smaller auto-incremented integer (commonly 4 bytes).

Depending on the database you use, UUIDs could have better or worse performance. If you are concerned about that, you should check the documentation for the specific database.

SQLite doesn't have a specific UUID type, so it will store the UUID as a string. Other databases like Postgres have a specific UUID type which would result in better performance and space usage than strings.

Models with UUIDs Copy Chapter

To use UUIDs as primary keys we need to import uuid, which is part of the Python standard library (we don't have to install anything) and use uuid.UUID as the type for the ID field.

We also want the Python code to generate a new UUID when creating a new instance, so we use default_factory.

The parameter default_factory takes a function (or in general, a "callable"). This function will be called when creating a new instance of the model and the value returned by the function will be used as the default value for the field.

For the function in default_factory we pass uuid.uuid4, which is a function that generates a new UUID version 4.

/// tip

We don't call uuid.uuid4() ourselves in the code (we don't put the parenthesis). Instead, we pass the function itself, just uuid.uuid4, so that SQLModel can call it every time we create a new instance.

///

This means that the UUID will be generated in the Python code, before sending the data to the database.

{* ./docs_src/advanced/uuid/tutorial001_py310.py ln[1:10] hl[1,7] *}

Pydantic has support for UUID types.

For the database, SQLModel internally uses SQLAlchemy's Uuid type.

Create a Record with a UUID Copy Section

When creating a Hero record, the id field will be automatically populated with a new UUID because we set default_factory=uuid.uuid4.

As uuid.uuid4 will be called when creating the model instance, even before sending it to the database, we can access and use the ID right away.

And that same ID (a UUID) will be saved in the database.

{* ./docs_src/advanced/uuid/tutorial001_py310.py ln[23:34] hl[25,27,29,34] *}

Select a Hero Copy Section

We can do the same operations we could do with other fields.

For example we can select a hero by ID:

{* ./docs_src/advanced/uuid/tutorial001_py310.py ln[37:54] hl[49] *}

/// tip

Even if a database like SQLite stores the UUID as a string, we can select and run comparisons using a Python UUID object and it will work.

SQLModel (actually SQLAlchemy) will take care of making it work. ✨

///

Select with `session.get()`

We could also select by ID with session.get():

{* ./docs_src/advanced/uuid/tutorial002_py310.py ln[37:53] hl[49] *}

The same way as with other fields, we could update, delete, etc. 🚀

Run the program Copy Section

If you run the program, you will see the UUID generated in the Python code, and then the record saved in the database with the same UUID.

$ uv run python app.py

// Some boilerplate and previous output omitted 😉

// In SQLite, the UUID will be stored as a string
// other DBs like Postgres have a specific UUID type
CREATE TABLE hero (
        id CHAR(32) NOT NULL,
        name VARCHAR NOT NULL,
        secret_name VARCHAR NOT NULL,
        age INTEGER,
        PRIMARY KEY (id)
)

// Before saving in the DB we already have the UUID
The hero before saving in the DB
name='Deadpond' secret_name='Dive Wilson' id=UUID('0e44c1a6-88d3-4a35-8b8a-307faa2def28') age=None
The hero ID was already set
0e44c1a6-88d3-4a35-8b8a-307faa2def28

// The SQL statement to insert the record uses our UUID
INSERT INTO hero (id, name, secret_name, age) VALUES (?, ?, ?, ?)
('0e44c1a688d34a358b8a307faa2def28', 'Deadpond', 'Dive Wilson', None)

// And indeed, the record was saved with the UUID we created 😎
After saving in the DB
age=None id=UUID('0e44c1a6-88d3-4a35-8b8a-307faa2def28') name='Deadpond' secret_name='Dive Wilson'

// Now we create a new hero (to select it in a bit)
Created hero:
age=None id=UUID('9d90d186-85db-4eaa-891a-def7b4ae2dab') name='Spider-Boy' secret_name='Pedro Parqueador'
Created hero ID:
9d90d186-85db-4eaa-891a-def7b4ae2dab

// And now we select it
Selected hero:
age=None id=UUID('9d90d186-85db-4eaa-891a-def7b4ae2dab') name='Spider-Boy' secret_name='Pedro Parqueador'
Selected hero ID:
9d90d186-85db-4eaa-891a-def7b4ae2dab

Learn More Copy Chapter

You can learn more about UUIDs in:

• The official Python docs for UUID.
• The Wikipedia for UUID.

File: docs/learn/index.md Copy Chapter

Learn

Learn how to use SQLModel here.

This includes an introduction to databases, SQL, how to interact with databases from code and more.

You could consider this a book, a course, and the official recommended way to learn SQLModel. 😎

File: docs/resources/index.md Copy Chapter

Resources

Additional resources, how to help and get help, how to contribute, and more. ✈️

File: docs/tutorial/connect/create-connected-rows.md Copy Chapter

Create and Connect Rows

We will now create rows for each table. ✨

The team table will look like this:

And after we finish working with the data in this chapter, the hero table will look like this:

Each row in the table hero will point to a row in the table team:

/// note

We will later update Spider-Boy to add him to the Preventers team too, but not yet.

///

We will continue with the code in the previous example and we will add more things to it.

{* ./docs_src/tutorial/connect/create_tables/tutorial001_py310.py ln[0] *}

Make sure you remove the database.db file before running the examples to get the same results.

Create Rows for Teams with **SQLModel** Copy Chapter

Let's do the same we did before and define a create_heroes() function where we create our heroes.

And now we will also create the teams there. 🎉

Let's start by creating two teams:

{* ./docs_src/tutorial/connect/insert/tutorial001_py310.py ln[29:35] hl[29:35] *}

This would hopefully look already familiar.

We start a session in a with block using the same engine we created above.

Then we create two instances of the model class (in this case Team).

Next we add those objects to the session.

And finally we commit the session to save the changes to the database.

Add It to Main Copy Chapter

Let's not forget to add this function create_heroes() to the main() function so that we run it when calling the program from the command line:

{* ./docs_src/tutorial/connect/insert/tutorial001_py310.py ln[61:63] hl[63] *}

Run it Copy Chapter

If we run that code we have up to now, it will output:

$ uv run python app.py

// Previous output omitted 😉

// Automatically start a transaction
INFO Engine BEGIN (implicit)
// Add the teams to the database
INFO Engine INSERT INTO team (name, headquarters) VALUES (?, ?)
INFO Engine [generated in 0.00050s] ('Preventers', 'Sharp Tower')
INFO Engine INSERT INTO team (name, headquarters) VALUES (?, ?)
INFO Engine [cached since 0.002324s ago] ('Z-Force', 'Sister Margaret's Bar')
INFO Engine COMMIT

You can see in the output that it uses common SQL INSERT statements to create the rows.

Create Rows for Heroes in Code Copy Chapter

Now let's create one hero object to start.

As the Hero class model now has a field (column, attribute) team_id, we can set it by using the ID field from the Team objects we just created before:

{* ./docs_src/tutorial/connect/insert/tutorial001_py310.py ln[29:39] hl[38] *}

We haven't committed this hero to the database yet, but there are already a couple of things to pay attention to.

If the database already had some teams, we wouldn't even know what is the ID that is going to be automatically assigned to each team by the database, for example, we couldn't just guess 1 or 2.

But once the team is created and committed to the database, we can access the object's id field to get that ID.

Accessing an attribute in a model that was just committed, for example with team_z_force.id, automatically triggers a refresh of the data from the DB in the object, and then exposes the value for that field.

So, even though we are not committing this hero yet, just because we are using team_z_force.id, that will trigger some SQL sent to the database to fetch the data for this team.

That line alone would generate an output of:

INFO Engine BEGIN (implicit)
INFO Engine SELECT team.id AS team_id, team.name AS team_name, team.headquarters AS team_headquarters
FROM team
WHERE team.id = ?
INFO Engine [generated in 0.00025s] (2,)

Let's now create two more heroes:

{* ./docs_src/tutorial/connect/insert/tutorial001_py310.py ln[29:50] hl[40:46] *}

When creating hero_rusty_man, we are accessing team_preventers.id, so that will also trigger a refresh of its data, generating an output of:

INFO Engine SELECT team.id AS team_id, team.name AS team_name, team.headquarters AS team_headquarters
FROM team
WHERE team.id = ?
INFO Engine [cached since 0.001795s ago] (1,)

There's something else to note. We marked team_id as int | None, meaning that this could be NULL on the database (and None in Python).

That means that a hero doesn't have to have a team. And in this case, Spider-Boy doesn't have one.

Next we just commit the changes to save them to the database, and that will generate the output:

INFO Engine INSERT INTO hero (name, secret_name, age, team_id) VALUES (?, ?, ?, ?)
INFO Engine [generated in 0.00022s] ('Deadpond', 'Dive Wilson', None, 2)
INFO Engine INSERT INTO hero (name, secret_name, age, team_id) VALUES (?, ?, ?, ?)
INFO Engine [cached since 0.0007987s ago] ('Rusty-Man', 'Tommy Sharp', 48, 1)
INFO Engine INSERT INTO hero (name, secret_name, age, team_id) VALUES (?, ?, ?, ?)
INFO Engine [cached since 0.001095s ago] ('Spider-Boy', 'Pedro Parqueador', None, None)
INFO Engine COMMIT

Refresh and Print Heroes Copy Chapter

Now let's refresh and print those new heroes to see their new ID pointing to their teams:

{* ./docs_src/tutorial/connect/insert/tutorial001_py310.py ln[29:58] hl[52:54,56:58] *}

If we execute that in the command line, it will output:

$ uv run python app.py

// Previous output omitted 😉

// Automatically start a transaction
INFO Engine BEGIN (implicit)

// Refresh the first hero
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id
FROM hero
WHERE hero.id = ?
INFO Engine [generated in 0.00021s] (1,)
// Refresh the second hero
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id
FROM hero
WHERE hero.id = ?
INFO Engine [cached since 0.001575s ago] (2,)
// Refresh the third hero
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id
FROM hero
WHERE hero.id = ?
INFO Engine [cached since 0.002518s ago] (3,)

// Print the heroes
Created hero: id=1 secret_name='Dive Wilson' team_id=2 name='Deadpond' age=None
Created hero: id=2 secret_name='Tommy Sharp' team_id=1 name='Rusty-Man' age=48
Created hero: id=3 secret_name='Pedro Parqueador' team_id=None name='Spider-Boy' age=None

They now have their team_ids, nice!

Relationships Copy Chapter

Relationships in SQL databases are just made by having columns in one table referencing the values in columns on other tables.

And here we have treated them just like that, more column fields, which is what they actually are behind the scenes in the SQL database.

But later in this tutorial, in the next group of chapters, you will learn about Relationship Attributes to make it all a lot easier to work with in code. ✨

File: docs/tutorial/connect/create-connected-tables.md Copy Chapter

Create Connected Tables

Now we will deal with connected data put in different tables.

So, the first step is to create more than one table and connect them, so that each row in one table can reference another row in the other table.

We have been working with heroes in a single table hero. Let's now add a table team.

The team table will look like this:

To connect them, we will add another column to the hero table to point to each team by the ID with the team_id:

This way each row in the table hero can point to a row in the table team:

One-to-Many and Many-to-One Copy Chapter

Here we are creating connected data in a relationship where one team could have many heroes. So it is commonly called a one-to-many or many-to-one relationship.

The many-to-one part can be seen if we start from the heroes, many heroes could be part of one team.

This is probably the most popular type of relationship, so we'll start with that. But there's also many-to-many and one-to-one relationships.

Create Tables in Code Copy Chapter

Create the `team` Table Copy Section

Let's start by creating the tables in code.

Import the things we need from sqlmodel and create a new Team model:

{* ./docs_src/tutorial/connect/create_tables/tutorial001_py310.py ln[1:7] hl[4:7] *}

This is very similar to what we have been doing with the Hero model.

The Team model will be in a table automatically named "team", and it will have the columns:

• id, the primary key, automatically generated by the database
• name, the name of the team
  • We also tell SQLModel to create an index for this column
• headquarters, the headquarters of the team

And finally we mark it as a table in the config.

Create the New `hero` Table Copy Section

Now let's create the hero table.

This is the same model we have been using up to now, we are just adding the new column team_id:

{* ./docs_src/tutorial/connect/create_tables/tutorial001_py310.py ln[1:16] hl[16] *}

Most of that should look familiar:

The column will be named team_id. It will be an integer, and it could be NULL in the database (or None in Python), because there could be some heroes that don't belong to any team.

We add a default of None to the Field() so we don't have to explicitly pass team_id=None when creating a hero.

Now, here's the new part:

In Field() we pass the argument foreign_key="team.id". This tells the database that this column team_id is a foreign key to the table team. A "foreign key" just means that this column will have the key to identify a row in a foreign table.

The value in this column team_id will be the same integer that is in some row in the id column on the team table. That is what connects the two tables.

The Value of `foreign_key`

Notice that the foreign_key is a string.

Inside it has the name of the table, then a dot, and then the name of the column.

This is the name of the table in the database, so it is "team", not the name of the model class Team (with a capital T).

If you had a custom table name, you would use that custom table name.

/// note

You can learn about setting a custom table name for a model in the Advanced User Guide.

///

Create the Tables Copy Section

Now we can add the same code as before to create the engine and the function to create the tables:

{* ./docs_src/tutorial/connect/create_tables/tutorial001_py310.py ln[19:26] hl[19:20,22,25:26] *}

And as before, we'll call this function from another function main(), and we'll add that function main() to the main block of the file:

{* ./docs_src/tutorial/connect/create_tables/tutorial001_py310.py ln[29:34] hl[29:30,33:34] *}

Run the Code Copy Chapter

/// tip

Before running the code, make sure you delete the file database.db to make sure you start from scratch.

///

If we run the code we have up to now, it will go and create the database file database.db and the tables in it we just defined, team and hero:

$ uv run python app.py

// Automatically start a new transaction
INFO Engine BEGIN (implicit)

// Check if the tables exist already
INFO Engine PRAGMA main.table_info("team")
INFO Engine [raw sql] ()
INFO Engine PRAGMA temp.table_info("team")
INFO Engine [raw sql] ()
INFO Engine PRAGMA main.table_info("hero")
INFO Engine [raw sql] ()
INFO Engine PRAGMA temp.table_info("hero")
INFO Engine [raw sql] ()

// Create the tables
INFO Engine
CREATE TABLE team (
        id INTEGER,
        name VARCHAR NOT NULL,
        headquarters VARCHAR NOT NULL,
        PRIMARY KEY (id)
)


INFO Engine [no key 0.00010s] ()
INFO Engine
CREATE TABLE hero (
        id INTEGER,
        name VARCHAR NOT NULL,
        secret_name VARCHAR NOT NULL,
        age INTEGER,
        team_id INTEGER,
        PRIMARY KEY (id),
        FOREIGN KEY(team_id) REFERENCES team (id)
)


INFO Engine [no key 0.00026s] ()
INFO Engine COMMIT

Create Tables in SQL Copy Chapter

Let's see that same generated SQL code.

As we saw before, those VARCHAR columns are converted to TEXT in SQLite, which is the database we are using for these experiments.

So, the first SQL could also be written as:

CREATE TABLE team (
    id INTEGER,
    name TEXT NOT NULL,
    headquarters TEXT NOT NULL,
    PRIMARY KEY (id)
)

And the second table could be written as:

CREATE TABLE hero (
    id INTEGER,
    name TEXT NOT NULL,
    secret_name TEXT NOT NULL,
    age INTEGER,
    team_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(team_id) REFERENCES team (id)
)

The only new part is the FOREIGN KEY line, and as you can see, it tells the database what column in this table is a foreign key (team_id), which other (foreign) table it references (team) and which column in that table is the key to define which row to connect (id).

Feel free to experiment with it in DB Browser for SQLite.

Recap Copy Chapter

Using SQLModel, in most of the cases you only need a field (column) with a foreign_key in the Field() with a string pointing to another table and column to connect two tables.

Now that we have the tables created and connected, let's create some rows in the next chapter. 🚀

File: docs/tutorial/connect/index.md Copy Chapter

Connect Tables - JOIN - Intro

By this point, you already know how to perform the main CRUD operations with SQLModel using a single table. 🎉

But the main advantage and feature of SQL databases is being able to handle related data, to connect or "join" different tables together. Connecting rows in one table to rows in another.

Let's see how to use SQLModel to manage connected data in the next chapters. 🤝

/// tip

We will extend this further in the next group of chapters making it even more convenient to work with in Python code, using relationship attributes.

But you should start in this group of chapters first. 🤓

///

File: docs/tutorial/connect/read-connected-data.md Copy Chapter

Read Connected Data

Now that we have some data in both tables, let's select the data that is connected together.

The team table has this data:

And the hero table has this data:

We will continue with the code in the previous example and we will add more things to it.

{* ./docs_src/tutorial/connect/insert/tutorial001_py310.py ln[0] *}

`SELECT` Connected Data with SQL Copy Chapter

Let's start seeing how SQL works when selecting connected data. This is where SQL databases actually shine.

If you don't have a database.db file, run that previous program we had written (or copy it from the preview above) to create it.

Now open DB Browser for SQLite and open the database.db file.

To SELECT connected data we use the same keywords we have used before, but now we combine the two tables.

Let's get each hero with the id, name, and the team name:

SELECT hero.id, hero.name, team.name
FROM hero, team
WHERE hero.team_id = team.id

/// note

Because we have two columns called name, one for hero and one for team, we can specify them with the prefix of the table name and the dot to make it explicit what we refer to.

///

Notice that now in the WHERE part we are not comparing one column with a literal value (like hero.name = "Deadpond"), but we are comparing two columns.

It means, more or less:

Hey SQL database 👋, please go and SELECT some data for me.

I'll first tell you the columns I want:

• id of the hero table
• name of the hero table
• name of the team table

I want you to get that data FROM the tables hero and team.

And I don't want you to combine each hero with each possible team. Instead, for each hero, go and check each possible team, but give me only the ones WHERE the hero.team_id is the same as the team.id.

If we execute that SQL, it will return the table:

You can go ahead and try it in DB Browser for SQLite:

/// note

Wait, what about Spider-Boy? 😱

He doesn't have a team, so his team_id is NULL in the database. And this SQL is comparing that NULL from the team_id with all the id fields in the rows in the team table.

As there's no team with an ID of NULL, it doesn't find a match.

But we'll see how to fix that later with a LEFT JOIN.

///

Select Related Data with **SQLModel** Copy Chapter

Now let's use SQLModel to do the same select.

We'll create a function select_heroes() just as we did before, but now we'll work with two tables.

Remember SQLModel's select() function? It can take more than one argument.

So, we can pass the Hero and Team model classes. And we can also use both their columns in the .where() part:

{* ./docs_src/tutorial/connect/select/tutorial001_py310.py ln[61:63] hl[63] *}

Notice that in the comparison with == we are using the class attributes for both Hero.team_id and Team.id.

That will generate the appropriate expression object that will be converted to the right SQL, equivalent to the SQL example we saw above.

Now we can execute it and get the results object.

And as we used select with two models, we will receive tuples of instances of those two models, so we can iterate over them naturally in a for loop:

{* ./docs_src/tutorial/connect/select/tutorial001_py310.py ln[61:66] hl[65] *}

For each iteration in the for loop we get a tuple with an instance of the class Hero and an instance of the class Team.

And in this for loop we assign them to the variable hero and the variable team.

/// note

There was a lot of research, design, and work behind SQLModel to make this provide the best possible developer experience.

And you should get autocompletion and inline errors in your editor for both hero and team. 🎉

///

Add It to Main Copy Chapter

As always, we must remember to add this new select_heroes() function to the main() function to make sure it is executed when we call this program from the command line.

{* ./docs_src/tutorial/connect/select/tutorial001_py310.py ln[69:72] hl[72] *}

Run the Program Copy Chapter

Now we can run the program and see how it shows us each hero with their corresponding team:

$ uv run python app.py

// Previous output omitted 😉

// Get the heroes with their teams
2021-08-09 08:55:50,682 INFO sqlalchemy.engine.Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id, team.id AS id_1, team.name AS name_1, team.headquarters
FROM hero, team
WHERE hero.team_id = team.id
2021-08-09 08:55:50,682 INFO sqlalchemy.engine.Engine [no key 0.00015s] ()

// Print the first hero and team
Hero: id=1 secret_name='Dive Wilson' team_id=2 name='Deadpond' age=None Team: headquarters='Sister Margaret's Bar' id=2 name='Z-Force'

// Print the second hero and team
Hero: id=2 secret_name='Tommy Sharp' team_id=1 name='Rusty-Man' age=48 Team: headquarters='Sharp Tower' id=1 name='Preventers'
2021-08-09 08:55:50,682 INFO sqlalchemy.engine.Engine ROLLBACK

`JOIN` Tables with SQL Copy Chapter

There's an alternative syntax for that SQL query from above using the keyword JOIN instead of WHERE.

This is the same version from above, using WHERE:

SELECT hero.id, hero.name, team.name
FROM hero, team
WHERE hero.team_id = team.id

And this is the alternative version using JOIN:

SELECT hero.id, hero.name, team.name
FROM hero
JOIN team
ON hero.team_id = team.id

Both are equivalent. The differences in the SQL code are that instead of passing the team to the FROM part (also called FROM clause) we add a JOIN and put the team table there.

And then, instead of putting a WHERE with a condition, we put an ON keyword with the condition, because ON is the one that comes with JOIN. 🤷

So, this second version means, more or less:

Hey SQL database 👋, please go and SELECT some data for me.

I'll first tell you the columns I want:

• id of the hero table
• name of the hero table
• name of the team table

...up to here it's the same as before, LOL.

Now, I want you to get that data starting FROM the table hero.

And to get the rest of the data, I want you to JOIN it with the table team.

And I want you to join those two tables ON the combinations of rows that have the hero.team_id with the same value as the team.id.

Did I say all this before already? I feel like I'm just repeating myself. 🤔

That will return the same table as before:

Also in DB Browser for SQLite:

/// tip

Why bother with all this if the result is the same?

This JOIN will be useful in a bit to be able to also get Spider-Boy, even if he doesn't have a team.

///

Join Tables in **SQLModel** Copy Chapter

The same way there's a .where() available when using select(), there's also a .join().

And in SQLModel (actually SQLAlchemy), we don't have to pass an ON part when using .join(). It is inferred automatically because we already declared the foreign_key in the model definition:

{* ./docs_src/tutorial/connect/select/tutorial002_py310.py ln[61:66] hl[63] *}

Also notice that we are still including Team in the select(Hero, Team), because we still want to access that data.

This is equivalent to the previous example.

And if we run it in the command line, it will output:

$ uv run python app.py

// Previous output omitted 😉

// Select using a JOIN with automatic ON
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id, team.id AS id_1, team.name AS name_1, team.headquarters
FROM hero JOIN team ON team.id = hero.team_id
INFO Engine [no key 0.00032s] ()

// Print the first hero and team
Hero: id=1 secret_name='Dive Wilson' team_id=2 name='Deadpond' age=None Team: headquarters='Sister Margaret's Bar' id=2 name='Z-Force'

// Print the second hero and team
Hero: id=2 secret_name='Tommy Sharp' team_id=1 name='Rusty-Man' age=48 Team: headquarters='Sharp Tower' id=1 name='Preventers'

`JOIN` Tables with SQL and `LEFT OUTER` (Maybe `JOIN`) Copy Chapter

When working with a JOIN, you can imagine that you start with a table on the FROM part and put that table in an imaginary space on the left side.

And then you want another table to JOIN the result.

And you put that second table in the right side on that imaginary space.

And then you tell the database ON which condition it should join those two tables and give you the results back.

But by default, only the rows from both left and right that match the condition will be returned.

In this example of tables above 👆, it would return all the heroes, because every hero has a team_id, so every hero can be joined with the team table:

Foreign Keys with `NULL` Copy Section

But in the database that we are working with in the code above, Spider-Boy doesn't have any team, the value of team_id is NULL in the database.

So there's no way to join the Spider-Boy row with some row in the team table:

Running the same SQL we used above, the resulting table would not include Spider-Boy 😱:

Include Everything on the `LEFT OUTER` Copy Section

In this case, that we want to include all heroes in the result even if they don't have a team, we can extend that same SQL using a JOIN from above and add a LEFT OUTER right before JOIN:

SELECT hero.id, hero.name, team.name
FROM hero
LEFT OUTER JOIN team
ON hero.team_id = team.id

This LEFT OUTER part tells the database that we want to keep everything on the first table, the one on the LEFT in the imaginary space, even if those rows would be left out, so we want it to include the OUTER rows too. In this case, every hero with or without a team.

And that would return the following result, including Spider-Boy 🎉:

/// tip

The only difference between this query and the previous is that extra LEFT OUTER.

///

And here's another of the SQL variations, you could write LEFT OUTER JOIN or just LEFT JOIN, it means the same.

Join Tables in **SQLModel** with `LEFT OUTER` Copy Chapter

Now let's replicate the same query in SQLModel.

.join() has a parameter we can use isouter=True to make the JOIN be a LEFT OUTER JOIN:

{* ./docs_src/tutorial/connect/select/tutorial003_py310.py ln[61:66] hl[63] *}

And if we run it, it will output:

$ uv run python app.py

// Previous output omitted 😉

// SELECT using LEFT OUTER JOIN
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id, team.id AS id_1, team.name AS name_1, team.headquarters
FROM hero LEFT OUTER JOIN team ON team.id = hero.team_id

INFO Engine [no key 0.00051s] ()

// Print the first hero and team
Hero: id=1 secret_name='Dive Wilson' team_id=2 name='Deadpond' age=None Team: headquarters='Sister Margaret's Bar' id=2 name='Z-Force'
// Print the second hero and team
Hero: id=2 secret_name='Tommy Sharp' team_id=1 name='Rusty-Man' age=48 Team: headquarters='Sharp Tower' id=1 name='Preventers'
// Print the third hero and team, we included Spider-Boy 🎉
Hero: id=3 secret_name='Pedro Parqueador' team_id=None name='Spider-Boy' age=None Team: None

What Goes in `select()` Copy Chapter

You might be wondering why we put the Team in the select() and not just in the .join().

And then why we didn't include Hero in the .join(). 🤔

In SQLModel (actually in SQLAlchemy), all these functions and tools try to replicate how it would be to work with the SQL language.

Remember that SELECT defines the columns to get and WHERE how to filter them?.

This also applies here, but with JOIN and ON.

Select Only Heroes But Join with Teams Copy Section

If we only put the Team in the .join() and not in the select() function, we would not get the team data.

But we would still be able to filter the rows with it. 🤓

We could even add some additional .where() after .join() to filter the data more, for example to return only the heroes from one team:

{* ./docs_src/tutorial/connect/select/tutorial004_py310.py ln[61:66] hl[63] *}

Here we are filtering with .where() to get only the heroes that belong to the Preventers team.

But we are still only requesting the data from the heroes, not their teams.

If we run that, it would output:

$ uv run python app.py

// Select only the hero data
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id
// But still join with the team table
FROM hero JOIN team ON team.id = hero.team_id
// And filter with WHERE to get only the Preventers
WHERE team.name = ?
INFO Engine [no key 0.00066s] ('Preventers',)

// We filter with the team, but only get the hero
Preventer Hero: id=2 secret_name='Tommy Sharp' team_id=1 name='Rusty-Man' age=48

Include the `Team` Copy Section

By putting the Team in select() we tell SQLModel and the database that we want the team data too.

{* ./docs_src/tutorial/connect/select/tutorial005_py310.py ln[61:66] hl[63] *}

And if we run that, it will output:

$ uv run python app.py

// Select the hero and the team data
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id, team.id AS id_1, team.name AS name_1, team.headquarters
// Join the hero with the team table
FROM hero JOIN team ON team.id = hero.team_id
// Filter with WHERE to get only Preventers
WHERE team.name = ?
INFO Engine [no key 0.00018s] ('Preventers',)

// Print the hero and the team
Preventer Hero: id=2 secret_name='Tommy Sharp' team_id=1 name='Rusty-Man' age=48 Team: headquarters='Sharp Tower' id=1 name='Preventers'

We still have to .join() because otherwise it would just compute all the possible combinations of heroes and teams, for example including Rusty-Man with Preventers and also Rusty-Man with Z-Force, which would be a mistake.

Relationship Attributes Copy Chapter

Here we have been using the pure class models directly, but in a future chapter we will also see how to use Relationship Attributes that let us interact with the database in a way much more close to the code with Python objects.

And we will also see how to load their data in a different, simpler way, achieving the same we achieved here. ✨

File: docs/tutorial/connect/remove-data-connections.md Copy Chapter

Remove Data Connections

We currently have a team table:

And a hero table:

Let's see how to remove connections between rows in tables.

We will continue with the code from the previous chapter.

{* ./docs_src/tutorial/connect/update/tutorial001_py310.py ln[0] *}

Break a Connection Copy Chapter

We don't really have to delete anything to break a connection. We can just assign None to the foreign key, in this case, to the team_id.

Let's say Spider-Boy is tired of the lack of friendly neighbors and wants to get out of the Preventers.

We can simply set the team_id to None, and now it doesn't have a connection with the team:

{* ./docs_src/tutorial/connect/delete/tutorial001_py310.py ln[29:30,66:70] hl[66] *}

Again, we just assign a value to that field attribute team_id, now the value is None, which means NULL in the database. Then we add() the hero to the session, and then commit().

Next we refresh() it to get the recent data, and we print it.

Running that in the command line will output:

$ uv run python app.py

// Previous output omitted 😉

// Update the hero
INFO Engine UPDATE hero SET team_id=? WHERE hero.id = ?
INFO Engine [cached since 0.07753s ago] (None, 3)
// Commit the session
INFO Engine COMMIT
// Automatically start a new transaction
INFO Engine BEGIN (implicit)
// Refresh the hero
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id
FROM hero
WHERE hero.id = ?
INFO Engine [cached since 0.1661s ago] (3,)

// Print the hero without a team
No longer Preventer: id=3 secret_name='Pedro Parqueador' team_id=None name='Spider-Boy' age=None

That's it, we now removed a connection between rows in different tables by unsetting the foreign key column. 💥

File: docs/tutorial/connect/update-data-connections.md Copy Chapter

Update Data Connections

At this point we have a team table:

And a hero table:

Some of these heroes are part of a team.

Now we'll see how to update those connections between rows in tables.

We will continue with the code we used to create some heroes, and we'll update them.

{* ./docs_src/tutorial/connect/insert/tutorial001_py310.py ln[0] *}

Assign a Team to a Hero Copy Chapter

Let's say that Tommy Sharp uses his "rich uncle" charms to recruit Spider-Boy to join the team of the Preventers, now we need to update our Spider-Boy hero object to connect it to the Preventers team.

Doing it is just like updating any other field:

{* ./docs_src/tutorial/connect/update/tutorial001_py310.py ln[29:30,60:64] hl[60] *}

We can simply assign a value to that field attribute team_id, then add() the hero to the session, and then commit().

Next we refresh() it to get the recent data, and we print it.

Running that in the command line will output:

$ uv run python app.py

// Previous output omitted 😉

// Update the hero
INFO Engine UPDATE hero SET team_id=? WHERE hero.id = ?
INFO Engine [generated in 0.00014s] (1, 3)
// Commit the session saving the changes
INFO Engine COMMIT
// Automatically start a new transaction
INFO Engine BEGIN (implicit)
// Refresh the hero data
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id
FROM hero
WHERE hero.id = ?
INFO Engine [cached since 0.08837s ago] (3,)

// Print the updated hero
Updated hero: id=3 secret_name='Pedro Parqueador' team_id=1 name='Spider-Boy' age=None

And now Spider-Boy has the team_id=1, which is the ID of the Preventers. 🎉

Let's now see how to remove connections in the next chapter. 💥