pg-worm

PostgreSQL's Worst ORM

pg-worm is a straightforward, fully typed, async ORM and Query Builder for PostgreSQL. Well, at least that's the goal.

Roadmap

• Concise, intutitive syntax for Insert, Select, Update and Delete queries
• Raw queries
• Transactions
• Auto migrations not just for table creations
• Make pool accessable to user

Usage

This library is based on tokio_postgres and is intended to be used with tokio.

Fortunately, using pg-worm is very easy.

Simply derive the Model trait for your type, connect to your database and you are ready to go!

Here's a quick example:

// Import the prelude to get started quickly
use pg_worm::prelude::*;

#[derive(Model)]
struct Book {
    // An auto-generated primary key
    #[column(primary_key, auto)]
    id: i64,
    title: String,
    author_id: i64
}

#[derive(Model)]
struct Author {
    #[column(primary_key, auto)]
    id: i64,
    name: String
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // First create a connection. This can be only done once.
    Connection::build("postgres://postgres:postgres@localhost:5432").connect()?;

    // Then, create tables for your models. 
    // Use `try_create_table!` if you want to fail if a
    // table with the same name already exists.
    //
    // `force_create_table` drops the old table,
    // which is useful for development.
    //
    // If your tables already exist, skip this part.
    force_create_table!(Author, Book).await?;

    // Next, insert some data.
    // This works by passing values for all
    // fields which aren't autogenerated.
    Author::insert("Stephen King").await?;
    Author::insert("Martin Luther King").await?;
    Author::insert("Karl Marx").await?;
    Book::insert("Foo - Part I", 1).await?;
    Book::insert("Foo - Part II", 2).await?;
    Book::insert("Foo - Part III", 3).await?;

    // Let's start with a simple query for all books:
    let books = Book::select().await?; // Vec<Book>
    assert_eq!(books.len(), 3);

    // You can also search for a specific book.
    // Adding a `WHERE` clause is as simple as
    // calling a method on the respective field:
    let book = Book::select_one()
        .where_(Book::title.eq(&"Foo - Part I".to_string()))
        .await?; // Option<Book>
    assert!(book.is_some());

    // Or update exsisting records:
    let books_updated = Book::update()
        .set(Book::title, &"Foo - Part III".to_string())
        .where_(Book::title.eq(&"Foo - Part II".to_string()))
        .await?; // u64
    assert_eq!(books_updated, 1);

    // Or delete a book, you don't like:
    let books_deleted = Book::delete()
        .where_(Book::title.eq(&"Foo - Part III".to_string()))
        .await?; // u64
    assert_eq!(books_deleted, 2);

    Ok(())
}

If you want to see more code examples, have a look at the tests directory.

Query Builders

As you can see above, pg-worm allows you to build queries by chaining methods on so called 'builders'. For each query type pg-worm provides a respective builder (except for INSERT which is handled differently).

These builders expose a set of methods for building queries. Here's a list of them:

Method	Description	Availability
where_	Attach a WHERE clause to the query.	All builders (Select, Update, Delete)
where_raw	Same as where_ but you can pass raw SQL.	All builders (Select, Update, Delete)
set	SET a column's value. Note: this method has to be called at least once before you can execute the query.	Update
limit, offset	Attach a LIMIT or OFFSET to the query.	Select

Filtering using WHERE

.where_() can be used to easily include WHERE clauses in your queries.

This is done by passing a Where object which can be constructed by calling methods on the respective column. pg-worm automatically constructs a constant for each field of your Model.

A practical example would look like this:

let where_: Where<'_> = MyModel::my_field.eq(&5);

Available methods

Currently, the following methods are implemented:

Function	Description	Availability
eq	Checks for equality.	Any type
gt, gte, lt, lte	Check whether this column's value is greater than, etc than some other value.	Any type which implements PartialOrd. Note: it's not guaranteed that Postgres supports these operator for a type just because it's PartialOrd. Be sure to check the Postgres documentation for your type beforehand.
null, not_null	Checks whether a column is NULL.	Any Option<T>. All other types are not NULLable and thus guaranteed not to be NULL.
contains, contains_not, contains_all, conatains_none, contains_any	Array operations. Check whether this column's array contains a value, a value not, or any/all/none values of another array.	Any Vec<T>.

Boolean logic

You can also chain/modify these filters with standard boolean logic:

Book::select()
    .where_(!Book::id.eq(&1) & Book::id.gt(&3))
    .await?;

Operator/Method	Description
!, .not()	Negate a filter using a locigal NOT
&, .and()	Combine two filters using a logical AND
||, .or()	Combine two filters using a logical OR

Executing a query

After having finished building your query, you can simply call .await. This will turn the builder into a Query object which is then executed asynchronously.

Executing a query will always result in a Result.

Raw queries

Though these features are nice, they are not sufficient for all applications. This is why you can easily execute custom queries and still take advantage of automatic parsing, etc:

// NOTE: You have to pass the exact type that PostgreSQL is 
// expecting. Doing otherwise will result in a runtime error.
let king_books = Book::query(r#"
        SELECT * FROM book 
        JOIN author ON author.id = book.author_id
        WHERE POSITION(? in author.name) > 0 
    "#, 
    vec![&"King".to_string()]
).await?;
assert_eq!(king_books.len(), 2);

Alse see .where_raw on query builders by which you can pass a raw condition without needing to write the whole query yourself.

Transactions

pg-worm also supports transactions. You can easily execute any query inside a Transaction and only commit when you are satisfied.

Transactions are automatically rolled-back when dropped, unless they have been committed beforehand.

Here's an example:

use pg_worm::prelude::*;

#[derive(Model)]
struct Foo {
    bar: i64
}

async fn foo() -> Result<(), Box<dyn std::error::Error>> {
    // Easily create a new transaction
    let transaction = Transaction::begin().await?;

    // Execute any query inside the transaction
    let all_foo = transaction.execute(
        Foo::select()
    ).await?;   

    // Commit the transaction when done.
    // If not committed, transaction are rolled back
    // when dropped.
    transaction.commit().await?;
}

Supported types

The following is a list of supported (Rust) types and which PostgreSQL type they are mapped to.

Rust type	PostgreSQL type
bool	BOOL
i16	INT2
i32	INT4
i64	INT8
f32	FLOAT4
f64	FLOAT8
String	TEXT
Option<T>*	T (but the column becomes NULLABLE)
Vec<T>*	T[]

*T must be another supported type. Nesting and mixing Option/Vec is currently not supported.

JSON, timestamps and more

are supported, too. To use them activate the respective feature, like so:

# Cargo.toml
[dependencies]
pg-worm = { version = "latest-version", features = ["foo"] }

Here is a list of the supported features/types with their respective PostgreSQL type:

• "serde-json" for serde_json v1.0

  Rust type	PostgreSQL type
  Value	JSONB

• "time" for time v3.0

  Rust type	PostgreSQL type
  Date	DATE
  Time	TIME
  PrimitiveDateTime	TIMESTAMP
  OffsetDateTime	TIMESTAMP WITH TIME ZONE

• "uuid" for uuid v1.0

  Rust type	PostgreSQL type
  Uuid	UUID

derive options

You can configure some options for you Model. This is done by using one of the two attributes pg-worm exposes.

The #[table] attribute

The #[table] attribute can be used to pass configurations to a Model which affect the respective table itself.

use pg_worm::prelude::*;

#[derive(Model)]
#[table(table_name = "book_list")]
struct Book {
    id: i64
}

Option	Meaning	Usage	Default
table_name	Set the table's name	table_name = "new_table_name"	The struct's name converted to snake case using this crate.

The #[column] attribute

The #[column] attribute can be used to pass configurations to a Model's field which affect the respective column.

use pg_worm::prelude::*;

#[derive(Model)]
struct Book {
    #[column(primary_key, auto)]
    id: i64
}

Option	Meaning	Usage	Default
column_name	Set this column's name.	#[column(column_name = "new_column_name")]	The fields's name converted to snake case using this crate.
primary_key	Make this column the primary key. Only use this once per Model. If you want this column to be auto generated use auto as well.	#[column(primary_key)]	false
auto	Make this column auto generated. Works only for i16, i32 and i64, as well as Uuid if the "uuid" feature has been enabled and you use PostgreSQL version 13 or later.	#[column(auto)]	false
unique	Add the UNIQUE constraint to this column	#[column(unique)]	false

MSRV

The minimum supported rust version is 1.70 as this crate uses the recently introduced OnceLock from the standard library.

License

This project is dual-licensed under the MIT and Apache 2.0 licenses.