Diesel – Type-safe SQL

Pascal Hertleif

2017-03-01

Pascal Hertleif

Web stuff by day, Rust by night!
Coorganizer of Rust Cologne
{twitter,github}.com/killercup

First off

diesel.rs

Requires Rust 1.15

Supported Backends

Postgres
SQLite
MySQL

Getting started

$ cargo new --bin diesel-example
$ cd diesel-example
$ echo "DATABASE_URL=test.db" > .env

Add to Cargo.toml:

[dependencies]
diesel = { version = "0.10.1", features = ["sqlite"] }
diesel_codegen = { version = "0.10.1", features = ["sqlite"] }
dotenv = "0.8.0"

And you are good to go!

Query Builder

Write queries in Rust

Invalid queries are compile-time errors

Basic Usage

let still_todo: Vec<Todo> =
    todos
    .filter(done.eq(false))
    .limit(100)
    .load(&connection)
    .unwrap();

Not shown:

#[derive(Queryable)]
struct Todo { title: String, done: bool };

pub mod schema {
    infer_schema!("dotenv:DATABASE_URL");
}

use schema::todos::dsl::{todos, done};

Tell me more about this DSL
What kind of sorcery is infer_schema!?!

DSL

Idea

Create zero-sized structs for table and its columns
Implement traits on these structs
???
PROFIT!

Intuitive Methods

todos.select((id, title))
.filter(done.eq(false))
.limit(100)

Each method returns a new, nested type.

todos.filter(done.eq(false)).limit(100)

SelectStatement<
    (Integer, Text, Bool),
    (todos::id, todos::title, todos::done),
    todos::table,
    NoDistinctClause,
    WhereClause<Eq<todos::done, Bound<Bool, bool>>>,
    NoOrderClause,
    LimitClause<Bound<BigInt, i64>>
>

That sounds like a lot of boilerplate…
Is this what makes it fast?

Traits for Everything

Diesel has i32::MAX traits in its codebase last time I counted

Add methods to methods to query builder? Write a trait like FilterDsl!

And implement generically:

impl<T, Predicate, ST> FilterDsl<Predicate> for T

Well, actually...

impl<T, Predicate, ST> FilterDsl<Predicate> for T where
  Predicate: Expression<SqlType=Bool> + NonAggregate,
  FilteredQuerySource<T, Predicate>: AsQuery<SqlType=ST>,
  T: AsQuery<SqlType=ST> + NotFiltered

(Those constraints are all traits as well!)

Using rustdoc to Create Mazes

docs.diesel.rs

Pro tip: Just search for what you think it should be called

Do I need to implement these traits myself?
What's with the SqlType?

A Duality of Types

There are

SQL types
Rust types

and ways to convert between them

Your schema contains SQL types
Your application's structs contain Rust types

FromSql, ToSql
Can map 1:1 like Float
Or not, like Timestamp

Do I need to implement these conversions myself?

The `table!` Macro

table! {
    todos (id) {
        id -> Integer,
        title -> Text,
        done -> Bool,
    }
}

Let's see what we end up with

Those don't look like Rust types
Do I have to type that?

The Amazing Schema Inference

Never let your code and database schema diverge!

infer_schema!("dotenv:DATABASE_URL");

It basically generates the table! macro calls for you.

You need to have a database running on your dev machine
The schema needs to be the same as in production

diesel print-schema

prints the table! macro calls infer_schema! generates

(So you can e.g. put it in version control)

How do you manage migrations
Oh, what's that diesel tool?

Derive ALL the Traits

Did I tell you about our Lord and Savior, Macros 1.1?

#[macro_use] extern crate diesel;
#[macro_use] extern crate diesel_codegen;

#[derive(Debug, Queryable)]
struct Todo {
    id: i32,
    title: String,
    done: bool,
}

It Just Works™

Diesel Codegen provides custom derive implementations for Queryable, Identifiable, Insertable, AsChangeset, and Associations.

It also provides the macros infer_schema!, infer_table_from_schema!, and embed_migrations!.

– diesel_codegen Readme

How do these traits work?
Associations, you say?

Associations

#[derive(Identifiable, Queryable, Associations)]
#[has_many(posts)]
pub struct User { id: i32, name: String, }

#[derive(Identifiable, Queryable, Associations)]
#[belongs_to(User)]
pub struct Post { id: i32, user_id: i32, title: String, }

let user = try!(users::find(1).first(&connection));
let posts = Post::belonging_to(&user).load(&connection);

Read much more about this at docs.diesel.rs/diesel/associations/

Diesel CLI Tool

Install it with

$ cargo install diesel

This makes it easy to

Setup your database
Manage your migrations
Print your schema

Tell me more about migrations
What was that about schema printing?

Migrations

migrations/datetime-name/{up,down}.sql

Simple SQL files that change your database schema (e.g. CREATE TABLE, DROP TABLE)

diesel migration generate create_todos
diesel migration run
diesel migration revert

Oh, what's that diesel tool?

Performance

Almost all queries can be represented by unique types
Each of these types returns a query (that uses bind params)
Let's cache these queries as Prepared Statements!

Is it faster than c? Yes you can use Diesel in warp drives

Type System Shenanigans

Macros to implement traits for generic tuples of up to 52 elements

Enable query builder features depending on the used backend

Basically every type and trait is generic over the backend
E.g.: Only Postgres implements RETURNING clause

Testing Diesel

Unit tests

For helper/converter functions

unix_epoch_decodes_correctly_with_timezone
queries_with_different_types_have_different_ids

Integration tests

Using diesel like a library

Doc test

Examples in the API documentation are tests!

Secret sauce: include!("src/doctest_setup.rs");

Quickcheck

Test roundtrips from Rust → DB → Rust

(With lots of macros, of course)

Compile-fail Tests

Invalid queries should not compile

So let's test that they return the expected errors!

The compiletest tool is also used by the Rust compiler and Clippy

Thank you for listening!

Questions?

Slides are available at git.io/diesel-adventure

License: CC BY-SA 4.0

1

Diesel – Type-safe SQL Pascal Hertleif 2017-03-01