Local ORMs

[tantaman]

Hey, maintainer of http://aphrodite.sh here.

Trong ORM is a JavaScript library for building reactive SQLite queries for web, mobile and desktop

Looks like we're thinking along the same lines :)

Lmk if you'd like to bounce any ideas around when it comes to local ORMs.

On my end I've been wondering if native IndexDB support is worth it or to only support AbsurdSQL / sql.js.

Also, looks like AbsurdSQL isn't very well maintained anymore. The sql.js author is also looking for a new maintainer. Maybe those are things we could jointly work on / take over together.

Last thing -- have you seen or tried to work with https://github.com/rhashimoto/wa-sqlite/blob/master/README.md ?

[quolpr]

Hi @tantaman !

I happy to see you here 🙂

I checked your project — looks very cool!

Great idea, I like how it allows to generate strict typings for your queries, looks like prisma 🙂. But as for me I want to make deep integration to the SQLite, without any other DBs support. To use all the power of the SQLite(which is really poerwfull!).

On my end I've been wondering if native IndexDB support is worth it or to only support AbsurdSQL / sql.js.

I play around with IndexedDB a lot, and I can say it doesn't worth. I used Dexie, PouchDB — they all sucks on large datasets, and lacks support of, for example, full-text search. SQLite just far away in terms of performance and utility.

And one other thing, IndexedDB has a lot of bugs that varies from browser to browser. Absurd SQL make more simple read/write(as I understand it read/write by blocks of data) and absurd SQL breaks rarely (Acutally you could find all the bugs at Dexie.JS repo at issues)

I was using Dexie in https://github.com/quolpr/harika for ~4-5 month, and switched to Absurd SQL once it was released, and already use it for ~5 months. It works very stable and very performant.

One downside of AbsurdSQL — no iframe and other things support due to COOP https://github.com/jlongster/absurd-sql#requirements . But one of the workaround is to try service worker workaround — https://github.com/jlongster/absurd-sql/issues/17

I was speaking with author of AbsurdSQL. He is pretty busy, but I hope he will give me a maintain rights. Actually he wanted to give me them ~6 month ago 😅 I would be happy to maintain it together.

As for https://github.com/rhashimoto/wa-sqlite — looks cool! But the license is GPL, but I want to have MIT for my project.

[tantaman]

But as for me I want to make deep integration to the SQLite, without any other DBs support.

Yeah, there is a really good case to only support SQLite, some of that case being made here: https://changelog.com/posts/sqlite-web-renaissance

I think it's a solid decision. One that I myself might follow -- adding postgres (or others) to the mix detracts from the core mission of making local-first software easier to develop.

[quolpr]

@tantaman thanks, cool article 👍

[quolpr]

@tantaman there are some insides about Riffle https://www.youtube.com/watch?v=BmgL1SRfUyc (and overall what is Riffle — https://riffle.systems/essays/prelude/), maybe you will be interested 🙂

As I understand, there are working on to make integration of GraphQL with SQLite. It seems to be similar on what you are working on 🤔

[quolpr]

Btw, do you want to make trong-orm as a query engine? It already supports expo, electron, vite, CRA. I'm also working on doc https://trong-orm.netlify.app/ .

You can you it without any react integrations, it is totally framework-agnostic. To use it, you just need:

import sqlWasmUrl from "@trong-orm/sql.js/dist/sql-wasm.wasm?url";

const db = await initDbClient({
  dbName: "helloWorld",
  dbBackend: absurdWebBackend({
    wasmUrl: sqlWasmUrl,
  }),
  plugins: [
    // migrationsPlugin({ migrations: [] }), // uncomment if you need migrations support
    // reactiveQueriesPlugin(), uncomment if you need to support listenQuery https://trong-orm.netlify.app/listen-queries ( https://trong-orm.netlify.app/how-reactivity-works )
  ],
});

await runQuery(db, sql`SELECT 1`);

// Or with query builder:
await runQuery(db,
  select(
    { col1: select(sql`1`) },
    { col2: select(sql`1`) },
    alias(select("3"), "other_column")
  )
    // It supports CTE
    .with({
      table: "people_cte",
      columns: ["name", "age"],
      select: values(["Andrew", 1], ["Dima", 2], ["Sergey", 3])
    })
    .distinct(true)
    .from(select().from("books").limit(10))
    // Short version of equal()
    .where({ title: eq$("Harry Potter"), pages: gtEq$(5) })
    .where(not(and({ title: eq$("Harry Potter") }, { pages: gtEq$(5) })))
    .where(or({ title: eq$("Little Prince"), otherCol: like$("Little") }))
    // Or you can use the full version, like gtEq(col, val)
    .where(gtEq("pages", 10))
    // You can also use raw sql in where
    .orWhere(sql`books.type1 = "type1"`, sql`books.type2 = "type2"`)
    .orderBy(desc("createdAt"), asc("updatedAt"))
    .orderBy(asc("pages", "NULLS FIRST"))
    .groupBy("books.author")
    .having(gtEq(sql`COUNT(pages)`, 5))
    // support union
    .union(select(sql`1`).limit(5))
)

// or transaction

await transaction(db, () => {
  await runQuery(db, sql`DELETE * FROM ${table('notes')}`);
})

// You can subscribe to queries (only if reactiveQueriesPlugin used), and reactivity will work in multi-tab

const notesTable = sql.table("notes");

listenQueries(db, [select().from(notesTable)]).subscribe((res) => {
  console.log("Queries result: ", res);
});

It also supports middlewares on query run, and some events on transaction run. Actually reactiveQueriesPlugin ( https://github.com/trong-orm/trong-orm/blob/main/packages/reactive-queries-plugin/src/reactivePlugin.ts#L24 ) uses transaction events and query middlewares.

Let me know what do you think 🙂

[quolpr]

And you can check all examples:

With CRA: https://github.com/trong-orm/trong-cra-example

With Vite + React: https://github.com/trong-orm/trong-orm/tree/main/packages/vite-react-example

With Expo (native+web): https://github.com/trong-orm/trong-expo-example

With Electron: https://github.com/trong-orm/trong-electron-better-sqlite3-example

[quolpr]

@tantaman hey! I built the vFS for wa-sqlite with the performance that absurd-sql has (or almost the same, sometime 10% difference). You can run benchmark here, and see the VFS implementation here. The code is pretty messy, but it does the job. I plan to refactor it.

[quolpr]

So, who will be the hero that will make MIT version of wa-sqlite? 😅

[tantaman]

Awesome. Do you see any advantages of wa-sqlite over absurd-sql?

[quolpr]

No need to use COOP. With COOP you are not able to use iframes from the other domain (like youtube.com). Also, I noticed that wa-sqlite version starts faster (especially on big data sets) — but I didn't measure it.

https://stackoverflow.com/questions/68654550/cross-origin-embedder-policy-how-to-allow-only-certain-domains

[twoxfh]

Awesome. Do you see any advantages of wa-sqlite over absurd-sql?

@tantaman Absurd is MIT licensed and WASQL is GPL V3. Big difference depending on your goals.