sea-query-merge

This is a crate that provides a MERGE statement for sea-query.

Note: This crate currently only supports PostgreSQL syntax for the MERGE statement.

Installation

Add this to your Cargo.toml:

[dependencies]
sea-query-merge = "0.3.0"

Usage

You can create a MergeStatement using the QueryExt trait.

use sea_query_merge::QueryExt;
use sea_query::Query;

let query = Query::merge();

Basic Merge

Here is a basic example of a MERGE statement:

use sea_query::{Condition, Expr, Iden, PostgresQueryBuilder, Query, TableRef};
use sea_query_merge::{
    QueryExt,
    merge_insert::MergeInsert,
    when_clause::{WhenAction, WhenClause},
};

#[derive(Iden)]
enum TestTable {
    Table,
    Col1,
    Col2,
    Col3,
}

let query = Query::merge()
.into_table(TestTable::Table)
.using(TestTable::Table, Condition::all().add(Expr::col("id").eq(1)))
.when_matched(WhenAction::DoNothing)
.to_owned();

let sql = query.to_string(PostgresQueryBuilder);

assert_eq!(
    sql,
    r#"MERGE INTO "test_table" USING "test_table" ON "id" = 1 WHEN MATCHED DO NOTHING"#
);

WHEN MATCHED with a condition

You can add a condition to the WHEN MATCHED clause:

let query = Query::merge()
.into_table(TestTable::Table)
.using(TestTable::Table, Condition::all().add(Expr::col("id").eq(1)))
.when(
WhenClause::matched(WhenAction::DoNothing)
.and(Condition::all().add(Expr::col("id").eq(3)))
.to_owned(),
)
.to_owned();

let sql = query.to_string(PostgresQueryBuilder);

assert_eq!(
    sql,
    r#"MERGE INTO "test_table" USING "test_table" ON "id" = 1 WHEN MATCHED AND "id" = 3 DO NOTHING"#
);

WHEN NOT MATCHED

You can use WHEN NOT MATCHED to insert new rows:

let query = Query::merge()
.into_table(TestTable::Table)
.using(TestTable::Table, Condition::all().add(Expr::col("id").eq(1)))
.when_not_matched(
MergeInsert::new()
.columns(vec![TestTable::Col1, TestTable::Col2, TestTable::Col3])
.values(vec![Expr::val(1).into(), Expr::val(2).into(), Expr::val(3).into()])
.unwrap()
.into(),
)
.to_owned();

let sql = query.to_string(PostgresQueryBuilder);

assert_eq!(
    sql,
    r#"MERGE INTO "test_table" USING "test_table" ON "id" = 1 WHEN NOT MATCHED INSERT ("col1", "col2", "col3") VALUES (1, 2, 3)"#
);

WHEN NOT MATCHED BY SOURCE

You can also use WHEN NOT MATCHED BY SOURCE:

let query = Query::merge()
.into_table(TestTable::Table)
.using(TestTable::Table, Condition::all().add(Expr::col("id").eq(1)))
.when_not_matched_by_source(WhenAction::DoNothing)
.to_owned();

let sql = query.to_string(PostgresQueryBuilder);

assert_eq!(
    sql,
    r#"MERGE INTO "test_table" USING "test_table" ON "id" = 1 WHEN NOT MATCHED BY SOURCE DO NOTHING"#
);