1. Install Prisma
1.1. Install Prisma CLI via npm/yarn
This is for schema-migration based on our schema.prisma
.
yarn init -y yarn add -D prisma@4.8.0 yarn add @prisma/client@4.8.0
1.2. Install Prisma CLI (Rust binary) via Cargo
This is for building rust
entites code based on our schema.prisma
.
- Install with SQLite support (add other databases as needed)
cargo install --git https://github.com/Brendonovich/prisma-client-rust \ --tag 0.6.11 \ prisma-cli \ --features sqlite \˛ --force
- Create symlink for cargo subcommand
ln -s ~/.cargo/bin/prisma ~/.cargo/bin/cargo-prisma
Available database features:
sqlite
- SQLitepostgresql
- PostgreSQLmysql
- MySQLmssql
- Microsoft SQL Servermongodb
- MongoDB
1.3. Add Prisma Client Rust to Cargo.toml
[dependencies] prisma-client-rust = { git = "https://github.com/Brendonovich/prisma-client-rust", tag = "0.6.11", default-features = false, features = ["sqlite", "migrations"] } serde = { version = "1.0", features = ["derive"] } tokio = { version = "1.0", features = ["full"] } [build-dependencies] prisma-client-rust-cli = { git = "https://github.com/Brendonovich/prisma-client-rust", tag = "0.6.11" }
1.4. Create build.rs
in project root
build.rs
in project rootThe following script will be executed when we cargo build
or cargo run
:
fn main() { // Generate Prisma Client Rust whenever schema.prisma changes println!("cargo:rerun-if-changed=prisma/schema.prisma"); prisma_client_rust_cli::run(); }
2. Brief Introduction to Prisma
2.1. Prisma Schema File
2.1.1. Initialize prisma
Create prisma/schema.prisma
file:
npx prisma init --datasource-provider sqlite
2.1.2. Complete prisma/schema.prisma
example
prisma/schema.prisma
examplegenerator client { provider = "cargo prisma" output = "../src/prisma.rs" } datasource db { provider = "sqlite" url = "file:../dev-database.db" } // Your data models model Counter { id Int @id @default(autoincrement()) value BigInt name String description String? isActive Boolean @default(true) @map("is_active") multiplier Float? createdAt DateTime @default(now()) @map("created_at") updatedAt DateTime @updatedAt @map("updated_at") @@map("counter") } model User { id Int @id @default(autoincrement()) email String @unique name String createdAt DateTime @default(now()) @map("created_at") @@map("users") }
Note that we may also need:
created_at Float @default(dbgenerated("(CAST((julianday('now') - 2440587.5) * 86400000.0 AS REAL))")) created_at_hk String @default(dbgenerated("(strftime('%Y-%m-%d %H:%M:%S', datetime('now', '+8 hours')))"))
which are the sqlite
specific way to create
-
auto-generate
created_at
in milli-second and -
created_at
in hong kong time string format.
2.1.3. Schema syntax reference
Field Types
String
→TEXT
Int
→INTEGER
BigInt
→BIGINT
Float
→REAL
/DOUBLE
Boolean
→BOOLEAN
DateTime
→TIMESTAMP
Json
→JSON
Field Modifiers
?
→ Optional field (nullable)[]
→ Array/List field@id
→ Primary key@unique
→ Unique constraint@default(value)
→ Default value@updatedAt
→ Auto-update timestamp@map("db_name")
→ Custom column name@@map("table_name")
→ Custom table name
Default Values
@default(autoincrement()) // Auto-increment ID @default(now()) // Current timestamp @default(true) // Boolean default @default("text") // String default @default(0) // Number default
2.2. Schema Migration & Code Generation
2.2.1. Create our first migration
This command will:
- Generate SQL migration file
- Create/update the database
- Generate Rust code in src/prisma.rs
npx prisma migrate dev --name init
which outputs:
✓ Generated SQL migration ✓ Applied migration to database ✓ Generated Prisma Client Rust to ./src/prisma.rs
2.2.2. When we change the schema
After editing schema.prisma, create a new migration by:
npx prisma migrate dev --name add_user_table
Prisma will automatically detects what is changed!
2.2.3. Where to find generated code
Generated file location: src/prisma.rs
, this file contains:
- Type-safe Rust structs for your models
- Query builder methods (create, find, update, delete)
- Field helper functions
- PrismaClient struct
Do not edit this file manually! It's regenerated on every migration.
2.2.4. Other useful commands
-
Regenerate client without creating migration
npx prisma generate
-
Reset database (WARNING: deletes all data)
npx prisma migrate reset
-
Open Prisma Studio (database GUI)
npx prisma studio
3. Connecting to Database & CRUD Operations
3.1. Database Connection String
-
Set in
prisma/schema.prisma
:datasource db { provider = "sqlite" url = "file:../dev-database.db" } // For PostgreSQL: // provider = "postgresql" // url = "postgresql://user:password@localhost:5432/mydb" // For MySQL: // provider = "mysql" // url = "mysql://user:password@localhost:3306/mydb"
-
Or use environment variable:
datasource db { provider = "sqlite" url = env("DATABASE_URL") }
and create
.env
file:DATABASE_URL="file:./dev-database.db"
3.2. Connect to Database in Rust
// In main.rs mod prisma; use prisma::PrismaClient; #[tokio::main] async fn main() -> Result<(), Box<dyn std::error::Error>> { // Create client (reads connection string from schema.prisma) let client = prisma::new_client().await?; // Or specify custom URL at runtime // let client = prisma::new_client_with_url("file:./my-database.db").await?; // Now use the client for queries Ok(()) }
3.3. CRUD Operations
3.3.1. CREATE
CREATE
- Create a single record
let counter = client .counter() .create( 0, // value (required field) "My Counter".to_string(), // name (required field) vec![ // optional fields prisma::counter::description::set(Some("A test counter".to_string())), prisma::counter::is_active::set(true), prisma::counter::multiplier::set(Some(2.5)), ] ) .exec() .await?;
- Create multiple records
client .counter() .create_many(vec![ prisma::counter::create(0, "Counter 1".to_string(), vec![]), prisma::counter::create(10, "Counter 2".to_string(), vec![]), prisma::counter::create(20, "Counter 3".to_string(), vec![]), ]) .exec() .await?;
3.3.2. READ
READ
-
Find by
id
(returnsOption<Data>
)let counter = client .counter() .find_unique(prisma::counter::id::equals(1)) .exec() .await?; if let Some(c) = counter { println!("Found: {}", c.name); } else { println!("Not found"); }
-
Find first match
let counter = client .counter() .find_first(vec![ prisma::counter::is_active::equals(true) ]) .exec() .await?;
-
Find many with filters
let counters = client .counter() .find_many(vec![ prisma::counter::is_active::equals(true), prisma::counter::value::gte(10), ]) .exec() .await?; println!("Found {} active counters", counters.len());
-
Find many with ordering
let counters = client .counter() .find_many(vec![]) .order_by(prisma::counter::value::order(prisma::Direction::Desc)) .exec() .await?;
-
Find many with pagination
let counters = client .counter() .find_many(vec![]) .skip(10) .take(5) // Limit to 5 results .exec() .await?;
-
Select specific fields only
let names = client .counter() .find_many(vec![]) .select(prisma::counter::select!({ id name })) .exec() .await?; for counter in names { println!("ID: {}, Name: {}", counter.id, counter.name); }
3.3.3. UPDATE
UPDATE
-
Update a single record
let updated = client .counter() .update( prisma::counter::id::equals(1), // which record vec![ // what to update prisma::counter::value::increment(1), prisma::counter::name::set("Updated Name".to_string()), prisma::counter::description::set(Some("New description".to_string())), ] ) .exec() .await?; println!("New value: {}", updated.value);
-
Update many records
let count = client .counter() .update_many( vec![ prisma::counter::is_active::equals(false) // filter ], vec![ prisma::counter::is_active::set(true) // update ] ) .exec() .await?; println!("Updated {} records", count);
-
Upsert (
update
if exists,create
if not)let counter = client .counter() .upsert( prisma::counter::id::equals(1), // find by this prisma::counter::create(0, "New".to_string(), vec![]), // create if not found vec![prisma::counter::value::increment(1)] // update if found ) .exec() .await?;
3.3.4. DELETE
DELETE
- Delete a single record
let deleted = client .counter() .delete(prisma::counter::id::equals(1)) .exec() .await?; println!("Deleted counter: {}", deleted.name);
- Delete many records
let count = client .counter() .delete_many(vec![ prisma::counter::is_active::equals(false) ]) .exec() .await?; println!("Deleted {} inactive counters", count);
4. Advanced Queries
4.1. Query with Conditions
-
Combine multiple conditions (
AND
)let counters = client .counter() .find_many(vec![ prisma::counter::is_active::equals(true), prisma::counter::value::gte(10), prisma::counter::value::lte(100), prisma::counter::name::contains("test"), ]) .exec() .await?;
-
OR
conditionslet counters = client .counter() .find_many(vec![ prisma::counter::or(vec![ prisma::counter::name::equals("Counter A".to_string()), prisma::counter::name::equals("Counter B".to_string()), ]) ]) .exec() .await?;
-
NOT
conditionlet counters = client .counter() .find_many(vec![ prisma::counter::not(vec![ prisma::counter::is_active::equals(false) ]) ]) .exec() .await?;
4.2. String Filters
-
Exact match
prisma::counter::name::equals("exact name")
-
Contains substring
prisma::counter::name::contains("substring")
-
Starts with
prisma::counter::name::starts_with("prefix")
-
Ends with
prisma::counter::name::ends_with("suffix")
-
Case insensitive (if supported by database)
prisma::counter::name::mode(prisma::QueryMode::Insensitive)
4.3. Number Filters
prisma::counter::value::equals(10) prisma::counter::value::lt(10) // less than prisma::counter::value::lte(10) // less than or equal prisma::counter::value::gt(10) // greater than prisma::counter::value::gte(10) // greater than or equal prisma::counter::value::in_vec(vec![1, 2, 3, 4]) prisma::counter::value::not_in_vec(vec![5, 6])
4.4. Optional Field Filters
-
Check if field is null
prisma::counter::description::equals(None)
-
Check if field is not null
prisma::counter::description::not(None)
-
Specific value
prisma::counter::description::equals(Some("text".to_string()))
4.5. Count Records
let count = client .counter() .count(vec![ prisma::counter::is_active::equals(true) ]) .exec() .await?; println!("Total active counters: {}", count);
4.6. Transactions
- Execute multiple operations atomically
let result = client ._transaction() .run(|tx| async move { // Create user let user = tx .user() .create("user@example.com".to_string(), "John".to_string(), vec![]) .exec() .await?; // Create counter linked to user let counter = tx .counter() .create(0, "User's Counter".to_string(), vec![]) .exec() .await?; Ok((user, counter)) }) .await?; println!("Created user {} and counter {}", result.0.id, result.1.id);
4.7. Raw SQL Queries
-
Raw query with type safety
use prisma_client_rust::raw; let counters: Vec<prisma::counter::Data> = client ._query_raw(raw!( "SELECT * FROM counter WHERE value > {}", 10 )) .exec() .await?;
-
Raw execute (
INSERT
/UPDATE
/DELETE
)let affected = client ._execute_raw(raw!( "UPDATE counter SET value = value + 1 WHERE is_active = {}", true )) .exec() .await?; println!("Updated {} rows", affected);
4.8. Left Joins over Association Table
Suppose that we have the following tables:
Then to get all scripts belonging to a folder:
pub fn get_all_scripts_of_folder(&self, folder_id: i32) -> Vec<prisma::shell_script::Data> { let runtime = tokio::runtime::Runtime::new().unwrap(); let scripts = runtime .block_on(async move { // Query the folder with all related scripts through the junction table self.db .scripts_folder() .find_unique(prisma::scripts_folder::id::equals(folder_id)) .with( prisma::scripts_folder::rel_scriptsfolder_shellscript::fetch(vec![]) .with(prisma::rel_scriptsfolder_shellscript::shell_script::fetch()), ) .exec() .await }) .unwrap(); // Extract the shell scripts from the joined data if let Some(folder) = scripts { folder .rel_scriptsfolder_shellscript .unwrap_or_default() .into_iter() .filter_map(|rel| rel.shell_script.map(|s| *s)) .collect() } else { vec![] } }
-
.with()
means left join; -
.fetch()
follows from what we declared inschema.prisma
; Do we fetch a list? or fetch a single item?
5. Complete Example: Iced App with Prisma
use iced::{executor, Application, Command, Element, Settings, Theme}; use iced::widget::{button, column, container, text}; mod prisma; struct CounterApp { count: i64, client: Option<prisma::PrismaClient>, counter_id: Option<i32>, } #[derive(Debug, Clone)] enum Message { DatabaseConnected(prisma::PrismaClient), Increment, Decrement, CounterSaved, CounterLoaded(i64), } impl Application for CounterApp { type Executor = executor::Default; type Message = Message; type Theme = Theme; type Flags = (); fn new(_flags: ()) -> (Self, Command<Message>) { ( Self { count: 0, client: None, counter_id: None, }, Command::perform(setup_database(), Message::DatabaseConnected), ) } fn title(&self) -> String { String::from("Prisma Counter") } fn update(&mut self, message: Message) -> Command<Message> { match message { Message::DatabaseConnected(client) => { self.client = Some(client); Command::perform(load_counter(), Message::CounterLoaded) } Message::Increment => { self.count += 1; Command::perform( save_counter(self.client.clone(), self.count), |_| Message::CounterSaved, ) } Message::Decrement => { self.count -= 1; Command::perform( save_counter(self.client.clone(), self.count), |_| Message::CounterSaved, ) } Message::CounterLoaded(value) => { self.count = value; Command::none() } Message::CounterSaved => Command::none(), } } fn view(&self) -> Element<Message> { let content = column![ text(format!("Count: {}", self.count)).size(50), button("Increment").on_press(Message::Increment), button("Decrement").on_press(Message::Decrement), ] .spacing(20) .padding(20); container(content).center_x().center_y().into() } } async fn setup_database() -> prisma::PrismaClient { prisma::new_client().await.expect("Failed to connect to database") } async fn load_counter() -> i64 { // Implement loading logic 0 } async fn save_counter(client: Option<prisma::PrismaClient>, value: i64) { if let Some(client) = client { client .counter() .upsert( prisma::counter::id::equals(1), prisma::counter::create(value, "Main Counter".to_string(), vec![]), vec![prisma::counter::value::set(value)], ) .exec() .await .ok(); } } fn main() -> iced::Result { CounterApp::run(Settings::default()) }