Why Flask SQL Alchemy allowing save of primary key of None?

Question

Flask SQL Alchemy saves to DB despite a Null Primary Key and the addition of nullable=False to the Primary Key field (even though it should not be necessary to add it).

I am assigning the id and not using the normal auto-increment or whatever comes with SQL Alchemy.

model.py

class User(db.Model):
  id = db.Column(db.Integer, primary_key=True, unique=True, nullable=False)

  # passing in the ID manually
  @classmethod
  def new(cls, sender_id):
      try:
         db.create_all()
         d = cls()
         d.id = sender_id
         return d
      except Exception as e:
         print('Error in User new:', e)

   def insert(self):
      try:
          db.session.add(self)
          db.session.commit()
          print('INSERT OKAY')
      except Exception as e:
          print('RollBack', e)
          db.session.rollback()

test.py

#FLASK_ENV = 'dev_testing'
#----SETUP
# load env variables
def setup_testing_environment():
    load_dotenv(find_dotenv(".env", raise_error_if_not_found=True)

#test setup testing DB
def create_test_app():
    try:
        app = Flask(__name__)
        app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
        if os.environ['FLASK_ENV'] == 'development' or os.environ['FLASK_ENV'] == 'dev_testing':
            setup_testing_environment()
        app.config['SQLALCHEMY_DATABASE_URI'] = "sqlite:///:memory:"
        return app
    except Exception as e:
        print('Error in create_test_app', e)


#---- ACTUAL TEST
def test_should_fail(unittest.testcase):
    #assign DB and create context for test to run
    app = create_test_app()
    with app.app_context():
       db.init_app(app)
       # make ID value None;
       user = User.new(None)
       # <User id=None>
       user.insert()
      # fails - user *is* being added to DB 
       assert user in not db.session

       #end test
       db.session.remove()
       db.drop_all()

No rollback is occurring. No error are being shown by SQL Alchemy. The User is being saved to the DB but it is impossible to look up since there is no ID. The User table is completely empty.

Why is SQL Alchemy not catching this? How can I assign the ID so that it will work?

Does this answer your question? [How does \`nullable=False\` work in SQLAlchemy](https://stackoverflow.com/questions/33192062/how-does-nullable-false-work-in-sqlalchemy) — lee-pai-long, Nov 26 '19 at 00:09
No I don't think so b/c the DB is getting created and destroyed at the time with `sqlite:///:memory:` and so doesn't exist before this test runs. Therefore no table with a `nullable=True` can exist. — Mote Zart, Nov 26 '19 at 00:10

Ilja Everilä · Accepted Answer · 2019-11-26T08:53:44.143

The author of SQLAlchemy has noted here that

SQLAlchemy doesn't build this in as it defers to the DBAPI/database as the best and most efficient source of validation and coercion of values.

In other words enforcement of NOT NULL constraints and such is left to the database, as it should – the database is the single source of truth. You can of course implement validation in your models as well, if required.

In case of a primary key attribute setting it to None on a transient / pending instance is essentially a no-operation, and has the same effect as not setting it at all, and so the default is used. The default setting for autoincrement in Column is "auto", which means that if the model has a single column integer primary key with no explicit defaults it receives auto incrementing behaviour. If you'll enable logging, you'll see that the emitted query is something like

INSERT INTO user DEFAULT VALUES

In other words in your case your instances receive the auto incremented primary key value, when flushed to the database. Were you to access user.id after db.commit(), you would get the generated primary key value.

Finally,

# fails - user *is* being added to DB 
assert user not in db.session

does not assert if the user is in the database or not, but if it is in the current session, though in your test they are indeed also in the database after the call to db.commit(), which implicitly flushes pending state from the session.

It does catch `nullable=False` for other items in other models, if the field is an ordinary, non-Primary Key field. That is my confusion - but I think I understand what U say. It is, in fact, being set and so no error is thrown. I did save it to a persistent DB though, unlike the test with a flush, and checking that the User table had no users. Only a blank table, so not sure what happened. I'll add my own validations then. Thanks for the link. — Mote Zart, Nov 27 '19 at 03:54

Why Flask SQL Alchemy allowing save of primary key of None?

1 Answers1