ORMagic 0.11.0

Simple ORM based on Pydantic and SQLite with minimalistic API

ORMagic - Simple ORM for Python

The main goal of ORMagic is to provide a simple and easy-to-use ORM for Python, that is easy to understand and use, while still providing the necessary features to interact with a database. The library is in the early stages of development, so it is not recommended to use it in production. Is based on the Pydantic model and extends it with the ability to save, read, update and delete data from a SQLite database.

Installation

pip install ORMagic

Usage

Define a model

To define a model, create a class that inherits from DBModel and define the fields using Pydantic's field types.

from ormagic import DBModel

class User(DBModel):
    name: str
    age: int

# Create the table in the database
User.create_table()

Save, read, update and delete data

# Save data to the database, this will create a new record or update an existing one if the primary key is already present
user = User(name="John", age=30)
user.save()

# Read data from the database
user = User.get(id=1)
>>> User(id=1, name='John', age=30)

# Read all data from the database
users = User.all()
>>> [User(id=1, name='John', age=30), User(id=2, name='Alice', age=25), ...]

# Delete data from the database
user.delete()

# Update data
user = User.get(id=1)
user.age = 31
user.save()

# Filter data and retrieve multiple records
users = User.filter(age=31)
>>> [User(id=1, name='John', age=31), User(id=2, name='Alice', age=31), ...]

Define foreign keys

To define a foreign key, use other models as fields in the model. By default, the foreign key will be set to CASCADE, but you can change it by setting the on_delete parameter of the pydantic field to one of the following values: CASCADE, SET NULL, RESTRICT, SET DEFAULT, NO ACTION.

from ormagic import DBModel

class User(DBModel):
    name: str

class Post(DBModel):
    title: str
    content: str
    user: User # Define a foreign key with default on_delete=CASCADE

User.create_table()
Post.create_table()

user = User(name="John")
user.save()

Post(title="Hello", content="World", user=user).save()

# You can also save child models with new parent object in one step, this will save the parent object first and then the child object
Post(title="Hello", content="World", user=User(name="Alice")).save()

Define foreign key with custom on_delete

from ormagic import DBModel, DBField

class User(DBModel):
    name: str

class Post(DBModel):
    title: str
    content: str
    user: User = DBField(on_delete="CASCADE")
    user: User = DBField(on_delete="RESTRICT")
    user: User = DBField(on_delete="NO ACTION")
    user: User = DBField(on_delete="SET DEFAULT", default=1)
    user: User = DBField(on_delete="SET NULL", default=None)

User.create_table()
Post.create_table()

Unique constraints

To define a unique constraint, use the unique parameter set to True in the Pydantic field.

from ormagic import DBModel, DBField

class User(DBModel):
    name: str
    email: str = DBField(unique=True)

You can also use the unique parameter to define one to one relationships between tables.

from ormagic import DBModel, DBField

class User(DBModel):
    name: str

class UserProfile(DBModel):
    user: User = DBField(unique=True)
    bio: str

Deleting and updating tables

To delete a table, use the drop_table method.

User.drop_table()

To update a table, use the update_table method. (Not implemented yet)

User.update_table()

There are some restrictions on updating tables:

• The new column cannot have unique or primary_key set to True.
• The new column needs to have a default value or set as optional.
• You can rename multiple columns at once and add multiple columns at once but you cannot mix this two operations in one call.

Many-to-many relationships

To define a many-to-many relationship, use list of other model as a field in the model.

from ormagic import DBModel

class Player(DBModel):
    name: str
    teams: list["Team"] = []

class Team(DBModel):
    name: str
    players: list[Player] = []

Player.create_table()
Team.create_table()

player0 = Player(name="Messi").save()
player1 = Player(name="Ronaldo").save()

Team(name="Barcelona", players=[player0, player1]).save()

Team.get(id=1)
>>> Team(id=1, name='Barcelona', players=[Player(id=1, name='Messi'), Player(id=2, name='Ronaldo')])

Filtering data

To filter data and retrieve multiple records, use the filter method. There are several filter options available:

Equal

User.filter(name="John")

Not equal

User.filter(name__ne="John")

Greater than

User.filter(age__gt=30)

Greater than or equal

User.filter(age__gte=30)

Less than

User.filter(age__lt=30)

Less than or equal

User.filter(age__lte=30)

Like (Pattern matching with % and _)

User.filter(name__like="%Cat%")

Not like (Pattern matching with % and _)

User.filter(name__nlike="%Cat%")

In (List of values)

User.filter(name__in=["John", "Alice"])

Not in (List of values)

User.filter(name__nin=["John", "Alice"])

Between (Two values)

User.filter(age__between=[30, 40])

Not between (Two values)

User.filter(age__nbetween=[30, 40])

Order by

To order the results, use the filter or all method with the order_by parameter.

User.filter(order_by="age")

To order the results in descending order, use the - sign before the field name.

User.all(order_by="-age")

You can also order by multiple fields and mix them with filters.

User.filter(name="John", order_by=["age", "-name"])

Limit and offset

To limit the number of results, use the limit parameter.

User.all(limit=10)

You can also use the offset parameter to skip a certain number of results to implement pagination.

User.all(limit=10, offset=10)

You can also use the limit and offset parameters with filters and order by.

User.filter(age__between=[30, 40], order_by="age", limit=10, offset=10)

Integration with FastAPI

Because ORMagic is based on Pydantic, it can be easily integrated with FastAPI. Below is an example of how to use ORMagic with FastAPI to create a simple CRUD REST API.

from fastapi import FastAPI
from ormagic import DBModel

app = FastAPI()

class User(DBModel):
    name: str
    age: int

User.create_table()

@app.post("/users/")
def create_user(user: User):
    return user.save()

@app.get("/users/")
def read_users():
    return User.all()

@app.get("/users/{id}")
def read_user(id: int):
    return User.get(id=id)

@app.put("/users/{id}")
def update_user(id: int, user: User):
    user.id = id
    return user.save()

@app.delete("/users/{id}")
def delete_user(id: int):
    User.get(id=id).delete()
    return {"message": "User deleted"}

Features and Roadmap

• Define table schema using Pydantic models
• Basic CRUD operations
  • Save data to the database
  • Read data from the database
  • Update data in the database
  • Delete data from the database
• Relationships between tables
  • One-to-many
    • Create a tables with a foreign key
    • Save data with a foreign key
    • Read data with a foreign key
    • Update data with a foreign key
    • Delete data with a foreign key
      • Cascade
      • Set null
      • Restrict
      • Set default
      • No action
  • One-to-one
  • Many-to-many
• Unique constraints
• Remove table
• Read all data from the database
• Filter data and retrieve multiple records
  • Equal
  • Not equal
  • Greater than
  • Greater than or equal
  • Less than
  • Less than or equal
  • Like (Pattern matching with % and _)
  • Not like (Pattern matching with % and _)
  • In (List of values)
  • Not in (List of values)
  • Between (Two values)
  • Not between (Two values)
• Protect against SQL injection
• Order by
• Limit and offset
• Update table schema
  • Add new column
  • Rename column
  • Drop column
• Custom primary key
• Bulk operations (save, update, delete)
• Migrations

Changelog

Changes for each release are thoroughly documented in release notes

License

This project is licensed under the terms of the MIT license

Contributing

Contributions are welcome! Feel free to open an issue or submit a pull request.

Why?

There are many ORMs for Python, but most of them are too complex or have too many features that are not needed for simple projects.