Alembic migration issue with PostgreSQL schema and invalid one-to-many relationship

Question

I've created a database and schema in Postgres. I have my models and when I run python manager.py db migrate which uses Flask-Migrate, I get the error below. However, the db init command works.

sqlalchemy.exc.ProgrammingError: (ProgrammingError) no schema has been selected to create in

Now when I add __tablename__ and __table_args__ = {"schema": "name_of_schema"} to my models, I get the error below for both, db init and db migrate:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'deploy.instance_id' could not find table 'instance' with which to generate a foreign key to target column 'id'

My relationships however, look okay. I've seen many examples and they worked properly on SQLite without Flask-Migrate.

I have three tables as follows (removing most columns):

class Application(db.Model):
    __tablename__ = 'application'
    __table_args__ = {"schema":"v1"}
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=False)
    instances = db.relationship('Instance', backref='application', lazy='dynamic')

    def __repr__(self):
        return '<ID %r>' % (self.id)


class Instance(db.Model):
    __tablename__ = 'instance'
    __table_args__ = {"schema":"v1"}
    id = db.Column(db.Integer, primary_key=True)
    host = db.Column(db.String(80), unique=False)
    application_id = db.Column(db.Integer, db.ForeignKey('application.id'))
    deploys = db.relationship('Deploy', backref='instance', lazy='dynamic')

    def __repr__(self):
        return '<ID %r>' % (self.id)


class Deploy(db.Model):
    __tablename__ = 'deploy'
    __table_args__ = {"schema":"v1"}
    id = db.Column(db.Integer, primary_key=True)
    tag = db.Column(db.String(80), unique=False)
    instance_id = db.Column(db.Integer, db.ForeignKey('instance.id'))

    def __repr__(self):
        return '<ID %r>' % (self.id)

The relationships are:

• Application to Instance (one-to-many; one application many instances)
• Instance to Deploy (one-to-many; one instance many deploys)

When I remove all relationships and create a standalone table, I still get the first error: sqlalchemy.exc.ProgrammingError: (ProgrammingError) no schema has been selected to create in. What is the problem and how can I fix it?

Answer 1

Postgresql has the concept of a "default schema", which is set using SEARCH_PATH. It sounds like the user you are connecting as does not have this configured. Take a look at Client Connection Defaults on the Postgresql website.

For the foreign key error, when you use an explicit schema argument with a Table, that name needs to be specified in ForeignKey objects as well:

ForeignKey('myschema.mytable.id')