2022-04-03Getting Started with Microsoft SQL Server and Rust: Connecting and Querying Data

Time to join the Rust hype train 🚄

The goal in this article is to be able to connect to a SQL Server and store the results in a struct. I have been primarily writing programs in C# and Golang. One of the cruicial features in most apps is being able to interface with a database. There are lots of great libraries out there but only a few support Microsoft SQL Server.

I wrote this guide to help myself and others easily get started.

Step 1 - Creating a new project & Installing dependencies

Let's create a brand new Rust project using cargo cli.

cargo new sqlserver-query

Open the newly created directory in your favorite editor.

We will be using the following dependencies:

dotenv - for storing enviornment variables in an .env file
tiberius - SQL Server Driver
async-std - Async version of rust std
anyhow - for easy idiomatic error handling

Open up the Cargo.toml file and update the dependencies list:

[dependencies]
dotenv = "0.15.0"
tiberius = "0.7.1"
anyhow = "1.0"
[dependencies.async-std]
version = "1.11.0"
features = ["attributes"]

Then run cargo run to install and build the dependencies

Step 2 - Storing our Connection String in an .env file

I have a test database with a table called Vehicles containg the following rows:

Year	Make	Model
2019	Mercedes	CLS
2021	Audi	SQ5
2021	Tesla	Model 3

Coming from C#, we typically use an ADO Connection String like so:
Server=IP_ADDR,PORT;Database=DB;User Id=UID;Password=PASSWORD

Create a new file called .env and add the CONNECTION_STRING key-value pair. We will need to wrap the Connection String in quotes to escape the equal signs.

CONNECTION_STRING="Server=IP_ADDR,PORT;Database=DB;User Id=UID;Password=PASSWORD"

Clear out the main.rs file and replace the contents with the following:

use dotenv::dotenv;
use std::env;

fn main() {
    dotenv().ok();

    let connection_string = env::var("CONNECTION_STRING").expect("CONNECTION_STRING must be set");
    println!("{}", connection_string);
}

Run cargo run to makes sure we are getting our connection string value successfully

Step 3 - Setting up async-std

To setup async std, we will annotate the main function with the macro #[async_std::main]

#[async_std::main]
async fn main() {
    // ...
}

Next, we will have the main function return anyhow::Result<()>

#[async_std::main]
async fn main() -> anyhow::Result<()> {
    // ...

    Ok(())
}

Step 4 - Time to SQL

Now to the fun part, creating the connection and executing the query. We'll create a function called get_vehicles.

Here's the breakdown:

Create our struct to store the row data
Create the connection to the sql server
Execute the query and store results in an array of structs (i.e. Vec)

1 - Create our struct to store the row data

struct Vehicle {
  year: i32,
  make: String,
  model: String
}

2 - Create the connection to the sql server

To do this we'll need to update the imports

use async_std::net::TcpStream;
use tiberius::{Client, Config};

async fn get_vehicles() -> anyhow::Result<Vec<Vehicle>> {
    let connection_string = env::var("CONNECTION_STRING").expect("CONNECTION_STRING must be set");

    let config = Config::from_ado_string(&connection_string)?;
    let tcp = TcpStream::connect(config.get_addr()).await?;
    tcp.set_nodelay(true)?;

    let mut client = Client::connect(config, tcp).await?;

    Ok(vec![])
}

3 - Execute the query and store results in an array of structs (i.e. `Vec`)

async fn get_vehicles() -> anyhow::Result<Vec<Vehicle>> {
    // ...

    // Create our Vec (array) to hold our vehicle structs
    let mut vehicles = Vec::<Vehicle>::new();

    // Execute our queries
    let rows = client
        .query("SELECT Year, Make, Model FROM Vehicles", &[&1i32])
        .await?
        .into_first_result()
        .await?;

    // Loop through the rows
    for row in rows {
        let year: i32 = row.get("Year").unwrap();
        let make: &str = row.get("Make").unwrap();
        let model: &str = row.get("Model").unwrap();

        // Add each vehicle to the array
        vehicles.push(Vehicle {
            year,
            make: make.to_string(),
            model: model.to_string(),
        });
    }

    // Return the vehicles array
    Ok(vehicles)
}

That's it! - Here is the final main.rs file

use async_std::net::TcpStream;
use dotenv::dotenv;
use std::env;
use tiberius::{Client, Config};

struct Vehicle {
    year: i32,
    make: String,
    model: String,
}

#[async_std::main]
async fn main() -> anyhow::Result<()> {
    dotenv().ok();

    let vehicles = get_vehicles().await?;

    for vehicle in vehicles {
        println!("{} {} {}", vehicle.year, vehicle.make, vehicle.model);
    }

    Ok(())
}

async fn get_vehicles() -> anyhow::Result<Vec<Vehicle>> {
    let connection_string = env::var("CONNECTION_STRING").expect("CONNECTION_STRING must be set");

    let config = Config::from_ado_string(&connection_string)?;
    let tcp = TcpStream::connect(config.get_addr()).await?;
    tcp.set_nodelay(true)?;

    let mut client = Client::connect(config, tcp).await?;

    let mut vehicles = Vec::<Vehicle>::new();

    let rows = client
        .query("SELECT Year, Make, Model FROM Vehicles", &[&1i32])
        .await?
        .into_first_result()
        .await?;

    for row in rows {
        let year: i32 = row.get("Year").unwrap();
        let make: &str = row.get("Make").unwrap();
        let model: &str = row.get("Model").unwrap();

        vehicles.push(Vehicle {
            year,
            make: make.to_string(),
            model: model.to_string(),
        });
    }

    Ok(vehicles)
}