"A pleasure to meet you. I am C-3p0, JSON-DB Relations."

C3p0: "Hello, I don't believe we have been introduced. A pleasure to meet you. I am C-3p0, JSON-DB Relations."

Do you think JSON is excellent, but it could be better handled in your DB code?

If you use Sqlx, C3p0 brings you a set of tools to simplify JSON integration in your database workflow.

So, if you would like to be able to fetch/delete/insert/update JSON object interactively with your Sql DB like it was a NoSQL DB, then keep reading!

What C3p0 is not

Although it provides a high-level interface for basic CRUD operations, C3p0 is neither an ORM nor a replacement for one—or for any similar tool. In fact, C3p0 is not an ORM at all. It allows storing and retrieving JSON objects from the database, but it does not manage cross-table relationships. Each object type is stored in its own dedicated table, using a single column of JSON type.

C3p0: "I see, Sir Luke".

Great!

What C3p0 is

C3p0 is a library designed for integrating JSON data with relational databases. It offers the following capabilities:

• Performs basic CRUD operations on JSON objects
• Automatically generates the necessary SQL queries to interact with database tables, without relying on macros
• Based in sqlx, supports Postgres, as well as MySQL and SQLite

Prerequisites

It uses async closures, so it requires at least Rust version 1.85.

Supported database versions

The minimum versions required to support these features are:

Our CI runs on the Millennium Falcon on-board computer that, at the time of the last hyperdrive upgrade, was equipped with Postgres latest, MySQL 8.1, MariaDB 11.3, TiDB v8.5.0, and SQLite as bundled by libsqlite3-sys.

History

The first C3p0 version was written in Java...

C3p0: "If I told you half the things I’ve heard about this Jabba the Hutt, you’d probably short circuit."

I said "Java", "Ja"-"va". Stay focused, please!

Anyway, Java is slowly showing its age, and we got a bit bored about it.

C3p0: "they're using a very primitive dialect".

Indeed.

On the contrary, our interest in the Rust programming language has kept growing over time; so, we experimented with it more and more and, finally, migrated some critical portions of our code to Rust.

Just said, we love it.

We believe that Rust is a better overall language.

C3p0: "The city's central computer told you?"

Yes! It allows us to achieve better resource usage, to avoid the garbage collector and the virtual machine, and, at the same time, to get a better and safer concurrency level.

Can I use it in production?

Han "Don't worry. Everything's gonna be fine. Trust me."

C3p0: "Every time he employs that phrase, my circuitry becomes erratic!"

Han: ???

C3p0: "Artoo says that the chances of survival are 725 to 1".

By the way, regardless of what Artoo said, we've survived using it in production since 2018.

Example of usage

Here is an example of how to use C3p0 with sqlx and a Postgres database:

#[cfg(feature = "postgres")]
pub mod with_postgres {

    use c3p0::{
        sqlx::PgPool, C3p0Error, C3p0Pool, DataType, PgC3p0Pool, Tx
    };
    use serde::{Deserialize, Serialize};

    /// Example of a model for a database table
    #[derive(Clone, Serialize, Deserialize, Debug, PartialEq)]
    pub struct UserData {
        pub username: String,
        pub email: String,
    }

    /// Implement the Data trait for the UserData model using the table "USER_DATA"
    impl DataType for UserData {
        const TABLE_NAME: &'static str = "USER_DATA";
        type CODEC = Self;
    }

    /// Example of how to use c3p0 with sqlx and a Postgres database
    pub async fn example(sqlx_pool: PgPool) {

        // Create a C3p0 pool from the Sqlx pool
        let c3p0 = PgC3p0Pool::new(sqlx_pool);

        // Open a transaction to the database.
        // C3p0 will commit or rollback the transaction automatically.
        // The transaction will be committed if the result is Ok, otherwise it will be rolled back.
        let result: Result<_, C3p0Error> = c3p0
            .transaction(async |tx| {
                // Create the table if it doesn't exist. Usually this would be done in a migration
                assert!(tx.create_table_if_not_exists::<UserData>().await.is_ok());
                println!("Table created!");

                // Create a new UserData object
                let user_data = UserData {
                    username: "Francesco Cina".to_string(),
                    email: "ufoscout@ufoscout.com".to_string(),
                };

                // Save the new UserData object to the database
                let create_user = tx.save(user_data.into()).await.unwrap();
                println!("Saved user data: {create_user:?}");

                // Get the saved UserData object from the database
                let fetch_user = tx.fetch_one_by_id::<UserData>(create_user.id).await.unwrap();
                assert_eq!(fetch_user, create_user);

                // Delete the saved UserData object from the database
                let deleted_rows_count = tx.delete_by_id::<UserData>(create_user.id).await.unwrap();
                assert_eq!(deleted_rows_count, 1);

                // Count the number of UserData objects in the database
                let count = tx.count_all::<UserData>().await.unwrap();
                assert_eq!(count, 0);

                // delete the table
                tx.drop_table_if_exists::<UserData>(true).await.unwrap();

                Ok(())
            })
            .await;
        
        assert!(result.is_ok());
    }
}

Schema management — use sqlx migrations in production

C3p0 ships a Tx::create_table_if_not_exists::<DATA>() helper, but it is meant for unit tests, integration tests, and quick local exploration only, for real applications enable the migrate feature and manage your schema with sqlx migrations (or another migration tool).

The tables used by C3p0 should have the following structure (the id, version, create_time, update_time, and data columns are mandatory), example with a USER_DATA table:

PostgreSQL:

-- Mandatory columns
CREATE TABLE USER_DATA (
    id bigserial PRIMARY KEY,
    version bigint NOT NULL,
    create_time TIMESTAMPTZ NOT NULL,
    update_time TIMESTAMPTZ NOT NULL,
    data JSONB NOT NULL
);

-- You can add your constraints and indexes here, e.g.:
CREATE INDEX USER_DATA_username_idx ON USER_DATA ((data ->> 'username'));

MySQL / MariaDB / TiDB:

-- Mandatory columns
CREATE TABLE USER_DATA (
    id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    version BIGINT NOT NULL,
    create_time TIMESTAMP(3) NOT NULL,
    update_time TIMESTAMP(3) NOT NULL,
    data JSON NOT NULL
);

-- You can add your constraints and indexes here, e.g.:
CREATE UNIQUE INDEX USER_DATA_username_idx
    ((JSON_VALUE(data, '$.username' RETURNING CHAR(255))));

SQLite:

-- Mandatory columns
CREATE TABLE USER_DATA (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    version INTEGER NOT NULL,
    create_time TEXT NOT NULL,
    update_time TEXT NOT NULL,
    data JSON NOT NULL CHECK (json_valid(data))
);

-- You can add your constraints and indexes here, e.g.:
CREATE UNIQUE INDEX USER_DATA_username ON USER_DATA(DATA->>'$.username');