App-ready databases from Python with SQLModel and FastAPI

Python is ubiquitous for those of us coming into programming from academic research backgrounds, both for generating scientific data and for analysing it. Of course, modern data-driven research only thrives when data is FAIR (findable, accessible, interoperable, and reusable) and traditional relational databases and APIs for interacting with them are a tried-and-true method of ensuring FAIRness. (And in the age of machine learning, the machines need things to be FAIR arguably even more than we do…)

“But I’m a data scientist/computational chemist/bioinformatician!” you protest. “I just (ab)use databases using Python, I don’t make them for someone else to use!” Understandable, but the barrier to entry may be much lower than you think, thanks to two related Python libraries: SQLModel and FastAPI. These powerful tools by developer Sebastián Ramírez (@tiangolo on GitHub) work in concert to make relational databases and APIs much easier to implement for Python natives. Let’s see how they can help…

Disclaimer: OPIG has no relationship with the author of SQLModel and FastAPI beyond using the tools to power the latest-and-greatest version of our flagship Structural Antibody Database (SAbDab2). The author of this post has no relationship either, beyond being extremely grateful for these tools and the work that has gone into them. This blog post, no matter how compelling, does not represent any official endorsement. Do your due diligence on whether SQLModel and/or FastAPI are right for your needs.

SQLModel: Turning Python classes into relational data

SQLModel is a Python library that allows you to define and build database tables (the foundation of relational databases), and the relationships between them, using Python classes. Founded on the Python-to-SQL mapping of SQLAlchemy with many much-appreciated usability features on top, SQLModel empowers those used to thinking in Python classes to quickly and coherently implement a relational database. The advantages:

  • You can implement your object-relational mapping in Python, fitting naturally into both your expertise and your analysis environment.
  • You get not just the database modelling, but type hints and data validation as well (aided by Pydantic).
  • Because you’re already working in Python, your database schema remains readable and intuitive (at least to you), but the SQL underneath ensures compatibility with common database implementations like SQLite and PostgreSQL.

So now you can use Python to build a database, but how do you make it easier for people (and machines) to get at it? Enter FastAPI.

FastAPI: Connecting your database to applications

As you might expect based on their shared provenance, FastAPI and SQLModel play very nicely together. FastAPI is a web API framework in Python that allows you to define API endpoints to your Python methods for building, updating, and reading your database tables. How does this benefit you?

  • Eases implementation of programmatic access to your new database, so other people (or tools, or websites) can make use of it.
  • Provides faster and simpler routes to adding, updating, and querying that data, both for others and for you, using open API standards (OpenAPI and JSON Schema). It even automatically generates some documentation!
  • FastAPI routes are particularly suitable for simple web-based tools or dashboards, reducing the time/workload to implement these (and hence to heighten the impact of your research).
  • Structured, documented API routes using open standards lower the barrier to applying automated tools to your resource to power AI/ML approaches — if you build it, the bots will come…

One small step towards big data

FastAPI and SQLModel make it possible for you to take your research from local project to accessible database while remaining firmly in your Python wheelhouse. However, they (sadly) are not magic! You still need to design your schema carefully, i.e. which data and which objects will be stored, what relationships will be defined, and how to represent these. And depending on your needs (e.g. a web gateway, an app interface), you will need additional software infrastructure to support you, particularly for larger or busier systems.

Again, your own project or application may require a different approach, depending on your local tech stack, the nature of your data, and the intended use case(s). However, if the above sounds useful to you, then have a closer look at both SQLModel and FastAPI, and explore how Python natives might begin to make their research data FAIRer and heighten the impact of their work.

Happy coding! Remember, keep calm, and read the damn error message...

Author