Error creating DB migration for Plugin model

10caret100 · May 3, 2020, 12:15pm

I have an “Invoices” model for my Indico plugin:

class InvoiceItem(db.Model):

    __tablename__ = 'invoice_items'
    __table_args__ = {'schema': 'plugin_invoices'}

     #: Invoice item ID
    id = db.Column(db.Integer, primary_key=True)
    invoice_id = db.Column(db.Integer, db.ForeignKey('invoices.id'))

    item_number = db.Column(db.Integer)

    currency = db.Column(db.String(3))

class InvoiceType(db.Model):
    __tablename__ = 'invoice_types'
    __table_args__ = {'schema': 'plugin_invoices'}

    #: Invoice type ID
    id = db.Column(db.Integer, primary_key=True)

    invoice_id = db.Column(db.Integer, db.ForeignKey('invoices.id'))
class Invoice(db.Model):
    __tablename__ = 'invoices'
    __table_args__ = {'schema': 'plugin_invoices'}

    #: Invoice ID
    id = db.Column(db.Integer, primary_key=True)

    #: Invoice Number
    sequential_number = db.Column(db.Integer, nullable=False)

    invoice_items = db.relationship("InvoiceItem", backref="invoices")  
    invoice_type = db.relationship("InvoiceType", backref="invoices")  
    
    created_by_id = db.Column(
        db.Integer,
        db.ForeignKey('users.users.id'),
        index=True,
        nullable=False)

    created_on = db.Column(UTCDateTime, nullable=False, default=now_utc)

when I run the following command:

, I get the following error:

No idea how to fix it

Please help!

ThiefMaster · May 3, 2020, 1:55pm

You forgot the schema:

invoice_id = db.Column(db.ForeignKey('plugin_invoices.invoices.id'))

General advice:

You can omit te type (first argument) for FK columns, since SA can automatically use the target column’s type.
Providing a length indicator for db.String() is Postgres is not necessary and usualy not particularly useful.
depending on how the sequential_number works (why can’t you use the ID) you may be able to use logic similar to what we do for friendly_id (sequential numbers scoped to something else, like the event) in the core to auto-generate it
I’d rename created_by_id to creator_id (and the relationship which you probably have from created_by to creator). Using *_by_id for FKs/relationships is a weird naming scheme we only used for a very short time in the core and then ditched it in favor of less awkward-sounding names.

10caret100 · May 3, 2020, 2:03pm

Where did I forget to put:

invoice_id = db.Column(db.ForeignKey('plugin_invoices.invoices.id')) ?

I have it inside both “children” (Item and Type) of Invoice

Note taken regarding the general advise.

Thank you!

ThiefMaster · May 3, 2020, 2:05pm

Right inside your InvoiceItem model:

this needs to be one of these:

invoice_id = db.Column(db.Integer, db.ForeignKey('plugin_invoices.invoices.id'))
# or the shorter version:
invoice_id = db.Column(db.ForeignKey('plugin_invoices.invoices.id'))

10caret100 · May 3, 2020, 2:08pm

Got it… just fixed it.

New model is this:


class InvoiceItem(db.Model):

    __tablename__ = 'invoice_items'
    __table_args__ = {'schema': 'plugin_invoices'}

     #: Invoice item ID
    id = db.Column(db.Integer, primary_key=True)
    invoice_id = db.Column(db.ForeignKey('plugin_invoices.invoices.id'))

    item_number = db.Column(db.Integer)

    currency = db.Column(db.String())

class InvoiceType(db.Model):
    __tablename__ = 'invoice_types'
    __table_args__ = {'schema': 'plugin_invoices'}

    #: Invoice type ID
    id = db.Column(db.Integer, primary_key=True)

    invoice_id = db.Column(db.ForeignKey('plugin_invoices.invoices.id'))
class Invoice(db.Model):
    __tablename__ = 'invoices'
    __table_args__ = {'schema': 'plugin_invoices'}

    #: Invoice ID
    id = db.Column(db.Integer, primary_key=True)

    #: Invoice Number
    sequential_number = db.Column(db.Integer, nullable=False)

    invoice_items = db.relationship("InvoiceItem", backref="invoices")  
    invoice_type = db.relationship("InvoiceType", backref="invoices")  
    
    creator_id = db.Column(
        db.Integer,
        db.ForeignKey('users.users.id'),
        index=True,
        nullable=False)

    created_on = db.Column(UTCDateTime, nullable=False, default=now_utc)

, but now I get this:

10caret100 · May 3, 2020, 2:11pm

Ok, I noticed some “old” migration .py and .pyc files, deleted them, and re-ran the command:

This seems to have created the migration successfully.

But now I get this, after running :

10caret100 · May 3, 2020, 2:12pm

Here are the contents of the migration .py file:

"""Invoice tables

Revision ID: 3219f6e48a1c
Revises: 
Create Date: 2020-05-03 17:08:54.344144
"""

import sqlalchemy as sa
from alembic import op
from indico.core.db.sqlalchemy import UTCDateTime

from sqlalchemy.sql.ddl import CreateSchema, DropSchema


# revision identifiers, used by Alembic.
revision = '3219f6e48a1c'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    op.execute(CreateSchema('plugin_invoices'))
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('invoice_items',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('invoice_id', sa.Integer(), nullable=True),
    sa.Column('item_number', sa.Integer(), nullable=True),
    sa.Column('currency', sa.String(), nullable=True),
    sa.ForeignKeyConstraint(['invoice_id'], [u'plugin_invoices.invoices.id'], name=op.f('fk_invoice_items_invoice_id_invoices')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_invoice_items')),
    schema='plugin_invoices'
    )
    op.create_table('invoice_types',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('invoice_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['invoice_id'], [u'plugin_invoices.invoices.id'], name=op.f('fk_invoice_types_invoice_id_invoices')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_invoice_types')),
    schema='plugin_invoices'
    )
    op.create_table('invoices',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('sequential_number', sa.Integer(), nullable=False),
    sa.Column('creator_id', sa.Integer(), nullable=False),
    sa.Column('created_on', UTCDateTime, nullable=False),
    sa.ForeignKeyConstraint(['creator_id'], [u'users.users.id'], name=op.f('fk_invoices_creator_id_users')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_invoices')),
    schema='plugin_invoices'
    )
    op.create_index(op.f('ix_invoices_creator_id'), 'invoices', ['creator_id'], unique=False, schema='plugin_invoices')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_invoices_creator_id'), table_name='invoices', schema='plugin_invoices')
    op.drop_table('invoices', schema='plugin_invoices')
    op.drop_table('invoice_types', schema='plugin_invoices')
    op.drop_table('invoice_items', schema='plugin_invoices')
    # ### end Alembic commands ###
    op.execute(DropSchema('plugin_invoices'))

ThiefMaster · May 3, 2020, 2:14pm

You need to re-order the create_table calls so you don’t create a table referencing another table before creating the referenced table… i.e. create the invoices table first and drop it last.

10caret100 · May 3, 2020, 3:09pm

Reorder them inside the auto-generated migration .py file, or in the Model .py file?

Thanks

ThiefMaster · May 3, 2020, 5:16pm

In the migration. The definition order in the model doesn’t matter.

10caret100 · May 4, 2020, 6:57am

Got it. Is this something normal? I mean - is it expected that (from time to time, I guess) one will have to manually tweak the auto-generated migration .py file to make it work? I’d expect something like that to be completely taken care of by Alembic, but I am still inexperienced with this technology stack to know any better.

Thanks @ThiefMaster!

ThiefMaster · May 4, 2020, 7:04am

Simple cases work out of the box, but you should always double-check! The comments added there are there for a reason

The point of autogeneration is to save you some work, not to do everything for you. For example. in the core we always tweak the code to be more concise (and e.g. remove things that are not needed or change things to use more straightforward APIs (e.g. index=True for a single-column index during create_table instead of adding the index separately, and removing the drop_index since dropping the table/column will already do that).

10caret100 · May 4, 2020, 7:08am

I see. In my case (3 separate entities/tables), is it “normal” for Alembic to not “know” that it needs to first create the table(s) the rest of the tables depend on, or I am doing something wrong in, somehow, not “telling” Alembic how to properly autogenerate correctly everything?

Thanks

ThiefMaster · May 4, 2020, 7:10am

I would expect it to resolve this kind of dependencies but I also encountered the exact same problem so maybe it just doesn’t do it automatically after all…

10caret100 · May 4, 2020, 7:34am

Great. Just wanted to make sure it’s not something I am doing wrong to the “patient”
Thanks!!