FastAPI

FastAPI is a modern, fast (high-performance) web framework for building APIs with Python 3.6+ based on standard Python type hints. Connect it to your bit.io database so you can build web services and APIs for your data stored in bit.io.

This document will guide you through the coding of an HTTP API using FastAPI and sqlmodel.

To begin with, lets install the python dependencies:

python -m pip install \
  uvicorn \
  fastapi \
  "uvicorn[standard]" \
  psycopg2-binary \
  sqlmodel

Now create a python file named city.py and add the following code:

from typing import Optional

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

app = FastAPI()

The above code includes the import of required python classes and functions that will be used through the course of this document and instantiation of the FastAPI() class.

Connecting to bit.io with FastAPI

In order to connect to bit.io with FastAPI, you'll need to find your PosgreSQL connection credentials from the bit.io Connect Tab of the database to which you would like to connect. You can find everything you need to know about your PostgreSQL credentials here

The connection string has the following format: "postgresql://{USERNAME}:{PASSWORD}@db.bit.io/{USERNAME}/{DATABASE}". You can copy your connection string directly from the connect tab.

Set is as an environment variable and then reference it in the source code as follows:

engine = create_engine(os.getenv("CONNECTION_STRING"))

Or add it directly to the source code like this:

CONNECTION_STRING = "postgresql://foo:[email protected]/foo/bar"
engine = create_engine(CONNECTION_STRING))

Using FastAPI with bit.io

In this example, we will create an API for adding and reading details about cities from a bit.io table.

Our main entity, which in this example is the City python class (a subclass of SQLModel) that will represent the records of our city table:

class City(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    population: Optional[int] = Field(default=None, index=True)

It defines the id, name and population fields of our city table.

We need our table to be created as soon as our api starts, so lets configure a "startup" event that creates the tables in our database.

def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


@app.on_event("startup")
def on_startup():
    create_db_and_tables()

The api should allow us to insert records into our table through HTTP POST requests, so lets define the POST /city/ handler:

@app.post("/city/")
def create_database(city: City):
    with Session(engine) as session:
        session.add(city)
        session.commit()
        session.refresh(city)
        return city

We also need to retrieve the list of cities in the database, for this lets define the GET /cities/ handler:

@app.get("/cities/")
def read_cities():
    with Session(engine) as session:
        cities = session.exec(select(City)).all()
        return cities

Now proceed to start the api service using uvicorn

$ uvicorn city:app

You should now see the following output in the terminal:

INFO:     Started server process [12727]
INFO:     Waiting for application startup.
INFO:     Application startup complete.
INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)

Your API is available in http://127.0.0.1:8000 and autogenerated OpenAPI interface and docs for the api endpoints on http://127.0.0.1:8000/docs.

Lets proceed to insert some records through the command line to test out the insert functionality of the API.

curl -XPOST \
  -H "Content-Type: application/json" \
  -d '{"id": 1, "name": "San Francisco", "population": 842724}' \
  http://127.0.0.1:8000/city/
  
{"name":"San Francisco","id":1,"population":842724}%                                                                                                                         

curl -XPOST \
  -H "Content-Type: application/json" \
  -d '{"id": 2, "name": "Los Angeles", "population": 3900000}' \
  http://127.0.0.1:8000/city/

{"name":"Los Angeles","id":2,"population":3900000}

Now read them either by going to http://127.0.0.1:8000/cities/ on your browser or executing the following command in your terminal:

curl -XGET http://127.0.0.1:8000/cities/ -s | jq "."
[
  {
    "name": "San Francisco",
    "id": 1,
    "population": 842724
  },
  {
    "name": "Los Angeles",
    "id": 2,
    "population": 3900000
  }
]

Alternatively query for records of city table through bit.io Query Editor.

That's all you need to use your bit.io database from FastAPI, all source code is below for reference:

import os
from typing import Optional

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

app = FastAPI()

engine = create_engine(os.getenv("CONNECTION_STRING"))


class City(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    population: Optional[int] = Field(default=None, index=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


@app.on_event("startup")
def on_startup():
    create_db_and_tables()


@app.post("/city/")
def create_city(city: City):
    with Session(engine) as session:
        session.add(city)
        session.commit()
        session.refresh(city)
        return city
    
    
@app.get("/cities/")
def read_cities():
    with Session(engine) as session:
        cities = session.exec(select(City)).all()
        return cities

Read here for a more in-depth guide on using FastAPI with relational databases.