FastSQLModel

Simplie and Fast utility for SQLModel/SQLAlchemy and Alembic

Overview

FastSQLModel is a utility for simplifying the process of using SQLModel/SQLAlchemy and Alembic. It provides a CLI for initializing and managing Alembic migrations, and a set of tools for working with SQLModel and SQLAlchemy models.

Features

  • CLI for Alembic: FastSQLModel provides a CLI for initializing and managing Alembic migrations.
  • SQLModel and SQLAlchemy Models: FastSQLModel provides a set of tools for working with SQLModel and SQLAlchemy models.
  • Database Management: FastSQLModel provides a set of tools for managing the database, including creating, dropping, and querying the database.

Developer Guide

Install FastSQLModel in Development

If you want to make changes to the package, you can install it in development mode. This project uses nbdev for development, so you can make changes to the code and documentation in the notebooks under the nbs/ directory. To find out more about amazing nbdev, visit the nbdev documentation.

To make changes to the package, you can install it in development mode.

# make sure FastSQLModel package is installed in development mode
$ pip install -e .

# make changes under nbs/ directory
# ...

# compile to have changes apply to FastSQLModel
$ nbdev_prepare

Usage

Installation

Install latest from the GitHub repository:

$ pip install git+https://github.com/ndendic/FastSQLModel.git

or from conda

$ conda install -c ndendic FastSQLModel

or from pypi

$ pip install FastSQLModel

To establish a connection to the database, please specify the DATABASE_URL in the .env file.

Documentation

Documentation can be found hosted on this GitHub repository’s pages. Additionally you can find package manager specific guidelines on conda and pypi respectively.

How to use

Create your first model

To create your first model, you can can import the BaseTable class from the FastSQLModel.db module and create a new model by subclassing it. BaseTable is a subclass of SQLModel, so it has all the same features, but it also has a few extra features to help with some standard db operations and 3 extra fields: - id: primary key, default to a uuid4 - created_at: datetime, default to now - updated_at: datetime, default to now, and updated on every save

class BaseTable(SQLModel):
    model_config = ConfigDict(json_encoders={datetime: lambda dt: dt.isoformat()})
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    created_at: datetime = Field(
        default_factory=utc_now,
        sa_type= sa.DateTime(timezone=True),
        sa_column_kwargs={"server_default": sa.func.now()},
        nullable=False,
        title="Created At",
        schema_extra={"icon": "clock", "input_type": "datetime"},
    )
    updated_at: datetime = Field(
        default_factory=utc_now,
        sa_type=sa.DateTime(timezone=True),
        sa_column_kwargs={
            "server_default": sa.func.now(),
            "server_onupdate": sa.func.now(),
        },
        # onupdate=utc_now,
        nullable=False,
        title="Updated At",
        schema_extra={"icon": "clock", "input_type": "datetime"},
    )

Here is an example of how to create a new model using BaseTable

# users.py
from typing import Optional
from sqlmodel import Field
from datetime import datetime
from FastSQLModel.db import BaseTable

class User(BaseTable, table=True):
    name: Optional[str] = Field(nullable=True)
    email: str = Field(nullable=False)
    password: str = Field(nullable=False)
    joined_at: datetime = Field(nullable=False)

Now that you have created your first model, you can use the CLI to initialize and manage Alembic project.

$ fastmodel init

This will create a new Alembic project in the current directory, and create a new .alembic.ini file.

  1. Then make sure to add your models to the migrations/env.py file before running migrations.
# migrations/env.py
from users import User
# ...
  1. Now you can run migrations to prepare the database for your models.
$ fastmodel migrations
  1. And now you can migrate your models to the database.
$ fastmodel migrate

Let’s see how this works

Initialization:

!fastmodel init
Successfully initialized Alembic in migrations directory!
Please make sure to add your models to migrations/env.py file before running 
migrations!

Making migrations

!fastmodel migrations
Generating Alembic migration with message: Pushing changes
DATABASE_URL sqlite:///test.db
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'user'
  Generating /home/ndendic/WebDev/FastSQLModel/nbs/migrations/versions/4d9613e97
  da3_pushing_changes.py ...  done
Migration created successfully!

Migrating changes

!fastmodel migrate
Applying database migrations...
DATABASE_URL sqlite:///test.db
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 4d9613e97da3, Pushing changes
Migrations applied successfully!

Once our table is migrated, we can start adding some data like this.

user = User(name="Homer Simpson", email="homer@simpson.com", password="password", joined_at=datetime.now())
user.save()
user.model_dump()
{'name': 'Homer Simpson',
 'email': 'homer@simpson.com',
 'password': 'password',
 'joined_at': datetime.datetime(2024, 12, 19, 16, 41, 37, 575422),
 'id': UUID('4b3a4311-6103-4694-8954-d6771233bc97'),
 'created_at': datetime.datetime(2024, 12, 19, 15, 41, 37, 576371, tzinfo=datetime.timezone.utc),
 'updated_at': datetime.datetime(2024, 12, 19, 15, 41, 37, 576413, tzinfo=datetime.timezone.utc)}

Let’s get our user by id

homer = User.get(user.id)
if homer:
    print(f"Name: {homer.name}, Email: {homer.email}")
else:
    print("User not found")
Name: Homer Simpson, Email: homer@simpson.com

Or by alternative key value

homer = User.get("homer@simpson.com",alt_key="email")
if homer:
    print(f"Name: {homer.name}, Email: {homer.email}")
else:
    print("User not found")
Name: Homer Simpson, Email: homer@simpson.com

Now let’s modify our record and save it back to our database and retreive back

homer.email = "homer.simpson@simpson.com"
homer.save()
homer = User.get("homer.simpson@simpson.com",alt_key="email")
homer.email
'homer.simpson@simpson.com'

Let’s define a bit more Simprons, this time like this

bart = User()
bart.name = "Bart Simpson"
bart.email = "bart@simpson.com"
bart.password = "password"
bart.joined_at = datetime.now()
bart.save()

bart.name, bart.email
('Bart Simpson', 'bart@simpson.com')

Let’s retrive records in our table. We can simply do that by calling all function

User.all()
[User(name='Homer Simpson', email='homer.simpson@simpson.com', joined_at=datetime.datetime(2024, 12, 19, 16, 41, 32, 766940), id=UUID('fc724993-651f-47ea-a332-e2318e9c09a2'), created_at=datetime.datetime(2024, 12, 19, 15, 41, 32, 784370), updated_at=datetime.datetime(2024, 12, 19, 15, 41, 37, 952971), password='password'),
 User(name='Homer Simpson', email='homer@simpson.com', joined_at=datetime.datetime(2024, 12, 19, 16, 41, 37, 575422), id=UUID('4b3a4311-6103-4694-8954-d6771233bc97'), created_at=datetime.datetime(2024, 12, 19, 15, 41, 37, 589591), updated_at=datetime.datetime(2024, 12, 19, 15, 41, 37, 589608), password='password'),
 User(name='Bart Simpson', email='bart@simpson.com', joined_at=datetime.datetime(2024, 12, 19, 16, 41, 38, 63184), id=UUID('418531a9-c114-4d16-8432-c2d7f899f237'), created_at=datetime.datetime(2024, 12, 19, 15, 41, 38, 66553), updated_at=datetime.datetime(2024, 12, 19, 15, 41, 38, 66583), password='password')]

Here we can see that we have forgot to set some unique values to our fields and prevent duplicates. So let’s remove our duplicates manualy now

First, we can use search to get all the records that contain some character in some of their string fields. This is usefull for filtering records where you’re not sure where the value shuld match.

users = User.search(search_value="Homer")
for user in users:
    print(f"Name: {user.name} , Email: {user.email}, ID: {user.id}")
Name: Homer Simpson , Email: homer@simpson.com, ID: 4b3a4311-6103-4694-8954-d6771233bc97
Name: Homer Simpson , Email: homer.simpson@simpson.com, ID: fc724993-651f-47ea-a332-e2318e9c09a2

You can also set the fields you want to retreive from specific fields using fields argument. This will now not return the instance of the User rable but a list of tuples.

users = User.search(search_value="Simpson", fields=['name','email'])
users
[('Bart Simpson', 'bart@simpson.com'),
 ('Homer Simpson', 'homer@simpson.com'),
 ('Homer Simpson', 'homer.simpson@simpson.com')]

Now let’s retreive our records again

users = User.search(search_value="homer")
users
[User(name='Homer Simpson', email='homer@simpson.com', joined_at=datetime.datetime(2024, 12, 19, 16, 41, 37, 575422), id=UUID('4b3a4311-6103-4694-8954-d6771233bc97'), created_at=datetime.datetime(2024, 12, 19, 15, 41, 37, 589591), updated_at=datetime.datetime(2024, 12, 19, 15, 41, 37, 589608), password='password'),
 User(name='Homer Simpson', email='homer.simpson@simpson.com', joined_at=datetime.datetime(2024, 12, 19, 16, 41, 32, 766940), id=UUID('fc724993-651f-47ea-a332-e2318e9c09a2'), created_at=datetime.datetime(2024, 12, 19, 15, 41, 32, 784370), updated_at=datetime.datetime(2024, 12, 19, 15, 41, 37, 952971), password='password')]

..and remove the first two results using the delete function

for user in users[:len(users)-1]:
    user.delete()

for user in User.all():
    print(user.name)
Homer Simpson
Bart Simpson

we also have the option to filter the records using filter function for a specific model field.

results = User.filter(name="Homer Simpson")
results
[User(name='Homer Simpson', email='homer.simpson@simpson.com', joined_at=datetime.datetime(2024, 12, 19, 16, 41, 32, 766940), id=UUID('fc724993-651f-47ea-a332-e2318e9c09a2'), created_at=datetime.datetime(2024, 12, 19, 15, 41, 32, 784370), updated_at=datetime.datetime(2024, 12, 19, 15, 41, 37, 952971), password='password')]
results = User.filter(email="homer.simpson@simpson.com")
results
[User(name='Homer Simpson', email='homer.simpson@simpson.com', joined_at=datetime.datetime(2024, 12, 19, 16, 41, 32, 766940), id=UUID('fc724993-651f-47ea-a332-e2318e9c09a2'), created_at=datetime.datetime(2024, 12, 19, 15, 41, 32, 784370), updated_at=datetime.datetime(2024, 12, 19, 15, 41, 37, 952971), password='password')]

Similar to search, filter can take the same argumants, like fields, sorting_field and other (for full list navigate to the db section).

results = User.filter(name="Simp",exact_match=False,fields=["name","email"])
results
[('Bart Simpson', 'bart@simpson.com'),
 ('Homer Simpson', 'homer.simpson@simpson.com')]

We can also combine field filters.

results = User.filter(name="simp",email="hom",exact_match=False,fields=["name","email"])
results
[('Homer Simpson', 'homer.simpson@simpson.com')]

For more deatails visit related docs for SQLModel and Alembic