main.rs

/// Demonstrates serialize/deserialize by embedding a SQLite database inside a custom
/// binary container format.
///
/// The container prepends a magic header to the raw SQLite bytes, making it impossible
/// to open directly with `SqliteConnectOptions::filename()`. This is the whole point:
/// `sqlite3_serialize` / `sqlite3_deserialize` let you treat a database as an opaque
/// byte slice that can live inside any format you control.
///
/// Container layout:
///   [4 bytes]  magic: b"SQLX"
///   [n bytes]  SQLite database bytes
use sqlx::sqlite::SqliteOwnedBuf;
use sqlx::{Connection, SqliteConnection};
use std::io::{self, Write};
use std::path::Path;

const MAGIC: &[u8; 4] = b"SQLX";

fn write_container(path: &Path, db_bytes: &[u8]) -> io::Result<()> {
    let mut file = std::fs::File::create(path)?;
    file.write_all(MAGIC)?;
    file.write_all(db_bytes)?;
    Ok(())
}

fn read_container(path: &Path) -> io::Result<Vec<u8>> {
    let raw = std::fs::read(path)?;
    assert_eq!(&raw[..4], MAGIC, "not a valid container file");
    Ok(raw[4..].to_vec())
}

#[tokio::main(flavor = "current_thread")]
async fn main() -> anyhow::Result<()> {
    let container_path = Path::new("notes.sqlx");

    let mut conn = SqliteConnection::connect("sqlite::memory:").await?;

    sqlx::raw_sql(
        "create table notes(id integer primary key, body text not null);
         insert into notes(body) values ('hello'), ('world');",
    )
    .execute(&mut conn)
    .await?;

    // serialize and persist inside the custom container
    let snapshot: SqliteOwnedBuf = conn.serialize(None).await?;
    write_container(container_path, snapshot.as_ref())?;
    conn.close().await?;

    // restore into a fresh in-memory connection
    let db_bytes = read_container(container_path)?;
    let owned = SqliteOwnedBuf::try_from(db_bytes.as_slice())?;
    let mut restored = SqliteConnection::connect("sqlite::memory:").await?;
    restored.deserialize(None, owned, false).await?;

    let rows = sqlx::query_as::<_, (i64, String)>("select id, body from notes order by id")
        .fetch_all(&mut restored)
        .await?;
    assert_eq!(rows.len(), 2);

    sqlx::query("insert into notes(body) values ('from restored connection')")
        .execute(&mut restored)
        .await?;

    // serialize the updated database back into the container
    let updated: SqliteOwnedBuf = restored.serialize(None).await?;
    write_container(container_path, updated.as_ref())?;

    std::fs::remove_file(container_path)?;

    Ok(())
}