Problem in sqlacodegen handling columns with an identity construct.

[soypedroamaya]

I am doing a project with supabase (postgres) and it has recommended me to make my table like this:

create table "public"."sexual_genders" (
"id" smallint generated by default as identity not null,
"gender" text not null
);

CREATE UNIQUE INDEX sexual_genders_gender_key ON public.sexual_genders USING btree (gender);
CREATE UNIQUE INDEX sexual_genders_pkey ON public.sexual_genders USING btree(id);
alter table "public"."sexual_genders" add constraint "sexual_genders_pkey" PRIMARY KEY using index "sexual_genders_pkey";
alter table "public"."sexual_genders" add constraint "sexual_genders_gender_key" UNIQUE using index "sexual_genders_gender_key";

But when executing the ./sqlcodegen.sh script to build the sqlalchemy files, it gave me the following error:

main.py:47: SAWarning: Skipped unsupported reflection of expression-based index users_instance_id_email_idx
metadata.reflect(engine, args.schema, not args.noviews, tables)
Traceback (most recent call last):
File "/bend-calcetinesperdidos/venv-exp/bin/sqlacodegen", line 8, in
sys.exit(main())
File "/bend-calcetinesperdidos/venv-exp/lib/python3.10/site-packages/sqlacodegen/main.py", line 53, in main
generator.render(outfile)
File "/bend-calcetinesperdidos/venv-exp/lib/python3.10/site-packages/sqlacodegen/codegen.py", line 727, in render
rendered_models.append(self.render_class(model))
File "/bend-calcetinesperdidos/venv-exp/lib/python3.10/site-packages/sqlacodegen/codegen.py", line 707, in render_class
self.indentation, attr, self.render_column(column, show_name))
File "/bend-calcetinesperdidos/venv-exp/lib/python3.10/site-packages/sqlacodegen/codegen.py", line 601, in render_column
default_expr = self._get_compiled_expression(column.server_default.arg)
AttributeError: 'Identity' object has no attribute 'arg'

And helping me with ChatGPT tells me the following:

The error you are experiencing seems to be related to a problem in sqlacodegen handling columns with an identity construct. The column with the identity construct in question appears to have an Identity object, but sqlacodegen expects an object with an arg attribute.

This problem may be caused by a specific version of sqlacodegen or SQLAlchemy that does not fully support identity construction.

To fix this problem, you can try the following:

1. Update sqlacodegen and SQLAlchemy to the latest available versions:

   pip install --upgrade sqlacodegen
   pip install --upgrade SQLAlchemy
   

2. If the problem persists even after upgrading, you might consider applying a temporary patch to sqlacodegen. First, locate the codegen.py file within your virtual environment. In your case, it seems to be in the following path:

   /bend-lostsocks/venv-exp/lib/python3.10/site-packages/sqlacodegen/codegen.py
   

   Open the codegen.py file in a text editor and look for the render_column function. Inside this function, add a condition to check if the server_default object is an instance of Identity. If so, skip generating the arg attribute.

   For example, you can modify the corresponding function as follows:

def render_column(self, column, show_name):
kwarg = []
is_sole_pk = column.primary_key and len(column.table.primary_key) == 1
dedicated_fks = [c for c in column.foreign_keys if len(c.constraint.columns) == 1]
is_unique = any(isinstance(c, UniqueConstraint) and set(c.columns) == {column}
for c in column.table.constraints)
is_unique = is_unique or any(i.unique and set(i.columns) == {column}
for i in column.table.indexes)
has_index = any(set(i.columns) == {column} for i in column.table.indexes)
server_default = None

     # Render the column type if there are no foreign keys on it or any of them points back to
     #itself
     render_coltype = not dedicated_fks or any(fk.column is column for fk in dedicated_fks)

     if column.key != column.name:
         kwarg.append('key')
     if column.primary_key:
         kwarg.append('primary_key')
     if not column.nullable and not is_sole_pk:
         kwarg.append('nullable')
     if is_unique:
         column.unique = True
         kwarg.append('unique')
     elif has_index:
         column.index = True
         kwarg.append('index')

     if Computed and isinstance(column.server_default, Computed):
         expression = self._get_compiled_expression(column.server_default.sqltext)

         persist_arg = ''
         if column.server_default.persisted is not None:
             persist_arg = ', persisted={}'.format(column.server_default.persisted)

         server_default = 'Computed({!r}{})'.format(expression, persist_arg)
     elif column.server_default:
         # Add the following condition to check if the server_default object is an Identity instance
         if not isinstance(column.server_default, Identity):
             default_expr = self._get_compiled_expression(column.server_default.arg)
             if '\n' in default_expr:
                 server_default = 'server_default=text("""\\\n{0}""")'.format(default_expr)
             else:
                 default_expr = default_expr.replace('"', '\\"')
                 server_default = 'server_default=text("{0}")'.format(default_expr)

     comment = getattr(column, 'comment', None)
     return 'Column({0})'.format(', '.join(
         ([repr(column.name)] if show_name else []) +
         ([self.render_column_type(column.type)] if render_coltype else []) +
         [self.render_constraint(x) for x in dedicated_fks] +
         [repr(x) for x in column.constraints] +
         ['{0}={1}'.format(k, repr(getattr(column, k))) for k in kwarg] +
         ([server_default] if server_default else []) +
         (['comment={!r}'.format(comment)] if comment and not self.nocomments else [])
     ))

Save the changes and close the file.

3. Run the script again and check if the error has been resolved.

Please note that this fix is only a temporary patch and may not be fully compatible with future versions of sqlacodegen or SQLAlchemy. I suggest you follow the releases of these libraries and upgrade to a version that natively resolves this issue when available.

Results:

And it worked, but it seems appropriate to mention that sqlacodegen is not supporting these postgres features that supabase recommends when using their dashboard studio to build tables.