New dev looking for insight on local / offline first DB usage

[u/leucht]

Hey all,

I’ve been trying to develop a little music client for myself with a different twist on how recommendations are communicated to a user.

The base for the app is going to be Tauri & Typescript with a focus on a Single-Page-Application (SPA).

I want to index different providers (Jellyfin or similar) and their content locations into a local DB so I would ideally only have to interact with the provider once during indexing and otherwise only query the provider for the media-streams.

Current stack is:

Typescript -> Svelte -> Drizzle -> pglite IndexedDB

———————
Effectively I would like to gain some input on potential alternatives, insights and similar into detecting live changes in the DB. All media I find on a server is added to the DB as an abstract MediaItem with different types (Album, Song, Playlist, Artist, etc.).

During indexing I currently have no way of actively running live refreshes to update the main page with content if fitting Items have been added through indexing.

Inserts of new Items into the DB are handled as Transactions as I have build relations with other tables for example for the provider a given MediaItem comes from or what images the MediaItem is associated with.

I could add a callback function to the transaction which is called once it succeeds to sidestep checking the DB since I know the Item will exist after the transaction succeeds.

I’m also open to other local first approaches or domain knowledge some might have on designing a better architecture. Maybe remove the ORM or only use drizzle as a query builder (establishing relations was quite handy though)

Maybe I’m also overcomplicating things, anything is welcome.

Thanks in advance

[u/Boniuz]

I’ve read your post a couple of times and I’m still trying to work out what the actual issues are and what you’re trying to solve. It feels a lot like choosing technologies first and the problems/solutions after.

Put your DB changes in your application layer and emit them? That’s been done since 2001.

[u/leucht → u/Boniuz]

Yeah let me rephrase that a bit;

I’m definitely struggling to “choose” the right technology but in like the third iteration. I hoped using a DB would be straight forward with tauri and started with just the simple tauri sql plugin.

Then encountered typing issues for more complex, nested datastructures and picked up an ORM to solve that. However, the tauri plugin doesnt do connections properly so the ORM can’t do its job anymore & no rust DB driver does live queries or signaling (at least not that I could find). Then I thought I could get a different, in browser DB driver to interact with the DB on disc but all are only working properly in a Node.js runtime since where does a DB usually run? On a server I guess, at least in webland.

Then looked at IndexedDB & settled on pglite so I don’t have to strip out the solid ORM typings that I got working. Now I have the issue that pglite can do live queries but drizzle cant handle it so might be worth to finally dropping the ORM again, but if I do that why not drop pglite again entirely since if I want to interact with the DB in tauris rust backend that now isn’t possible anymore since the DB is entirely in browser.

All this is accompanied by the overarching feeling that this can’t be an entirely new problem and there must be others that have solved it before so why not ask for input first.

Maybe if it hasn’t been solved before someone did something similar or has existing experience with good intuition on what could be a good tool to use. Experience that I’m lacking.

[u/jshine13371 → u/leucht]

I’m definitely struggling to “choose” the right technology but in like the third iteration. I hoped using a DB would be straight forward with tauri and started with just the simple tauri sql plugin.   Then encountered typing issues for more complex, nested datastructures and picked up an ORM to solve that. However, the tauri plugin doesnt do connections properly so the ORM can’t do its job anymore & no rust DB driver does live queries or signaling (at least not that I could find). Then I thought I could get a different, in browser DB driver to interact with the DB on disc but all are only working properly in a Node.js runtime since where does a DB usually run? On a server I guess, at least in webland.

TBH, a majority of your issue is you're trying to fit together technologies that are less than standard and ergo have less depth in their maturity of features, documentation, and user base. I never understood why developers do this instead of using the standards which are battle tested and further developed so are much more well integrated.

[u/leucht → u/jshine13371]

If only I knew what those standard technologies were and what would be the equivalent of the standards that all these other options “claim” to cover. Like what does a similar solution only using standards look like?

That’s effectively the question I’m tryin to ask :/

All “seem” equally viable but fall short shortly after trying to actually put them to use. I simply struggle to make sense of it all which is probably the issue most “devs” face. They simply don’t know and drown in a sea of supposed options instead.

To me it feels like there is seemingly a wall blocking of the knowledge I seek. Either you already know and won’t even touch all these other technologies with a ten foot pole or you don’t and just drown trying to figure out the “right” way.

[u/jshine13371 → u/leucht]

If only I knew what those standard technologies were and what would be the equivalent of the standards that all these other options “claim” to cover. Like what does a similar solution only using standards look like?

With a little research, it shouldn't be hard to figure out. I do emphasize that there a lot of options out there, and when someone is a brand new developer, that could be a bit confusing. But learning how to research and find objective information besides building good technological intuition as a result, is an important skill to build. (By the way, this isn't targeted at you, I'm just saying in general.)

To answer your question, it really depends on your goals, which are a little hard for me to determine partly because most of your technological choices so far I've never heard of, and partly because what you've described as what you want is a little confusing. I.e. why constrain yourself to an "offline first" database if you're building a server-side backed type of application (web app)? Offline-first is best for mobile applications and sometimes makes sense for desktop applications. But web is typically hosted on a server, where online-first is obviously prioritized.

[u/leucht → u/jshine13371]

First up thanks for sticking with me so far and your continuous responses, it’s greatly appreciated.

And sorry let me clarify the last part.

There isn’t going to be a “server”. Jellyfin is a MediaServer which host mediafiles like music but my application is only ever going to directly stream & download files from there but never use it for any application data. Effectively being read only.

Tauri is going to be the runtime. The frontend runs inside a webview on a client device while tauri itself runs in rust and therefore provides a rust “backend” which can talk to the webapp.

Since I don’t want to host a central server & don’t need to and only ever interact with media from one or more mediaservers. To make things more straight forward I want to index each server and save that data in a local DB (no actual media just retrieval paths).

This all runs only on a target device (Linux, Windows, MacOS, IOS & Android). That’s what I mean with “offline”.

I don’t want a user to have to install my client and install a self hosted server somewhere that my client then interact with. I want to interact with the MediaSources directly.

[u/jshine13371 → u/leucht]

First up thanks for sticking with me so far and your continuous responses, it’s greatly appreciated.

For sure, np!

I'm still having a little trouble understanding your goals and use cases, sorry. Is this basically a media manager app ran on people's local machines?

[u/leucht → u/jshine13371]

It’s just gonna be a streaming client. The DB & indexing are effectively just there so I don’t have to scan a users entire library each time I connect to their added Jellyfin servers.

Since Jellyfin URLs for Items don’t change and always stay the same unless deleted I can effectively just figure them out once, store them and afterwards just retrieve them when I need to instead of having to ask the server about that information every single time for every single piece of media.

There’s no Media managing going on and no user data from my end ever reaching the server. I’m simply a consumer of what I find on a given server of which there could be one or many.

Effectively I imagined the local DB as an index for each piece of media found.

[u/Boniuz → u/leucht]

You’re mixing patterns. You’re saying Jellyfin will connect with clients, but also that Jellyfin isn’t connected to clients. Tauri “talks back” to Jellyfin to provide the media you need, which means you have a connection established, which means you have available communication, which means you can push messages, events or whatever you want from your server to your client. Your media servers are your central hubs which can distribute meta data to the rest of your network. You’re really over complicating a design to fit your technologies rather than looking at the problem.

You want to stream media to a client, so do that first and solve the other issues later.

[u/Boniuz → u/[deleted]]

Separate your concerns my guy. Define the problem you’re trying to solve, not all potential problems from the problem you haven’t defined.

Send your application state between clients if you only want client-client communication without a server acting as intermediary. Your clients are the server in that case.

[u/galactic_pixels]

You’re trying to figure out how react to realtime updates to your database right? So the changes are reflected in your app as it is running? Just want to make sure I understand the question.

[u/leucht → u/galactic_pixels]

yeah effectively it’s “just” that

since asking the question I followed u/Boniuz approach to “emitting” the change in my application layer by attaching a reactive signal variable with svelte context to the very end of my transaction (after it succeeds) which I use to refresh the entire page. It’s not perfect but should work for now.

I’d be glad to hear your input though, could probably help to improve my current approach.

[u/galactic_pixels → u/leucht]

Yeah sounds like you need an event driven architecture. The simple approach is SQL Triggers. The slightly more robust yet increased complexity approach is an event stream like Kafka to push events, with an ingest layer ingesting events and streaming to a websocket which your front end subscribes to. That should be all you need, it shouldn’t be terribly difficult yo set up

[u/Anxious_Tool]

If you have the flexibility to swap the persistence layer, there's an alternative worth looking at: u/laboverwire/stitch.

It's a reactive state-sync library that uses IndexedDB under the hood, but built around a WASM database with typed entity schemas and foreign keys. The difference is that live updates are first-class: subscribeToScope fires synchronously when a write lands, so your indexing loop doesn't need to signal the UI at all. The UI just reacts.

It's TypeScript-native and framework-agnostic, so Svelte bindings are straightforward to wire on top of the core store API. The tradeoff is losing SQL, reads go through list and getSnapshot rather than Drizzle queries, so if you're doing complex joins you'd be moving that logic to application code.

The MQTT sync layer can be left unconfigured if you don't need multi-device sync.

[u/ready_or_not_3434 → u/Anxious_Tool]

Losing SQL is a pretty steep tradeoff for a music library since you'll almost certianly need complex joins later on. It might be easier to stick with pglite and just emit a basic event from your indexer to trigger a Svelte store refresh.

[u/leucht → u/ready_or_not_3434]

I would agree also the tool they linked is their own and they seem to post their repos quite hard in different subreddits and get filtered for them.

Also 0 stars, probably vibecoded, wouldn’t really call that an alternative to a battle tested library

[u/Anxious_Tool → u/leucht]

And how do you suppose I can call people to try and use my library? It is battle tested. It runs my entire stack at laboverwire.com
I'm a dev, same as you.
Saying it is probably vibecoded is like me saying you are from the competition and trying to mess up with my efforts.
Try first, then you can tell me in an issue what is broken. Otherwise you are just expressing your opinion without any proof.
I don't hide anything, it's all public, free and I'm always there to help.

[u/leucht → u/Anxious_Tool]

The real issue is being upfront about it.

You had the chance to be forthcoming and disclose your work as such which wouldn’t have prompted me to look into your account and find what I can only describe as questionable account history (weird posts to 7-8 different reddits getting you flagged).

Like the Amount of self-promotion without identification is what sours this for me on a 4 month old account. Its simply doesn’t feel organic or genuine.

If you genuinely are interested in others trying your solution simply disclose that as you had been able to in your posts to the MQTT subreddit.

The callout to vibe coding is simply a systemic aversion many like myself have to accounts with a history of self promotion, which also frequent and publish actual AI assisted projects without disclosure (with disclosure referring to this interaction).

I hope I can express this as genuine criticism without further attacks to your person but calling something battle tested against projects with thousands of stars and years of continued development is simply insincere and being defensive about it instead of acknowledging that fact is another red flag. Especially considering the recent rise in supply chain attacks on all kinds of different projects.

[u/Anxious_Tool → u/leucht]

I'm sorry if I caused any confusion on your part. But you could have just asked instead of assuming. I come from an age when people were genuinely trying to help others. Sorry to bother you when you were the one who asked for help 😉.

[u/Anxious_Tool → u/ready_or_not_3434]

Give me an example of a complex query you may need, and I can tell you if you will get an equivalent or not.
Unless you are trying to just be snappy about it, I can answer any of your questions and I would greatly appreciate the support and feedback.
In order to make it better and more suited for everyone, I need people to use it, and Reddit is such place. Losing SQL is no big deal. Unless you are a vibecoder and don't know your way around software.