Rust | SQLx – PostgreSQL で UPSERT を実装してみた

2022-10-022024-09-21

URLをコピーしました！

PostgreSQL のON CONFLICT DO UPDATEを使って、UPSERT を実装してみました。

PostgreSQL で UPSERT

UPSERT とは

UPSERT とは、UPDATE or INSERT のことでレコードがあれば更新、なければ作成する処理のことを指します。

愚直に実装すれば、最初に SELECT で条件に一致するレコードを検索し、ヒットすれば UPDATE をおこない、ヒットしなければ INSERT するという処理順序で実装できます。

ただ、PostgreSQL ではON CONFLICT DO UPDATEというものがあり、これを使うと簡単に UPSERT をおこなうことができます。

INSERT INTO テーブル名 (カラム名, ...) VALUES (値, ...) 
ON CONFLICT ON CONSTRAINT 制約名
DO UPDATE SET カラム名 = 値, ...

INSERT を実行した際、指定した制約でコンクリフトが発生した場合、UPDATE が実行されるという流れです。

DO UPDATE SETの記述部分でテーブル名や WHERE 句を指定する必要はありません。

レコードが存在する場合は何もしないという場合にはDO NOTHINGを指定することもできます。

INSERT INTO テーブル名 (カラム名, ...) VALUES (値, ...) 
ON CONFLICT ON CONSTRAINT 制約名
DO NOTHING

SQLx で UPSERT を実装してみる

Axum と SQLx で Todo アプリを作成した際、PUT で UPSERT を実装したので、そこからコードを拝借します。

todos テーブル

以下の SQL で作成したテーブルを使用します。

todos のステータスに対して、外部キー制約が張ってあります。（UPSERT との直接的な関係はありません）

migrations/up.sql

-- Setup tables
create table todo_statuses (
    id varchar(26) not null,
    code varchar(255) not null,
    name varchar(255) not null,
    constraint pk_todo_statuses_id primary key (id)
);

insert into todo_statuses (id, code, name) values ('01GE4ZQCSW8QHKSCA172Q5F358', 'new', '新規');
insert into todo_statuses (id, code, name) values ('01GE4ZQPD3V5AYHZ4WFWHV9Y9S', 'working', '着手中');
insert into todo_statuses (id, code, name) values ('01GE4ZQXGH0S8AWEDFXE5903XR', 'waiting', '未着手');
insert into todo_statuses (id, code, name) values ('01GE50C7RJP4X8WEVWKEATRVKS', 'done', '完了');
insert into todo_statuses (id, code, name) values ('01GE50CDE8K0V9NTPAA6V58XV7', 'discontinued', '中止');
insert into todo_statuses (id, code, name) values ('01GE50CK0PADP4ZH7A7BFHDDH9', 'pending', '保留中');
insert into todo_statuses (id, code, name) values ('01GE50F00G30E08VVZ0PR9QT63', 'deleted', '削除');

create table todos (
    id varchar(26) not null,
    title varchar(255) not null,
    description text not null,
    status_id varchar(26) not null default '01GE4ZQCSW8QHKSCA172Q5F358',
    created_at timestamp with time zone not null default current_timestamp,
    updated_at timestamp with time zone not null default current_timestamp,
    constraint pk_todos_id primary key (id),
    constraint fk_todos_status_id_todo_statuses_id foreign key (status_id) references todo_statuses (id)
);

-- Insert sample data
insert into todos (id, title, description) values ('01GDT91DZ0FDZ7YJ426PB189V1', 'todo 1', 'init test data.');
insert into todos (id, title, description) values ('01GDT91MB0SGG49T974GX2A5G9', 'todo 2', '');
update todos set status_id = '01GE4ZQPD3V5AYHZ4WFWHV9Y9S', updated_at = current_timestamp where id = '01GDT91DZ0FDZ7YJ426PB189V1';

SQLx で UPSERT

SQL 文を実行する記述は、INSERT や UPDATE を実装する際と大きな違いはなく、query、bind、executeを使います。

todo-adapter/src/repository/todo.rs

async fn upsert(&self, source: UpsertTodo) -> anyhow::Result<Todo> {
    let pool = self.db.0.clone();
    let todo: UpsertStoredTodo = source.into();
    let id = todo.id.clone();

    let upsert_sql = r#"
        insert into todos (id, title, description, status_id) values ($1, $2, $3, $4)
        on conflict on constraint pk_todos_id
        do update set title = $2, description = $3, status_id = $4, updated_at = current_timestamp
    "#;

    let _ = query(upsert_sql)
        .bind(todo.id)
        .bind(todo.title)
        .bind(todo.description)
        .bind(todo.status_id)
        .execute(&*pool)
        .await?;

    let sql = r#"
        select
            t.id as id,
            t.title as title,
            t.description as description,
            ts.id as status_id,
            ts.code as status_code,
            ts.name as status_name,
            t.created_at as created_at,
            t.updated_at as updated_at
        from
            todos as t
            inner join
                todo_statuses as ts
                on ts.id = t.status_id
        where
            t.id = $1
    "#;

    let stored_todo = query_as::<_, StoredTodo>(sql)
        .bind(id)
        .fetch_one(&*pool)
        .await?;
    Ok(stored_todo.try_into()?)
}

UPSERT にあたるON CONFLICT DO UPDATEの SQL 文は以下です。

insert into todos (id, title, description, status_id) values ($1, $2, $3, $4)
on conflict on constraint pk_todos_id
do update set title = $2, description = $3, status_id = $4, updated_at = current_timestamp

pk_todos_idはプライマリーキー制約です。

todos テーブルの id で該当の ID がテーブルに存在しない場合は insert into が実行され、存在した場合は do update set が実行されます。

プライマリーキー制約以外にもユニーク制約のconstraint_nameを指定することも可能です。

constraint_name を調べる方法

constraint_nameを調べるには、以下の SQL を実行します。

migrations/check.sql

-- Show constraint name
select
    table_name,
    constraint_name,
    constraint_type
from
    information_schema.table_constraints
where
    table_name = 'todos';

ちなみに以下のような SQL でテーブルを作成した場合、constraint_nameは自動で制約名テーブル名_pkeyがつくため、todos_pkeyとなります。

create table todos (
    id varchar(26) not null primary key,
    title varchar(255) not null
);

Todo アプリ

作成した Todo アプリでは、UPSERT 以外に SELECT・INSERT・UPDATE・DELETE を一通り実装しています。

axum-ddd-explicit-architecture

Todo アプリのソースコードは Github においてあります。

GitHub

GitHub – codemountains/axum-ddd-explicit-architecture: An example of web application (REST API) by u… An example of web application (REST API) by using Rust and Axum with DDD, Clean Architecture, Explicit Architecture. – codemountains/axum-ddd-explicit-architect…