Add support for database migrations with alembic

[itay-dar-lmnd]

Is your feature request related to a problem? Please describe.

As applications evolve, changes in the database structure are often required, and managing these changes across different environments and different instances of the database can become challenging. Currently, PyNest users handle database schema changes manually, which is error-prone and not scalable.

Describe the solution you'd like

Implementing database migration support using Alembic could streamline this process. Alembic is a lightweight database migration tool for SQLAlchemy and is widely used in the Python community.

Integration of Alembic would allow developers to:

• Autogenerate migration scripts for each change in the SQLAlchemy database schema.
• Upgrade and downgrade scripts to apply or revert a database schema change, ensuring consistency across all environments.
• Maintain a version history of database schemas, which is crucial for safe deployments and rollbacks.

Proposed Implementation

1. Alembic Configuration: Set up Alembic in the PyNest project, including the alembic.ini file for configuration and the env.py script to specify the database connection and other settings.

2. Migration Scripts: Introduce commands to generate automatic migration scripts based on changes detected in the SQLAlchemy models. These scripts should be reviewed and edited as necessary before being applied.

3. Documentation: Update the project documentation to include detailed instructions on creating migration scripts, applying migrations to the database, and common Alembic commands and practices.

4. Testing: Ensure robust testing procedures are in place for migrations, possibly in a staging environment before deployment, to ensure that changes will not adversely affect the current application.

Additional context

Implementing Alembic migrations would make the PyNest framework more robust and production-ready, simplifying schema change management and reducing the risk of database-related errors during deployment.

We believe this feature is in line with PyNest's goals of scalability and maintainability, empowering developers to handle database changes more efficiently and reliably.

Request for Contributors

We welcome contributions from the community for this enhancement. Please feel free to discuss your approach, propose changes to the proposed implementation, or submit a PR addressing this issue.