SQLAlchemy violates not-null "id" on insert

Question

I try to insert in a PostgreSQL table but it fails because not-null contraint on ID column even though my "id" is set to auto-increment. I am not sure what is wrong with my code. I followed this tutorial. For more details, here is the details of my code:

######## entity model definition

from sqlalchemy import Column, INTEGER, VARCHAR, BOOLEAN, ForeignKey
from sqlalchemy.ext.declarative import as_declarative

@as_declarative()
class OrganizationEntity:
    __tablename__ = "organization"

    id = Column(INTEGER, primary_key=True, autoincrement=True)
    name = Column(VARCHAR, nullable=False)
    enabled = Column(BOOLEAN, default=False)
    created_by = Column(VARCHAR, nullable=False)
    networks = relationship("NetworkEntity", back_populates="organization")

@as_declarative()
class NetworkEntity:
    __tablename__ = "network"

    id = Column(INTEGER, primary_key=True, autoincrement=True)
    organization_id = Column(INTEGER, ForeignKey("organization.id"))
    name = Column(VARCHAR, nullable=False)
    organization = relationship("OrganizationEntity", back_populates="networks")

############# my repository class

from injector import inject

from app.core.database import DbConnection
from app.models import OrganizationEntity
from app.schemas.organization_schema import Organization

@inject
class OrganizationRepository:

    def __init__(self, database: DbConnection):
        self._session_factory = database.session

    def create_organization(self, name: str, enabled: bool, created_by: str) -> Organization:
        with self._session_factory() as session:
            _organization = OrganizationEntity(name=name, enabled=enabled, created_by=created_by)
            session.add(_organization)
            session.commit()
            session.refresh(_organization)
            return _organization

...

Here is the database script

CREATE TABLE organization (
    id INTEGER NOT NULL, 
    name VARCHAR NOT NULL, 
    enabled BOOLEAN, 
    created_by VARCHAR NOT NULL
);

ALTER TABLE organization ADD CONSTRAINT pk_organization PRIMARY KEY (id);

CREATE TABLE network (
    id INTEGER NOT NULL, 
    organization_id INTEGER NOT NULL, 
    name VARCHAR NOT NULL, 
    created_by VARCHAR NOT NULL
);

ALTER TABLE network ADD CONSTRAINT pk_network PRIMARY KEY (id);

ALTER TABLE network ADD CONSTRAINT fk_network_organization FOREIGN KEY(organization_id) REFERENCES organization (id);

And here is my ERROR that I have:

2024-02-17 15:11:12,686 INFO sqlalchemy.engine.Engine INSERT INTO organization (name, enabled, created_by) VALUES (%(name)s, %(enabled)s, %(created_by)s) RETURNING organization.id
2024-02-17 15:11:12,686 INFO sqlalchemy.engine.Engine [generated in 0.00016s] {'name': 'test', 'enabled': True, 'created_by': 'anonymous'}
(psycopg2.errors.NotNullViolation) null value in column "id" of relation "organization" violates not-null constraint
DETAIL:  Failing row contains (null, test, t, anonymous)

To not assume that other field is null, I changed my repository for the tests but still not work:

with self._session_factory() as session:
            _organization = OrganizationEntity(name="hello", enabled=True, created_by="fakeuser")
            session.add(_organization)
            session.commit()
            session.refresh(_organization)
            return _organization

Something weird, it is working when I remove relationship between organization and network.

Finally, It is also working if I specify the id like this, but the thing is I want to my id to be auto-increment without specify it

_organization = OrganizationEntity(name="hello", enabled=True, created_by="fakeuser")

Try to insert in PostgreSQL table using SQLAlchemy with Python

Answer 1

The CREATE TABLE in your database script does not match your SQLAlchemy model. Your script does

CREATE TABLE organization (
    id INTEGER NOT NULL, 
    name VARCHAR NOT NULL, 
    enabled BOOLEAN, 
    created_by VARCHAR NOT NULL
);
ALTER TABLE organization ADD CONSTRAINT pk_organization PRIMARY KEY (id);

which does not create a table with an autoincrement primary key. If you had used SQLAlchemy to create the table from your model definition it would have done

CREATE TABLE organization (
    id SERIAL NOT NULL, 
    name VARCHAR NOT NULL, 
    enabled BOOLEAN, 
    created_by VARCHAR NOT NULL, 
    PRIMARY KEY (id)
)

The model definition has id = Column(INTEGER, primary_key=True, autoincrement=True) which tells (the postgresql dialect of) SQLAlchemy to

1. create the "id" column as SERIAL instead of INTEGER if MetaData.create_all() is used to create the table, and
2. omit the "id" column from the INSERT when creating a new row (and use RETURNING to retrieve the autoincrement value).

When adding a new row (ORM object) SQLAlchemy does omit the "id" column (based on the model definition), but the "id" column in the table is not configured for autoincrement=True behaviour. PostgreSQL tries to insert null into that column and it fails because the column in the table is defined as NOT NULL.

TL;DR: Fix (or drop and re-create) your tables in the database to match your SQLAlchemy model definitions.