Using PostgresQL in Rust

RustNYC Meetup

Darin Gordon

September 26, 2018

intro

Database development often plays a significant role

How can we work with Postgres in Rust?

Goals of today's talk

Guided Tour of How I work with Postgres in Rust

Practices / Techniques adopted

Lessons Learned

About Darin

  •  (Founder) working on moonshot in Real Estate
  • Author/Contributor to Open Source: Rust, Python
  • Bachelors in CS @ Rutgers/NJIT (2003)
  • Attended EMBA @ Duke (2012) - ended early (startup)
  • ~Decade working in Financial Services:  IT, Prod Dev
  • The Yosai Project (app security)

outline

Transitioning to Rust from Python

Bulk of the talk:  Code Review and Discussion

Viable options for Postgres dev in Rust

Transitioning to Rust

Transition Plan

Working with Rust, full-time since Jan 2018

Worked extensively with Python prior to 2018

First:  Learn Rust Foundations

Second:  Learn by Porting

relational db dev in rust

Viable Options

  • Parameterized Raw SQL
  • ORM / Query Builder

ORM / Query Builder

  • leading Rust orm (Sept 2018)
  • use orm/qb for basics and raw sql+params for rest
  • default optimizations

Low ROI: Beyond the basics, you're using raw sql + params

(I'm not against ORM.  I just won't be using one.. yet)

  • migrations (raw sql)

parameterized raw sql

Libraries:  rust-postgres , rusqlite, ...

  • Easy to use
  • Easy to extend

Writing SQL and running it safely with Rust

rust-postgres

Includes many required features

  • Prepared statements for bulk operations
  • Column types (mapping pg to Rust)
  • Connection pooling

(r2d2_postgres)

  • async option

(tokio-postgres)

rust-postgres


    let conn = Connection::connect("postgres://user:pass@host:port/db",
                                   TlsMode::None)?;


    let updates = conn.execute("UPDATE foo SET bar = $1 WHERE baz = $2",
                               &[&my_int, &"bla"])?;


    for row in &conn.query("SELECT bar, baz FROM foo WHERE buz = $1", &[&an_int])? {
        let bar: i32 = row.get(0);
        let baz: String = row.get("baz");
        println!("bar: {}, baz: {}", bar, baz);
    }


    let stmt = conn.prepare("UPDATE foo SET bar = $1 WHERE baz = $2")?;
    for (bar, baz) in updates {
        stmt.execute(&[bar, baz])?;
    }

rust-postgres in action

A conference room meeting scheduler

  • Patterns in Rust
  • Illustrates tstzrange type and constraints
  • Imports a dataset as a query parameter

(unnest)

  • On GitHub! (url later)

project Requirements

Schedule a conf room

Check room availability for preferred timeslots

"Schedule a meeting for 9-11am today in conf room 2C in the Admin building

"Check whether conf room 2C in the Admin building is available at these timeslots: [...]

project Requirements

Schedule a conf room

Check room availability for preferred timeslots

Work Involved

  • create relational data model
  • functions to persist and query data
  • error handling, logging, ...
  • connection pool
  • integration tests

Too much to cover today

(read the code on GitHub)

Data Model

  • Buildings

have conference

Rooms

  • User

schedules a

Room

for a

Meeting

  • Room

cannot be scheduled for more

than 1

Meeting

per time slot

User Model


   CREATE TABLE testing.users (
	id          BIGSERIAL PRIMARY KEY,
	ext_id      UUID NOT NULL DEFAULT  uuid_generate_v4(),
	first_name  VARCHAR(200) NOT NULL,
	last_name   VARCHAR(200) NOT NULL,
	username    VARCHAR(50) UNIQUE NOT NULL,
	UNIQUE (first_name, last_name)
   );

    #[derive(Debug, Clone)]
    pub struct User {
        pub id: i64,
        pub ext_id: Uuid,
        pub first_name: String,
        pub last_name: String,
        pub username: String,
    }

adding a user


    pub fn add_user(first_name: String,
                    last_name: String,
                    username: String,
                    logger: &Logger,
                    tx: &Transaction)
                  -> Result<User, MyError> {

        let stmt = "
    		INSERT INTO testing.users(first_name, last_name, username)
    		VALUES ($1, $2, $3)
    		RETURNING testing.users.id,
    				  testing.users.ext_id,
    				  testing.users.first_name,
    				  testing.users.last_name,
    				  testing.users.username;";

(part 1)

continued on next page

adding a user



 tx.query(stmt, &[&first_name, &last_name, &username])
   .map_err(|err| {
         error!(logger, "Failed to add user: DB Error.";
 		"step"=>"add_user", "err"=>err.to_string());
         MyError::DBError(DBError::PGError(err))
 })

(part 2)

continued on next page

adding a user

          
.and_then(|rows: Rows| {
   rows.into_iter()
       .next()
       .map(|row: Row| {
            // example of referencing row elements by index
            let user = User { id: row.get(0),
                              ext_id: row.get(1),
                              first_name: row.get(2),
                              last_name: row.get(3),
                               username: row.get(4), };

             info!(logger, "Added user: {}", user.username);
             user
        })
        ok_or_else(|| {
          error!(logger, "Error adding user to db: No record returned.";
  	         "step"=>"add_user");
           MyError::DBError(DBError::NoRecord)
        })
 })

(part 3)

end of function

General pattern

  1. Transform input params to ToSql types
  2. Run SQL statement
  3. Handle errors, log activity
  4. Transform query results into Rust types

Integration tests

Lessons Learned

  • Don't use a connection pool during testing
  • Use Transactions and rollback (auto)
  • "cargo test" runs test functions in parallel by default

Solutions to address parallel testing issues

  • Use unique fake data in tests
  • Seed the DB with test data
  • Throttle threads:
cargo test -- --nocapture --test-threads=1

conclusion

Take Advantage of Rust's Concurrency

Just Because You Can Use Postgres to Do Things, It Doesn't Mean You Should

Rust is worth the effort

Questions?

https://github.com/Dowwie/pg_example

Using Postgres in Rust

By Darin Gordon

Made with Slides.com

Using Postgres in Rust

  • 948

More from Darin Gordon