Faild to run migrations after adding new columns to Table

[Tar8117]

Hi.
I created a new table in tables.py file. Then I create and run migrations. All worked great and in admin panel i could see my table. Then I added few columns to my table and again create migrations. It detected that I have added new columns, but migrations failed to run. I'm getting The command failed. relation "student" already exists.

Here is my code:

from piccolo.table import Table
from piccolo.columns import Varchar, Integer


class Student(Table):
    last_name = Varchar(length=100)
    first_name = Varchar(length=100)
    phone_number = Varchar(length=100)
    cohort_number = Integer()
    personal_email = Varchar() --> # New column
    personal_id = Integer() --> --> # New column
    personal_login = Varchar() -->  # New column

I tried manually add this changes to my first migration file, but it doesn't work too. So I think there should be a way to delete old table and create the new one (with the new columns), because it seems that piccolo remembered an old table and it tries to create a new table instead of adding new columns, and that's why he says relation "student" already exists.
It just my thoughts.
However, is there any way to delete tables in piccolo?
BTW I saw in documentation smth about dropping tables, but I failed using them

[dantownsend]

What you've done should work OK.

Your first migration file should look something like this (note how there's an add_table and several add_column):

from piccolo.apps.migrations.auto.migration_manager import MigrationManager
from piccolo.columns.column_types import Integer
from piccolo.columns.column_types import Varchar
from piccolo.columns.indexes import IndexMethod


ID = "2022-08-19T23:36:27:820870"
VERSION = "0.85.1"
DESCRIPTION = ""


async def forwards():
    manager = MigrationManager(
        migration_id=ID, app_name="home", description=DESCRIPTION
    )

    manager.add_table("Student", tablename="student")

    manager.add_column(
        table_class_name="Student",
        tablename="student",
        column_name="last_name",
        db_column_name="last_name",
        column_class_name="Varchar",
        column_class=Varchar,
        params={
            "length": 100,
            "default": "",
            "null": False,
            "primary_key": False,
            "unique": False,
            "index": False,
            "index_method": IndexMethod.btree,
            "choices": None,
            "db_column_name": None,
            "secret": False,
        },
    )

    manager.add_column(
        table_class_name="Student",
        tablename="student",
        column_name="first_name",
        db_column_name="first_name",
        column_class_name="Varchar",
        column_class=Varchar,
        params={
            "length": 100,
            "default": "",
            "null": False,
            "primary_key": False,
            "unique": False,
            "index": False,
            "index_method": IndexMethod.btree,
            "choices": None,
            "db_column_name": None,
            "secret": False,
        },
    )

    manager.add_column(
        table_class_name="Student",
        tablename="student",
        column_name="phone_number",
        db_column_name="phone_number",
        column_class_name="Varchar",
        column_class=Varchar,
        params={
            "length": 100,
            "default": "",
            "null": False,
            "primary_key": False,
            "unique": False,
            "index": False,
            "index_method": IndexMethod.btree,
            "choices": None,
            "db_column_name": None,
            "secret": False,
        },
    )

    manager.add_column(
        table_class_name="Student",
        tablename="student",
        column_name="cohort_number",
        db_column_name="cohort_number",
        column_class_name="Integer",
        column_class=Integer,
        params={
            "default": 0,
            "null": False,
            "primary_key": False,
            "unique": False,
            "index": False,
            "index_method": IndexMethod.btree,
            "choices": None,
            "db_column_name": None,
            "secret": False,
        },
    )

    return manager

And the second should look something like this (note how there's no add_table):

from piccolo.apps.migrations.auto.migration_manager import MigrationManager
from piccolo.columns.column_types import Integer
from piccolo.columns.column_types import Varchar
from piccolo.columns.indexes import IndexMethod


ID = "2022-08-19T23:36:48:327594"
VERSION = "0.85.1"
DESCRIPTION = ""


async def forwards():
    manager = MigrationManager(
        migration_id=ID, app_name="home", description=DESCRIPTION
    )

    manager.add_column(
        table_class_name="Student",
        tablename="student",
        column_name="personal_email",
        db_column_name="personal_email",
        column_class_name="Varchar",
        column_class=Varchar,
        params={
            "length": 255,
            "default": "",
            "null": False,
            "primary_key": False,
            "unique": False,
            "index": False,
            "index_method": IndexMethod.btree,
            "choices": None,
            "db_column_name": None,
            "secret": False,
        },
    )

    manager.add_column(
        table_class_name="Student",
        tablename="student",
        column_name="personal_id",
        db_column_name="personal_id",
        column_class_name="Integer",
        column_class=Integer,
        params={
            "default": 0,
            "null": False,
            "primary_key": False,
            "unique": False,
            "index": False,
            "index_method": IndexMethod.btree,
            "choices": None,
            "db_column_name": None,
            "secret": False,
        },
    )

    manager.add_column(
        table_class_name="Student",
        tablename="student",
        column_name="personal_login",
        db_column_name="personal_login",
        column_class_name="Varchar",
        column_class=Varchar,
        params={
            "length": 255,
            "default": "",
            "null": False,
            "primary_key": False,
            "unique": False,
            "index": False,
            "index_method": IndexMethod.btree,
            "choices": None,
            "db_column_name": None,
            "secret": False,
        },
    )

    return manager

To make Piccolo delete a table, unregister it from AppConfig in piccolo_app.py. If table_finder is being used:

APP_CONFIG = AppConfig(
    ...
    table_classes=table_finder(modules=["home.tables"]),
)

Then just comment out the Student class, then create a new migration, and running it should delete the table.

You might want to use a tool like PgAdmin too so you can inspect the current state of the database.

[Tar8117]

Thank you for suggestions. Sorry for a long pause. I tried everything you advised, but only one thing helped me. I deleted the table from pgadmin graphic interface. And deleted the migration file from project. After these, new migrations created and run successfully.
Today i m going to try to run postgres from docker, and i hope it will work normal.
Thank you for help

[dantownsend]

No worries, glad you got it sorted!