CCLee / Blog / Prisma with SQLite for GUI Application in Rust

Prisma with SQLite for GUI Application in Rust

1.Install Prisma
2.Brief Introduction to Prisma
- 2.1.Prisma Schema File
  - 2.1.1.Initialize prisma
  - 2.1.2.Complete prisma/schema.prisma example
  - 2.1.3.Schema syntax reference
- 2.2.Schema Migration & Code Generation
  - 2.2.1.Create our first migration
  - 2.2.2.When we change the schema
  - 2.2.3.Where to find generated code
  - 2.2.4.Other useful commands
3.Connecting to Database & CRUD Operations
- 3.1.Database Connection String
- 3.2.Connect to Database in Rust
- 3.3.CRUD Operations
  - 3.3.1.CREATE
  - 3.3.2.READ
  - 3.3.3.UPDATE
  - 3.3.4.DELETE
4.Advanced Queries
- 4.1.Query with Conditions
- 4.2.String Filters
- 4.3.Number Filters
- 4.4.Optional Field Filters
- 4.5.Count Records
- 4.6.Transactions
- 4.7.Raw SQL Queries
- 4.8.Left Joins over Association Table
5.Complete Example: Iced App with Prisma

October 14, 2025

Prisma

Rust

1.Install Prisma
2.Brief Introduction to Prisma
- 2.1.Prisma Schema File
  - 2.1.1.Initialize prisma
  - 2.1.2.Complete prisma/schema.prisma example
  - 2.1.3.Schema syntax reference
- 2.2.Schema Migration & Code Generation
  - 2.2.1.Create our first migration
  - 2.2.2.When we change the schema
  - 2.2.3.Where to find generated code
  - 2.2.4.Other useful commands
3.Connecting to Database & CRUD Operations
- 3.1.Database Connection String
- 3.2.Connect to Database in Rust
- 3.3.CRUD Operations
  - 3.3.1.CREATE
  - 3.3.2.READ
  - 3.3.3.UPDATE
  - 3.3.4.DELETE
4.Advanced Queries
- 4.1.Query with Conditions
- 4.2.String Filters
- 4.3.Number Filters
- 4.4.Optional Field Filters
- 4.5.Count Records
- 4.6.Transactions
- 4.7.Raw SQL Queries
- 4.8.Left Joins over Association Table
5.Complete Example: Iced App with Prisma

1. Install Prisma

1.1. Install Prisma CLI via npm/yarn

This is for schema-migration based on our schema.prisma.

1yarn init -y
2yarn add -D prisma@4.8.0
3yarn 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)

1cargo install --git https://github.com/Brendonovich/prisma-client-rust \
2--tag 0.6.11 \
3prisma-cli \
4--features sqlite \˛
5--force

Create symlink for cargo subcommand

1ln -s ~/.cargo/bin/prisma ~/.cargo/bin/cargo-prisma

Available database features:

sqlite - SQLite
postgresql - PostgreSQL
mysql - MySQL
mssql - Microsoft SQL Server
mongodb - MongoDB

1.3. Add Prisma Client Rust to Cargo.toml

1[dependencies]
2prisma-client-rust = { git = "https://github.com/Brendonovich/prisma-client-rust", tag = "0.6.11", default-features = false, features = ["sqlite", "migrations"] }
3serde = { version = "1.0", features = ["derive"] }
4tokio = { version = "1.0", features = ["full"] }
5[build-dependencies]
6prisma-client-rust-cli = { git = "https://github.com/Brendonovich/prisma-client-rust", tag = "0.6.11" }

1.4. Create build.rs in project root

The following script will be executed when we cargo build or cargo run:

1fn main() {
2    // Generate Prisma Client Rust whenever schema.prisma changes
3    println!("cargo:rerun-if-changed=prisma/schema.prisma");
4    prisma_client_rust_cli::run();
5}

2. Brief Introduction to Prisma

2.1. Prisma Schema File

2.1.1. Initialize prisma

Create prisma/schema.prisma file:

1npx prisma init --datasource-provider sqlite

2.1.2. Complete prisma/schema.prisma example

1generator client {
2  provider = "cargo prisma"
3  output   = "../src/prisma.rs"
4}
5
6datasource db {
7  provider = "sqlite"
8  url      = "file:../dev-database.db"
9}
10
11// Your data models
12model Counter {
13  id          Int      @id @default(autoincrement())
14  value       BigInt
15  name        String
16  description String?
17  isActive    Boolean  @default(true) @map("is_active")
18  multiplier  Float?
19  createdAt   DateTime @default(now()) @map("created_at")
20  updatedAt   DateTime @updatedAt @map("updated_at")
21
22  @@map("counter")
23}
24
25model User {
26  id        Int      @id @default(autoincrement())
27  email     String   @unique
28  name      String
29  createdAt DateTime @default(now()) @map("created_at")
30  
31  @@map("users")
32}

Note that we may also need:

1created_at        Float          @default(dbgenerated("(CAST((julianday('now') - 2440587.5) * 86400000.0 AS REAL))"))
2created_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

1@default(autoincrement())   // Auto-increment ID
2@default(now())             // Current timestamp
3@default(true)              // Boolean default
4@default("text")            // String default
5@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

1npx prisma migrate dev --name init

which outputs:

1✓ Generated SQL migration
2✓ Applied migration to database
3✓ 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:

1npx 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

1npx prisma generate

Reset database (WARNING: deletes all data)

1npx prisma migrate reset

Open Prisma Studio (database GUI)

1npx prisma studio

3. Connecting to Database & CRUD Operations

3.1. Database Connection String

Set in prisma/schema.prisma:

1datasource db {
2  provider = "sqlite"
3  url = "file:../dev-database.db"
4}
5
6// For PostgreSQL:
7// provider = "postgresql"
8// url = "postgresql://user:password@localhost:5432/mydb"
9
10// For MySQL:
11// provider = "mysql"
12// url = "mysql://user:password@localhost:3306/mydb"

Or use environment variable:

1datasource db {
2provider = "sqlite"
3url = env("DATABASE_URL")
4}

and create .env file:

1DATABASE_URL="file:./dev-database.db"

3.2. Connect to Database in Rust

1// In main.rs
2mod prisma;
3use prisma::PrismaClient;
4
5#[tokio::main]
6async fn main() -> Result<(), Box<dyn std::error::Error>> {
7    // Create client (reads connection string from schema.prisma)
8    let client = prisma::new_client().await?;
9    
10    // Or specify custom URL at runtime
11    // let client = prisma::new_client_with_url("file:./my-database.db").await?;
12    
13    // Now use the client for queries
14    Ok(())
15}

3.3. CRUD Operations

3.3.1. CREATE

Create a single record

1let counter = client
2    .counter()
3    .create(
4        0,                          // value (required field)
5        "My Counter".to_string(),   // name (required field)
6        vec![                       // optional fields
7            prisma::counter::description::set(Some("A test counter".to_string())),
8            prisma::counter::is_active::set(true),
9            prisma::counter::multiplier::set(Some(2.5)),
10        ]
11    )
12    .exec()
13    .await?;

Create multiple records

1client
2    .counter()
3    .create_many(vec![
4        prisma::counter::create(0, "Counter 1".to_string(), vec![]),
5        prisma::counter::create(10, "Counter 2".to_string(), vec![]),
6        prisma::counter::create(20, "Counter 3".to_string(), vec![]),
7    ])
8    .exec()
9    .await?;

3.3.2. READ

Find by id (returns Option<Data>)

1let counter = client
2    .counter()
3    .find_unique(prisma::counter::id::equals(1))
4    .exec()
5    .await?;
6
7if let Some(c) = counter {
8    println!("Found: {}", c.name);
9} else {
10    println!("Not found");
11}

Find first match

1let counter = client
2    .counter()
3    .find_first(vec![
4        prisma::counter::is_active::equals(true)
5    ])
6    .exec()
7    .await?;

Find many with filters

1let counters = client
2    .counter()
3    .find_many(vec![
4        prisma::counter::is_active::equals(true),
5        prisma::counter::value::gte(10),
6    ])
7    .exec()
8    .await?;
9
10println!("Found {} active counters", counters.len());

Find many with ordering

1let counters = client
2    .counter()
3    .find_many(vec![])
4    .order_by(prisma::counter::value::order(prisma::Direction::Desc))
5    .exec()
6    .await?;

Find many with pagination

1let counters = client
2    .counter()
3    .find_many(vec![])
4    .skip(10)
5    .take(5)  // Limit to 5 results
6    .exec()
7    .await?;

Select specific fields only

1let names = client
2    .counter()
3    .find_many(vec![])
4    .select(prisma::counter::select!({
5        id
6        name
7    }))
8    .exec()
9    .await?;
10
11for counter in names {
12    println!("ID: {}, Name: {}", counter.id, counter.name);
13}

3.3.3. UPDATE

Update a single record

1let updated = client
2    .counter()
3    .update(
4        prisma::counter::id::equals(1),  // which record
5        vec![                             // what to update
6            prisma::counter::value::increment(1),
7            prisma::counter::name::set("Updated Name".to_string()),
8            prisma::counter::description::set(Some("New description".to_string())),
9        ]
10    )
11    .exec()
12    .await?;
13
14println!("New value: {}", updated.value);

Update many records

1let count = client
2    .counter()
3    .update_many(
4        vec![
5            prisma::counter::is_active::equals(false)  // filter
6        ],
7        vec![
8            prisma::counter::is_active::set(true)      // update
9        ]
10    )
11    .exec()
12    .await?;
13
14println!("Updated {} records", count);

Upsert (update if exists, create if not)

1let counter = client
2    .counter()
3    .upsert(
4        prisma::counter::id::equals(1),                     // find by this
5        prisma::counter::create(0, "New".to_string(), vec![]), // create if not found
6        vec![prisma::counter::value::increment(1)]          // update if found
7    )
8    .exec()
9    .await?;

3.3.4. DELETE

Delete a single record

1let deleted = client
2    .counter()
3    .delete(prisma::counter::id::equals(1))
4    .exec()
5    .await?;
6
7println!("Deleted counter: {}", deleted.name);

Delete many records

1let count = client
2    .counter()
3    .delete_many(vec![
4        prisma::counter::is_active::equals(false)
5    ])
6    .exec()
7    .await?;
8
9println!("Deleted {} inactive counters", count);

4. Advanced Queries

4.1. Query with Conditions

Combine multiple conditions (AND)

1let counters = client
2    .counter()
3    .find_many(vec![
4        prisma::counter::is_active::equals(true),
5        prisma::counter::value::gte(10),
6        prisma::counter::value::lte(100),
7        prisma::counter::name::contains("test"),
8    ])
9    .exec()
10    .await?;

OR conditions

1let counters = client
2    .counter()
3    .find_many(vec![
4        prisma::counter::or(vec![
5            prisma::counter::name::equals("Counter A".to_string()),
6            prisma::counter::name::equals("Counter B".to_string()),
7        ])
8    ])
9    .exec()
10    .await?;

NOT condition

1let counters = client
2    .counter()
3    .find_many(vec![
4        prisma::counter::not(vec![
5            prisma::counter::is_active::equals(false)
6        ])
7    ])
8    .exec()
9    .await?;

4.2. String Filters

Exact match

1prisma::counter::name::equals("exact name")

Contains substring

1prisma::counter::name::contains("substring")

Starts with

1prisma::counter::name::starts_with("prefix")

Ends with

1prisma::counter::name::ends_with("suffix")

Case insensitive (if supported by database)

1prisma::counter::name::mode(prisma::QueryMode::Insensitive)

4.3. Number Filters

1prisma::counter::value::equals(10)
2prisma::counter::value::lt(10)      // less than
3prisma::counter::value::lte(10)     // less than or equal
4prisma::counter::value::gt(10)      // greater than
5prisma::counter::value::gte(10)     // greater than or equal
6prisma::counter::value::in_vec(vec![1, 2, 3, 4])
7prisma::counter::value::not_in_vec(vec![5, 6])

4.4. Optional Field Filters

Check if field is null

1prisma::counter::description::equals(None)

Check if field is not null

1prisma::counter::description::not(None)

Specific value

1prisma::counter::description::equals(Some("text".to_string()))

4.5. Count Records

1let count = client
2    .counter()
3    .count(vec![
4        prisma::counter::is_active::equals(true)
5    ])
6    .exec()
7    .await?;
8
9println!("Total active counters: {}", count);

4.6. Transactions

Execute multiple operations atomically

1let result = client
2    ._transaction()
3    .run(|tx| async move {
4        // Create user
5        let user = tx
6            .user()
7            .create("user@example.com".to_string(), "John".to_string(), vec![])
8            .exec()
9            .await?;
10        
11        // Create counter linked to user
12        let counter = tx
13            .counter()
14            .create(0, "User's Counter".to_string(), vec![])
15            .exec()
16            .await?;
17        
18        Ok((user, counter))
19    })
20    .await?;
21
22println!("Created user {} and counter {}", result.0.id, result.1.id);

4.7. Raw SQL Queries

Raw query with type safety

1use prisma_client_rust::raw;
2
3let counters: Vec<prisma::counter::Data> = client
4    ._query_raw(raw!(
5        "SELECT * FROM counter WHERE value > {}",
6        10
7    ))
8    .exec()
9    .await?;

Raw execute (INSERT/UPDATE/DELETE)

1let affected = client
2    ._execute_raw(raw!(
3        "UPDATE counter SET value = value + 1 WHERE is_active = {}",
4        true
5    ))
6    .exec()
7    .await?;
8
9println!("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:

1pub fn get_all_scripts_of_folder(&self, folder_id: i32) -> Vec<prisma::shell_script::Data> {
2    let runtime = tokio::runtime::Runtime::new().unwrap();
3
4    let scripts = runtime
5        .block_on(async move {
6            // Query the folder with all related scripts through the junction table
7            self.db
8                .scripts_folder()
9                .find_unique(prisma::scripts_folder::id::equals(folder_id))
10                .with(
11                    prisma::scripts_folder::rel_scriptsfolder_shellscript::fetch(vec![])
12                        .with(prisma::rel_scriptsfolder_shellscript::shell_script::fetch()),
13                )
14                .exec()
15                .await
16        })
17        .unwrap();
18
19    // Extract the shell scripts from the joined data
20    if let Some(folder) = scripts {
21        folder
22            .rel_scriptsfolder_shellscript
23            .unwrap_or_default()
24            .into_iter()
25            .filter_map(|rel| rel.shell_script.map(|s| *s))
26            .collect()
27    } else {
28        vec![]
29    }
30}

.with() means left join;
.fetch() follows from what we declared in schema.prisma; Do we fetch a list? or fetch a single item?

5. Complete Example: Iced App with Prisma

1use iced::{executor, Application, Command, Element, Settings, Theme};
2use iced::widget::{button, column, container, text};
3
4mod prisma;
5
6struct CounterApp {
7    count: i64,
8    client: Option<prisma::PrismaClient>,
9    counter_id: Option<i32>,
10}
11
12#[derive(Debug, Clone)]
13enum Message {
14    DatabaseConnected(prisma::PrismaClient),
15    Increment,
16    Decrement,
17    CounterSaved,
18    CounterLoaded(i64),
19}
20
21impl Application for CounterApp {
22    type Executor = executor::Default;
23    type Message = Message;
24    type Theme = Theme;
25    type Flags = ();
26
27    fn new(_flags: ()) -> (Self, Command<Message>) {
28        (
29            Self {
30                count: 0,
31                client: None,
32                counter_id: None,
33            },
34            Command::perform(setup_database(), Message::DatabaseConnected),
35        )
36    }
37
38    fn title(&self) -> String {
39        String::from("Prisma Counter")
40    }
41
42    fn update(&mut self, message: Message) -> Command<Message> {
43        match message {
44            Message::DatabaseConnected(client) => {
45                self.client = Some(client);
46                Command::perform(load_counter(), Message::CounterLoaded)
47            }
48            Message::Increment => {
49                self.count += 1;
50                Command::perform(
51                    save_counter(self.client.clone(), self.count),
52                    |_| Message::CounterSaved,
53                )
54            }
55            Message::Decrement => {
56                self.count -= 1;
57                Command::perform(
58                    save_counter(self.client.clone(), self.count),
59                    |_| Message::CounterSaved,
60                )
61            }
62            Message::CounterLoaded(value) => {
63                self.count = value;
64                Command::none()
65            }
66            Message::CounterSaved => Command::none(),
67        }
68    }
69
70    fn view(&self) -> Element<Message> {
71        let content = column![
72            text(format!("Count: {}", self.count)).size(50),
73            button("Increment").on_press(Message::Increment),
74            button("Decrement").on_press(Message::Decrement),
75        ]
76        .spacing(20)
77        .padding(20);
78
79        container(content).center_x().center_y().into()
80    }
81}
82
83async fn setup_database() -> prisma::PrismaClient {
84    prisma::new_client().await.expect("Failed to connect to database")
85}
86
87async fn load_counter() -> i64 {
88    // Implement loading logic
89    0
90}
91
92async fn save_counter(client: Option<prisma::PrismaClient>, value: i64) {
93    if let Some(client) = client {
94        client
95            .counter()
96            .upsert(
97                prisma::counter::id::equals(1),
98                prisma::counter::create(value, "Main Counter".to_string(), vec![]),
99                vec![prisma::counter::value::set(value)],
100            )
101            .exec()
102            .await
103            .ok();
104    }
105}
106
107fn main() -> iced::Result {
108    CounterApp::run(Settings::default())
109}

Contents