prefer-identity

problem#

Serial types make permissions and schema management difficult. Identity columns are standard SQL and have more features and better usability.

solution#

Instead of:

create table app.users
(
    id  bigserial
)

Use:

create table app.users
(
    id  bigint generated by default as identity primary key
)

solution for alembic and sqlalchemy#

As of sqlalchemy 1.4, use:

# models.py
import sqlalchemy as sa

class User(BaseModel):
    id = sa.Column(sa.Integer, sa.Identity(), primary_key=True)

For sqlalchemy 1.3 or prior built-in support for rendering of IDENTITY is not available yet, however the following compilation hook may be used to replace occurrences of SERIAL with IDENTITY. See the SQLAlchemy docs for more information.

from sqlalchemy.schema import CreateColumn
from sqlalchemy.ext.compiler import compiles

@compiles(CreateColumn, "postgresql")
def use_identity(element, compiler, **kw):
    text = compiler.visit_create_column(element, **kw)
    text = text.replace("SERIAL", "INT GENERATED BY DEFAULT AS IDENTITY")
    return text

problem#

solution#

solution for alembic and sqlalchemy#

links#