The Problem with API Boilerplate
When you develop an API, you might feel like you're writing a lot of boilerplate code: routes, database schemas, the API interface, validation, and so on. Today, I'll show you how to drastically reduce that boilerplate with SQLModel and FastAPI. I'll start by showing you the limitations of using something like SQLAlchemy, and then I'll show you how SQLModel fixes those things. However, there is one thing you need to be mindful of when you're using tools like SQLAlchemy and SQLModel. More about that later.
Quite commonly, your API is going to rely on a database for creating, reading, updating, and deleting objects. Now, you can use SQL queries directly for that, which is not a bad way of doing things, just a different way of doing things. But it's also quite common to use something like SQLAlchemy. So here I have an example that uses SQLAlchemy to set up a database connection and deal with a variety of different operations you want to perform on that database.
So the main class that I have here is the DBHero, that's a hero stored in the database. And as you can see, this is the way that we do it in SQLAlchemy. So we start with creating a declarative base, and then we create a hero class that inherits from that base. We supply it with a table name, and then we provide a couple of different columns in that database. So there's nothing very special here. This is actually based on one of the core examples in the SQLModel package. But there's an ID, which is an integer primary key and an index. It has a name, and each hero also has a secret name, and each hero has an age. And each of these are columns.
A FastAPI App with SQLAlchemy
Now that we have this, we can start using this to interact with the database. So this is actually in FastAPI, I'll come back to this part of the code a bit later. But as you can see, we have a database URL, so it's just a SQLite database for now. We create the engine, then we create a session, and then we bind the base and all the classes that inherit from it to that particular engine.
Then we have the FastAPI part, and here is where we interact with the database. So get_session is actually something that creates a session for us, and I'm using the FastAPI dependency injection mechanism to supply that to each of the endpoints. So that's basically this part right here. And then inside each endpoint, I have the code to actually do something with the database. In this case, I'm creating a DBHero from the hero information, I add that, I commit the session, and I do a refresh so that I can actually return the newly created object that now has an ID.
And this is also where you see you have to do some extra work in FastAPI. So in order to supply the hero information, we need to define a Pydantic model for that. That's how FastAPI works. And we also have a response model, so what FastAPI sends back. So this is what I've defined here. So you see we have Hero, which is a subclass of BaseModel. BaseModel—that's from Pydantic. And there we have an ID, which is an optional int because we may not always have an ID, right? If we create the hero, we don't supply an ID, that's generated for us. We have a name, secret name, and an age. And then there's other endpoints like an endpoint for reading all the heroes that respond with a list of Hero objects. Then we have an endpoint for getting a particular hero by ID, and one for updating a hero, and one for deleting a hero. So I won't go into all the code here in detail, this is pretty straightforward, but you can find the full code in GitHub.
Let me run this file. And then this is going to start our FastAPI server. I'm using uvicorn in order to run this. Another thing you may notice that I'm actually using uv here, which is a tool that I've recently started using to run my Python projects. And is actually quite interesting. It's really fast. I'm going to do another video about this soon and tell you more about it. So we're now running it on this particular local server. And then what I can do is send a GET request to this particular server that I'm running now, and this tells me that there is a single hero in this particular database. And then let's create another hero called, I don't know, Mrs. Awesome. And there we have our second hero. And now when I get the list of heroes again, I'm going to see that there are now two heroes in the database. So this is how our simple FastAPI app works.
The Downsides of Duplication
So you might say, oh, this is actually pretty nice, why do we need to change anything? Well, if you look at the code, you've probably noticed that SQLAlchemy is great for database definitions, but when you're building an API with FastAPI, things tend to get a bit repetitive. We need separate models for defining how our database looks like, as well as for how the input and response of the API works, which means that we're duplicating quite a bit of code. This feels like deja vu, and this extra boilerplate that we write can lead to mistakes and make our code less maintainable.
Now, similar deja vu can happen in your design as well. To avoid those, I have a free design guide that goes through the steps I take when starting a new project. You can grab it at arjan.codes/designguide.
Now another issue apart from duplication is that we're using two different mechanisms for defining the database schema and for the API interface model, while conceptually these things are actually pretty close. Having two different ways to do things is confusing and leads to code that's harder to read.
Introducing SQLModel
Now this is where SQLModel comes to the rescue. With SQLModel, you define your model once and you can use it for both the database and for the API interface. So let's modify this example so that it uses SQLModel instead of SQLAlchemy.
So we have all these SQLAlchemy imports, I'm simply going to remove them. That's going to lead, of course, now to a bunch of errors, but then I'm going to replace them step-by-step by SQLModel alternatives. So what I'm going to do first is from SQLModel, I'm going to import a couple of things, namely the SQLModel class, which we're going to need in order to create subclasses of this. This basically combines the ORM part of SQLAlchemy with Pydantic, which is really cool. And then we're going to need a couple of other things like—actually GitHub Copilot already has a bunch of these—like creating the engine, a session, a field... We don't need a declarative base, and of course, we need columns and a few other things as well.
So in SQLModel, what we do, we don't have to create a declarative base like in SQLAlchemy. SQLModel actually uses SQLAlchemy under the hood, so it does this for us. But then what we do is we take our hero class, and I'm simply going to call this Hero for simplification, and I'm going to make this a subclass of SQLModel. And also, if we want something to be a table in a database, we have to specify that with the table argument and set that to true. Now, we don't have to supply the table name; with SQLModel, it's going to infer this from the class name.
And now let me actually remove this because we don't need this anymore. But what we're going to do now is use a Pydantic kind of style to define this particular hero. So instead of using column like we did here, we can simply write Optional[int]. So this is an integer. And this is going to be a field, so that's what we take from SQLModel, and we're going to set primary_key to True, and we also set a index to True. Also, the Pydantic input we actually no longer need. And then let's define the other things as well. So we have the name, this is a string. And actually, this shouldn't be an equal sign, this should be a colon, like so. Secret name is also a string, and age is an Optional[int], and by default, that's going to be None.
And actually, I over-imported a couple of things here that are actually not really necessary, like these things because we can simply use the built-in types by Python.
Refactoring the FastAPI App with SQLModel
Now that we have this, there's just a couple of basic things that we need to fix. So here we have all the SQLAlchemy boilerplate stuff, and actually this also gets simplified quite a bit. So of course, we still need the database URL, we still need the engine, but now I can simply do SQLModel.metadata.create_all and then pass it the engine, and that's basically it. The get_session dependency also gets simpler because here we can just use with Session(engine) as session: and then yield that particular session.
And then of course, we have to update the endpoints. So here we simply use this same Hero input model and response model, so we don't need to change anything there. And that also means that we no longer have to make this translation step right here. So I can simply remove this line and I can then directly add the hero to the session and then commit that and refresh it and then return that hero as a result. So this considerably simplifies the code.
And when reading all the heroes from the database, we can also set that up slightly differently. So here, I'm using a query from SQL Alchemy, but what you would do in SQLModel is do this slightly differently, which is that we call .exec which is going to execute a query, and we're going to call select of the hero and then offset that with a skip value, so that gives us pagination, and then a limit, and then give us all the parts of that. And in order for this to work, we need actually to import the select function that does that for us. So then this is what you get.
Reading a hero by ID is actually also a bit simpler, which is nice. We can simply do session.get and we pass it the Hero type, and we give it the hero_id that we pass as an argument here. So that's the only thing we need to change here. When we update the hero, so we do the same thing, so we retrieve the hero by ID, and then we're going to update the hero's attributes, commit the session, refresh it, and return the hero. So not much to change here. And finally, we have the delete operation. So in this case, we also get the hero because actually what we do in the delete endpoint is that we return the hero as a result of this, so you can still use it. Then we delete it, and then we return the actual hero.
So this is all it takes. So this has dramatically simplified our whole setup because we no longer need separate things for database models and for inputs and response models of our API endpoints. So I'm going to stop this server that we were running and then let's restart that and then see what happens.
So now with our newly started server, let's see how this works. So I've deleted the original database, the thing that I was testing with, so that also means that when I now get the list of heroes, this is going to be empty. The database is empty. So again, let's create a new hero, so that's Captain Strong. If I do a GET request and retrieve all the heroes, and this is the list that we get. And I can also get, of course, hero by ID one, and then this is what you get. If I try a hero by another ID, this is going to raise an exception and that will give this particular response. So that all works. And then I can also use delete to delete a hero from the database.
Now, probably it would be nice that actually the endpoint removes the ID from the hero in this case, so that's something if you want to update the example and use that for yourself, then feel free to do that. But now when I get again the list of heroes, then this is going to be empty. All the heroes are dead.
How SQLModel Handles Relationships
So overall, I really like this approach. SQLModel combines the ORM part with Pydantic, so you only need one model. You get a fully typed model, it has data validation out of the box, it reduces redundancy, and that also decreases the risk of errors when you copy and paste things between different classes. So it's cleaner, more Pythonic, lets you focus on the fun part, which is building the actual features.
Now, I want to briefly show you something that's slightly more complex, which is relationships. Relationships are always complex. SQLModel actually handles these quite smoothly, and they include both one-to-many and many-to-many relationships. So how does this actually work? Well, I have a slightly more involved setup here that shows a couple of examples of these relationships.
So the first thing that I did in this particular example is that there is now a Team class. This is also a table. You can see that by this setting table equals true. So it has an ID, which is again optional int, it has a name, and this is where we have the interesting part: we have a list of heroes, and that's a relationship, and that's back-populated by the team. And then I have a hero model which is also a table in the database which has the same things: ID, name, secret name, same thing that we had before. But then you see we have a team_id, and that's a foreign key that maps to the ID from a team. And I have a team which is a relationship that back-populates heroes. So this specifies that a hero can be part of a team. So that's a one-to-many relationship, or a many-to-one relationship depending on where you start, right? A team consists of multiple heroes, so that's one team maps to many heroes, and the hero is part of a team.
Now, if you have a many-to-many relationship, this works slightly differently. So here you see we also have missions. So a hero can be part of a mission. And actually, we like our heroes to do multiple things, so they can be part of multiple missions. So we see that we have a list of missions here, which again is a relationship that back-populates heroes. And we see something else here, which is a link model. I'll talk more about that in a minute. But another class that we have here is a Mission, and a mission can have multiple heroes. So this is a typical example of many-to-many relationships: multiple heroes can be on multiple missions. And this is how you define that.
And in order to reduce redundancy, because you don't want to store like a bunch of lists everywhere in the database, we have a separate model which is called a link model, and that links a hero_id with a mission_id, and these are both foreign keys. So this maps to an ID of a hero object, and this maps to an ID of a mission. And you can also see that these link models, they are used here in the SQLModel specification. So we have them both in the list of missions and in the list of heroes. And by doing it this way, that means that the relationship is stored in this particular table. So this is also a table in the database. But that means we're being quite efficient because we don't store a list of missions in the hero and the list of heroes in each mission. This reduces redundancy.
And then of course, there's extra code here to create a mission or assign a hero to a mission or read a hero with the relationships and so on and so on.
By the way, if you're enjoying this video so far, make sure to give it a like and subscribe to the channel. Doing that helps me a lot with reaching more people here on YouTube.
SQLModel Features and When to Use It
Now before I finish this video, I just want to highlight a couple of additional features, but then I also want to talk about one thing you really have to be careful about. So what are some of the additional features you get with SQLModel? So the first thing is that SQLModel includes asynchronous support, which is perfect if you're using it with FastAPI and you want to handle multiple requests concurrently. There's also automatic data validation. Type hints are strictly enforced so you can catch data mismatches immediately. Also, SQLModel works seamlessly with Alembic (Alembic, how do you call it?) for database migrations, so there's no need to worry about breaking your schema every time you update it.
So when should you use SQLModel? When should you use SQLAlchemy or something else? So it's like a whole discussion we can talk like a full video about that. But in principle, use SQLModel if you're building something with FastAPI, you want easy Pydantic integration, you like a simple, Pythonic API that enforces type safety. And I'd also say this is pretty neat if you have like a small to medium project you want to get started quickly.
Now, if you need advanced capabilities like complex transactions, custom SQL queries and you don't really need an ORM-style interface, perhaps SQLModel or even directly using SQL queries might be better for you. Or perhaps you're managing a large legacy system with very specific customization needs, that's also a good reason to stick with something more basic. Or perhaps you don't need validation and serialization and async support out of the box.
The Important Caution: Data Coupling
Now there's one thing you need to be careful of when using SQLModel. So the starting point is that you combine your database schema with validation and the structure of what your API expects and sends back as a response. However, the question is whether you want those things to actually be so coupled. It potentially leads to problems, right? Such as accidentally sharing things via your API that were supposed to be internal only, like sensitive user data, access keys, and so on. So even though you may be tempted to keep them the same like I did with the hero example, it also makes a lot of sense to separate those two, to separate the database structure from the API interface. This also forces you to be explicit about what your API exposes.
Now to be clear, that's not something that SQLModel prohibits. You can actually simply define a separate Pydantic model and not set table to true to do this. So here I have the SQLModel documentation. As you can see, it shows that we can have a Hero SQLModel which is a database table, but we also have a HeroCreate model and a HeroPublic model, which is I think the response model that they use in this particular example. So it is definitely possible to do this. And if you want, you can even use inheritance and combine models that are and aren't tables to furthermore reduce duplication. Because of course, in this particular example, you see that we have again these three hero classes, but you can actually use multiple models with inheritance. So you could have a herobase model like we have here, but then if we scroll down, you see that we have a hero table model which actually has an ID and that table model inherits from HeroBase. But be careful again, because this will of course increase coupling if you're using inheritance in this way.
Conclusion
But what do you think? Do you think it's a good thing to keep database schema and API interface separated, or do you think we should just make those the same things and just be a little bit extra careful? Do you have any other suggestions for using SQLModel that you came up with in your own projects? Let me know in the comments below.
So in short, I think it's a really great tool for cutting down on boilerplate code, making your life as a developer easier, especially if you're working with FastAPI. And talking about that, writing tests for your API is incredibly important, and there's a particular way to do it properly in FastAPI. Check out this video next where I show you exactly how to set that up. Thanks for watching and see you next time.
