# Objectifying our database with SQLAlchemy ORM

By Yonatan Vega · September 24, 2025

> Source: https://fullstack.rocks/article/objectifying-our-database-with-sqlalchemy-orm

![A friendly covet of witches standing around a green glowing orb.](https://storage.googleapis.com/fullstack-rocks-media/objectifying-database-6eecd690924a.webp)

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:

```python
from sqlalchemy import MetaData

metadata_obj = MetaData()
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/objectifying-our-database-with-sqlalchemy-orm#code-6ac165385e21826463286887)_

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.

> **Note:** Creating multiple MetaData objects can be beneficial in the case of a single application with multiple sets of tables that are not related to each other.
>
> At the same time, managing multiple sets of MetaData objects can be time-consuming and prone to errors.
>
> It may be a good idea to consider the architecture of such an application before investing time into managing multiple instances of MetaData objects.

## 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

```python
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),
)
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/objectifying-our-database-with-sqlalchemy-orm#code-6ac165385e21826463286889)_

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` 
    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
    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.

> **Note:** Reflection occurs when an object provides all of the necessary information for the program to make decisions based on internals, such as data types and object types.
>
> It is usually used in `metaprogramming`, where a program is required to reflect on another program (or itself) and make decisions, analyze or even manipulate itself at run-time.

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

```python
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),
)
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/objectifying-our-database-with-sqlalchemy-orm#code-6ac165385e2182646328688b)_

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

```python
for table in metadata_obj.tables:
  print(table)

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

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/objectifying-our-database-with-sqlalchemy-orm#code-6ac165385e2182646328688c)_

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

```python
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)
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/objectifying-our-database-with-sqlalchemy-orm#code-6ac165385e2182646328688d)_

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

```python
alchemists_table.c.name
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/objectifying-our-database-with-sqlalchemy-orm#code-6ac165385e2182646328688e)_

## 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

```python
from sqlalchemy import create_engine

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

metadata_obj.create_all(engine)
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/objectifying-our-database-with-sqlalchemy-orm#code-6ac165385e2182646328688f)_

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

> **Note:** While this feature is convenient, it is hardly scalable.
>
> As your application grows, you might want to have more control over which DDL operations you perform and when. 
>
> Features like rolling back the creation of a table or pushing changes to tables in production exist with other open-source projects, such as `alembic`, which is tightly integrated with SQLAlchemy and provides much more flexibility when it comes to migrations.

# 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.

```python
from sqlalchemy.orm import DeclarativeBase

class BaseModel(DeclarativeBase):
  pass
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/objectifying-our-database-with-sqlalchemy-orm#code-6ac165385e21826463286891)_

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.

```python
print(BaseModel.metadata)
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/objectifying-our-database-with-sqlalchemy-orm#code-6ac165385e21826463286892)_

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

```python
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]
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/objectifying-our-database-with-sqlalchemy-orm#code-6ac165385e21826463286893)_

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.

> **Note:** Using the `Mapped` typing annotation is completely optional.
>
> When using the `mapped_column` function, explicit datatypes can be passed to it directly (such as `String` and `Integer`), and vice versa.
>
> If the column does not require any additional constraints, such as `nullable=false`, it may be omitted. 
>
> Personally, I prefer to use `mapped_column` anyway, just to keep all of my columns defined in the same manner.

# 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.

> **Note:** About `FromClause`.
>
> SQLAlchemy represents anything that can be used as a `FROM` clause for an `SQL SELECT` as a `FromClause`.
>
> This includes `JOIN`, `Subquery` and even collections of `Column` elements.
>
> This means that using SQLAlchemy, you can create classes inheriting from `DeclarativeBase` that do not even have to represent a “table”
>
> You can create declarative models for custom `SQL Function` results, collections, and more, which is a feature that creates even more possibilities for levels of abstraction and reusability.

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

```python
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)
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/objectifying-our-database-with-sqlalchemy-orm#code-6ac165385e21826463286896)_

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.

> **Note:** As we delve more deeply into advanced topics of SQLAlchemy, we will explore more possibilities for hybrid declarative models.