Drizzle + SQLite in Tauri App

GitHub: https://github.com/HuakunShen/tauri-demo/tree/master/examples/drizzle-sqlite-proxy

Info

This demo let you use drizzle to control your sqlite DB in a Tauri app, without any sidecar. This is a Tauri v2 reproduction for the archived repo https://github.com/tdwesten/tauri-drizzle-sqlite-proxy-demo

Tauri’s backend is in Rust, so I always thought the only way to use sqlite ORM in a Tauri app is with projects like diesel or prisma-client-rust, which could be hard because they are in rust. What is even harder is sqlite db encryption. Both of the 2 ORMs don’t support cipher encryption, thus I had to write raw sql queries in kunkun Example Maintaining raw sql queries is a nightmare to me, especially when it comes to schema migration. I have to rely on thorough testing to make sure everything is correct. There is no type checking.

I never knew it’s possible to use TypeScript ORM like drizzle in Tauri without a sidecar. In my project kkrpc I implemented a Tauri adapter and made it possible to use compiled TypeScript backend as a sidecar in a Tauri app. With kkrpc, it’s easy to call TypeScript backend from frontend. I can use any TypeScript libraries in a compiled deno/bun/node binary, including drizzle. However that introduces at least 60MB to the bundle size. It’s a good deal if you can take advantage of many node packages, but not really worth it for just DB.

Then I found this project https://github.com/tdwesten/tauri-drizzle-sqlite-proxy-demo using drizzle proxy to send queries to Tauri’s sql plugin. Drizzle Proxy Docs: https://orm.drizzle.team/docs/connect-drizzle-proxy

Basically, it’s a translator between the tauri-plugin-sql and drizzle ORM. drizzle computes the sql query in frontend and send the query + params to backend to execute. The backend can be a http server, or Tauri core.

// Example of driver implementation
import { drizzle } from 'drizzle-orm/pg-proxy';
 
const db = drizzle(async (sql, params, method) => {
  try {
    const rows = await axios.post('http://localhost:3000/query', { sql, params, method });
 
    return { rows: rows.data };
  } catch (e: any) {
    console.error('Error from pg proxy server: ', e.response.data)
    return { rows: [] };
  }
});

Excalidraw Diagrams

Here is real code

import { drizzle } from "drizzle-orm/sqlite-proxy";
import Database from "@tauri-apps/plugin-sql";
import * as schema from "./schema";
 
export async function getDb() {
  return await Database.load("sqlite:test.db");
}
 
export const db = drizzle<typeof schema>(
  async (sql, params, method) => {
    const sqlite = await getDb();
    let rows: any = [];
    let results = [];
 
    // If the query is a SELECT, use the select method
    if (isSelectQuery(sql)) {
      rows = await sqlite.select(sql, params).catch((e) => {
        console.error("SQL Error:", e);
        return [];
      });
    } else {
      // Otherwise, use the execute method
      rows = await sqlite.execute(sql, params).catch((e) => {
        console.error("SQL Error:", e);
        return [];
      });
      return { rows: [] };
    }
 
    rows = rows.map((row: any) => {
      return Object.values(row);
    });
 
    // If the method is "all", return all rows
    results = method === "all" ? rows : rows[0];
    await sqlite.close();
    return { rows: results };
  },
  // Pass the schema to the drizzle instance
  { schema: schema, logger: true }
);

And here is how it can be used (just like regular drizzle code)

const loadUsers = async () => {
  db.query.users
    .findMany()
    .execute()
    .then((results) => {
      console.log("🚀 ~ FindMany response from Drizzle:", results);
      users = results;
    });
};
 
async function addUser() {
  await db.insert(schema.users).values({ name: nameInput });
  nameInput = "";
  loadUsers();
}

graph TD
    A[Frontend JavaScript/TypeScript] --> B[Drizzle ORM]
    B --> C[Drizzle Proxy]
    C --> D[Tauri SQL Plugin]
    D --> E[SQLite Database]
    
    subgraph "Frontend JavaScript/TypeScript"
        A
        B
        C
    end
    
    subgraph "Backend Rust"
        D
        E
    end
    
    F[SQL Query + Params] -.Generated by Drizzle.-> C
    C -.Translates and forwards.-> D
    D -.Executes query.-> E
    E -.Returns results.-> D
    D -.Returns data.-> C
    C -.Formats for ORM.-> B
    B -.Type-safe results.-> A