Welcome to the new section of the course! In this section, you'll learn all you need to do to work with SQLAlchemy and Alembic. You'll learn to model database tables with SQLAlchemy, and to manage database migration from code using Alembic.

You'll learn best practices for designing your SQLAlchemy models, for running your queries, and for using Alembic.

SQLAlchemy is one of the best and most widely used libraries in the entire Python ecosystem. However, it's also one of the most poorly understood, and in my work as a consultant, I often encounter an abundance of anti-patterns when I review codebases. The lessons in this section will set you apart from other developers by teaching you how to avoid all those mistakes and anti-patterns.

SQLAlchemy went through a major release recently with the launch of SQLAlchemy 2.0. In this section, you'll learn to use SQLAlchemy 2.0. This version of SQLAlchemy brings a lot of interesting features and improvements, such as better performance, dataclass mappers, support for asyncio, and a new query API.

In the first lesson of this section, you'll learn about the main patterns that SQLAlchemy implements. There's a link below to an article with a more extensive discussion of the patterns implemented by SQLAlchemy.

The patterns mention in this lesson are:

• Data Mapper: this pattern allows you to have a bi-directional mapping between your database tables and your objects. This is the pattern that allows you to access your database records as code.
• Unit of Work: this pattern ensures the atomicity of your database transactions. SQLAlchemy implements the Unit of Work pattern in its session objects. Whenever you open a new session, SQLAlchemy begins a database transaction, and every database operation you perform within the scope of the session is added to the transaction until you commit. This ensures that all the transactions succeed or fail at the same time.
  
• Active Record: active record is a pattern that allows you to add domain/business logic to your database objects. In SQLAlchemy, this is possible when we use the Declarative way of defining our models. Declarative models are defined as normal Python classes which we can enrich with additional methods. The alternative approach is to use the imperative method.

Active Record is sometimes considered an anti-pattern because it tightly couples your business layer with your data layer. This is sometimes fine, but it makes things hairy when you have complex business logic.

Additional resources

• SQLAlchemy docs
  
• Alembic docs
• Patterns implemented by SQLAlchemy