Home Connecting Diesel to an existing Postgresql server
Post
Cancel

Connecting Diesel to an existing Postgresql server

This article serves as a companion to the video posted above.

Code found here

The goal here is to connect to an existing database using the Diesel toolset. The broader goal is to create an API Gateway that implements a GraphQL interface and grants access to legacy applications. The database was created by a rails application that modestly extends an older version of the Spree E-commerce framework. There are a lot of tables, and I’d like to use generators when possible.

Getting Started

Diesel provides generators for the schema definitions, but not for the models that the table results are mapped to. Luckily there is an extension library that provides generators for models, as well as other targets.

Assuming that you have the rust toolchain installed and updated, the tools can be installed, and project generated, by cargo.

1
2
cargo install diesel_cli diesel_cli
cargo new diesel --bin

Next, the initial dependencies are added to the cargo file.

1
2
3
[dependencies]
diesel = { version = "1.4.2", features = ["postgres"]}
dotenv = "0.14.1

Generating Code Schema

Initially, the schema generation fails.

1
2
> diesel print-schema > src/schema.rs
Diesel only supports tables with primary keys. Table spree_option_types_prototypes has no primary key

There are several tables in this database that don’t have primary keys, so they have to be excluded in the diesel.toml file.

1
2
3
4
5
6
7
8
9
10
11
# src/diesel.toml

# For documentation on how to configure this file,
# see diesel.rs/guides/configuring-diesel-cli

[print_schema]
file = "src/schema.rs"
filter = { except_tables = [
  "schema_migrations", "spree_option_types_prototypes",
  "spree_option_values_variants", "spree_orders_promotions", "spree_products_promotion_rules", "spree_promotion_rules_users", "spree_properties_prototypes", "spree_roles_users", "spree_shipping_methods_zones"
]}

Running diesel print-schema > src/schema.rs now prints the schema.

The missing tables will have to be resolved in the future, but for now I’m going to continue without them.

Model generation mostly works right away.

1
diesel_ext > src/models.rs

There are some missing type definitions, but we’ll resolve those later.

Fixing Dependencies

There are quite a few compilation errors. The spree_order and spree_user tables were commented out. Again this will have to be resolved later but aren’t necessary to read from the spree_products table.

There chrono and bigdecimal libraries have to be added, and their types loaded into models.rs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
// Cargo.toml
[package]
name = "diesel-gateway"
version = "0.1.0"
authors = ["Ross Harrison <rtharrison86@gmail.com>"]
edition = "2018"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
diesel = { version = "1.4.2", features = ["postgres", "chrono"]}
dotenv = "0.14.1"
chrono = { version = "0.4.9" , features = ["serde"] }
bigdecimal = { version = "0.1.0" }
1
2
3
4
5
6
// model.rs
use diesel::sql_types::{Float8, Array, Int4};
use chrono::{NaiveDateTime, NaiveDate};
use bigdecimal::BigDecimal;

/* Models below */

Establishing Connection, Printing Values

The Connection establishing code only has to be somewhat modified from the getting started guide.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
// Allow macros more recursion
#![recursion_limit="512"]
#[macro_use]
extern crate diesel;
extern crate dotenv;
extern crate chrono; // load chrono types
extern crate bigdecimal; // load big decimal

use diesel::prelude::*;
use diesel::pg::PgConnection;
use dotenv::dotenv;
use std::env;

// Load Models and schema
mod models;
mod schema;
use models::SpreeProduct;
use schema::spree_products::dsl::*;

fn main() {
    dotenv().ok();
    let data_url = env::var("DATABASE_URL")
        .expect("DATABASE_URL must be set");
    let connection = PgConnection::establish(&data_url)
        .expect(&format!("Error connect to {}", data_url));

    let results = spree_products.limit(2)
        .load::<SpreeProduct>(&connection)
        .expect("Error loading products");

    for product in results {
        print!("{} {} \n", product.id, product.name);
    }
}

Conclusion

The immature nature of Diesel, Rust Language server, and the Rust ecosystem as a whole made getting this done more difficult that with say JOOQ and Kotlin. While I’m not ready to take Diesel into production, the potential for running this in an AWS Lambda an environment without the startup time of a Kotlin application, is intriguing enough to use for personal projects.

-->