SQLAlchemy introduces two approaches when declaring tables.

These approaches are referred to as:

1. The Imperative approach
2. The Declarative approach

The difference between the two is their ease of use and code style.

The imperative approach is the original approach that SQLAlchemy took in the first versions. It is still supported and can still work; it exposes more “low-level” details about the tables at the cost of abstraction.

The declarative approach is the modern approach; it provides a good level of abstraction while retaining all of the capabilities of the imperative approach. It is also recommended that developers new to SQLAlchemy use the declarative approach for new projects.

I think it is essential to understand both approaches. As you work with SQLAlchemy, you will most likely have read many code examples found online, many of which will use the imperative approach. Knowing both will allow you to read and understand these code samples and adapt them to your use case.

The imperative approach

As a reminder, the imperative approach is the old version of declaring SQLAlchemy tables and it is recommended that new projects and developers use the declarative approach, either way we will be covering the imperative approach first.

The MetaData class

Since SQLAlchemy is an ORM, it makes sense that there would be some central place where all of the objects defined in the program are stored and mapped, this way SQLAlchemy can connect between these tables using only their names as defined in our code, as well as perform many more operations behind the scenes.

To create a MetaData object we can do the following:

from sqlalchemy import MetaData

metadata_obj = MetaData()

In practice, you would most likely want to have only one MetaData object for an application, since SQLAlchemy relies on the tables in the object for making DDL operations (CREATE, DELETE, etc..) in the correct order.

Creating Tables

So far, we've created tables by issuing queries to our database directly, but we should always strive to have all of our database tables represented as code.

Using the imperative declaration approach, it would look something like this

from sqlalchemy import Table, Column, Integer, String

alchemists_table = Table(
  'alchemists', # The name of the table
  metadata_obj, # The metatada_obj defined earlier
  Column('id', Integer, primary_key=True), # For the sake of simplicity, we will use integer as an ID
  Column('name', String(45)),
  Column('nickname', String(100)),
  Column('age', Integer),
)

As we can see, the Table definitions is quite straightforward.

We are using multiple constructs given to us by SQLAlchemy

1. Table - represents a database table and registers itself with our MetaData object.
2. Column - Represents a column for the Table
3. 1. The column also assigns a column name for itself
   2. Defines the datatype of the column
   3. Provides additional information, such as constraints
3. String and integer - Are imported from SQLAlchemy, and provide information about the datatype constraints
4. 1. The datatype also defines its own limits, such as the length of the underlying VARCHAR.

Reflection

SQLAlchemy provides us with everything we need for reflection of our table.

Reflection is a great tool for database objects, imagine that we were to create a search engine for our application that would allow our users to construct their own queries based on many different objects.

If we would want to allow them to filter based on columns in these tables, doing so dynamically without the ability to reflect on objects at run-time would be possible, but incredibly time consuming.

As an example, lets begin by creating two more tables

from sqlalchemy import ForeignKey

potions_table = Table(
  'potions',
  metadata_obj,
  Column('id', Integer, primary_key=True),
  Column('name', String(100)),
  Column('effect', String(100)),
  Column('duration', Integer),
)

brewed_potions_table = Table(
  'brewed_potions',
  metadata_obj,
  Column('id', Integer, primary_key=True),
  Column('potion_id', ForeignKey("potions.id"), nullable=False),
  Column('alchemist_id', ForeignKey("alchemists.id"), nullable=False),
)

I took this opportunity to introduce the ForeignKey object, which, when used, replaces the datatype that we would normally assign to the column, as the datatype is automatically inferred by the column it points to.

Next, I would like to get some information about my tables and their definition

for table in metadata_obj.tables:
  print(table)

print(alchemists_table.columns)
print(potions_table.columns)
print(brewed_potions_table.columns)

Here we begin by printing the names of all of our tables using the metadata_obj, which tracks the definition of tables internally, and allows us to access all of them through one interface.

We can also list the columns of each of our tables.

If we would want to make this even less hands-on, we could do something like this

for table in metadata_obj.tables:
  table_def = metadata_obj.tables[table]
  print("---")
  print("Table name: ", table_def.name)
  print("Primary key: ", table_def.primary_key)
  print("Columns: ", table_def.columns)

If we would like to get a specific column, we can also

alchemists_table.c.name

Populating the Database

So far, we’ve only written the definitions of our tables but have not actually created the equivalent tables in our database. Thankfully, SQLAlchemy provides us with the basic functionality we need to achieve this.

To push our objects tracked by the metadata_obj to the database, we can do it like this

from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:', echo=True)

metadata_obj.create_all(engine)

As we can see, when using echo=True, SQLAlchemy outputs the raw SQL queries it generates and other information.

The declarative approach

We’ve covered the basics of the imperative approach and will get back to it in later chapters. Now, I believe it is a good time to present you with the modern approach, which is also the recommended approach when beginning a new project using SQLAlchemy.

Defining tables

When defining declarative tables, it is good to think of them as any other class you would create in your application that represents a specific entity.

Defining a Base

As opposed to the MetaData object that we are instantiating in the imperative approach, for the declarative approach, SQLAlchemy handles that for us and instead provides us with a base class called DeclarativeBase

It is recommended that we create our own Base class from it, as we can use it for additional configuration as we see fit.

from sqlalchemy.orm import DeclarativeBase

class BaseModel(DeclarativeBase):
  pass

It is worth noting that, behind the scenes, SQLAlchemy does, in fact, create its own MetaData object, which is accessible to us through our BaseModel and any other model that inherits from it.

print(BaseModel.metadata)

This allows us to do anything that we would want to do with the MetaData object from the imperative approach, using the declarative approach.

CREAT(E)ing a Table

Using the declarative approach, creating a table definition is quite simple

from sqlalchemy.orm import mapped_column, Mapped

class Alchemist(BaseModel):
  __tablename__ = 'alchemists'
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(45), nullable=False)
  nickname: Mapped[str] = mapped_column(String(100))
  age: Mapped[int]

As you can see, with the declarative approach, we defined our tables by

1. Creating a class that inherit from our BaseModel
2. Indicating what our __tablename__ is
3. Defining our columns using the Mapped type annotation and the mapped_column function

the mapped_column function takes many arguments, one of which is primary_key, which will indicate to the migrate_all function to create a primary key constraint on the column and will also be used for relationships.

Mapped[<type>]

Mapped is a generic type annotation.

It is used whenever you pull or push information to the database. When a user selects anything from the database using ORM objects, SQLAlchemy will attempt to convert the database types (such as VARCHAR) to python using the Mapped annotation. This allows the developer to work with native datatypes when working with SQLAlchemy objects, which makes manipulating or reading data much more comfortable.

For simple datatypes (such as str), it is possible to define columns without the mapped_column, and SQLAlchemy will automatically convert these datatypes to their DB equivalent.

In this example, we are using primitive data types, but in reality, the type mapping feature of SQLAlchemy is very extendable and allows for custom types (and other SQLAlchemy models) to be used and defined as required. String literals, enums, etc., can be easily used when defining SQLAlchemy models.

Bonus: The Hybrid Declarative Approach

If you made it all this way down the chapter, you might wonder if there is a way of combining the imperative and declarative ways. Well, there is!

More specifically, SQLAlchemy will accept any Table or FromClause as an argument for the ___table___ property on a declarative model.

As an example, we may have a Table object like so

flasks_table = Table(
"flasks",
  metadata_obj,
  Column('id', Integer, primary_key=True),
  Column('type', String(100)),
  Column('volume', Integer),
)

class Flask(BaseModel):
  __table__ = flasks_table

print(Flask.__table__.columns)

As we can see, if we inspect the table columns of our declarative mode, we will see the columns from the flasks_table object, which means we can access and query on these columns just as we would using any other declarative model.