クロスマート バックエンドエンジニアの武(@pouhiroshi)です。
弊社のプロダクトはPythonのDjangoフレームワークを使っており、
OR Mapper(以下ORM)という機能を備えています。
ORMとはわかりやすく言うと、データベースとアプリケーションの橋渡しをしてくれる機能です。
データベースからデータを取り出したり書き込んだりする際は、SQLというデータベース操作をするためのプログラム言語を使う必要があるのですが、これを書かずにサーバーサイドのプログラムでデータベース操作を行えるようにできる、便利な機能です。
ある程度は便利なのですが、欠点もあります。
DjangoORMで困ること
- SQLを知ってる人は、SQLをDjango ORMに翻訳している感じ。Django ORMで思い通りのSQLを発行するのは結構難しい。
- SQLに不慣れな人は、目隠しして(SQLを見ずに)データを取り出そうとする感じ。Django ORMで思い通りのデータを得るのは結構難しい。
- DjangoORMはSQLと用語がかなり異なる。
- DjangoORMで表現が難しいSQLがある。
以下はDjango ORMの例です。どのようなSQLが発行されるか、パッと頭に思い浮かぶでしょうか?
Topic.objects.annotate(f=Case(When(record__user=may, then=F('record__value')), output_field=IntegerField())) .order_by('id', 'name', 'f') .distinct('id', 'name') .values_list('name', 'f')
弊社のクロスオーダーはお客様が増えてきており、性能改善は喫緊の課題です。
性能改善のために狙ったSQLでORMに実行させることができないという点は、かなりのストレス問題であると頭を悩ませていました。
救世主Aldjemy
そんな中、とてもよいライブラリを発見したので、今回ご紹介したいと思います。
AldjemyというDjangoでSQLAlchemyという別のORMライブラリを使えるようにするライブラリです。
github.com
SQLAlchemyはDjango ORMとは全く記法の異なるORMで、SQL記法に則った書き方ができます。
www.sqlalchemy.org
SQLAlchemyを利用する場合、データベースのテーブルに対応するModelクラスを定義する必要があります。
Django ORMも同様にModelクラスを定義しますので、普通に併用すると、2種類のModelクラスを定義することとなり、メンテナンス性が悪くなってしまいます。
そんな問題を解決してくれるのが、Aldjemyです。
AldjemyはDjango ORMのModelクラスをSQLAlchemyに流用して利用可能にしてくれます。
私は清水川さんのこちらのスライドがめちゃくちゃ参考になりました。こちらもぜひ参照ください。
利用例
例えば以下のようなSQLを実行させたいとします。
SELECT count(distinct table_a.id) as `count` FROM table_a INNER JOIN table_b ON table_a.id = table_b.header_id LEFT OUTER JOIN table_c on table_a.id = table_c.order_id WHERE table_a.supplier_id = 100 AND table_a.is_active = 1 AND table_a.is_canceled = 0 AND table_b.is_active = 1 AND table_c IS NULL
これをaldjemy(SQLAlchemy)で書くと以下のようになります。
Django Modelに.sa とつけるだけでSQLAlchemyで書くことができます。
ほとんど考えたSQLのままの記述ができています。わかりやすいですね!
session.query(func.count(distinct(TableA.sa.id)).label("count")) .select_from(TableA.sa) .filter(TableA.sa.supplier_id == 100, TableA.sa.is_active == true(),TableA.sa.is_canceled == false()) .join(TableB.sa, TableA.sa.id == TableB.sa.header_id) .filter(TableB.sa.is_active == true()) .outerjoin(TableC.sa, TableB.sa.id == TableC.sa.order_id) .filter(TableC.sa.id.is_(None))
SQLAlchemyの使い方は、他でもたくさん紹介されているので割愛します。
今回の記事では、Aldjemyの導入方法について書きたいと思います。
Aldjemyの導入方法
まずバージョンの注意点があります。
現時点(2023/4/15)でのバージョンは aldjemy 2.6、SQLAlchemy 1.4.47 で動作確認しています。
SQLAlchemyは最近、version2系のリリースがありました。かなり変更が入っており、aldjemyがそれにまだ対応できていないようです。
aldjemyを使う場合、SQLAlchemy1.4系を利用するようにしてください。
上記を踏まえ、インストールは以下のようにします。
pip install aldjemy pip install sqlalchemy~=1.4
プロジェクトの`settings.py` にある `INSTALLED_APPS` の最後に`aldjemy` を追加します。
INSTALLED_APPS = [ 'django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'django_extensions', 'aldjemy', ]
続いて、データベース接続の渡し方です。
SQLAlchemyの実行に必要なデータベース接続は sqlalchemy.orm.Session です。
使用例で記載した session.query(〜 のsessionの部分ですね。
こちらをDjangoで定義したデータベース接続定義から取得する必要があります。
この部分はaldjemyのaldjemy.orm.get_sessionで取得することができます。
クロスオーダーでは、読み込み用DBと書き込み用DBを分けることで負荷分散を行っています。(リードレプリカ構成)
ですので、以下のようにSessionを取得する関数を2種類用意しました。
SQLAlchemyを実行する際に、SELECT系か、それ以外かで使い分けます。
from aldjemy.orm import get_session from sqlalchemy.orm import Session def get_read_sa_session() -> Session: # DjangoのDB設定"replica"からaldjemyをつかってSQLAlchemyのSessionを取得する session: Session = get_session("replica") return session def get_write_sa_session() -> Session: # DjangoのDB設定"default"からaldjemyをつかってSQLAlchemyのSessionを取得する session: Session = get_session() return session
ModelクラスはDjangoで定義したものが使えますので、SQLAlchemy用に定義しなおす必要はありません。
これだけでSQLAlchemyを利用できるようになりました!!
まとめ
SQLAlchemyは書きたいSQLを直感的にPythonで書けます。Django ORMでクエリに迷ったらぜひSQLAlchemyを使いましょう。
Djangoモデル定義をそのまま使えて、お手軽ですね!!
WebDBシステムを構築する際、やはりSQL知識は欠かせません。目的にあった性能のよいSQLを考え、実行するようにしましょう!
Enjoy SQL!!
最後に
クロスマートでは絶賛エンジニア(SRE・バックエンド・フロントエンド)募集中です。
このお話を読んでちょっとでも気になった方がいたら
「話を聞きに行きたい」を押してみてください!