Skip to main content
The bomboni_common crate provides seamless database integration through optional PostgreSQL and MySQL features. These enable automatic type conversions for Id and UtcDateTime types, allowing direct storage and retrieval without manual serialization.

​
Overview

Bomboni’s database integration provides:
  • Type Safety: Compile-time guarantees for database conversions
  • Zero-Copy Operations: Efficient serialization using references
  • Multiple SQL Dialects: PostgreSQL and MySQL support
  • Automatic Conversions: Implement ToSql/FromSql traits for seamless integration

​
PostgreSQL Integration

Enable PostgreSQL support in your Cargo.toml: 
[dependencies]
bomboni_common = { version = "*", features = ["postgres"] }
postgres-types = "0.2"
bytes = "1.0"

​
Id Type with PostgreSQL

The Id type implements PostgreSQL’s ToSql and FromSql traits:
bomboni_common/src/id/postgres.rs
use bytes::BytesMut;
use postgres_types::{FromSql, IsNull, ToSql, Type, accepts, to_sql_checked};
use bomboni_common::id::Id;

impl ToSql for Id {
    fn to_sql(
        &self,
        ty: &Type,
        out: &mut BytesMut,
    ) -> Result<IsNull, Box<dyn std::error::Error + Sync + Send>>
    where
        Self: Sized,
    {
        self.to_string().to_sql(ty, out)
    }

    accepts!(VARCHAR, TEXT, CSTRING);

    to_sql_checked!();
}

impl<'a> FromSql<'a> for Id {
    fn from_sql(
        ty: &Type,
        raw: &'a [u8],
    ) -> Result<Self, Box<dyn std::error::Error + Sync + Send>> {
        let s = <&str as FromSql>::from_sql(ty, raw)?;
        Ok(s.parse()?)
    }

    accepts!(VARCHAR, TEXT, CSTRING);
}

​
Usage Example

use bomboni_common::id::Id;
use postgres::{Client, NoTls};

let mut client = Client::connect("postgresql://user:pass@localhost/db", NoTls)?;

// Create table
client.execute(
    "CREATE TABLE users (id VARCHAR(26) PRIMARY KEY, name TEXT)",
    &[]
)?;

// Generate and insert ID
let user_id = Id::generate();
client.execute(
    "INSERT INTO users (id, name) VALUES ($1, $2)",
    &[&user_id, &"Alice"]
)?;

// Query with automatic conversion
let row = client.query_one(
    "SELECT id, name FROM users WHERE id = $1",
    &[&user_id]
)?;
let retrieved_id: Id = row.get(0);
assert_eq!(user_id, retrieved_id);

​
UtcDateTime with PostgreSQL

The UtcDateTime type integrates with PostgreSQL’s timestamp types:
bomboni_common/src/date_time/postgres.rs
use bytes::BytesMut;
use postgres_types::{FromSql, IsNull, ToSql, Type, accepts, to_sql_checked};
use time::OffsetDateTime;
use bomboni_common::date_time::UtcDateTime;

impl ToSql for UtcDateTime {
    fn to_sql(
        &self,
        ty: &Type,
        out: &mut BytesMut,
    ) -> Result<IsNull, Box<dyn std::error::Error + Sync + Send>>
    where
        Self: Sized,
    {
        OffsetDateTime::from(*self).to_sql(ty, out)
    }

    accepts!(TIMESTAMP, TIMESTAMPTZ);

    to_sql_checked!();
}

impl<'a> FromSql<'a> for UtcDateTime {
    fn from_sql(
        ty: &Type,
        raw: &'a [u8],
    ) -> Result<Self, Box<dyn std::error::Error + Sync + Send>> {
        let offset_date_time = OffsetDateTime::from_sql(ty, raw)?;
        Ok(offset_date_time.into())
    }

    accepts!(TIMESTAMP, TIMESTAMPTZ);
}

​
Usage Example

use bomboni_common::date_time::UtcDateTime;
use postgres::{Client, NoTls};

let mut client = Client::connect("postgresql://user:pass@localhost/db", NoTls)?;

// Create table with timestamp
client.execute(
    "CREATE TABLE events (id SERIAL PRIMARY KEY, created_at TIMESTAMPTZ)",
    &[]
)?;

// Insert current time
let now = UtcDateTime::now();
client.execute(
    "INSERT INTO events (created_at) VALUES ($1)",
    &[&now]
)?;

// Query with automatic conversion
let row = client.query_one(
    "SELECT created_at FROM events ORDER BY id DESC LIMIT 1",
    &[]
)?;
let created_at: UtcDateTime = row.get(0);
assert_eq!(now, created_at);

​
MySQL Integration

Enable MySQL support in your Cargo.toml: 
[dependencies]
bomboni_common = { version = "*", features = ["mysql"] }
mysql_common = "0.32"

​
Id Type with MySQL

MySQL integration uses the FromValue trait pattern:
bomboni_common/src/id/mysql.rs
use mysql_common::{FromValueError, Value, value::convert::FromValue};
use bomboni_common::id::{Id, ParseIdError};

mod ir {
    use super::{FromValue, FromValueError, Id, ParseIdError, Value};

    #[derive(Debug, Clone, Copy, Eq, PartialEq)]
    pub struct ParseIdIr<T: FromValue>(T::Intermediate, Id);

    impl TryFrom<Value> for ParseIdIr<String> {
        type Error = <<String as FromValue>::Intermediate as TryFrom<Value>>::Error;

        fn try_from(value: Value) -> Result<Self, Self::Error> {
            match value {
                Value::Bytes(bytes) => match String::from_utf8(bytes) {
                    Ok(x) => {
                        let id = x
                            .parse()
                            .map_err(|err: ParseIdError| FromValueError(err.to_string().into()))?;
                        Ok(Self(x, id))
                    }
                    Err(e) => Err(FromValueError(Value::Bytes(e.into_bytes()))),
                },
                v => Err(FromValueError(v)),
            }
        }
    }
}

impl FromValue for Id {
    type Intermediate = ParseIdIr<String>;
}

impl From<Id> for Value {
    fn from(value: Id) -> Self {
        Self::Bytes(value.to_string().into_bytes())
    }
}

​
Usage Example

use bomboni_common::id::Id;
use mysql::{Pool, prelude::*};

let pool = Pool::new("mysql://user:pass@localhost/db")?;
let mut conn = pool.get_conn()?;

// Create table
conn.query_drop(
    "CREATE TABLE users (id VARCHAR(26) PRIMARY KEY, name TEXT)"
)?;

// Generate and insert ID
let user_id = Id::generate();
conn.exec_drop(
    "INSERT INTO users (id, name) VALUES (?, ?)",
    (&user_id, "Alice")
)?;

// Query with automatic conversion
let retrieved: Option<(Id, String)> = conn.exec_first(
    "SELECT id, name FROM users WHERE id = ?",
    (&user_id,)
)?;

if let Some((id, name)) = retrieved {
    assert_eq!(user_id, id);
    assert_eq!(name, "Alice");
}

​
UtcDateTime with MySQL

MySQL integration for timestamps:
bomboni_common/src/date_time/mysql.rs
use mysql_common::{
    Value,
    value::convert::{FromValue, ParseIr},
};
use time::PrimitiveDateTime;
use bomboni_common::date_time::UtcDateTime;

impl FromValue for UtcDateTime {
    type Intermediate = ParseIr<PrimitiveDateTime>;
}

impl From<ParseIr<PrimitiveDateTime>> for UtcDateTime {
    fn from(ir: ParseIr<PrimitiveDateTime>) -> Self {
        ir.commit().into()
    }
}

impl From<UtcDateTime> for Value {
    fn from(value: UtcDateTime) -> Self {
        PrimitiveDateTime::from(value).into()
    }
}

​
Usage Example

use bomboni_common::date_time::UtcDateTime;
use mysql::{Pool, prelude::*};

let pool = Pool::new("mysql://user:pass@localhost/db")?;
let mut conn = pool.get_conn()?;

// Create table
conn.query_drop(
    "CREATE TABLE events (id INT AUTO_INCREMENT PRIMARY KEY, created_at DATETIME)"
)?;

// Insert current time
let now = UtcDateTime::now();
conn.exec_drop(
    "INSERT INTO events (created_at) VALUES (?)",
    (&now,)
)?;

// Query with automatic conversion
let retrieved: Option<UtcDateTime> = conn.query_first(
    "SELECT created_at FROM events ORDER BY id DESC LIMIT 1"
)?;

assert_eq!(Some(now), retrieved);

​
SQL Generation with bomboni_request

Combine database integration with bomboni_request for dynamic SQL generation: 
use bomboni_request::sql::{SqlFilterBuilder, SqlDialect, SqlRenameMap};
use bomboni_request::filter::Filter;
use bomboni_request::testing::schema::RequestItem;
use bomboni_macros::btree_map_into;

let schema = RequestItem::get_schema();
let filter = Filter::parse(r#"NOT task.deleted AND user.age >= 30"#).unwrap();

// Generate PostgreSQL SQL with placeholders
let (sql, args) = SqlFilterBuilder::new(SqlDialect::Postgres, &schema)
    .set_rename_map(&SqlRenameMap {
        members: btree_map_into! {
            "user" => "u",
            "task.userId" => "user_id",
        },
        functions: std::collections::BTreeMap::new(),
    })
    .build(&filter)
    .unwrap();

assert_eq!(sql, r#"NOT ("task"."deleted") AND "u"."age" >= $1"#);
assert_eq!(args[0], bomboni_request::value::Value::Integer(30));

​
Using with Database Queries

use bomboni_common::id::Id;
use bomboni_request::filter::Filter;
use bomboni_request::sql::{SqlFilterBuilder, SqlDialect};
use postgres::{Client, NoTls};

let mut client = Client::connect("postgresql://user:pass@localhost/db", NoTls)?;

// Parse filter from API request
let filter = Filter::parse(r#"age >= 18 AND name:"John""#).unwrap();

// Generate SQL
let schema = /* your schema */;
let (where_clause, args) = SqlFilterBuilder::new(SqlDialect::Postgres, &schema)
    .build(&filter)
    .unwrap();

// Execute query with generated SQL
let query = format!("SELECT id, name, age FROM users WHERE {}", where_clause);
let rows = client.query(&query, &args)?;

for row in rows {
    let id: Id = row.get(0);
    let name: String = row.get(1);
    let age: i32 = row.get(2);
    println!("User: {} - {} (age {})", id, name, age);
}

​
Type Mappings

​
PostgreSQL Type Compatibility

Rust TypePostgreSQL TypesNotes
IdVARCHAR, TEXT, CSTRINGStored as ULID string (26 chars)
UtcDateTimeTIMESTAMP, TIMESTAMPTZMicrosecond precision

​
MySQL Type Compatibility

Rust TypeMySQL TypesNotes
IdVARCHAR(26), TEXTStored as ULID string
UtcDateTimeDATETIME, TIMESTAMPSecond precision

​
Best Practices

Use VARCHAR(26) for Id fields in database schemas to match ULID string length exactly.
When using UtcDateTime with MySQL, be aware that MySQL’s DATETIME type has second precision, while PostgreSQL’s TIMESTAMPTZ has microsecond precision.

​
Schema Design

-- PostgreSQL schema example
CREATE TABLE books (
    id VARCHAR(26) PRIMARY KEY,
    title TEXT NOT NULL,
    author_id VARCHAR(26) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

CREATE INDEX idx_books_author ON books(author_id);
CREATE INDEX idx_books_created ON books(created_at);

-- MySQL schema example
CREATE TABLE books (
    id VARCHAR(26) PRIMARY KEY,
    title TEXT NOT NULL,
    author_id VARCHAR(26) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

CREATE INDEX idx_books_author ON books(author_id);
CREATE INDEX idx_books_created ON books(created_at);

​
Connection Pooling

Always use connection pooling in production: 
use bomboni_common::id::Id;
use deadpool_postgres::{Config, Manager, ManagerConfig, Pool, RecyclingMethod, Runtime};
use tokio_postgres::NoTls;

// Configure pool
let mut cfg = Config::new();
cfg.host = Some("localhost".to_string());
cfg.dbname = Some("mydb".to_string());
cfg.manager = Some(ManagerConfig {
    recycling_method: RecyclingMethod::Fast,
});

let pool = cfg.create_pool(Some(Runtime::Tokio1), NoTls)?;

// Use in application
async fn get_user(pool: &Pool, id: Id) -> Result<User, Error> {
    let conn = pool.get().await?;
    let row = conn.query_one(
        "SELECT id, name, email FROM users WHERE id = $1",
        &[&id]
    ).await?;
    
    Ok(User {
        id: row.get(0),
        name: row.get(1),
        email: row.get(2),
    })
}

Common Types

Learn about Id and UtcDateTime types

Request Parsing

Build dynamic queries with filters

gRPC Services

Use database types in gRPC services

Build docs developers (and LLMs) love

Get started for freeTalk to us