Build a multi-tenant application with Python and Nile

In this tutorial, you will learn about Nile's tenant virtualization features, while building a todo list application with Python, FastAPI, SQLAlchemy, React and Vite.

1. Create a database

Sign up for an invite to Nile if you don't have one already
You should see a welcome message. Click on "Lets get started"
Give your workspace and database names, or you can accept the default auto-generated names. In order to complete this quickstart in a browser, make sure you select to “Use Token in Browser”.

2. Create a table

After you created a database, you will land in Nile's query editor. For our todo list application, we'll need tables to store tenants, users and todos. Tenants and users already exists in Nile, they are built-in tables and you can see them in the list on the left side of the screen. We'll just need to create a table for todos.

create table todos (
    id uuid DEFAULT (gen_random_uuid()),
    tenant_id uuid,
    title varchar(256),
    complete boolean);

You will see the new table in the panel on the left side of the screen, and you can expand it to view the columns.

See the tenant_id column? By specifying this column, You are making the table tenant aware. The rows in it will belong to specific tenants. If you leave it out, the table is considered shared, more on this later. Creating a table in Nile's admin dashboard

3. Get credentials

In the left-hand menu, click on "Settings" and then select "Credentials". Generate credentials and keep them somewhere safe. These give you access to the database.

4. Set the environment

Enough GUI for now. Let's get to some code.

If you haven't cloned this repository yet, now will be an excellent time to do so.

git clone https://github.com/niledatabase/niledatabase
cd niledatabase/examples/quickstart/python

Copy .env.example to .env and fill in the details of your Nile DB. The ones you copied and kept safe in step 3.

It should look something like this:

DATABASE_URL=postgresql://user:password@db.thenile.dev:5432/mydb
LOG_LEVEL=DEBUG
SECRET_KEY = "09d25e094faa6ca2556c818166b7a9563b93f7099f6f0f4caa6cf63b88e8d3e7"
ALGORITHM = "HS256"
ACCESS_TOKEN_EXPIRE_MINUTES = 30

Optional, but recommended, step is to set up a virtual Python environment:

python -m venv venv
source venv/bin/activate

Then, install dependencies:

pip install -r requirements.txt

5. Run the application

If you'd like to use the app with the UI, you'll want to build the UI assets first:

cd ui
npm install
npm run build

Then start the Python webapp:

uvicorn main:app --reload

Go to http://localhost:8000 in a browser to see the app.

You can try a few things in the app:

Sign up as a new user
Create a tenant
Create a todo task

You can also use the API directly:

# login
curl -c cookies -X POST 'http://localhost:8000/api/login' \
--header 'Content-Type: application/json' \
--data-raw '{"email":"test9@pytest.org","password":"foobar"}'

# create tenant
curl -b cookies -X POST 'localhost:8000/api/tenants' \
--header 'Content-Type: application/json' \
--data-raw '{"name":"my first customer"}'

# list tenants
curl -b cookies -X GET 'http://localhost:8000/api/tenants'

# create todo for a tenant (make sure you replace the tenant ID with the one you got from the previous step)
curl -b cookies -X POST \
  'http://localhost:8000/api/todos' \
  --header 'Content-Type: application/json' \
  --header 'X-Tenant-Id: 3c9bfcd0-7702-4e0e-b3f0-4e84221e20a7' \
  --data-raw '{"title": "feed the cat", "complete": false}'

# list todos for a tenant (make sure you replace the tenant ID with the one you got from the previous step)
curl  -b cookies -X GET \
  --header 'X-Tenant-Id: 3c9bfcd0-7702-4e0e-b3f0-4e84221e20a7' \
  'http://localhost:8000/api/todos'

6. Check the data in Nile

Go back to the Nile query editor and see the data you created from the app.

SELECT tenants.name, title, complete
FROM todos join tenants on tenants.id = todos.tenant_id;

You should see all the todos you created, and the tenants they belong to.

7. How does it work?

The app uses FastAPI, a modern Python web framework, and SQLAlchemy, a popular ORM. The app is built with tenants in mind, and it uses Nile's tenant context to isolate data between tenants.

main.py is the entry point of the app. It sets up the FastAPI app, registers the middleware and has all the routes.

The first thing we do in the app is set up the tenant middleware. The TenantAwareMiddleware is defined in middleware.py, it is a simple middleware that reads the X-Tenant-Id header and sets the tenant context for the request. This is how we know which tenant the request is for.

app = FastAPI()
app.add_middleware(TenantAwareMiddleware)

The middleware runs before any request is processed. But not every request has a tenant context. For example, login or create_tenant routes doesn't need a tenant context. Requests that don't have a tenant context are considered to be global since they are performed on the database as a whole, not in the virtual database for a specific tenant.

To handle a request in the global context, we use a global session. This is a session that doesn't have a tenant context. For example to create a new tenant:

@app.post("/api/tenants")
async def create_tenant(tenant:Tenant, request: Request, session = Depends(get_global_session)):
    session.add(tenant)
    session.commit()
    return tenant

To handle a request in the tenant context, we use a tenant session. This is a session that has a tenant context. For example to list todos:

@app.get("/api/todos")
async def get_todos(session = Depends(get_tenant_session)):
    todos = session.query(Todo).all()
    return todos

This looks like it could return all todos from all tenants, but it doesn't. The get_tenant_session function sets the tenant context for the session, and the query is executed in the virtual database of the tenant.

The last piece of the puzzle is the get_tenant_session function. It is defined in db.py and is responsible for creating the session with the correct context.

def get_tenant_session():
    session = Session(bind=engine, expire_on_commit=False)
    try:
        tenant_id = get_tenant_id()
        user_id = get_user_id()
        session.execute(text(f"SET nile.tenant_id='{tenant_id}';"))
        session.execute(text(f"SET nile.user_id='{user_id}';"))
        yield session
    except:
        session.rollback()
        raise
    finally:
        session.execute(text("RESET nile.user_id;"))
        session.execute(text("RESET nile.tenant_id;"))
        session.commit()
        pass

We are setting both the user and tenant context in the session. This is important for security and isolation. The user context is used to check if the user has access to the tenant, and the tenant context is used to isolate the data.

Note that we are using FastAPI dependency injection to get the session in the route handlers. This is a powerful feature of FastAPI that makes it easy to manage resources like sessions. The yield keyword is used to return the session to the caller, and the finally block is used to clean up the session after the request is processed.

And this is it. Thats all we need to do to build a multi-tenant app with Nile, FastAPI and SQLAlchemy.

8. Looking good!

🏆 Tada! You have learned the basic Nile concepts:

Tenant aware tables
Tenant context
Tenant isolation