Cant find Postgres schema and one-to-many relationship error.

[nayefc]

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)

What am I doing here? Why are my relationships wrong? 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.

Any help is much appreciated.

[nayefc]

I think the issue comes down to tying Flask-Migrate (or Alembic) to a Postgres schema. How can I do that?

[miguelgrinberg]

I'm not very knowledgeable in Postgres, but have you set the search_path to include the schema in which your table exists?

[nayefc]

I did that. I figured it out. Apart from that and setting __table_args__ = {"schema":"v1"}, the schema needs to be specified in ForeignKey such that: ForeignKey('myschema.table.id')

[tomitsui123]

I did that. I figured it out. Apart from that and setting __table_args__ = {"schema":"v1"}, the schema needs to be specified in ForeignKey such that: ForeignKey('myschema.table.id')

You save my life. Thanks