- Notifications
You must be signed in to change notification settings - Fork15
An ORM in Rust for developers that love SQL.
License
kurtbuilds/ormlite
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
ormlite is an ORM in Rust for developers that love SQL. Let's see it in action:
use ormlite::model::*;use ormlite::sqlite::SqliteConnection;#[derive(Model,Debug)]pubstructPerson{pubid:i32,pubname:String,pubage:i32,}#[tokio::main]asyncfnmain() ->Result<(),Box<dyn std::error::Error>>{/// Start by making a database connection.letmut conn =SqliteConnection::connect(":memory:").await.unwrap();/// You can insert the model directly.letmut john =Person{id:1,name:"John".to_string(),age:99,}.insert(&mut conn).await?;println!("{:?}", john);/// After modifying the object, you can update all its fields. john.age +=1; john.update_all_fields(&mut conn).await?;/// Query builder syntax closely follows SQL syntax, translated into chained function calls.let people =Person::select().where_("age > ?").bind(50).fetch_all(&mut conn).await?;println!("{:?}", people);}
You might likeormlite because:
- It auto-generates migrations from Rust structs. To my knowledge, it is the only Rust ORM with this capability.
- The join API (in alpha) has far fewer moving pieces than any other Rust ORM. It only relies on the table
structs themselves, and does not rely on relation traits (SeaORM) or modules (Diesel). - There's little to no query builder syntax to learn. The query builder basically joins together &str fragments of raw SQL. It strikes the right level of abstraction between composability, and having near-zero learning curve for anyone who already knows SQL.
Install withcargo:
#Forpostgrescargoaddormlite--featurespostgres#Forsqlitecargoaddormlite--featuressqlite
Or update yourCargo.toml:
[dependencies]# For postgresormlite = {version ="..",features = ["postgres"] }# For sqliteormlite = {version ="..",features = ["sqlite"] }
Other databases and runtimes are supported, but are less tested. Please submit an issue if you encounter any.
You needDATABASE_URL in your environment. We recommend a tool likejust, whichcan pull in a.env file, but for simplicity, here we'll use shell directly.
export DATABASE_URL=postgres://postgres:postgres@localhost:5432/postgresIf you are querying a static database and don't need migrations, skip this section. If you want migrations, keep reading.
First, installormlite-cli. Currently, the CLI only supports Postgres. Whileormlite-cli is separate fromsqlx-cli, they are 100% compatible with each other.sqlx-cli does not support auto-generating migrations or snapshots (to rollback in development without writing down migrations), but it is less bleeding edge and supports more database types.
cargo install ormlite-cli
Next, create the database and the migrations table.init creates a_sqlx_migrations table that tracks your migrations.
# Create the database if it doesn't exist. For postgres, that's:# createdb <dbname>ormlite init
Let's see migrations in action. Create a Rust struct with#[derive(Model)], which the CLI tool detects to auto-generate migrations:
# src/models.rsuse ormlite::model::*;#[derive(Model, Debug)]pub struct Person { pub id: i32, pub name: String, pub age: i32,}Next, auto-generate the migration.
ormlite migrate initial
This creates a plain SQL file inmigrations/. Let's review it before we execute it:
cat migrations/*.sqlOnce you're satisfied reviewing it, you can execute it:
ormlite up
By default,up also creates a snapshot, so you can rollback usingormlite down if need be. There's also an option to generate paired up/down migrations instead of only up migrations.
That's the end of setup. Let's now look at how to run queries.
The insert and update syntax at the top of the README is most effective for UUID primary key tables.
use ormlite::model::*;use uuid::Uuid;#[derive(Model,Debug)]pubstructEvent{pubid:Uuid,pubname:String,}#[tokio::main]asyncfnmain() ->Result<(),Box<dyn std::error::Error>>{letmut conn = ormlite::sqlite::SqliteConnection::connect(":memory:").await.unwrap();letmut event =Event{id:Uuid::new_v4(),name:"user_clicked".to_string(),}.insert(&mut conn).await?;println!("{:?}", event);}
This syntax has two possible issues. First,id is notOption, so it must be set,causing problems for autoincrement id fields. Second, the struct cannot track which fields are modified, so the updatemethod must update all columns.
To work around the autoincrement issue, you can use an insertion struct, shown here, or a builder, shown below.
use ormlite::types::Json;use serde_json::Value;#[derive(Model,Debug)]#[ormlite(insert ="InsertPerson")]pubstructPerson{pubid:i32,// Because the other fields are the primary key, and marked as default and default_value respectively,// `name` is the only field in the InsertPerson struct.pubname:String,// This field will not be part of the InsertPerson struct,// and rows will take the database-level default upon insertion.#[ormlite(default)]pubarchived_at:Option<DateTime<Utc>>,// This field will not be part of the InsertPerson struct,// which will always pass the provided value when inserting.#[ormlite(default_value ="serde_json::json!({})")]pubmetadata:Json<Value>,}asyncfninsertion_struct_example(conn:&mutSqliteConnection){let john:Person =InsertPerson{name:"John".to_string(),}.insert(&mut conn).await?;println!("{:?}", john);}
If the derived struct doesn't meet your needs, you can manually define a struct that only contains the fields you want,specifyingtable = "<table>" to route the struct to the same database table.
#[derive(Model,Debug)]#[ormlite(returns ="User")]pubstructInsertPerson{pubname:String,pubage:i32,}
You can also use builder syntax for insertion or to update only certain fields.
#[derive(Model,Debug)]pubstructPerson{pubid:i32,pubname:String,pubage:i32,}asyncfnbuilder_syntax_example(){// builder syntax for insertlet john =Person::builder().name("John".to_string()).age(99).insert(&mut conn).await?;println!("{:?}", john);// builder syntax for updatelet john = john.update_partial().age(100).update(&mut conn).await?;println!("{:?}", john);}
You can handle insertion on conflict usingOnConflict (docs).
use ormlite::{ model::*, query_builder::OnConflict,};#[derive(Debug,Model)]pubstructUsers{#[ormlite(primary_key)]pubid:i32,pubname:String,pubemail:String,}asyncfnupsert_example(conn:&mutPgConnection){Users{id:1,name:String::from("New name"),email:String::from("New email"),}.insert(&mut conn)// update values of all columns on primary key conflict.on_conflict(OnConflict::do_update_on_pkey("id")).await.unwrap();}
You can useModel::select to build a SQL query using Rust logic.
Note: Postgres's approach of using numbered dollar sign placeholders quickly breaks down when building queries. Instead, even with Postgres, use
?for parameters,andormlitewill replace the?placeholders with$placeholders when it constructs the final query.
#[derive(Model,Debug)]pubstructPerson{pubid:i32,pubname:String,pubage:i32,}asyncfnquery_builder_example(){let people =Person::select().where_("age > ?").bind(50i32).fetch_all(&mut conn).await?;println!("All people over 50: {:?}", people);}
You can fall back to raw queries if the ORM methods don't work for you. You can include handwritten strings, or ifyou want a lower-level query builder, you can usesqlmo,the underlying engine that powersormlite's query builder & migration auto-generation.
asyncfnmodel_query_example(){// Query using the Model to still deserialize results into the structlet _person =Person::query("SELECT * FROM person WHERE id = ?").bind(1).fetch_one(&mut conn).await?;}asyncfnraw_query_example(){// You can also use the raw query API, which will return tuples to decode as you likelet _used_ids:Vec<i32> = ormlite::query_as("SELECT id FROM person").fetch_all(pool).await.unwrap().into_iter().map(|row:(i32,)| row.0).collect();}
Attributes are defined inthese structs.
This example shows them in action:
#[derive(Model,Debug)]#[ormlite(table ="people", insert ="InsertPerson")]pubstructPerson{#[ormlite(primary_key)]pubid:i32,pubname:String,#[ormlite(column ="name_of_db_column")]pubage:i32,}
Join support is alpha stage. Right now,ormlite only support many-to-one relations (e.g. Person belongs to Organization).Support for many-to-many and one-to-many is planned. If you use this functionality, please report any bugs you encounter.
#[derive(Model,Debug)]pubstructPerson{pubid:Uuid,pubname:String,pubage:i32,// Note that we don't declare a separate field `pub organization_id: Uuid`.// It is implicitly defined by the Join and the join_column attribute.#[ormlite(column ="organization_id")]puborganization:Join<Organization>,}#[derive(Model,Debug)]pubstructOrganization{pubid:Uuid,pubname:String,}#[tokio::main]asyncfnmain() ->Result<(),Box<dyn std::error::Error>>{// Note we don't need to insert it.let org =Organization{id:Uuid::new_v4(),name:"Acme".to_string(),};let user =Person{id:Uuid::new_v4(),name:"John".to_string(),age:99,organization:Join::new(org),};letmut conn = ormlite::sqlite::SqliteConnection::connect(":memory:").await.unwrap();let user = user.insert(&mut conn).await?;assert_eq!(user.organization.loaded(),true);println!("{:?}", user);// You can choose whether you want to load the relation or not. The value will be Join::NotQueried if you don't// opt-in to loading it.let users =Person::select().join(Person::organization()).fetch_all(&mut conn).await?;for userin users{assert!(user.organization.loaded());println!("{:?}", user);}}
If you want Uuid or DateTime, combined with serde, you need to depend directly onuuid,time orchrono,and add theserde feature to each of them.
# Cargo.toml[dependencies]uuid = { version = "...", features = ["serde"] }chrono = { version = "...", features = ["serde"] }time = { version = "...", features = ["serde"] }use ormlite::model::*;use serde::{Serialize,Deserialize};use ormlite::types::Uuid;use ormlite::types::chrono::{DateTime,Utc};#[derive(Model,Debug,Serialize,Deserialize)]pubstructPerson{pubuuid:Uuid,pubcreated_at:DateTime<Utc>,pubname:String,}
You can either useormlite::types::Json for JSON or JSONB fields, or you can use thejson attribute.For unstructured data, useserde_json::Value as the inner type. Use a struct withDeserialize + Serialize as thegeneric for structured data.
use ormlite::model::*;use ormlite::types::Json;use serde_json::Value;#[derive(Debug,Serialize,Deserialize)]pubstructJobData{pubname:String,}#[derive(Model,Serialize,Deserialize)]pubstructJob{pubid:i32,pubstructured_data:Json<JobData>,pubunstructured_data:Json<Value>,#[ormlite(json)]pubunstructured_data2:Value,#[ormlite(json)]pubstructured_data2:JobData,}
You can log queries using sqlx's logger:RUST_LOG=sqlx=info
- Insert, update, delete directly on model instances
- Builder for partial update and insertions
- User can create insert models that ignore default values
- Select query builder
- Build the derive macro
- Get() function for fetching a single entity.
- Ability to specify the name of a table and name of primary column
- Automatically generate insert models
- Automatically generate migrations
- Eliminate need for FromRow macro
- Many to one joins
- created_at should naturally default to now()
- id: i32 should default to identity by default
- Autogenerate indexes for migrations
- Many to many joins
- One to many joins
- Make sure features are wired up correctly to support mysql and different runtimes & SSL libraries.
- Macro option to auto adjust columns like updated_at
- Upsert functionality
- Bulk insertions
- Query builder for bulk update
- Handle on conflict clauses for bulk update
- Benchmarks against raw sql, sqlx, ormx, seaorm, sqlite3-sys, pg, diesel
- Support for patch records, i.e. update with static fields.
- Consider a blocking interface, perhaps for sqlite/Rusqlite only.
Open source thrives on contributions, andormlite is a community project. We welcome you to file bugs, featurerequests, requests for better docs, pull requests, and more!
About
An ORM in Rust for developers that love SQL.