Implement reader & writer database connection

[NiekFi]

We're trying to use a database connection that has separated reader and writer endpoints. Since TiPg is only use a single database connection we're running into problems when trying to create the tipg_catalog functions. Our idea is to add another writer connection pool that, if exists in env, will run the database writing logic on that connection, whereas all the other queries are run against the default/reader connection.

[vincentsarago]

I'm not sure to understand but could you achieve this in your own application?

tipg goal is to create a proper python package which you can use to create your own application. Everything within main.py can be considered as a demo application.

[NiekFi]

Not sure how we would do this..

await conn.execute(DB_CATALOG_FILE.read_text()) this line is basically stopping us from using a reader / writer endpoint database (I believe this is the only CREATE operation). We would like to be able to change that conn object with a different postgres host url.

[vincentsarago]

Here is an example of a full custom application using tipg

from typing import Any, List

import jinja2
import orjson
from buildpg import asyncpg
from tipg.dbmodel import get_collection_index
from tipg.db import DB_CATALOG_FILE

from tipg.errors import DEFAULT_STATUS_CODES, add_exception_handlers
from tipg.factory import OGCFeaturesFactory

from fastapi import FastAPI, Request

from starlette.templating import Jinja2Templates
from starlette_cramjam.middleware import CompressionMiddleware


app = FastAPI(
    title="My Application"
    openapi_url="/api",
    docs_url="/api.html",
)

templates = Jinja2Templates(
    directory="",
    loader=jinja2.ChoiceLoader(
        [
            # Custom HTML overwrite (e.g logo)
            # ref https://github.com/developmentseed/eoAPI/blob/bbc430332aa7c167dd95812d1135873bbfed1be9/src/eoapi/vector/eoapi/vector/app.py#L30-L39
            jinja2.FileSystemLoader("/tmp/my_tipg_templates/"),
            jinja2.PackageLoader("tipg", "templates"),
        ]
    ),
)

ogc_api = OGCFeaturesFactory(
    title="OGC Features",
    templates=templates,
    with_common=True,
)
app.include_router(ogc_api.router, tags=["OGC Features API"])
app.add_middleware(CompressionMiddleware)
add_exception_handlers(app, DEFAULT_STATUS_CODES)


def con_init(conn: asyncpg.Connection):
    await conn.set_type_codec(
        "json", encoder=orjson.dumps, decoder=orjson.loads, schema="pg_catalog"
    )
    await conn.set_type_codec(
        "jsonb", encoder=orjson.dumps, decoder=orjson.loads, schema="pg_catalog"
    )

    await conn.execute(
        f"""
        SELECT set_config(
            'search_path',
            'pg_temp,public,' || current_setting('search_path', false),
            false
            );
        """
    )

    # Register TiPG functions in `pg_temp`
    await conn.execute(DB_CATALOG_FILE.read_text())


@app.on_event("startup")
async def startup_event() -> None:
    """Connect to database on startup."""
    app.state.pool = await asyncpg.create_pool_b(
        "YOU DATABASE URL",
        min_size=0,
        max_size=50,
        init=con_init,
        **kwargs,
    )

    app.state.collection_catalog = await get_collection_index(app.state.pool, schemas=["public"])


@app.on_event("shutdown")
async def shutdown_event() -> None:
    """Close database connection."""
    await app.state.pool.close()

Note: when we are going to do #37 we might remove the need of conn.execute(DB_CATALOG_FILE.read_text()) which I agree makes things a bit weird

cc @bitner

[vincentsarago]

@NiekFi is there still an issue here?

[NiekFi]

Hi, I think your example should suffice, running into some other issue on my end, I think you can close it :)