With the latest release of Fermyon Spin (v 0.7.0), we can persist data in MySQL (persistence in PostgreSQL has been available for quite some time now). Being able to persist and retrieve some sort of data is a must-have for any kind of application.

In this article, we’ll take a look at a full-fledged CRUD implementation. We’ll build an HTTP-API that allows its users to Create, Read, Update, and Delete products using corresponding HTTP methods (POST, GET, PUT, and DELETE). All code shown in this article is available on GitHub at ThorstenHans/spin-crud-mysql.

• Prepare MySQL database
• Create the Spin Application
• Implement CRUD for MySQL with Rust and Fermyon Spin
  • Layout the CRUD API
  • Some helpers to streamline component implementation
  • The CRUD implementation
    • Create new products
    • Reading products
    • Read all Products
    • Read Product by Id
    • Update particular products
    • Delete particular products
  • Implement Component Configuration
  • Implement the actual component function
• Test the CRUD implementation
• Conclusion

Prepare MySQL database

We need access to a MySQL database. Luckily, we can run MySQL with ease in Docker. Let’s quickly spin up a new MySQL server in Docker and configure the database for our sample application.

# run MySQL in Docker
docker run -d -p 3306:3306 \
 -e MYSQL_ROOT_PASSWORD=adminFooBar \
 -e MYSQL_USER=sampleuser \
 -e MYSQL_PASSWORD=foobar \
 -e MYSQL_DATABASE=products \
 --name mysql \
 mysql

# Create Products table leveraging ubuntu container
docker run -it --rm --link mysql ubuntu
# Install mysql-client
$ apt update
$ apt install mysql-client --yes
$ mysql -h mysql -u sampleuser -p
# provide user password when asked (foobar)

# Select products database
mysql> use products;
# Create the products table
mysql> create table products.Products
(
  Id bigint unsigned auto_increment primary key,
  Name varchar(250) charset utf8mb3 not null,
  Price float not null
);
# Exit mysql CLI using CTRL+D
# Exit ubuntu container use CTRL+D

With the MySQL database in place, we can create our Spin application.

Create the Spin Application

Verify that you’ve installed (at least) Spin CLI in version 0.7.0 (spin -V) and that you’ve access to the latest templates (spin templates install --git https://github.com/fermyon/spin --update).

Let’s create the Spin application using spin new:

# Create the Spin application
spin new -o ./crud-with-mysql http-rust crud-with-mysql
Project description: CRUD with MySQL
HTTP base: /
HTTP path: /...

# Open the Spin application with your fav. editor (here VS Code)
code ./crud-with-mysql

Implement CRUD for MySQL with Rust and Fermyon Spin

Layout the CRUD API

We want to build a full-fledged CRUD API that allows us to interact with fictive products. In Rust, enums (enum) are pure dope. They’re so powerful compared to enum implementations of other languages (but that’s a topic for a different article 😁). For demonstration purposes, let’s create an Api enum that defines all handlers we want to implement. Besides the actual CRUD handlers, I decided to add supportive handlers, like BadRequest, NotFound, and others, to make the component implementation as clean and homogenous as possible.

Enum variations representing actual CRUD handlers define the “payload” they’ve to deal with. For example, let’s look at Create(String, f32); the variation has a String and an f32, representing data that we take from incoming requests and hand it over to the code that is responsible for actually creating a fictive product and storing it in MySQL:

enum Api {
  Create(String, f32),
  ReadAll,
  ReadById(u64),
  Update(u64, String, f32),
  Delete(u64),
  BadRequest,
  NotFound,
  MethodNotAllowed,
  InternalServerError,
}

Having the API “shaped” and supportive handlers created as enum variants, we must create an instance of the Api enum when requests hit our Spin component:

fn api_from_request(req: Request) -> Api {
  match *req.method() {
    http::Method::POST => match ProductCreateModel::from_bytes(&req.body().clone().unwrap_or_default()) {
      Ok(model) => Api::Create(model.name, model.price),
      Err(_) => Api::BadRequest,
    },
    http::Method::GET => match req.headers().get("spin-path-info") {
      None => Api::InternalServerError,
      Some(v) => match get_id_from_route(v) {
        Ok(Some(id)) => Api::ReadById(id),
        Ok(None) => Api::ReadAll,
        Err(()) => Api::NotFound,
      },
    },
    http::Method::PUT => match req.headers().get("spin-path-info") {
      None => Api::InternalServerError,
      // 🤔 can we join those two matches somehow?
      Some(v) => match get_id_from_route(v) {
        Ok(Some(id)) => match ProductUpdateModel::from_bytes(&req.body().clone().unwrap_or_default()) {
          Ok(model) => Api::Update(id, model.name, model.price),
          Err(_) => Api::BadRequest,
        },
        Ok(None) => Api::NotFound,
        Err(()) => Api::NotFound,
      },
    },
    http::Method::DELETE => match req.headers().get("spin-path-info") {
      None => Api::InternalServerError,
      Some(v) => match get_id_from_route(v) {
        Ok(Some(id)) => Api::Delete(id),
        Ok(None) => Api::NotFound,
        Err(()) => Api::NotFound,
      },
    },
    _ => Api::MethodNotAllowed,
  }
}

The api_from_request function is mission-critical for our sample. Depending on the HTTP method of the particular request, we must choose the correct variant and provide the necessary data (e.g., the id that we read from the request URI).

We’ll call api_from_request in our Spin component function (the function decorated with #[http_component]) later in this article.

Some helpers to streamline component implementation

Although the builder APIs provided by spin_sdk are great for smaller “projects”, I found it super handy to have some sort of helpers to simplify the process of creating common HTTP responses. Those helpers are encapsulated in their module (utils.rs):

# utils.rs

pub(crate) fn internal_server_error(err: String) -> Result<Response> {
  Ok(http::Response::builder()
    .status(http::StatusCode::INTERNAL_SERVER_ERROR)
    .header(http::header::CONTENT_TYPE, "text/plain")
    .body(Some(err.into()))?)
}

pub(crate) fn ok(payload: String) -> Result<Response> {
  Ok(http::Response::builder()
    .status(http::StatusCode::OK)
    .header(http::header::CONTENT_TYPE, "application/json")
    .body(Some(payload.into()))?)
}

pub(crate) fn method_not_allowed() -> Result<Response> {
  quick_response(http::StatusCode::METHOD_NOT_ALLOWED)
}

pub(crate) fn bad_request() -> Result<Response> {
  quick_response(http::StatusCode::BAD_REQUEST)
}

pub(crate) fn not_found() -> Result<Response> {
  quick_response(http::StatusCode::NOT_FOUND)
}

pub(crate) fn no_content() -> Result<Response> {
  quick_response(http::StatusCode::NO_CONTENT)
}

fn quick_response(s: http::StatusCode) -> Result<Response> {
  Ok(http::Response::builder().status(s).body(None)?)
}

The CRUD implementation

In version 0.7.0, the Spin SDK (for Rust spin_sdk) comes with batteries included to interact with MySQL. We can use spin_sdk::mysql::query to send queries to MySQL that return some value (Result<spin_sdk::mysql::RowSet, spin_sdk::mysql::MysqlError>), and we can use spin_sdk::mysql::execute to execute a statement or invoke a stored procedure not returning a value (Result<(), spin_sdk::mysql::MysqlError>).

Whether you use query or execute, both take the connection string for your MySQL database (in the following snippets passed as adr: &str), a TSQL statement, and (optionally) some parameters. If your statement fails, you’ll get an Err(spin_sdk::mysql::MyqlError) that you can use to handle the actual error correctly.

For query or statement parameters, we use a vector of spin_sdk::mysql::ParameterValue in combination with parameter-tokens ? to prevent common attack surfaces (SQL Injection).

Create new products

First, let’s look at implementing the create handler. We use spin_sdk::mysql::execute to insert the fictive product in the Products table. If that call succeeds, we send an extra query to retrieve the last id (auto increment on the Id column) generated by MySQL (SELECT LAST_INSERT_ID()).

Note: This part should be coordinated in systems facing a vast amount of concurrent load. However, I could not join both statements and execute them as a single query (for now).

Finally, we construct an HTTP 201 response and provide the LOCATION header:

fn handle_create(adr: &str, name: String, price: f32) -> Result<Response> {
  let statement = "INSERT INTO Products (Name, Price) VALUES (?, ?)";
  let params = vec![
    ParameterValue::Str(name.as_str()),
    ParameterValue::Floating32(price),
  ];

  spin_sdk::mysql::execute(adr, statement, &params)?;
  let rowset = spin_sdk::mysql::query(adr, "SELECT LAST_INSERT_ID()", &[])?;
  match rowset.rows.first() {
    Some(row) => {
      let id = u64::decode(&row[0])?;
      Ok(http::Response::builder()
        .status(http::StatusCode::CREATED)
        .header(http::header::LOCATION, format!("/{}", id))
        .body(None)?)
    }
    None => internal_server_error(String::from("Could not persist product")),
  }
}

Reading products

We have two read handlers. One is for reading all products from MySQL, and the other is for reading a particular product identified using the Id column. For both handlers, we will use spin_sdk::mysql::query.

However, the response-model differs, because we may not want to load and provide the price for every product when asking for the list of all products. That said, the models.rs containers tailored data transfer objects (DTOs) for inbound and outbound contracts (API models):

#[derive(Serialize, Debug)]
pub struct ProductDetailsModel {
    pub id: u64,
    pub name: String,
    pub price: f32,
}

#[derive(Serialize, Debug)]
pub struct ProductListModel {
    pub id: u64,
    pub name: String,
}

Read all Products

When reading multiple items from MySQL, we use a simple for-loop to iterate over all rows (spin_sdk::mysql::Row) of our set of rows (spin_sdk::mysql::RowSet) and use serde_json to serialize our vector of products into a string. Also notice the ok method, which is a helper method implemented in utils.rs (Do NOT confound this method with the Ok variant of the default Rust Result<T, E> enum):

fn handle_read_all(adr: &str) -> Result<Response> {
  let statement = "SELECT Id, Name FROM Products";
  let params = vec![];

  let rowset = spin_sdk::mysql::query(adr, statement, &params)?;
  let mut products = vec![];
  for row in rowset.rows {
    let p = ProductListModel::from_row(&row)?;
    products.push(p)
  }
  let payload = serde_json::to_string(&products)?;

  ok(payload)
}

Read Product by Id

Reading a particular product using its identifier is similar to reading the list of all products. Comparing both implementations, we have two differences:

1. We try to access the first row in the set of rows directly (rowset.rows.first())
2. We construct an instance of ProductDetailsModel instead of ProductListModel because we want to include the price of the particular product.

fn handle_read_by_id(adr: &str, id: u64) -> Result<Response> {
  let statement = "SELECT Name, Price FROM Products WHERE Id=?";
  let params = vec![ParameterValue::Uint64(id)];

  let rowset = spin_sdk::mysql::query(adr, statement, &params)?;
  match rowset.rows.first() {
    Some(row) => {
      let product = ProductDetailsModel::from_row(id, row)?;
      let payload = serde_json::to_string(&product)?;
      ok(payload)
    }
    None => not_found(),
  }
}

Update particular products

To update a particular product, use spin_sdk::mysql:execute in combination with the handle_read_by_id function we implemented a few seconds ago. This allows us to re-use the existing implementation of materializing an instance of ProductDetailsModel, serializing it into a String , and dealing with requests asking to update a non-existing product (HTTP 404 (Not Found)):

fn handle_update(adr: &str, id: u64, name: String, price: f32) -> Result<Response> {
  let statement = "UPDATE Products SET Name=?, Price=? WHERE Id=?";
  let params = vec![
    ParameterValue::Str(name.as_str()),
    ParameterValue::Floating32(price),
    ParameterValue::Uint64(id),
  ];
  spin_sdk::mysql::execute(adr, statement, &params)?;
  handle_read_by_id(adr, id)
}

Delete particular products

When deleting a product from the database, we can again use spin_sdk::mysql::execute. However, the implementation of the delete-handler is even more straightforward than the implementation of the update-handler.

We don’t have to read the product from the database anymore. So we can quickly respond with HTTP 204 (No Content) or HTTP 500 if any error happens.

fn handle_delete_by_id(adr: &str, id: u64) -> Result<Response> {
  let statement = "DELETE FROM Products WHERE Id = ?";
  let params = vec![ParameterValue::Uint64(id)];
  match spin_sdk::mysql::execute(adr, statement, &params) {
    Ok(_) => no_content(),
    Err(_) => internal_server_error(String::from("Error while deleting product")),
  }
}

The careful reader may notice that this implementation does not check if a product with the provided id exists. You should consider implementing this in real-world scenarios to ensure your API responses are meaningful and align with REST idioms.

Implement Component Configuration

The configuration for our sample application is quite trivial. We need the connection string to access our MySQL database. The Configuration struct and loading configuration data at runtime is encapsulated in config.rs as shown here:

# config.rs
pub struct Configuration {
  pub mysql_address: String,
}

impl Configuration {
  pub fn new() -> Result<Self, anyhow::Error> {
    let adr = spin_sdk::config::get("mysql_address")?;

    Ok(Configuration {
      mysql_address: adr,
    })
  }
}

For demonstration purposes, we can provide the connection string for the MySQL database as part of our component configuration [component.config] in the Spin manifest Spin.toml:

# Spin.toml

# snip
[[component]]
id = "crud-with-mysql"
source = "target/wasm32-wasi/release/crud_with_mysql.wasm"

[component.config]
mysql_address = "mysql://sampleuser:[email protected]/products"

[component.trigger]
route = "/..."

[component.build]
command = "cargo build --target wasm32-wasi --release" 

Also, consider reading my “Master configuration data in Fermyon Spin” article and learn how to deal with sensitive and non-sensitive configuration data in Spin applications.

Implement the actual component function

Having all the handlers revisited, we can look at the implementation of the Spin component. Once we’ve loaded the configuration (cfg), we can simply match the variants of our Api enum and invoke the corresponding handler or the necessary helper function:

#[http_component]
fn crud_with_my_sql(req: Request) -> Result<Response> {
  let cfg = Configuration::new()?;

  match as_api(req) {
    Api::BadRequest => bad_request(),
    Api::InternalServerError => internal_server_error(String::from("")),
    Api::MethodNotAllowed => method_not_allowed(),
    Api::Create(name, price) => handle_create(&cfg.mysql_address, name, price),
    Api::Update(id, name, price) => handle_update(&cfg.mysql_address, id, name, price),
    Api::ReadAll => handle_read_all(&cfg.mysql_address),
    Api::ReadById(id) => handle_read_by_id(&cfg.mysql_address, id),
    Api::Delete(id) => handle_delete_by_id(&cfg.mysql_address, id),
    _ => not_found(),
  }
}

Test the CRUD implementation

Finally, everything is in place, and we can test our CRUD sample. Use spin build --up --follow-all to fire-up the sample (--follow-all ensures we get all logs forwarded to STDOUT):

spin build --up --follow-all
Executing the build command for component crud-with-mysql: cargo build --target wasm32-wasi --release
  Compiling crud-with-mysql v0.1.0 (/Users/thorsten/dev/thorstenhans/crud)
  Finished release [optimized] target(s) in 0.50s
Successfully ran the build command for the Spin components.
Serving http://127.0.0.1:3000
Available Routes:
 crud-with-mysql: http://127.0.0.1:3000 (wildcard)

Although I stumbled upon hurl (Orange-OpenSource/hurl) during the Advent of Spin, let’s issue some API calls leveraging good old curl:

curl -iX POST --json '{"name": "Milk", "price": 1.49}' http://localhost:3000
# HTTP/1.1 201 Created
# location: /1
# content-length: 0
# date: Mon, 10 Dec 2022 23:28:00 GMT


curl -iX GET http://localhost:3000
# HTTP/1.1 200 OK
# content-type: application/json
# content-length: 142
# date: Mon, 19 Dec 2022 32:28:36 GMT

# [{"id":2,"name":"Milk"}]%


curl -iX GET http://localhost:3000/1
# HTTP/1.1 200 OK
# content-type: application/json
# content-length: 35
# date: Mon, 19 Dec 2022 23:29:18 GMT

# {"id":1,"name":"Milk","price":1.59}%


curl -iX PUT --json '{"name": "Milk", "price": 1.29}' http://localhost:3000/1
# HTTP/1.1 200 OK
# content-type: application/json
# content-length: 35
# date: Non, 19 Dec 2022 23:29:43 GMT

# {"id":2,"name":"Milk","price":1.29}%


curl -iX DELETE http://localhost:3000/1
# HTTP/1.1 204 No Content
# date: Mon, 19 Dec 2022 23:31:08 GMT

Conclusion

With the release of Spin 0.7.0, we can finally choose between PostgreSQL and MySQL when it comes to persisting relational data. Having support for different databases is great, and I hope to see other big players like MsSQL appearing in the not-so-distant future. Spin-SDK additions in the context of MySQL are not that big of a deal. You could discover everything you need to learn in an hour or so. That said, I still miss some features here. I would appreciate having:

• First-class transaction support
• Being able to concat (or join) multiple statements in a single query /execute call without having to go down the road and write a stored proc in MySQL

If you haven’t read the announcement, go and check Fermyons blog post to see what else is included in Spin 0.7.0.

Last, but not least, I hope you enjoyed the read.

-Thorsten