pythonsqlitemigrationfastapipeewee

Peewee V3.17 migrator.drop_column method is not working


Attempting to drop a table's column in Sqlite DB fails with:

File "/home/.../venv/lib/python3.11/site-packages/peewee.py", line 3252, in execute_sql cursor.execute(sql, params or ()) peewee.OperationalError: near "DROP": syntax error

peewee==3.17.0 fastapi==0.109.0

Test code

# db.py
from peewee import *
from playhouse.migrate import *


db = SqliteDatabase('people.db')


class Person(Model):
    name = CharField()
    date_of_birth = DateField()
    age = SmallIntegerField()

    class Meta:
        database = db


class Pet(Model):
    owner = ForeignKeyField(Person, backref='pets')
    name = CharField()
    animal_type = CharField()

    class Meta:
        database = db


db.connect()
db.create_tables([Person, Pet])

migrator = SqliteMigrator(db)
migrate(
    migrator.drop_column(
        Person._meta.table_name,
        "date_of_birth"
    ),
)
db.close()

I use FastAPI and make migrations with below command (just run fastapi server)

uvicorn main:app --reload

migrator's rename_column, add_column, ... methods works I don't yet know the correct approach (folder, file structure e.g like in djangoORM) to migrate with Peewee


Solution

  • There was a bug in the version check for sqlite support of drop column. I had it as 3.25, but in fact DROP was not added until 3.35, that's why you were seeing the error. Thanks for reporting this, .drop_column(..., legacy=True) should continue to work until you pull in this patch -- what legacy=True does is works around limitations in Sqlite's ALTER TABLE support by:

    1. Creating a new table w/the desired schema
    2. Copying all data into the new table
    3. Dropping the original table
    4. Renaming the new table to the original table's name.