SQL Database

The SQL database capability allows your application component to interact with compatible SQL databases with prepared statements. While the key-value capability is great for unstructured data storage, the SQL capability lets you interact with data in relational databases where schema can be carefully crafted and managed. This capability is included as one of the open source wasmCloud interfaces and has the capability contract ID wasmcloud:sqldb.

Using this capability

This capability can be used in your project by adding a dependency on the wasmcloud-interface-sqldb crate, and by adding wasmcloud:sqldb to the capabilities section in your application's wasmcloud.toml file.

Cargo.toml

toml
wasmcloud-interface-sqldb = "0.8.1"

toml
wasmcloud-interface-sqldb = "0.8.1"

wasmcloud.toml

toml
[actor]
capabilities = ["wasmcloud:sqldb"]

toml
[actor]
capabilities = ["wasmcloud:sqldb"]

Operations

Operation	Input	Output
execute	Statement	ExecuteResult
query	Statement	QueryResult

Example Usage

This example queries a SQL database for a list of Cosmonauts, ordered by last name. Note here that we use CBOR to properly decode bytes into the Cosmonaut structure that we expect, and the helper functions for decoding are already included in the wasmcloud_interface_sqldb crate.

rust
use serde::{Deserialize, Serialize};
use wasmbus_rpc::actor::prelude::*;
use wasmcloud_interface_sqldb::{minicbor, SqlDb, SqlDbSender, Statement};
const COSMONAUT_TABLE: &str = "cosmonauts";
const COSMONAUT_DB: &str = "cosmonautdb";
#[derive(Default, Deserialize, Serialize, minicbor::Decode, Clone)]
pub(crate) struct Cosmonaut {
    #[n(0)]
    pub firstname: String,
    #[n(1)]
    pub lastname: String,
}
async fn query_database(ctx: &Context) -> RpcResult<Vec<Cosmonaut>> {
    let resp = SqlDbSender::new()
        .query(
            ctx,
            &Statement {
                sql: format!(
                    "select firstname, lastname from {} order by lastname, firstname",
                    COSMONAUT_TABLE
                ),
                database: Some(COSMONAUT_DB.to_string()),
                ..Default::default()
            },
        )
        .await?;
    if resp.num_rows == 0 {
        Ok(Vec::new())
    } else {
        Ok(wasmbus_rpc::minicbor::decode(&resp.rows)?)
    }
}

rust
use serde::{Deserialize, Serialize};
use wasmbus_rpc::actor::prelude::*;
use wasmcloud_interface_sqldb::{minicbor, SqlDb, SqlDbSender, Statement};
const COSMONAUT_TABLE: &str = "cosmonauts";
const COSMONAUT_DB: &str = "cosmonautdb";
#[derive(Default, Deserialize, Serialize, minicbor::Decode, Clone)]
pub(crate) struct Cosmonaut {
    #[n(0)]
    pub firstname: String,
    #[n(1)]
    pub lastname: String,
}
async fn query_database(ctx: &Context) -> RpcResult<Vec<Cosmonaut>> {
    let resp = SqlDbSender::new()
        .query(
            ctx,
            &Statement {
                sql: format!(
                    "select firstname, lastname from {} order by lastname, firstname",
                    COSMONAUT_TABLE
                ),
                database: Some(COSMONAUT_DB.to_string()),
                ..Default::default()
            },
        )
        .await?;
    if resp.num_rows == 0 {
        Ok(Vec::new())
    } else {
        Ok(wasmbus_rpc::minicbor::decode(&resp.rows)?)
    }
}

Choosing an Implementation

We have an implementation of the SQL capability for PostgreSQL databases. You can use the following capability provider for any relational database that supports a Postgres compatible API.

📄️ SQL Database (Postgres)

Postgres client capability provider