Introduction

EllarSQL Module adds support for SQLAlchemy and Alembic package to your Ellar application

Installation

$(venv) pip install ellar-sql

This library was inspired by Flask-SQLAlchemy

Features

• Migration
• Single/Multiple Database
• Pagination
• Compatible with SQLAlchemy tools

Usage

In your ellar application, create a module called db or any name of your choice,

ellar create-module db

Then, in models/base.py define your model base as shown below:

# db/models/base.pyfromdatetimeimportdatetimefromsqlalchemyimportDateTime, funcfromsqlalchemy.ormimportMapped, mapped_columnfromellar_sql.modelimportModelclassBase(Model):
__base_config__= {'as_base': True}
__database__='default'created_date: Mapped[datetime] =mapped_column(
"created_date", DateTime, default=datetime.utcnow, nullable=False
)
time_updated: Mapped[datetime] =mapped_column(
"time_updated", DateTime, nullable=False, default=datetime.utcnow, onupdate=func.now()
)

Use Base to create other models, like users in User in

# db/models/users.pyfromsqlalchemyimportInteger, Stringfromsqlalchemy.ormimportMapped, mapped_columnfrom .baseimportBaseclassUser(Base):
id: Mapped[int] =mapped_column(Integer, primary_key=True)
username: Mapped[str] =mapped_column(String, unique=True, nullable=False)
email: Mapped[str] =mapped_column(String)

Configure Module

# db/module.pyfromellar.appimportAppfromellar.commonimportModule, IApplicationStartupfromellar.coreimportModuleBasefromellar.diimportContainerfromellar_sqlimportEllarSQLAlchemyModule, EllarSQLServicefrom .controllersimportDbController@Module(controllers=[DbController],providers=[],routers=[],modules=[EllarSQLAlchemyModule.setup(databases={'default': 'sqlite:///project.db', }, echo=True, migration_options={'directory': 'my_migrations_folder' },models=['db.models.users'] ) ])classDbModule(ModuleBase, IApplicationStartup):
""" Db Module """asyncdefon_startup(self, app: App) ->None:
db_service=app.injector.get(EllarSQLService)
db_service.create_all()
defregister_providers(self, container: Container) ->None:
"""for more complicated provider registrations, use container.register_instance(...) """

Model Usage

Database session exist at model level and can be accessed through model.get_db_session() eg, User.get_db_session()

# db/models/controllers.pyfromellar.commonimportController, ControllerBase, get, post, BodyfrompydanticimportEmailStrfromsqlalchemyimportselectfrom .models.usersimportUser@ControllerclassDbController(ControllerBase):
@post("/users")asyncdefcreate_user(self, username: Body[str], email: Body[EmailStr]):
session=User.get_db_session()
user=User(username=username, email=email)
session.add(user)
session.commit()
returnuser.dict()
@get("/users/{user_id:int}")defget_user_by_id(self, user_id: int):
session=User.get_db_session()
stmt=select(User).filter(User.id==user_id)
user=session.execute(stmt).scalar()
returnuser.dict()
@get("/users")asyncdefget_all_users(self):
session=User.get_db_session()
stmt=select(User)
rows=session.execute(stmt.offset(0).limit(100)).scalars()
return [row.dict() forrowinrows]

License

Ellar is MIT licensed.