Simpler alternatives to SQL-like databases

[DaveC426913]

TL;DR Summary

Is there a middle-ground between a local read-only data file (such as a JSON object) and a full-blown SQL-like database? Specifically, something can can be edited?

Over in this thread I've outlined a one-page web app that uses a tiny data store composed of a couple of JSON objects. There's only 100 or so rows of data, but it is updated regularly. Currently, I rely on the client to let me know when there are changes and I update the .js file manually and upload it. I want to give the client the ability to make routine data changes at-will (Create, Read, Update, Delete).

The app is for internal use - it is not exposed to the public. Really, it serves no function that couldn't be accomplished by a simple document or spreadsheet - except to visualize the data geographically. (In fact, the data I use to update it is currently updated by the client in a Google doc, but it's not sufficiently well-defined to use as a data source, using area descriptions like "everything west of..." or "... and surrounding area"). Once the data is dynamic, I can create a editing page that will allow (i.e. force) the client to explicitly choose regions from a list/map.

I do not want to go with a full-flown SQL database for a couple of reasons associated with portability and upkeep. I don't want to be messing around with the client's webhost features and settings like I would have to do to set up a database.

Is there no middle-ground between a read-only, local data file and a full-blown database?

I thought NoSQL sounded like a solution, but it isn't what I thought.

As for security, right now I simply have a webhost-based pass-wall on the whole folder, so the site can't even be seen - let alone edited - without permissions. The only private data is workers' names - there's no other meaningful personal data in this app.

Here's the entire schema, currently stored in JSON objects in a .js file in a subfolder:

 

[PeterDonis]

Is there a middle-ground between a local read-only data file (such as a JSON object) and a full-blown SQL-like database? Specifically, something can can be edited?

The only reason the JSON can't be edited is that you're using Javascript in a browser (at least, it seems like you are). Yes, if you are limited to Javascript in a browser, you can't arbitrarily save JSON objects to disk. (You could, however, use browser local storage, which includes a key-value store, though IIRC the allowed value types might be more limited than the full JSON spec, and I don't know if any JS library has convenience functions that package up the workflow "save this JSON object to local storage -> retrieve this JSON object from local storage".)

In a standalone program in any language that supports JSON (the one I've used the most for this is Python), you can save JSON to disk by serializing it to bytes, just like anything else that can be serialized to bytes. Then you can read it back by deserializing the bytes from disk. (Under the hood your browser is serializing JSON to bytes when it sends it over the network as a request payload, and deserializing it from bytes when it receives a JSON response to a request.)

 

[jedishrfu]

One could use CSV|TSV files updatable by a spreadsheet program and easily read by a program into one or more dictionary objects / associative arrays ie key-value lookup tables for quick access.

This scheme should work well up to about 10,000 rows (a few years ago) and more now.

I think this would work better than the JSON unless you need multi-level structured data storage.

Alternatively, you could use the H2 Database application. It's a single java jar file that supports most SQL commands. It can be embedded in your application if it's a Java app, or you can access it thru the JDBC API.

https://h2database.com/html/main.html

I've used H2 to great success in a project in which my server code embedded the H2 engine (gets 6x speedup over using JDBC to access the database). Java client apps could use JDBC to query the data.

I created the schema tables and all necessary indices in my server app and managed record expirations via a background thread. I instead used other messaging protocols (ZMQ) so my java clients could ask the server to run a query instead of using JDBC.

The primary reason for using the messaging protocols over JDBC was so that the schema knowledge resided in my server app only and was not spread throughout the codebase in other classes. It allowed me to change table structures, indices, and how queries were done...

 

[FactChecker]

One of your first questions should be whether multiple clients can modify the database simultaneously without getting partial updates and conflicts. Suppose one client opens the database and goes to lunch (or home for the night) without saving and closing it. Would other clients be able to use the database? Would their changes be eliminated when the first client eventually saved and closed his session?
This is not a problem when you are the central coordinator who makes the changes by hand.

 

[pbuk]

There are three ways to get data in and out of an application running in a browser:

1. By making requests to a web resource
   The browser makes a GET request to retrieve data and POST (or PUT or DELETE requests) to save data. The server behind the web resource can be (and usually is) a complicated application including business logic with authentication and authorisation layers, but it could be as simple as an Amazon S3 resource with an API key hard-coded into the application.
2. By saving it in local storage as described in #2
   

   You could, however, use browser local storage, which includes a key-value store

   Noting:
   

   though IIRC the allowed value types might be more limited than the full JSON spec, and

   Localstorage only supports strings, but anything else can be converted to and from a string with JSON.stringify and JSON.parse.
   

   I don't know if any JS library has convenience functions that package up the workflow "save this JSON object to local storage -> retrieve this JSON object from local storage".

   Not necessary:
   
   const saveToLocalStorage = (key, value) => {
     const json = JSON.stringify(value);
     localStorage.setItem(key, json);
   };
   const retrieveFromLocalStorage = (key) => {
     const json = localStorage.getItem(key);
     return JSON.parse(json);
   };
3. By loading/saving it to a local file using the browser's File API
   This is a little more complicated than using localstorage; the File API is the mechanism used to upload and download files from a server but it can also be used to create a file object in javascript and allow a user to "download" it, and also to read the contents of a file "uploaded" by the user without sending it anywhere. For more information see https://developer.mozilla.org/en-US/docs/Web/API/File_API.

 

[Vanadium 50]

Isn't step one to figure out if the schema is right, and then figure out what supports what's needed? It looks like it will be doing JOINs left, right and center, which is at odds with "simple".

 

[jack action]

By saving it in local storage as described in #2

Although I don't think the OP wants to store the data in the browser, if we are talking about local storage, maybe mentioning IndexedDB is also in order. Even if I never used it, JsStore seems to be a nice SQL-like wrapper for IndexedDB.

 

[pbuk]

Isn't step one to figure out if the schema is right, and then figure out what supports what's needed? It looks like it will be doing JOINs left, right and center, which is at odds with "simple".

When we are looking at a small amount of data, as we are here, you don't need normalisation, you can just store everything in one large object held in memory.

Although I don't think the OP wants to store the data in the browser, if we are talking about local storage, maybe mentioning IndexedDB is also in order. Even if I never used it, JsStore seems to be a nice SQL-like wrapper for IndexedDB.

IndexedDB is a strange beast; I've never found a use case for it. Either you need local persistance of a small amount of fairly simply structured private data, in which case localStorage is fine, or you have a large amount of and/or complex and/or shared data, in which case you need a SQL or NoSQL back end.

 

[pbuk]

Here's the entire schema, currently stored in JSON objects in a .js file in a subfolder:

View attachment 323175

That's insane. If you are storing javascript objects you don't need a "table" for many-to-many joins, and you certainly don't want it to look like that in memory. And creating arbitrary unique IDs for things is daft.

Here is a better suggestion:

{
  meta: {
    lastUpdate: 1677950628000,
    lastSave: 1677950491000,
    // ...
  },
  workers: {
    '0': {
      id: 0,
      lastName: 'Cooper',
      firstName: 'Brianna',
      type: 'pnsw',
      level: 'Regular',
      isEducator: true,
      regions: ['algo', 'bran', 'region11'],
    },
    // ...
  },
  regions: {
    algo: {
      id: 'algo',
      x: 795,
      y: 632,
      label: 'Algoma',
    },
    // ...
  },
  types: {
    pnsw: {
      id: 'pnsw',
      name: 'PNSW',
      plural: 'PNSWs',
      color: 'red',
      // ^^^ Note I wouldn't store this in the data at all - this is UI, not
      // data.  Much better to use CSS classes e.g. <div class="pnsw-data"> and
      // set the actual colour (or colours) in a stylesheet.
    },
    // ...
  },
}

 

[DrJohn]

You could try the most widely used database of them all - sqlite. It's used everywhere.
A single file database, like your favourites in browsers world wide and many apps on your phone - which every phone maker you use. Commisioned for the US Navy 20+ years ago and still with a support team backing it.
https://www.sqlite.org/index.html
And if you know how to create a website that is powered by PHP and MySQL, then consider making it with PHP and SQLite. Then you can add data, query data etc. The full CRUD.

And the final step for a desktop app? PHPDesktop. https://github.com/cztomczak/phpdesktop
It comes with a built in webserver, browser, PHP and you can rename the executable to match your app name. Windows and Apple versions, just install and create.
I'm playing with it a little bit just now. It's not like XAMP or USBWebserver, where you first have to start the webserver and it's asssociated parts, you just start the one executable. The demo is rather simple, but you can edit things for practice then start building your app.

 

[DaveC426913]

Isn't step one to figure out if the schema is right, and then figure out what supports what's needed? It looks like it will be doing JOINs left, right and center, which is at odds with "simple".

That's insane. If you are storing javascript objects you don't need a "table" for many-to-many joins, and you certainly don't want it to look like that in memory. And creating arbitrary unique IDs for things is daft.

Sorry. Y'all caught me in a flub. The schema is something I designed to show a friend as a relational database.

The actual JSON is like this:

var workers = [
  ["Alice Smith",            "red",    "",        ["peel","halt","burl","york","toro","durh"]],
  ["Andrea Joh",            "red",    "",        ["toro","peel","halt","york"]]
]

 

[DaveC426913]

Ah, y'all have given me a lot to chew on. I didn't realize (or quite possibly have simply forgotten) there were so many options. I will take time to process.

 

[Greg Bernhardt]

What about https://redis.com/ or NoSQL like https://www.mongodb.com/?

 

[DaveC426913]

What about https://redis.com/ or NoSQL like https://www.mongodb.com/?

I'd still have to install/activate those on the clients' servers, right?