8 ms·

Show HN: Doculite – Use SQLite as a Document Database

• mixmastamyk 3y ago
  Does this mean documents in a database, or a database as a document? I've tried the second, but the time comes when you need to (re)order items which gets clumsy.
  • thenorthbay 3y ago
    If I understood you correctly, Documents in a database, and database as a file. If else please let me know.
• deleted 3y ago
  [deleted]
• bastawhiz 3y ago
  The one feature that I'd want out of this is atomic writes. If I have a document and want to increment the value of a field in it by one, I'm not sure that's possible with Doculite today: if two requests read the same document at the same time and both write an incremented value, the value is incremented by one, not two. The way _I_ would expect to do this is something like this: const ref = db.collection('page').doc('foo'); do { const current = await ref.get(); try { await ref.set({ likes: current.likes + 1 }, { when: { likes: current.likes } }); } catch { continue; } } while (false); If `set()` attempts to write to the ref when the conditions in `when` are not matched exactly, the write should fail and you should have to try the operation again. In this example, the `set()` call increments the like value by one, but specifies that the write is only valid if `likes` is equal to the value that the client read. In the scenario I provided, one of the two concurrent requests would fail and retry the write (and succeed on the second go).
  • DANmode 3y ago
    I'd like to enable the same in my startup. What are you using for this today?
    • bastawhiz 3y ago
      If you're using something like MySQL or Postgres, you can do locking with the built in transaction primitives (see, for instance, SELECT FOR UPDATE). Mongo has tools like findAndModify which can help. If you're using SQLite you can use exclusive transactions to perform the read+write but I'm sure there's probably a more efficient way to go about it. You can craft an UPDATE that selects on the primary key and the condition and then use sqlite3_changes() to get back the number of records modified (and fail if it's zero), but that may not be possible with your setup.
  • thenorthbay 3y ago
    Interesting. Updating values via incrementing them is a use case I barely had in Firebase. I mostly only dealt with 1-time updates to values, e.g. by the user or scheduled jobs. In which scenario would the current design cause you problems?
    • winrid 3y ago
      Honestly, it's weird that you never ran into this. This is a requirement for any data store and I've never not used atomic updates at any company. Most basic example: what if two users load the same object at the same time and you want to increment a "seen" count...?
      • thenorthbay 3y ago
        I guess it was sufficient to not have that kind of accuracy in most of the application to deliver user value. There probably were parts of the application where we used transactions. Could be a cool feature to build for this, though.
    • bastawhiz 3y ago
• tracker1 3y ago
  Kind of nifty... Just curious if this is using the JSON functions/operators for SQLite under the covers? https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html Edit: where is the database file stored? A parameter for the Database() constructor seems obvious, but not seeing it in the basic sample.
  • thenorthbay 3y ago
    Yes – I'm using JSON_extract and generated virtual columns https://www.sqlite.org/json1.html#jex https://www.sqlite.org/json1.html#jex Edit: the database is stored in a sqlite.db file in the cwd
    • simonw 3y ago
      Found where you're using those: https://github.com/thenorthbay/doculite/blob/c05d98c209d003161ab00fbbdb6016d7f5b26f7c/src/database.ts#L184-L209 https://github.com/thenorthbay/doculite/blob/c05d98c209d0031... It looks like your tables have a single value column and a id generated column that extracts $.id from that value: CREATE TABLE IF NOT EXISTS ${collection} ( value TEXT, id TEXT GENERATED ALWAYS AS (json_extract(value, "$.id")) VIRTUAL NOT NULL ) GENERATED ALWAYS AS was added in a relatively recent SQLite version - 2020-01-22 (3.31.0) - do you have a feel for how likely it is for Node.js users to be stuck on an older version? I've had a lot of concern about Python users who are on a stale SQLite for my own projects.
      • thenorthbay 3y ago
        Yup! I actually don't know about that. I figured this would be used for setting up newer, server-side Remix or Next projects rather than more dated ones. I could imagine that by keeping sqlite and sqlite3 up to date, people might also not be stuck on more dated versions of SQLite. The tracing/profiling functions were also only implemented recently in sqlite3 (node to C interface), in 2023.
      • jitl 3y ago
        Often sqlite libraries just bundle SQLite instead of relying on the system one, better-sqlite3 does that just fine and has provisions for building against a custom SQLite version if you really need it.
• cyanydeez 3y ago
  I'd see if you can easily port the on top of browser based sqlite in wasm, that's expand your user base and lead to some of the "holy Grail" in the offline first/sync systems
  • __jonas 3y ago
    Why not just use IndexedDB in the browser if you don’t want an SQL database?
    • jitl 3y ago
      a) IndexedDB's API is horrid, so everyone wants to layer an abstraction on top anyways. b) You can't use IndexedDB on the server, so you wouldn't be able to write sync code that runs on both the client and the server
    • erikpukinskis 3y ago
      The same reasons you wouldn’t use IndexedDB on the server? Modern offline-first applications include a local backend every bit as complex and demanding as a server-based backend.
• actionfromafar 3y ago
  I read point 1 as against using SQLite at first. :-D
• zdragnar 3y ago
  Why do you have async reads and writes? There's no client-server setup here, using async / await just introduces pointless waiting. https://github.com/WiseLibs/better-sqlite3 https://github.com/WiseLibs/better-sqlite3
  • thenorthbay 3y ago
    If you use the library on a server in a node.js environment, wouldn't it be useful to fetch data (e.g. Remix / NextJS)? Besides, I'm not sure if better-sqlite3 offers the listener functionalities I care about. Skimming the docs, it seems it doesn't.
    • jitl 3y ago
      better-sqlite3 is orders of magnitude faster than the async SQLite bindings. We found this to be true when testing SQLite options for Notion's desktop app anyways. The "why should I use this" bits sound boastful but are reasonable. https://github.com/WiseLibs/better-sqlite3#why-should-i-use-this-instead-of-node-sqlite3 https://github.com/WiseLibs/better-sqlite3#why-should-i-use-...
      • deleted 3y ago
        [deleted]
    • zdragnar 3y ago
      Listener functionality could be something you'd have to write yourself, I suppose. As for on the server, no. Sqlite is a c library, not a separate application- the work happens inside the node process. Regardless of how you do it, any call into sqlite is going to block. Adding promises or callbacks on top of that is just wasting CPU cycles, unlike reading from the filesystem or making a network request, where the work is offloaded to a process outside of node (and hence why it makes sense to let node do other things instead of waiting). In fact, if you synchronously read and write within a single function with no awaits or timeouts in-between, you don't have to worry about atomicity- no other request is being handled in the meantime.
• thenorthbay 3y ago
  Here's the source: https://github.com/thenorthbay/doculite https://github.com/thenorthbay/doculite
• plaguuuuuu 3y ago
  This is cool. There are a tonne of options for local KV stores that likely outperform this by a large margin, but the obvious benefit here is that SQLite is really simple to configure and operate!
  • thenorthbay 3y ago
    Thank you!
  • kstrauser 3y ago
    I wouldn’t count on it. SQLite is unreasonably fast in ways you’d think it shouldn’t be.
• theogravity 3y ago
  I'm the core maintainer of the npm sqlite package that your library uses. I recommend that you don't make it a direct dependency, but use dependency injection or an adapter pattern that way your library isn't dependent on a specific package version of sqlite/sqlite3 (the npm sqlite package API is pretty static at this point though!). The npm sqlite package used to have sqlite3 as a direct dependency in older major versions and most of the support issues were against sqlite3 instead of sqlite. Taking that dependency out and having the user inject it into sqlite instead removed 99% of the support issues. It's also really nice that sqlite has no dependencies at all. If you go the adapter pattern route, you can support other sqlite libraries like better-sqlite3. Sometimes sqlite/sqlite3 doesn't fit a user's use-case and alternative libraries do. Same deal with the pub/sub mechanism. You have the Typescript types defined to create abstractions. Would be nice to see adapters for Redis streams / kafka / etc, as in-memory pub/sub may not cut it after a certain point. Great start on your library!
  • skinkestek 3y ago
    > I recommend that you don't make it a direct dependency, but use dependency injection or an adapter pattern that way your library isn't dependent on a specific package version of sqlite/sqlite3 Do you have examples of what you mean? Do you just mean in the code or is this something about how it is imported as a dependency in package.json?
    • theogravity 3y ago
      They would have to install sqlite and sqlite3 separately and feed the sqlite instance to your library when creating a new instance of your library. It would no longer be a dependency in package.json (maybe a devDependency when you write unit tests using it). Look at how the npm sqlite source code does it as an example. https://github.com/kriasoft/node-sqlite https://github.com/kriasoft/node-sqlite In the src/index.ts file, the `open()` function takes in an instance of sqlite3, vs the file importing it from the sqlite3 package itself. An adapter / driver pattern would extend this where you can interchange usage of either the sqlite package or an alternative. For example, let's say you have two SQLite drivers. They both allow you to execute a statement, but their methods and maybe parameters are named differently. One might expose a `exec()`, while the other exposes `run()` to do the same thing. Your codebase probably is coded for one or the other, which means you can't freely interchange the drivers. So what you do to support this is create a an interface with methods that describe what you want to do. In the above case, you might describe a method called `executeStatement()`. Then you create two classes, one for each driver. They both implement `executeStatement()`, but under the hood, they'll run `exec()` and `run()` in their implementations. So your main code now will accept an instance of anything that implements your interface, and instead of calling like `exec()`, you'll be calling `executeStatement()`, which under the hood calls `exec()`. So it goes like this: - Define common interface for interacting with different database libraries (the abstraction) - Implementation class using that interface (the drivers) - Your constructor takes in anything that conforms to that interface - The user creates an instance of your implementation (eg SqliteDriver) and feeds in the instance of the driver (eg `new SqliteDriver(<output of npm sqlite open()>)` - Your code calls the interface methods in place of the actual database calls instead You had something like: db.collection('users').set(..) So rather than calling SQLite `run()`, it'd be calling your interface method `executeStatment()` (which calls `run()`) instead in that `set()` call. It looks like the "Bridge" pattern is what you want here: https://www.phind.com/agent?cache=cll1zw1n60010la08mn89dd8g https://www.phind.com/agent?cache=cll1zw1n60010la08mn89dd8g The generated code is Java, but the idea and concept is the exact same that I've described above.
• jinjin2 3y ago
  > Alternative, proven Document Databases only offered client/server support. We currently use Realm for this use case. It’s a local database just like SQLite, but with native support for documents and listeners. Did you try that out?
  • thenorthbay 3y ago
    Just didn't find it quickly enough after some browsing. Also, using SQLite seemed so simple.
• avinassh 3y ago
  > Listeners and real-time updates enhance UX greatly. how is this implemented?
  • thenorthbay 3y ago
    I'm using SQLites underlying Data Change Notification functionality. It's exposed by one of the libraries mine relies on.
• qwerty456127 3y ago
  Thank you, this can be handy really. I would love to have compatible replicas (implementing both the same API and the same storage schema) for other languages like C# and Python.
• datastorydesign 3y ago
  That is an interesting approach. > 6) SQLite is a proven, stable, and well-liked standard. How does adding this adapter on top affect the stability? Have you looked at SurrealDB? - https://surrealdb.com/ https://surrealdb.com/ Seems like it would provide you with what you need.
• audioheavy 3y ago
  If you are serious about transactionality, data consistency, and isolation levels, this sounds different from how you want to go. Both Firestore and Realm have shortcomings here. Fauna (where I work at, btw) and Surreal with FoundationDB on the backend, and Spanner are the only ones that can guarantee strict serializability in a distributed environment. I could argue that Fauna is the most turnkey (least pain to try, test, implement). With those "strict serializable" db's it is much easier to avoid data anomalies, as the ones mentioned in this thread.
  • deleted 3y ago
    [deleted]
  • thenorthbay 3y ago
    Yup! I doubt this project will naturally evolve into whatever you're describing. And that's ok! This project might help people who want to use SQLite like Firebase, with a similar API and experience but without letting network requests increase latency (see: https://news.ycombinator.com/item?id=31318708 https://news.ycombinator.com/item?id=31318708). Addressing main points like implementing atomic transactions (read and write operation on a doc) seems warranted since it exists in Firebase as well.
• vicspace 3y ago
  I forgot to share this very cool alternative approach to realtime reactivity, via websockets, by subscribing to actual raw queries from the frontend! - https://github.com/Rolands-Laucis/Socio https://github.com/Rolands-Laucis/Socio - https://www.youtube.com/watch?v=5MxAg-h38VA&list=PLuzV40bvrSqhXZF6HtB1u0nR_lw2Rn-e6&index=1 https://www.youtube.com/watch?v=5MxAg-h38VA&list=PLuzV40bvrS... (video updates of the code and functionality)
• gloosx 3y ago
  >DocuLite lets you use SQLite like Firebase Firestore. Honestly, that sounds absolutely frightening to my ear. There are redis, there is mongo, orient, and plenty of document-based rapid-development databases which will be a substantially better solution than turning sqlite into Firebase. Yes, you can use data change notification callbacks. It is a cool feature of SQLite, but did you know that their performance is a big concern in a large-scale database (document database grows very quickly by design)? What about COUNTs? Batching operations? Deadlocks? This will go out of hand quickly, because a hammer is used as a shovel here.
  • thenorthbay 3y ago
    I just did a quick search and struggled to find anything about the performance issues you referred to - can you link something so I can take another look? Thanks for the suggestions.
    • gloosx 3y ago
      From the code, it s easy to see that you create a WRITE transaction which has the side effect of triggering READ transactions. It is also important to understand that if you mix reads and writes you cannot do it well concurrently with SQLite, so every transaction will be sequential and blocking. Looking at the code further I understand that it will not scale well, since a single write can possibly trigger a waterfall of callbacks creating bottlenecks. The problem in your case is that sqlite_update_hook doesn't transport back data and that it is listening for changes in all tables having ROWID optimisation on. So first things first you only need one such callback and a different approach for integrating it in a document abstraction than table name and rowid predicate in a dozen of registered callbacks. You will unveil more problems with SQLite for this job as you dig deeper. What you really want is fast writes and dumb querying by document id, whereas SQLite gives you an ultra querying suite that you don't utilize but still have to pay with slower writes. This is a classic system-design problem. Just try rewriting your collection and document abstractions using proper document data storage and you will see how less complicated it is.