Column Order: Inheritance & Declarative Base

I prefer to have my primary key columns first in a table. I recognise that column order is irrelevant to the performance of the table, but I prefer this for personal aesthetic reasons. However, from SQLAlchemy 2.0.0 there’s a change in the way that column order works with inherited base classes.

Older SQLAlchemy

import sqlalchemy as sql
from sqlalchemy.ext.declarative import as_declarative, declared_attr

from sqlalchemy.schema import CreateTable
from sqlalchemy.dialects import postgresql

@as_declarative()
class Base(object):
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

    id = sql.Column(sql.Integer, primary_key=True)

class Person(Base):
    name = sql.Column(sql.String(128))
    email = sql.Column(sql.String(128))

You can see the column order by looking at Person.__table__.columns or by generating the DDL.

from sqlalchemy.schema import CreateTable
from sqlalchemy.dialects import postgresql

CreateTable(Person.__table__).compile(dialect=postgresql.dialect())

Using older versions of SQLAlchemy (prior to 2.0.0) that gives the following column order:

CREATE TABLE person (
        id SERIAL NOT NULL, 
        name VARCHAR(128), 
        email VARCHAR(128), 
        PRIMARY KEY (id)
)

Newer SQLAlchemy

But if you try the same code with a newer SQLAlchemy then the column order is screwed: the id primary key is last not first!

CREATE TABLE person (
        name VARCHAR(128), 
        email VARCHAR(128), 
        id SERIAL NOT NULL, 
        PRIMARY KEY (id)
)

This is what you need to do:

import sqlalchemy as sql
from sqlalchemy.ext.declarative import as_declarative, declared_attr
from sqlalchemy.orm import mapped_column

@as_declarative()
class Base(object):
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

    id = mapped_column(sql.Integer, primary_key=True, sort_order=-1)


class Person(Base):
    name = mapped_column(sql.String(128))
    email = mapped_column(sql.String(128))

Now the columns are back in the desired order.

CREATE TABLE person (
        id SERIAL NOT NULL, 
        name VARCHAR(128), 
        email VARCHAR(128), 
        PRIMARY KEY (id)
)

The mapped_column() function was introduced in SQLAlchemy 2.0.0 and the sort_order parameter was added in SQLAlchemy 2.0.4. See the corresponding release notes.