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?

 

[DaveC426913]

OK, a colleague has recommended SQLite. It seems to be what I'm looking for, but I have a few specific questions.

1. I have to download and install it. Presumably, that is just for the sake of developing in it. I hope that all I'm doing to deploy it is upload a bunch of files in my webhost, yes? i.e. no web host configuration such as install server-side apps and db activation like I would have to do for a full db-based app.
2. I get the feeling that, even though the db is a flat file(s), it's not text-editable, yes? During dev I would edit it in immediate mode via the developer interface.
3. My web app needs to have a data control layer. I hope that layer can be in client-side JavaScript? Or do I still need a server-side script like PHP?

My goal is to have:

• two pages: a data visualization page and a data edit page.
• possibly under two different logins: eg: user and admin.
• eventual portability to client's site, presumably all I have to do is upload the files to their webhost and set up login permissions there. Other than those permissions, I hope there's no configuration that needs to be done on the client's web host.

 

[jack action]

OK, a colleague has recommended SQLite. It seems to be what I'm looking for, but I have a few specific questions.

1. I have to download and install it. Presumably, that is just for the sake of developing in it. I hope that all I'm doing to deploy it is upload a bunch of files in my webhost, yes? i.e. no web host configuration such as install server-side apps and db activation like I would have to do for a full db-based app.
2. I get the feeling that, even though the db is a flat file(s), it's not text-editable, yes? During dev I would edit it in immediate mode via the developer interface.
3. My web app needs to have a data control layer. I hope that layer can be in client-side JavaScript? Or do I still need a server-side script like PHP?

My goal is to have:

• two pages: a data visualization page and a data edit page.
• possibly under two different logins: eg: user and admin.
• eventual portability to client's site, presumably all I have to do is upload the files to their webhost and set up login permissions there. Other than those permissions, I hope there's no configuration that needs to be done on the client's web host.

I did not know about SQLite and just found out that it is already on my computer (Ubuntu). Went to their website and I gave it a go. Seems very practical and I think you may like it for your user case as well since there are no logins or user access to manage.

Some quotes from their website that might be of interest to you:

Because it is simple to setup and use (installation is trivial: just copy the sqlite3 or sqlite3.exe executable to the target machine and run it)

Because an SQLite database is a single compact file in a well-defined cross-platform format, it is often used as a container for transferring content from one system to another. The sender gathers content into an SQLite database file, transfers that one file to the receiver, then the receiver uses SQL to extract the content as needed.

People who understand SQL can employ the sqlite3 command-line shell (or various third-party SQLite access programs) to analyze large datasets. Raw data can be imported from CSV files, then that data can be sliced and diced to generate a myriad of summary reports. More complex analysis can be done using simple scripts written in Tcl or Python (both of which come with SQLite built-in) or in R or other languages using readily available adaptors. Possible uses include website log analysis, sports statistics analysis, compilation of programming metrics, and analysis of experimental results. Many bioinformatics researchers use SQLite in this way.

The same thing can be done with an enterprise client/server database, of course. The advantage of SQLite is that it is easier to install and use and the resulting database is a single file that can be written to a USB memory stick or emailed to a colleague.

And the database file is not human-readable, just like MySQL.

Anyway, if you don't use it, I'm glad you made me discover it.

 

[pbuk]

Have you investigated what is already available (with support, maintenance and backup) from your client's web host? Many hosts provide MySQL as part of the basic package they are already paying for. This would be much easier than installing, supporting, maintaining and backing up sqlite yourself, even if that is possible.

• My web app needs to have a data control layer. I hope that layer can be in client-side JavaScript? Or do I still need a server-side script like PHP?

JavaScript running on your client can only send HTTP requests to the server, how is it going to access the data? Yes you do need a server-side script or application.

My goal is to have:

• possibly under two different logins: eg: user and admin.

How are you going to manage this login process? You need a server side script or application.

What you are planning to do is called Full Stack Development, you have a lot of learning to do.

 

[PeterDonis]

I hope that all I'm doing to deploy it is upload a bunch of files in my webhost, yes?

It depends on what language you are using on your server and whether it has built-in SQLite support. The actual database file itself is just a single file, yes, but the code that accesses it on the server is something else again.

 

[PeterDonis]

Some quotes from their website

Those quotes are not talking about web applications. They're talking about people hand-editing SQLite database files and passing them around. Which can be a useful thing to do, but it's not what the OP is asking about.

 

[jack action]

Going back to the OP, what's wrong with something like this:

var db = { hello: "world" };

var a = document.createElement("a");
a.href = window.URL.createObjectURL(new Blob([JSON.stringify(db, null, 2)], {
  type: "application/json",
}));
a.download = "database.json";
a.click();

The only drawback is that the user must know exactly where to save it to replace the original content, but that is inevitable with Javascript. It seems to be a minor problem if it comes with good instructions prior to saving. Especially if one doesn't want to use multiple programs.

 

[Vanadium 50]

OK, a colleague has recommended SQLite

What?

What?

SQLlite is a library that you wriite code and then compile and link against. It is not less complex than native SQ:L - if anything, it;s worse. It does come with a SQL command line interface, sqllite, but that's no easier to use than any other SQL command line. (It is slower and more memory hungry)

Honestly, I think you are trying to use a blunderbuss to kill a gnat, and have not really defined what you need. I suspect that you can spend hundreds of hours tryingh to automate a one hour job.

 

[PeterDonis]

SQLlite is a library that you wriite code and then compile and link against.

For languages that require that, yes. But in a web application context, PHP, for example, has SQLite support, you just need to tell it where the SQLite database file is. Yes, you'll still have to write code to query and update the database, but I think that will be true no matter what solution the OP adopts.

 

[DaveC426913]

Have you investigated what is already available (with support, maintenance and backup) from your client's web host? Many hosts provide MySQL as part of the basic package they are already paying for.

Dependency on my (or any) particular webhost's offerings is exactly what I am hoping to avoid. I want my app encapsulated and portable so it won't matter where it ends up. Ideally, it'll end up on my client's website, and I don't want to be responsible for collaborating with their hosts to set it up.

This would be much easier than installing, supporting, maintaining and backing up sqlite yourself, even if that is possible.

If I understand correctly, I have to do none of those things.

JavaScript running on your client can only send HTTP requests to the server, how is it going to access the data? Yes you do need a server-side script or application.

Right, yeah.

How are you going to manage this login process? You need a server side script or application.

Currently, that's entirely folder-based permissions - which is simple enough that every webhost has it and can be trivially configured.

What you are planning to do is called Full Stack Development, you have a lot of learning to do.

I've done a fair bit of Full Stack Dev, despite having been trying to avoid it for most of my career.

My own web portal is built in hand-rolled PHP and mySQL db. I just hate it. Also I think it's overkill for this application.

 

[PeterDonis]

If I understand correctly, I have to do none of those things.

You might have to install SQLite support for whatever language you are using for the server (PHP, for example), if your client's web host doesn't already have it.

You will also either have to have some kind of backup scheme in place for the SQLite database file, or make sure the client does.

 

[DaveC426913]

It depends on what language you are using on your server and whether it has built-in SQLite support. The actual database file itself is just a single file, yes, but the code that accesses it on the server is something else again.

So this raises some questions.
I am thinking that the engine is its own file (or files), right there in a folder, in a similar manner to a plugin or code library.
The CRUD (Create, Read, Edit, Delete) code for this, obviously, I'm still writing myself. that's what I will be doing in the SQLite developer interface.

Are you saying that SQLite requires integration on the server, like I have to do to wire a PHP application up a mySQL db?

 

[PeterDonis]

I am thinking that the engine is its own file (or files), right there in a folder, in a similar manner to a plugin or code library.

No, it's not. The library might be, but unless you are writing C code and compiling and linking it against the library directly (which is what @Vanadium 50 was describing in an earlier post), the library by itself does not help you.

The language bindings to the library for whatever language you are using are what you will need to find out about. For Python, the sqlite module in the standard library is always there. But I don't know if the corresponding code for PHP is always there, or if you need to install a plugin or something, or if it depends on the web host.

Are you saying that SQLite requires integration on the server, like I have to do to wire a PHP application up a mySQL db?

It shouldn't require as much integration because you don't have to "manage" the database; the database itself is just a single file on your server's disk somewhere. But you will still need to tell PHP where the file is; I would assume there is a way to do that with PHP's SQLite support using a config file or something similar.

 

[DaveC426913]

SQLlite is a library that you wriite code and then compile and link against. It is not less complex than native SQ:L - if anything, it;s worse. It does come with a SQL command line interface, sqllite, but that's no easier to use than any other SQL command line. (It is slower and more memory hungry)

Yes, naturally, I will be writing the code that my JavaScript calls to read and write data.

For clarity:

What I am trying to avoid is strong hooks to the web host services that need configuring.

In the past, when I have built apps that use PHP to access a mySQL database, they have tended to rot. I move webhosts, or my webhost upgrades its PHP version, or I want to hand the whole app over to someone else, and they stop working and I don't troubleshoot them - because the configuration is labyrinthine or the web hosts are lousy at their tech support or whatever.

(If I wanted to be an IT Systems Integration Specialist; I'd be a IT Systems Integration Specialist. What I want to be is a developer. )

 

[PeterDonis]

What I am trying to avoid is strong hooks to the web host services that need configuring.

Unfortunately, while SQLite might reduce that risk for the database side of things (since its configuration is much simpler than, say, MySQL), I don't think it will help you for the PHP side of things. My experience has been that PHP version upgrades are a huge source of bit rot even if no database is involved at all.

 

[DaveC426913]

Unfortunately, while SQLite might reduce that risk for the database side of things (since its configuration is much simpler than, say, MySQL), I don't think it will help you for the PHP side of things. My experience has been that PHP version upgrades are a huge source of bit rot even if no database is involved at all.

Sure. But are you saying I will still need PHP between my SQLite db and my client-side JavaScript?

I really hoped the SQLite IDE would let me write that middle bit into files that would live in a folder on my web host (which, I guess, is what PHP does anyway). But I assumed that the SQLite component would be self-contained (analogous to a framework/plug-in/library) and thus not subject to rot.

 

[PeterDonis]

are you saying I will still need PHP between my SQLite db and my client-side JavaScript?

What are you going to run on your server? Your client-side Javascript is talking to a server, right?

 

[PeterDonis]

the SQLite IDE

What "IDE" are you talking about?

 

[DaveC426913]

What are you going to run on your server? Your client-side Javascript is talking to a server, right?

I'd hoped the SQLite would take up the server-side comms. Isn't that the point of it?

 

[PeterDonis]

I'd hoped the SQLite would take up the server-side comms.

What "server-side comms" are you talking about? What "SQLite" program are you talking about?

It seems to me that you might benefit from actually trying to set up this kind of configuration so you can see for yourself what each of the pieces can and cannot do. Try setting it all up on your local machine, running whatever server you expect to run on the client's web host and surfing to it at a localhost address. Then add the "SQLIte" piece and see what you can and can't make it do.

 

[DaveC426913]

What "IDE" are you talking about?

The part where I download and install SQLite in my development environment.

Among other things, it gives me a prompt to create and edit tables, which - I assume - end up in a stand-alone file or files - along with code to handle CRUD that my JavaScript hooks into - that I upload alongside my web page.

 

[DaveC426913]

What "server-side comms" are you talking about? What "SQLite" program are you talking about?

It seems to me that you might benefit from actually trying to set up this kind of configuration so you can see for yourself what each of the pieces can and cannot do. Try setting it all up on your local machine, running whatever server you expect to run on the client's web host and surfing to it at a localhost address. Then add the "SQLIte" piece and see what you can and can't make it do.

Yes. But there are innumerable possible solutions. I can't try them all, so I'm asking questions of people who can guide me toward or way from appropriate ones.