Cookbook / Data and storage

Upserts, bulk writes and enums

Insert or update in one statement, load thousands of rows quickly, and store enums as text.

Three things you reach for as soon as an app syncs data from somewhere else: upserts for "insert, or update the existing row", insert_many for loading rows in a few statements instead of one per row, and SqlEnum for a typed status column. This runs on SQLite as written, and the same code works on PostgreSQL and MySQL.

src/main.rs

use axeon_orm::prelude::*;
use axeon_orm::{OnConflict, SqlEnum};

#[derive(SqlEnum, Clone, Copy, Debug, PartialEq)]
enum Status {
    Draft,      // "draft"
    InStock,    // "in_stock"
    #[sql_enum(rename = "gone")]
    Discontinued,
}

#[derive(Model, Clone, Debug)]
struct Product {
    id: i64,
    #[model(unique)]
    sku: String,
    name: String,
    price: i64,
    status: Status,
}

fn product(sku: &str, name: &str, price: i64) -> Product {
    Product { id: 0, sku: sku.into(), name: name.into(), price, status: Status::InStock }
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let db = Db::connect("sqlite://catalog.db?mode=rwc").await?;
    db.create_table::<Product>().await?; // for demos; use migrations in real apps

    // Load many rows in a few statements. Rows come back with their IDs, in order.
    let stored = Product::insert_many(
        &db,
        &[product("L-1", "Lamp", 120), product("D-1", "Desk", 480), product("C-1", "Chair", 150)],
    )
    .await?;
    println!("inserted {} rows", stored.len());

    // Insert, or update the row that already has this SKU. Returns the stored row.
    let lamp = product("L-1", "LED lamp", 99)
        .upsert(&db, OnConflict::on(Product::SKU))
        .await?;
    println!("{} now costs {}", lamp.name, lamp.price);

    // Overwrite only some columns when the row exists.
    product("D-1", "ignored name", 450)
        .upsert(&db, OnConflict::on(Product::SKU).update(Product::PRICE))
        .await?;

    // Sync a whole feed at once.
    let feed = vec![product("L-1", "LED lamp", 95), product("S-1", "Shelf", 80)];
    let synced = Product::upsert_many(&db, &feed, OnConflict::on(Product::SKU)).await?;
    println!("synced {} rows", synced.len());

    // Insert unless it exists: None means it was already there.
    let again = product("S-1", "Shelf", 80).insert_or_ignore(&db, OnConflict::on(Product::SKU)).await?;
    println!("duplicate ignored: {}", again.is_none());

    // Filter on the enum like any column.
    let on_sale = Product::query()
        .filter(Product::STATUS.is_in([Status::InStock, Status::Draft]))
        .order_by(Product::PRICE.asc())
        .all(&db)
        .await?;
    for p in on_sale {
        println!("{:>6} {} ({})", p.price, p.name, p.status.as_str());
    }
    Ok(())
}

Terminal

cargo run

Notes#

By default an upsert overwrites every column except the primary key, the conflict columns and created_at. Use .update(column) to limit it, and .and(column) to conflict on several columns together.
The conflict columns need a unique constraint or index. MySQL ignores the named columns and reacts to a conflict on any unique key of the table.
insert_many sends up to 1000 rows per statement. With &db, several statements share one transaction, so a failure inserts nothing; with &mut tx they join yours. A conflict key may appear only once per upsert_many call, which is checked before anything is written.
Enums are stored as text, so they behave the same everywhere. Generated tables get a CHECK listing the allowed values, and a row holding an unknown value is a decode error, never a panic.
On PostgreSQL, for_update().skip_locked() lets several workers claim different rows from one table. SQLite has no row locks, so those methods are left out there.