Multiple binds in v3.x

[philip-newzoo]

We have models in both Postgres and Google BigQuery. In the previous v2.5.x we defined a __bind_key__ on the models and then monkey patched the get_tables_for_bind to ensure that the metadata was correctly reflected. My question is how do I migrate this to v3.x?

My issues are that some internal functions that we used to monkeypatch are no longer present. I think _execute_for_all_tables should now be called _call_for_binds. I'm not sure what get_tables_for_bind should be as this has been removed.

Also open to suggestions if we've been going about the code all wrong and should have a different approach.

Any help gratefully received.

class Config:
    SQLALCHEMY_BINDS = {
            "gbq": f"bigquery://{GBQ_PROJECT_ID}/{GBQ_DATASET_ID}?use_query_cache=false",
            "pg": SQLALCHEMY_DATABASE_URI,
        }

# GBQ connection for user level data
metadata_gbq = MetaData(bind=Config.SQLALCHEMY_BINDS["gbq"])
metadata_gbq.reflect(views=True)
# Postgres for all other data
metadata_pg = MetaData(bind=Config.SQLALCHEMY_BINDS["pg"])


# MONKEY PATCH DROP_ALL AND CREATE_ALL, WE CAN NEVER TOUCH GBQ TABLES
def drop_all(self, bind_key="pg"):
    self._call_for_binds("pg", "drop_all")
    # Old v2.x code
    # self._execute_for_all_tables(app, "pg", "drop_all")


def create_all(self, bind_key="pg"):
    self._call_for_binds("pg", "create_all")
    # Old v2.x code
    # self._execute_for_all_tables(app, "pg", "create_all") 


# We're using __table__ to define gbq tables from the gbq metadata, but as a consequence, the
# tables don't get added to the db.Model metadata, monkey patch get_tables_for_bind to retrieve the
# tables from the gbq bind. As a result, sqlalchemy session knows which engine to query.
def get_tables_for_bind(self, bind=None):
    """Returns a list of all tables relevant for a bind."""
    tables = self.Model.metadata.tables
    if bind == "gbq":
        tables = metadata_gbq.tables
    result = []
    for table in tables:
        if table.info.get("bind_key") == bind:
            result.append(table)
    return result

# Monkey patch
from flask_sqlalchemy import SQLAlchemy
SQLAlchemy.drop_all = drop_all
SQLAlchemy.create_all = create_all
SQLAlchemy.get_tables_for_bind = get_tables_for_bind

Our models are defined like so

# This is a Google BigQuery table
class Players(db.Model):
    __bind_key__ = "gbq"
    __table__ = metadata_gbq.tables[Config.GBQ_PLAYERS_MONTHLY]
    __mapper_args__ = {"primary_key": [__table__.c.panel_date]}

    c = __table__.c

    def __repr__(self):
        return f"<Players {self.date}>"

# This is a Postgres table
class PlatformTotals(db.Model, AllFeaturesMixin):
    __bind_key__ = "pg"
    __tablename__ = "platform_totals"

    date = db.Column(db.Date, primary_key=True)
    platform = db.Column(db.String)
    device = db.Column(db.String)

Queries that access the GBQ tables fail because they're looking for them in Postgres.

[davidism]

You don't need to do anything now. There is a separate metadata per bind, each metadata will have only the tables for that bind.

[philip-newzoo]

I'm getting errors like this when I run a query that touches the GBQ tables.

(psycopg2.errors.UndefinedTable) relation \"player_segments__players_monthly\" does not exist

It looks like the query is still being routed to Postgres.

One difference that may not have been obvious from the code is that the GBQ models we have are purely reflected. Maybe this is the source of our problems?