Peewee and raw SQL create statements

Question

We have a dedicated Postgres database for each client, since we can't handle database migrations for each one without going crazy, we're using raw SQL files. We have one initial SQL file that creates the tables and inserts some data. The problem comes after we try to insert into a table which was previously populated with the initial SQL script using INSERT INTO sentences.

Somehow, peewee wants to create the new record using ID=1 thus raising an IntegrityrError (we created a bunch from the SQL file). I'm not really sure how to handle this scenario.

I have a base model:

class BaseModel(peewee.Model):
    active = peewee.BooleanField(default=True)
    created_at = peewee.DateTimeField(default=datetime.datetime.now)
    updated_at = peewee.DateTimeField(null=True)

    class Meta:
        database = database

An example model that uses the BaseModel:

class UserRole(BaseModel):
    user = peewee.ForeignKeyField(User)
    role = peewee.ForeignKeyField(Role)

    class Meta:
        indexes = (
            (('user', 'role'), True),
        )

Say UserRole has five records from the initial SQL script. If a try to create a new record using UserRole.create from the flask app, then I get an integrity error compalining about the ID=1.

Any thoughts?

Answer 1

Before inserting a new data, you need to fix sequences that provides you id values. Here is an example how to do that. After you do that, try to avoid explicit ids in your insert queries, allow db assign id value for you