バックエンド設計をシングルからマルチ化へ

内容

とある案件でバックエンドのデータベースをシングルユーザー　→　マルチユーザーで設計を変更する必要が生じた。

• ユーザー毎に同じテーブルのスケマを使いまわしたい
• ユーザー間でデータを共有する必要はない
• ソースコードの変更を最小限にしたい

データベース

　データベースは2種類を使っている

• POSTGRES　→　文書ライブラリ管理のようなデータ　→　データが正規化されていてRDBがベター
• MONGODB　→　ライブラリをAI学習させる履歴データ　→　JSON形式のためNOSQLがベター

マルチユーザー化

• POSTGRES　→　スケマで複数化するのが良い

After comparing all the different solutions, we concluded that schemas are the best option for us. Schemas are an extra hierarchy layer inside databases that can be used to group objects like tables, types or functions together. The only disadvantage is that some databases like MySQL/MariaDB do not have proper support for schemas - which was not an issue for us as we are using PostgreSQL.

• MONGODB　→　データベースで複数化するのが良い

ソースコード

　以下は設計変更後のコードである。

python.py

import pdb
from typing import Dict, List, Optional

from fastapi import Depends, FastAPI
from pydantic import BaseModel
from pymongo.mongo_client import MongoClient
from pymongo.server_api import ServerApi
from sqlalchemy import Column, Integer, String, create_engine,text
from sqlalchemy.dialects.postgresql import HSTORE
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, sessionmaker
from sqlalchemy.schema import CreateSchema
from sqlalchemy import MetaData

class CV(BaseModel):
    id: Optional[str]
    group: Optional[int]
    querystr: Optional[str]
    ai_train_label: Optional[str]
    ai_predict_label: Optional[str]
    ai_predict_score: Optional[str]
    ai_predict_candidate: Optional[str]

    class Config:
        orm_mode = True

class DocumentSchema(BaseModel):
    Document_id: Optional[str]
    title: Optional[str]
    ai_train_label: Optional[str]
    ai_predict_score: Optional[str]
    ai_predict_label: Optional[str]
    url: Optional[str]

    class Config:
        orm_mode = True

class ResponseSchema(BaseModel):
    postgres_data: Optional[List[DocumentSchema]]
    mongo_data: Optional[List[CV]]

    class Config:
        orm_mode = True

# PostgreSQL接続情報
POSTGRES_USER = 'dbuser'
POSTGRES_PASSWORD = 'dbpass'
POSTGRES_DB = 'dbname'
POSTGRES_HOST = 'xxxx'
POSTGRES_PORT = 'yyyy'

# SQLAlchemyエンジンの作成
DATABASE_URL = f"postgresql://{POSTGRES_USER}:{POSTGRES_PASSWORD}@{POSTGRES_HOST}:{POSTGRES_PORT}/{POSTGRES_DB}"
engine = create_engine(DATABASE_URL)

#MongoDB接続情報
MONGO_USER = 'xxx'
MONGO_PASSWORD = 'yyy'

uri = f"mongodb+srv://{MONGO_USER}:{MONGO_PASSWORD}@cluster0.ka8rkd5.mongodb.net/?retryWrites=true&w=majority"

def get_mongo_client():
    client = MongoClient(uri)
    try:
        yield client
    finally:
        client.close()

# SQLAlchemyセッションの作成
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

# FastAPIアプリケーションの作成
app = FastAPI()

def get_db():
    db = SessionLocal()
    try:
        yield db
    except:
        db.rollback()
        raise
    finally:
        db.close()

class Document(Base):
    __tablename__ = 'Document'
    Document_id = Column(String(255), primary_key=True)
    title = Column(String(400))
    ai_train_label = Column(String(255))
    ai_predict_score = Column(String(255))
    ai_predict_label = Column(String(255))
    url = Column(String(255))

テーブルの一括作成

以下が肝のコード

・db_postgres: Sessionで、postgresのインスタンスを引っ張ってきている。
・何故かdb_postgres.execute(CreateSchema(schema_name, if_not_exists=False))ではスケマが作れなかった

以下は動作したパターン
with engine.connect() as conn:　→　Engineの場合は、connect()

python.py

def create_dynamic_table(schema_name: str, db_postgres: Session, db_mongo: MongoClient):

    with engine.connect() as conn:
        try:
            conn.execute(CreateSchema(schema_name, if_not_exists=False))

            # Set the `schema` name before creating the table
            Document.__table__.schema = schema_name
            Base.metadata.create_all(conn)
        
            conn.commit()
            print('Table created successfully!')

            dummy_Document = Document(Document_id='1', title='Dummy Title',
                      ai_train_label='Label', ai_predict_score='0.75', ai_predict_label='Predicted Label',
                      url='http://www.example.com')

            db_postgres.add(dummy_Document)
            db_postgres.commit()

        except Exception as e:
            db_postgres.rollback()
            print(f'Error creating table: {e}')
            
    with db_mongo:
        collection = db_mongo[schema_name]
        collection['table'].insert_one({'id': 'test'})

@app.post("/data")
def create_table(schema_name: str, db_postgres: Session = Depends(get_db), db_mongo: MongoClient = Depends(get_mongo_client)):

    # create table if not exists and insert document into MongoDB
    create_dynamic_table(schema_name, db_postgres, db_mongo)
    
    return {"message": "Table created successfully"}

@app.get("/data", response_model=ResponseSchema)
def get_data(schema_name: str, db_postgres: Session = Depends(get_db), db_mongo: MongoClient = Depends(get_mongo_client)):

    Document.__table_args__ = {'schema': schema_name}
    results_postgres = db_postgres.query(Document).all()
    results_mongo = list(db_mongo[schema_name]['table'].find({}))

    return {'postgres_data': results_postgres, 'mongo_data': results_mongo}

EngineとSessionの関係

違いは何ですか？　
・with db_postgres.connection() as conn:　→ Sessionの場合は、connection()　
・with engine.connect() as conn:　→　Engineの場合は、connect()

BING回答

db_postgresはSessionクラスのインスタンスであり、engineはsqlalchemy.engine.Engineクラスのインスタンスです。
Sessionは、SQLAlchemyのオブジェクトリレーショナルマッパー（ORM）の高レベルオブジェクトであり、データベースとのやりとりに高レベルのインターフェイスを提供します。Sessionクラスのconnection()メソッドは、セッションのコネクションプールからコネクションを使用するコンテキストマネージャーを返します。これにより、単一のコンテキスト内でクエリやトランザクションを実行し、コンテキストが終了したときに自動的にコミットまたはロールバックされるようになります。
一方、EngineはSQLAlchemyの低レベルオブジェクトであり、特定のデータベースへの接続を管理します。Engineクラスのconnect()メソッドは、ORM関連機能なしに直接データベース接続を返します。新しい接続を作成するだけで、トランザクションコンテキストを処理するのはあなた次第です。
一般的に、SQLAlchemyのORMを使用している場合は、Sessionオブジェクトを使用してトランザクションコンテキストを管理する必要があります。これは、ORMがオブジェクトの変更を追跡し、それらの変更をデータベースに永続化するためのSQL文を自動的に生成するからです。Sessionオブジェクト上のconnection()メソッドを使用することで、単一のコンテキスト内のすべてのデータベース操作が同じトランザクションの一部となります。
ORMを使用していない場合や、トランザクションコンテキストをより細かく制御する必要がある場合は、Engineオブジェクトを直接使用できます。この場合、通常はconnect()メソッドを使用して明示的に新しい接続を作成し、begin()、commit()、rollback()呼び出しを使用してトランザクションコンテキストを自分で管理します。

関係性の作図

これもCHATGPTで描かせた

              +-----------+                       +-------------+
              |           |    has a              |             |
   create     |  Session  +----------------------->   Connection|
  <--------->|           |                       |             |
              +-----------+                       +-------------+
                    ^                                    |
                    |                                    |
                    |                                    |
                    |execute                             |execute
              +------------+                        +---------------+
              |            |   uses                |               |
              |   Engine   +---------------------->  DBAPI Driver  |
              |            |                        |               |
              +------------+                        +---------------+

はまったこと

Postgresで､SCHEMAとTableを動的に作る部分が動作せずに丸1日悩んだ。
→　最終的にはCOMMITが抜けていた。ここはCHATGPTでは発見してくれなかった。

今後の課題

いかに現状のコードに手を加えずに、マルチユーザー化が出来るか？
基本的にはクエリパラメータの追加だけで、API改良で何とかしたいところ・・・

まとめ

以下はユーザー毎にAPIコールでスケマを指定することで、ユーザー毎データベースにアクセスさせるためのコード（少々ダサいが仕方がない）

python.py

@app.get("/data", response_model=ResponseSchema)
def get_data(schema_name: str, db_postgres: Session = Depends(get_db), db_mongo: MongoClient = Depends(get_mongo_client)):

    Document.__table_args__ = {'schema': schema_name}

間違い　→　```Document.__table_args__ = {'schema': schema_name}```
正解　→　　```Document.__table__.schema = schema_name```

    results_postgres = db_postgres.query(Document).all()
    results_mongo = list(db_mongo[schema_name]['table'].find({}))

    return {'postgres_data': results_postgres, 'mongo_data': results_mongo}

• POSTGRES　→ Tableクラスにスケマ情報を埋め込む

間違い　→　Document.__table_args__ = {'schema': schema_name}
正解　→　　Document.__table__.schema = schema_name

参照方法　results_postgres = db_postgres.query(Document).all()

CHATGPT説明

POSTGRESからTableクラスにスキーマ情報を埋め込む方法は存在しません。ただし、POSTGRESの場合、一部のドライバでは、ネストされた名前空間としてスキーマを扱うことができます。
また、以下のコードは、Tableオブジェクトに直接__table_args__属性を設定する方法です。

Document.__table_args__ = {'schema': schema_name}

しかし、これは推奨されない方法であり、正しい方法は次のようにTableオブジェクトのschema属性に値を設定することです。

Document.__table__.schema = schema_name

この方法では、Tableオブジェクト自体が変更されるため、より柔軟性があり、他のプロパティやメソッドも使用できます。

• MONGODB　→　[schema_name]で指定する
  db_mongo[schema_name]['table'].find({})

感想

• どちらにしても、ある程度のコード変更が必要　→　ワンタッチと言う訳にはいかない・・・
• きちんとコメントしておかないと、メンテナンスが大変になりそう