Migrations fail due to non-null values in new column
Summary
Migrations fail due to new column not allowing null values.
Steps to reproduce
Software versions
- Device model:
- OS version:
- Software versions:
- Browser version:
What is the current bug behavior?
The new preferred_name
column doesn't allow null values, but since there are already users in the table, the creation of a column with this constraint fails.
What is the expected correct behavior?
The current cognito_name
column values get initially inserted into the preferred_name
column.
Relevant logs and/or screenshots
LOGS
ERROR [root] Async startup event failed
Traceback (most recent call last):
File "/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.NotNullViolation: column "preferred_name" of relation "mantik_user" contains null values
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/opt/app.py", line 154, in startup_event
async_startup_event()
File "/opt/app.py", line 133, in async_startup_event
alembic.command.upgrade(alembic_config, "head")
File "/venv/lib/python3.11/site-packages/alembic/command.py", line 398, in upgrade
script.run_env()
File "/venv/lib/python3.11/site-packages/alembic/script/base.py", line 579, in run_env
util.load_python_file(self.dir, "env.py")
File "/venv/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
module = load_module_py(module_id, path)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/venv/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 109, in load_module_py
spec.loader.exec_module(module) # type: ignore
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "<frozen importlib._bootstrap_external>", line 940, in exec_module
File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
File "/opt/alembic/env.py", line 87, in <module>
run_migrations_online()
File "/opt/alembic/env.py", line 81, in run_migrations_online
context.run_migrations()
File "<string>", line 8, in run_migrations
File "/venv/lib/python3.11/site-packages/alembic/runtime/environment.py", line 938, in run_migrations
self.get_context().run_migrations(**kw)
File "/venv/lib/python3.11/site-packages/alembic/runtime/migration.py", line 624, in run_migrations
step.migration_fn(**kw)
File "/opt/alembic/versions/2023-11-06T18-43_1ac0179d65bf_add_and_alter_user_columns.py", line 42, in upgrade
op.add_column(
File "<string>", line 8, in add_column
File "<string>", line 3, in add_column
File "/venv/lib/python3.11/site-packages/alembic/operations/ops.py", line 2126, in add_column
return operations.invoke(op)
^^^^^^^^^^^^^^^^^^^^^
File "/venv/lib/python3.11/site-packages/alembic/operations/base.py", line 393, in invoke
return fn(self, operation)
^^^^^^^^^^^^^^^^^^^
File "/venv/lib/python3.11/site-packages/alembic/operations/toimpl.py", line 168, in add_column
operations.impl.add_column(table_name, column, schema=schema, **kw)
File "/venv/lib/python3.11/site-packages/alembic/ddl/impl.py", line 327, in add_column
self._exec(base.AddColumn(table_name, column, schema=schema))
File "/venv/lib/python3.11/site-packages/alembic/ddl/impl.py", line 200, in _exec
return conn.execute(construct, multiparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1385, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/venv/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
return connection._execute_ddl(
^^^^^^^^^^^^^^^^^^^^^^^^
File "/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1477, in _execute_ddl
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
self._handle_dbapi_exception(
File "/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
util.raise_(
File "/venv/lib/python3.11/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) column "preferred_name" of relation "mantik_user" contains null values
[SQL: ALTER TABLE mantik_user ADD COLUMN preferred_name VARCHAR NOT NULL]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
Possible fixes
- The new
preferred_name
column needs to be filled with the values of each row from the previousname
column (nowcognito_name
)- Maybe postgres/alembic has a feature to to that?
default="cognito_name"
?
- Maybe postgres/alembic has a feature to to that?
- To prevent this in the future, ideally we should populate the database in the CI job that tests the migrations with data from the current database structure (e.g. from dev or with random data)
/cc @thomas_ambrosys /cc @fabian.emmerich