MSSQL #

Microsoft SQL Server (MSSQL) is the dominant enterprise database in the Microsoft ecosystem and in many large companies. Accessing it from Rust requires special attention to several things that differ from MySQL or PostgreSQL: different T-SQL syntax (parameter binding @P1 instead of ?), unique data types (NVARCHAR, UNIQUEIDENTIFIER, DATETIME2), how to get the ID after INSERT (OUTPUT INSERTED.id), and fewer driver options. There are two main choices: sqlx with the MSSQL feature, or the tiberius crate, a native Rust driver for MSSQL. This article covers both, with an emphasis on the important differences from MySQL.

Choosing a Driver #

flowchart TD
    Q{What do you need?}
    Q --> S["sqlx + mssql feature\nConsistent API with MySQL/PostgreSQL\nCompile-time query checking\nRecommended for new projects"]
    Q --> T["tiberius\nNative Rust driver\nLower-level control\nUseful when sqlx doesn't support a feature"]
    Q --> O["tiberius via connection pool\n(bb8 or deadpool)"]

Aspect	`sqlx` (mssql)	`tiberius` directly
API	Consistent with other databases	SQL Server specific
Query checking	Compile-time	Runtime
Connection pool	Built-in	Needs bb8/deadpool
Ease of use	High	Moderate
Control	Moderate	Full

Installation with `sqlx` #

[dependencies]
sqlx = { version = "0.7", features = ["runtime-tokio-native-tls", "mssql", "macros", "chrono"] }
tokio = { version = "1", features = ["full"] }
serde = { version = "1", features = ["derive"] }
chrono = "0.4"

MSSQL Connection Strings #

The MSSQL connection string format differs from MySQL:

# SQL Server authentication
mssql://username:password@host:1433/nama_database

# Windows authentication (domain)
mssql://domain%5Cusername:password@host/nama_database

# With TrustServerCertificate for dev/testing
mssql://sa:Password123!@localhost:1433/master?trustServerCertificate=true

# Azure SQL Database
mssql://username@server:[email protected]:1433/database

Connections and Connection Pools #

use sqlx::mssql::MssqlPoolOptions;
use sqlx::MssqlPool;

async fn buat_pool(url: &str) -> Result<MssqlPool, sqlx::Error> {
    MssqlPoolOptions::new()
        .max_connections(10)
        .min_connections(2)
        .acquire_timeout(std::time::Duration::from_secs(10))
        .connect(url)
        .await
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // For local development, trustServerCertificate is often needed
    let url = std::env::var("MSSQL_URL")
        .unwrap_or_else(|_| {
            "mssql://sa:Password123!@localhost:1433/master?trustServerCertificate=true"
                .to_string()
        });

    let pool = buat_pool(&url).await?;

    // Verify the connection with a simple query
    let versi: String = sqlx::query_scalar("SELECT @@VERSION")
        .fetch_one(&pool)
        .await?;

    println!("Connected to: {}", &versi[..50]);
    Ok(())
}

T-SQL Differences from MySQL #

MSSQL uses T-SQL (Transact-SQL), which has several important syntax differences:

-- MySQL                          -- MSSQL (T-SQL)

-- Limit and offset
SELECT * FROM t LIMIT 10          SELECT TOP 10 * FROM t
OFFSET 20 LIMIT 10                SELECT * FROM t ORDER BY id
                                  OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

-- Auto-increment
id INT AUTO_INCREMENT             id INT IDENTITY(1,1)

-- String functions
CONCAT(a, b)                      a + b  or  CONCAT(a, b)
IFNULL(a, b)                      ISNULL(a, b)
GROUP_CONCAT(col)                 STRING_AGG(col, ',')

-- Dates
NOW()                             GETDATE()  or  SYSDATETIME()
DATE_FORMAT(d, '%Y-%m-%d')        FORMAT(d, 'yyyy-MM-dd')
DATE_ADD(d, INTERVAL 1 DAY)       DATEADD(day, 1, d)

-- String types
VARCHAR(255) utf8mb4              NVARCHAR(255)  -- unicode built-in
TEXT                              NVARCHAR(MAX)

-- Backticks for identifiers
`nama_tabel`                      [nama_tabel]  or  "nama_tabel"

Creating Tables #

-- migrations/001_buat_tabel.sql for MSSQL

CREATE TABLE pengguna (
    id          BIGINT IDENTITY(1,1) PRIMARY KEY,
    nama        NVARCHAR(100)   NOT NULL,
    email       NVARCHAR(255)   NOT NULL,
    password    NVARCHAR(255)   NOT NULL,
    peran       NVARCHAR(20)    NOT NULL DEFAULT 'user',
    aktif       BIT             NOT NULL DEFAULT 1,
    dibuat_pada DATETIME2       NOT NULL DEFAULT SYSDATETIME(),
    diperbarui  DATETIME2       NOT NULL DEFAULT SYSDATETIME(),
    CONSTRAINT UQ_pengguna_email UNIQUE (email),
    CONSTRAINT CK_pengguna_peran CHECK (peran IN ('user', 'admin', 'moderator'))
);

CREATE TABLE artikel (
    id              BIGINT IDENTITY(1,1) PRIMARY KEY,
    pengguna_id     BIGINT          NOT NULL,
    judul           NVARCHAR(255)   NOT NULL,
    konten          NVARCHAR(MAX)   NOT NULL,
    diterbitkan     BIT             NOT NULL DEFAULT 0,
    dibuat_pada     DATETIME2       NOT NULL DEFAULT SYSDATETIME(),
    CONSTRAINT FK_artikel_pengguna FOREIGN KEY (pengguna_id)
        REFERENCES pengguna(id) ON DELETE CASCADE
);

CREATE NONCLUSTERED INDEX IX_artikel_pengguna ON artikel(pengguna_id);
CREATE NONCLUSTERED INDEX IX_artikel_diterbitkan ON artikel(diterbitkan) INCLUDE (dibuat_pada, judul);

Structs and MSSQL Data Types #

MSSQL has several unique data types — map them carefully to Rust types:

use chrono::NaiveDateTime;
use serde::{Deserialize, Serialize};
use sqlx::FromRow;

#[derive(Debug, FromRow, Serialize, Deserialize, Clone)]
struct Pengguna {
    pub id: i64,               // BIGINT IDENTITY → i64 (not u64 like MySQL)
    pub nama: String,          // NVARCHAR → String
    pub email: String,         // NVARCHAR → String
    #[serde(skip_serializing)]
    pub password: String,
    pub peran: String,         // NVARCHAR → String
    pub aktif: bool,           // BIT → bool
    pub dibuat_pada: NaiveDateTime,  // DATETIME2 → NaiveDateTime
    pub diperbarui: NaiveDateTime,
}

#[derive(Debug, FromRow, Serialize)]
struct Artikel {
    pub id: i64,
    pub pengguna_id: i64,
    pub judul: String,
    pub konten: String,
    pub diterbitkan: bool,
    pub dibuat_pada: NaiveDateTime,
}

MSSQL Type	Rust Type
`TINYINT`	`u8`
`SMALLINT`	`i16`
`INT`	`i32`
`BIGINT`	`i64`
`REAL`	`f32`
`FLOAT`	`f64`
`BIT`	`bool`
`NVARCHAR(n)`	`String`
`NVARCHAR(MAX)`	`String`
`DATETIME2`	`chrono::NaiveDateTime`
`DATETIMEOFFSET`	`chrono::DateTime<Utc>`
`UNIQUEIDENTIFIER`	`uuid::Uuid`
`VARBINARY(MAX)`	`Vec<u8>`
`DECIMAL(p,s)`	`rust_decimal::Decimal`

Queries and Fetching #

Parameter binding in MSSQL uses @P1, @P2, etc. — different from ? in MySQL:

use sqlx::MssqlPool;

async fn ambil_semua_pengguna(pool: &MssqlPool) -> Result<Vec<Pengguna>, sqlx::Error> {
    // sqlx handles parameter binding automatically
    sqlx::query_as!(
        Pengguna,
        "SELECT id, nama, email, password, peran, aktif, dibuat_pada, diperbarui
         FROM pengguna
         ORDER BY dibuat_pada DESC"
    )
    .fetch_all(pool)
    .await
}

async fn cari_pengguna_by_id(pool: &MssqlPool, id: i64) -> Result<Option<Pengguna>, sqlx::Error> {
    sqlx::query_as!(
        Pengguna,
        "SELECT id, nama, email, password, peran, aktif, dibuat_pada, diperbarui
         FROM pengguna
         WHERE id = @P1",  // MSSQL uses @P1, @P2, etc.
        id
    )
    .fetch_optional(pool)
    .await
}

// Pagination with OFFSET-FETCH (MSSQL 2012+)
async fn daftar_pengguna_halaman(
    pool: &MssqlPool,
    halaman: i64,
    per_halaman: i64,
) -> Result<Vec<Pengguna>, sqlx::Error> {
    let offset = (halaman.saturating_sub(1)) * per_halaman;

    sqlx::query_as!(
        Pengguna,
        "SELECT id, nama, email, password, peran, aktif, dibuat_pada, diperbarui
         FROM pengguna
         ORDER BY id DESC
         OFFSET @P1 ROWS FETCH NEXT @P2 ROWS ONLY",
        offset,
        per_halaman
    )
    .fetch_all(pool)
    .await
}

INSERT with `OUTPUT INSERTED` #

MSSQL doesn’t have LAST_INSERT_ID(). The idiomatic way to get the ID after INSERT is using OUTPUT INSERTED:

async fn buat_pengguna(
    pool: &MssqlPool,
    nama: &str,
    email: &str,
    password_hash: &str,
) -> Result<i64, sqlx::Error> {
    // OUTPUT INSERTED.id returns the newly created ID as a result set
    let row = sqlx::query!(
        "INSERT INTO pengguna (nama, email, password)
         OUTPUT INSERTED.id
         VALUES (@P1, @P2, @P3)",
        nama,
        email,
        password_hash
    )
    .fetch_one(pool)
    .await?;

    Ok(row.id)
}

// Alternative: separate INSERT and SELECT in a transaction
async fn buat_pengguna_alt(
    pool: &MssqlPool,
    nama: &str,
    email: &str,
    password_hash: &str,
) -> Result<i64, sqlx::Error> {
    let mut tx = pool.begin().await?;

    sqlx::query!(
        "INSERT INTO pengguna (nama, email, password) VALUES (@P1, @P2, @P3)",
        nama, email, password_hash
    )
    .execute(&mut *tx)
    .await?;

    let id: i64 = sqlx::query_scalar("SELECT SCOPE_IDENTITY()")
        .fetch_one(&mut *tx)
        .await?;

    tx.commit().await?;
    Ok(id)
}

UPDATE and DELETE #

async fn perbarui_pengguna(
    pool: &MssqlPool,
    id: i64,
    nama: &str,
    email: &str,
) -> Result<bool, sqlx::Error> {
    let hasil = sqlx::query!(
        "UPDATE pengguna
         SET nama = @P1, email = @P2, diperbarui = SYSDATETIME()
         WHERE id = @P3",
        nama, email, id
    )
    .execute(pool)
    .await?;

    Ok(hasil.rows_affected() > 0)
}

// UPDATE with OUTPUT — get data before and after the update
async fn toggle_aktif(
    pool: &MssqlPool,
    id: i64,
) -> Result<Option<bool>, sqlx::Error> {
    // MSSQL can return INSERTED (new value) or DELETED (old value)
    let row = sqlx::query!(
        "UPDATE pengguna
         SET aktif = CASE WHEN aktif = 1 THEN 0 ELSE 1 END
         OUTPUT INSERTED.aktif
         WHERE id = @P1",
        id
    )
    .fetch_optional(pool)
    .await?;

    Ok(row.map(|r| r.aktif))
}

async fn hapus_pengguna(pool: &MssqlPool, id: i64) -> Result<bool, sqlx::Error> {
    let hasil = sqlx::query!(
        "DELETE FROM pengguna WHERE id = @P1",
        id
    )
    .execute(pool)
    .await?;

    Ok(hasil.rows_affected() > 0)
}

Transactions #

async fn pindahkan_artikel(
    pool: &MssqlPool,
    artikel_id: i64,
    penulis_lama_id: i64,
    penulis_baru_id: i64,
) -> Result<(), sqlx::Error> {
    let mut tx = pool.begin().await?;

    // Verify the article belongs to the old author
    let artikel: Option<i64> = sqlx::query_scalar!(
        "SELECT id FROM artikel WHERE id = @P1 AND pengguna_id = @P2",
        artikel_id, penulis_lama_id
    )
    .fetch_optional(&mut *tx)
    .await?;

    if artikel.is_none() {
        // Auto-rollback when tx is dropped
        return Err(sqlx::Error::RowNotFound);
    }

    // Verify the new author exists
    let penulis_baru: Option<i64> = sqlx::query_scalar!(
        "SELECT id FROM pengguna WHERE id = @P1 AND aktif = 1",
        penulis_baru_id
    )
    .fetch_optional(&mut *tx)
    .await?;

    if penulis_baru.is_none() {
        return Err(sqlx::Error::RowNotFound);
    }

    // Move the article
    sqlx::query!(
        "UPDATE artikel SET pengguna_id = @P1 WHERE id = @P2",
        penulis_baru_id, artikel_id
    )
    .execute(&mut *tx)
    .await?;

    tx.commit().await?;
    println!("Article {} moved to user {}", artikel_id, penulis_baru_id);
    Ok(())
}

Stored Procedures #

MSSQL makes heavy use of stored procedures. How to call them from Rust:

-- Stored procedure in SQL Server
CREATE PROCEDURE sp_buat_pengguna
    @Nama       NVARCHAR(100),
    @Email      NVARCHAR(255),
    @Password   NVARCHAR(255),
    @NamaPeran  NVARCHAR(20) = 'user'
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM pengguna WHERE email = @Email)
    BEGIN
        RAISERROR('Email sudah terdaftar', 16, 1);
        RETURN;
    END

    INSERT INTO pengguna (nama, email, password, peran)
    VALUES (@Nama, @Email, @Password, @NamaPeran);

    SELECT SCOPE_IDENTITY() AS id_baru;
END;

async fn panggil_sp_buat_pengguna(
    pool: &MssqlPool,
    nama: &str,
    email: &str,
    password: &str,
) -> Result<i64, sqlx::Error> {
    // Call the stored procedure with EXEC
    let row = sqlx::query!(
        "EXEC sp_buat_pengguna @Nama = @P1, @Email = @P2, @Password = @P3",
        nama, email, password
    )
    .fetch_one(pool)
    .await?;

    Ok(row.id_baru.unwrap_or(0))
}

Using `tiberius` Directly #

For cases where sqlx doesn’t support a certain MSSQL feature (e.g. Bulk Insert or CDC features), use tiberius:

[dependencies]
tiberius = { version = "0.12", features = ["rustls", "chrono"] }
tokio = { version = "1", features = ["full"] }
tokio-util = { version = "0.7", features = ["compat"] }

use tiberius::{AuthMethod, Client, Config, Query};
use tokio::net::TcpStream;
use tokio_util::compat::TokioAsyncWriteCompatExt;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let mut config = Config::new();
    config.host("localhost");
    config.port(1433);
    config.authentication(AuthMethod::sql_server("sa", "Password123!"));
    config.database("contoh");
    config.trust_cert();  // for development — don't use in production

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

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

    // Simple query
    let query = Query::new("SELECT id, nama FROM pengguna WHERE aktif = @P1");
    // Can't use the query! macro — runtime binding
    let mut hasil = klien.query("SELECT id, nama FROM pengguna WHERE aktif = @P1",
        &[&1i32]).await?;

    while let Some(baris) = hasil.try_next().await? {
        let id: i32 = baris.get(0).unwrap_or(0);
        let nama: &str = baris.get(1).unwrap_or("");
        println!("ID: {}, Name: {}", id, nama);
    }

    Ok(())
}

// To use try_next
use futures::TryStreamExt;

Important Differences from MySQL #

MySQL                           MSSQL (T-SQL)

Parameter binding
  ?                             @P1, @P2, @P3, ...

Getting the ID after INSERT
  LAST_INSERT_ID()              OUTPUT INSERTED.id
                                or SCOPE_IDENTITY()

Pagination
  LIMIT 10 OFFSET 20            ORDER BY id
                                OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
  (or TOP 10 without offset)    SELECT TOP 10 * (without offset)

Current time
  NOW()                         GETDATE() or SYSDATETIME()

NULL checking
  IFNULL(a, b)                  ISNULL(a, b) or COALESCE(a, b)

Unicode strings
  VARCHAR charset utf8mb4       NVARCHAR (always unicode)

Auto increment
  AUTO_INCREMENT                IDENTITY(1,1)

Conditional
  IF(kondisi, a, b)             CASE WHEN kondisi THEN a ELSE b END
  IFNULL                        ISNULL

Identifier quoting
  `nama`                        [nama] or "nama"

Summary #

Parameter binding in MSSQL uses @P1, @P2 — different from ? in MySQL. sqlx handles this automatically — you still write parameters as ordinary arguments.
OUTPUT INSERTED.id to get the ID after INSERT — MSSQL has no LAST_INSERT_ID(). Use OUTPUT INSERTED.kolom to get values from the newly inserted row.
i64 type for BIGINT IDENTITY — different from MySQL which usually uses u64. MSSQL BIGINT is signed, so map it to i64.
NVARCHAR not VARCHAR for Unicode strings — all text columns should be NVARCHAR in MSSQL to support non-ASCII characters without extra configuration.
Pagination with OFFSET-FETCH — ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY instead of LIMIT 10 OFFSET 20. An ORDER BY is required for OFFSET-FETCH.
SYSDATETIME() is more precise than GETDATE() — use it for DATETIME2 columns that need nanosecond precision.
Stored procedures are common in enterprise environments — call them with EXEC nama_sp @Param = @P1. Make sure the stored procedure returns a result set if you need data back.
tiberius for MSSQL features sqlx doesn’t support — Bulk Insert, Change Data Capture, and advanced T-SQL features may need this lower-level driver.
trustServerCertificate=true is only for development — in production, configure a valid SSL certificate for secure connections.

← Previous: MySQL Next: Oracle →