[HN Gopher] Show HN: NoSQL, but it's SQLite
___________________________________________________________________
Show HN: NoSQL, but it's SQLite
Manipulate your SQLite database like a giant Javascript object.
Built with o1.
Author : vsroy
Score : 49 points
Date : 2024-12-22 10:20 UTC (12 hours ago)
(HTM) web link (gist.github.com)
(TXT) w3m dump (gist.github.com)
| revskill wrote:
| At work, we're using sql server, and i stored all json as base64
| string though.
| dontdoxxme wrote:
| Submitted it to The Daily WTF yet?
| revskill wrote:
| Why ??
| mcny wrote:
| How do you query json with SQL server like let's say you
| have one data point like this
|
| { "id": 42, "quantity": 12, bla bla bla
|
| And you want rows where this column has quantity and
| quantity >= 20
|
| How do you do it if you encode everything as base 64?
| isoprophlex wrote:
| You slap a full text index on the base64 string. There's
| only a finite number of base64 substrings for the un-
| encoded substrings "id", 42, etcetera, so you first
| filter on those. Then you decode those full strings into
| json and do the final filtering application side. Easy!
| 6510 wrote:
| <joking>have col names id, quantity, json and
| greaterthan20
| thih9 wrote:
| This is only a joke until a manager hears it. Then it's
| part of the Q1 roadmap and we will refactor it in Q3.
| eterm wrote:
| Others are being mean by not explaining the joke.
|
| Firstly, SQL server has a built-in JSON type, which lets
| you query and manipulate the JSON directly:
| https://learn.microsoft.com/en-us/sql/relational-
| databases/j...
|
| Secondly, JSON is already serialized, so it doesn't make
| sense to store as a base64 string. You're adding 30% data
| overhead to transform a string into a string. Base64 is
| useful for serializing opaque binary formats.
|
| Lastly, some people might be getting a wry smile that you
| have the power of a relational database but are just trying
| to store "json" rather than an actual relational model.
| szundi wrote:
| This needs some explanation
| revskill wrote:
| I'm still figuring out why i do this.
| p2detar wrote:
| Not sure what your exact use case is, I'm curious actually, but
| storing JSON strings should work much better. JSON functions
| are supported since SQL Server 2016 [0]. This is how I do it
| atm. I store only indexible content in table columns and
| everything else goes into an `attributes` JSON column. MSSQL
| supports indexes even on JSON fields, but I have not tried
| that, yet.
|
| 0 - https://learn.microsoft.com/en-us/sql/relational-
| databases/j...
| iLoveOncall wrote:
| The worst of both worlds, perfection.
| chx wrote:
| > Built with o1.
|
| Yes, yes, database with _AI written_ code. NoSQL with a database
| that can 't be trusted with your data? I. have. seen. this.
| before. To quote a classic:
|
| > I suggest you pipe your data to devnull it will be very fast
|
| In defense of the database that video was about, I worked as a
| software architect for the company which became the first
| commercial user of it, Eliot hilariously didn't want to accept
| money for support at first. Good old days. However, around 2015
| when all three large open source SQL databases --- SQLite,
| PostgreSQL, MySQL -- added JSON support I felt there was no more
| need for these NoSQL systems, though.
| b33f wrote:
| Couchbase mobile has been doing this for over a decade and early
| versions of membase 15 years ago were using a sqlite backend as a
| noSQL JSON datastore
| messe wrote:
| I'm using something like this for a small personal project
| that's only going to have a couple of users. Basically, just an
| app for myself and my girlfriend for all of the various
| restaurants, movies, recipes, tv shows, locations, etc. that we
| plan to go to/do at some point in the future. It's basically
| just a glorified todo list that uses APIs (TheMovieDataBase,
| OpenStreetMap, etc.) to grab additional metadata and images to
| present everything nicely
|
| I want us both to be able to make notes/add ratings to each
| item, so the set of tables looks like this: -
| TodoItems - Notes - Ratings
|
| Where every TodoItem can have multiple Ratings/Notes attached.
| Because each of the TodoItems is going to be of a different
| type with different metadata depending on the type of item
| (IMDB/TMDB id, image url, GPS location), and I want it to be
| extensible in future, its schema has ended up looking like
| this: CREATE TABLE TodoItems ( id
| INTEGER PRIMARY KEY NOT NULL, kind TEXT NOT NULL,
| metadata BLOB NOT NULL );
|
| With SQLite's json manipulation functions, it's actually pretty
| pleasant to work with. As it grows I might end up adding some
| indexes, but for now the performance seems like it will be fine
| for this very low traffic use case. And it makes deployment and
| backups incredibly simple.
| motorest wrote:
| Postgres added native support for JSON in 2012. People have
| been using RDBMS to store denormalized data and even as a key-
| value store for way longer than that. In fact, it's very hard
| not to do that
| richrichie wrote:
| Doesn't sqlite-utils does this and more, better?
| jazzyjackson wrote:
| Not a database, just a map of json strings where you can update
| the json stored at some key. You could write the same interface
| on top of localStorage.
| vsroy wrote:
| Yes that is exactly what it is! It's basically a very small
| upgrade over my favorite database: A JSON-file :)
| iKlsR wrote:
| Literally did something similar just last week, was looking
| for a good redis gui on windows and couldn't find one that
| clicked (closest I got was Another Redis Desktop Manager but
| it sorted my keys like 1, 11, 2 etc) so turned to sqlite and
| implemented this. Also added a "sync to disk" method so I get
| both the benefits of ram and persistence, worked out great
| since the data I'm getting over tcp has a sequence number so
| in case of any errors I resume from the last sequence number
| in the db. Thinking of fully committing and moving some stuff
| from the language like decoding the raw bytes to build a json
| object to an extension.
| eterm wrote:
| It's stored :memory:, there exists the same interface with:
| let x = {} x['foo'] = bar
|
| This is a parody because the implementation is hidden, and I'm
| not convinced the implementation isn't just newing an object.
| vsroy wrote:
| The implementation is very clearly included, if you... scrolled
| down.
|
| And the point is, you could easy do `const db = new
| Database("./database.sqlite")` instead.
|
| The wrapper makes it so manipulating your database is just like
| manipulating a plain Javascript object.
| eterm wrote:
| It's since been edited. At the time of my original post it
| was importing from a file not present on this page.
| keepamovin wrote:
| Beautiful. Please turn it into a repository. You wrangled that AI
| masterfully for this. Well done! :)
| stanac wrote:
| I did something similar with dotnet and linq. Idea was to create
| something like marten but for sqlite instead of postgres. Stopped
| working on it some time ago, the thing that was really slow was
| de/serialization, but with new source generators for json maybe
| it can be sped up.
| vsroy wrote:
| dang -- why was this flagged? Seems like a perfectly reasonable
| post.
| dang wrote:
| Users flagged it. We can only guess why users flag things, but
| there are usually clues in the comments. I've turned the flags
| off now.
|
| p.s. @dang doesn't work - you have to email hn@ycombinator.com
| if you want mostly-guaranteed message delivery.
___________________________________________________________________
(page generated 2024-12-22 23:01 UTC)