Build FastAPI really fast !!

Part 7: Connect with a database to pull real-time data

Connecting to a database is important to deliver real-time data with API. In FastAPI, you can connect to wide range of database options using either sqlmodel or sqlalchemy library.

In this blog, I will show you how to connect using these two libraries.

1. Connect to SQLite using sqlmodel
2. Connect to Azure using sqlalchemy

SQLite

SQLite is the quickest way to set up and test API requests in your local.

echo=True makes the engine to log all the query statements when querying data from the database.

check_same_thread by default, this value is set to True ; but we need to assign False to handle each query request to run on multiple interacting threads.

Azure SQL

If you or your organization uses cloud based databases, the code snippet below is the perfect way to connect to the database and serve data through an API.

In the code below, I use Azure SQL as my database of choice to build FastAPI — you may use the code for connecting with database options offered by AWS (Amazon Web Services) or GCP (Google Cloud Platform)

Connection URL parameters:

pool_size = 100 — Keeps 100 connections open inside the connection pool

max_overflow = 10 — Number of connections allowed in the connection pool. By default the value is 5.

pool_recycle = 3600 — Connections will be recycled after 1hr . The values are give in seconds timeframe.

pool_timeout = 30 — Number of seconds to wait before giving up on connecting to a pool.

Connection arguments

connect_timeout = 60 — Within 60 seconds a connection needs to be established between SQL server and the database engine. This is a very critical argument that avoids indefinite connection with the server.

isolation_level='READ UNCOMMITTED' Regulates interpretation of various SQL dialects. Other common possible values for this argument include AUTOCOMMIT, REPEATABLE READ, READ COMMITTED, SERIALIZABLE.

Session parameters

autocommit=False persists the commit between SQL dialect and connection pool.

autoflush=False flush occurs before every SQL statement is processed by the Sessionmaker.

bind=engine connects an engine to emit SQL outputs.

I detailed each function so you can choose which options are critical to your setup. Several parameters have default values. For example, if you don’t specify max_overflow = 0 , sqlalchemy will by default assign a value of 5.

Conclusion:

A fully functioning API should be able to pull data from a database in real-time so it will deliver to a front-end application or API as a service.

The code and notes I provided in this blog should equip you to connect with almost all of the cloud database options seemlessly and also experiment locally using SQLite.

In my upcoming posts, I will detail basic and advanced concepts. The full list of topics, both past and upcoming, is below. Thank you for reading !! Feel free to ask questions in the comments.

Part 1: Getting started with FastAPI

Part 2: Concepts needed to build routes

Part 3: Creating GET, POST, PUT, DELETE requests

Part 4: Middleware & Azure application Insights

Part 5: Pydantic data models

Part 6: Define schemas

Part 8: Secure your FastAPI & adding authentication

Part 9: Deploy using docker & AKS

Part 10: Bringing it all together