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.

 

[PeterDonis]

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

The "SQLite" you download is a standalone program that runs on your local machine. It has no "comms" with anything.

it gives me a prompt to create and edit tables, which - I assume - end up in a stand-alone file or files

Yes, an sqlite database file on your local machine. You would then have to upload it to your client's web host and tell the web host's server software (PHP with its SQLite support, I assume) where to find it. Then the web host's server software would be running code to access that file. The "SQLite" program on your local machine would be out of the picture entirely.

But there are innumerable possible solutions.

Not really. At any rate, it seems to me that you have enough information from this thread to try the one I described locally. I have used this method many times. It really helps me to understand what all the pieces are doing and how they have to fit together.

 

[PeterDonis]

along with code to handle CRUD that my JavaScript hooks into

This part I'm not familiar with. What kind of code does this SQLite program give you? Can you give an example? And an example of the Javascript that hooks into it?

 

[DaveC426913]

This part I'm not familiar with. What kind of code does this SQLite program give you? Can you give an example? And an example of the Javascript that hooks into it?

I can't. I'm not there yet.
I'm still at the 30,000 foot level of assessing if this is the tool to do what I want. And that's why I'm here asking those who know more.

And yes. I might have this all wrong.

 

[PeterDonis]

I'm still at the 30,000 foot level of assessing if this is the tool to do what I want. And that's why I'm here asking those who know more.

Well, you know more than I do about the "code to handle CRUD" that the SQLite tool you describe gives you. Even if you can't give a specific example for this application, some general information would help. Does it give you SQL statements? C code? Javascript? Something else?

 

[PeterDonis]

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

Which specific SQLite IDE are you using? There seem to be a number of them available.

 

[DaveC426913]

Yes, an sqlite database file on your local machine. You would then have to upload it to your client's web host

Yes of course.

and tell the web host's server software (PHP with its SQLite support, I assume) where to find it.

Ok so this sounds like SQLite is ONLY a database, and does not provide any inherent server-side layer that would normally be implemented in PHP for example.

So I'm still stuck having to deal with the headache of a dependency on the web host particulars. Is it activated, is it the right version, will it rot, etc.

Dang.

 

[pbuk]

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.

You want something to run on a web host that is not dependent on the web host? This is an impossible dream. But dependency on the client's web host is actually a good thing because it means that they are responsible for all of the boring bits of system admin that you don't want to worry about. All you need to do is make sure that you only depend on what every web host provides i.e. a current version of PHP (which includes a sqlite driver).

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

You seem to be thinking that you can access files or processes on the server as a local Linux (or Windows) user from a remote Javascript client. You can't, the client can only access scripts installed on the server via the web server which will always run as the same user (often www-data). You need to manage permissions via the server script (or possibly using HTTP basic authentication, but this would be clunky). This could be by checking for a "secret" key in a JavaScript Fetch request header (for standardization use an OAuth Bearer token https://www.oauth.com/oauth2-servers/differences-between-oauth-1-2/bearer-tokens/), although this is not a very secure solution.

My own web portal is built in hand-rolled PHP and mySQL db. I just hate it.

There could be many reasons for this: I'm not sure that "PHP and MySQL are inherently horrible" is one of them.

 

[pbuk]

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

This is all correct, but

- along with code to handle CRUD that my JavaScript hooks into

this is not correct. All you can do with a web browser is send HTTP requests to a server. You have to write the CRUD code (as well as code to authenticate the request) in a language that is available in the web server environment. Almost all web hosts provide PHP which has a driver to access the sqlite database file you have uploaded built in (since some version that was EOL years ago). Some web hosts also enable persistent Python and/or Node JS processes so you can use a framework like Django or Express if you really don't like PHP.

 

[jack action]

I'm not sure I'm following the actual problem.

You have a "database" already working in JSON. All you need to do is rewrite that file to update it.

It seemed you did not want a server - doing everything in Javascript - so I thought everything was done on a local machine, within a browser. This is why I gave the solution in post #20.

Now it seems you have a central server. I'm assuming the client's web browsers are fetching your JSON database every time they make a request to the website.

If that is the case, your server must have some kind of CGI (it may be PHP, but it could be Perl, Python, etc.). All you have to do is to create that JS file that can define the values to update the JSON "database" and send the modified values with a form to your server, more precisely your CGI script. That script should update the "database" by rewriting the JSON file. Some kind of URL renaming for the JSON file should be done such that anyone requesting it gets a fresh copy (something like /database.json?version=1.2). Such a script in PHP could be:

$JSONdb = file_get_contents('database.json');

$db = json_decode($JSONdb);

$newValues = $_POST['new-values']; // This should be sanitized

/* manipulate the $db object (or array) as you wish to update it with $newValues */

$updatedJSONdb = json_encode($db);

file_put_contents('database.json', $updatedJSONdb);

 

[pbuk]

It seemed you did not want a server - doing everything in Javascript - so I thought everything was done on a local machine, within a browser. This is why I gave the solution in post #20.

Indeed: I had already suggested this in #5.

Now it seems you have a central server. I'm assuming the client's web browsers are fetching your JSON database every time they make a request to the website.

No, the OP seems to have decided that sqlite (which implements the essential parts of SQL without requiring a daemon to be installed on the server, which the web host would almost certainly not allow) is the way to go.

If that is the case, your server must have some kind of CGI (it may be PHP, but it could be Perl, Python, etc.). All you have to do is to create that JS file that can define the values to update the JSON "database" and send the modified values with a form to your server

How very Web 1.0 . In 2023 we would do this with a JSON body rather than x-www-form-urlencoded.

Some kind of URL renaming for the JSON file should be done such that anyone requesting it gets a fresh copy (something like /database.json?version=1.2).

No need (and how would each client know which version to ask for?), just set headers in the response to prevent browser caching.

 

[PeterDonis]

this sounds like SQLite is ONLY a database, and does not provide any inherent server-side layer that would normally be implemented in PHP for example.

The SQLite project itself would not, but the PHP project might. The SQLite project just provides the library written in C and with a C interface. (The sqlite Python bindings, for example, are maintained by the Python project, not the SQLite project.)

 

[PeterDonis]

I'm still stuck having to deal with the headache of a dependency on the web host particulars.

This is going to be true for anything you run on a web host. That's inherent in how web hosts work. Don't you already have the same issue with the web host code you are running that displays things geographically for the client?

SQLite will be easier than MySQL because you won't have to also manage a MySQL database in addition to your web host software. An SQLite database has no "management"; it's just a single file.

 

[DaveC426913]

You want something to run on a web host that is not dependent on the web host? This is an impossible dream.

Taking that at face-value, I disagree. Plenty of websites are built - with quite complex content, graphics and interactivity that the web host has no involvement in except as a vessel.

So let's agree that it's a sliding scale of dependency - say flat HTML near one end, and PHP/mySQL near the other. I'm just looking for something in the middle.

But dependency on the client's web host is actually a good thing because it means that they are responsible for all of the boring bits of system admin that you don't want to worry about.

I am responsible for setting all that up. And that get a lot headachier when it's not my web host but my client's.

You seem to be thinking that you can access files or processes on the server as a local Linux (or Windows) user from a remote Javascript client.

No I do not.

I seem to be thinking that [SQLite] is an alternate equivalent to [database and the server-side code (eg. PHP) that accesses it]. i.e. that SQLite would allow me to write database access functions that were stored in files alongside the flat database files and alongside my HTML, CSS and JavaScript code on my web host.

(Afterall, PHP is likewise server-side code that sits in files alongside my HTML, CSS and JavaScript. It's just that PHP is - er - curated by the webhost.)

And I am getting the message that I am wrong about what SQLite can do for me.

 

[PeterDonis]

I am getting the message that I am wrong about what SQLite can do for me.

I think that is correct. More precisely, I think there are two issues here:

(1) The functions would have to be written in C, compiled and linked against the SQLite library, and the executable would have to live somewhere on the server. SQLite, as far as I know, does not give you an executable that can run arbitrary external functions just by itself.

(2) SQLite does not listen on any port on the server and thus has no way of responding to HTTP requests directly, so something that does (say an Apache webserver, or whatever server the web host is using) would have to be able to talk to whatever C program was compiled and linked against the SQLite library. (CGI, mentioned earlier in the thread, is one way to do this, but it's no less brittle than, say, PHP--you're just at the mercy of the web server's CGI implementation being "upgraded" instead of the PHP version being "upgraded".)

It's not impossible to set things up that way. But I don't think it's any less complex (or risky) than just using PHP. I would say it's more so.

 

[DrJohn]

If a webhost includes PHP, then that will come with its own functionss to interact with SQLite. Including the create database function. If you can run PHP, there is no need to install SQLite - I tested this about ten years ago, just to be sure, and nothing has changed in the later versions of PHP. If you have a website and can write the simplest php file
<?php
phpinfo()
?> ,
save it as info.php, upload it then call it in your browser, you will be able to check that the SQLite functions/option are there.
Whenever I've checked other configuration details of PHP, I have always seen that the PHP SQLite was enabled. If it was listed as disabled on a webhost, you would of course ask support to enable.

There are also free db tools that will look at an SQLite db and show the tables, and contents so you can check things are going as you hoped if making a desktop db. A quick google lead me to DB Browser SQLite.

A lot of this thread is talking as if this is an unknown tricky to use database that you'd struggle with. It is not. Even the PHP connection to it is easy than to MySQL. That's why I posted about it earlier and about the self-contained software package that was created specifically to make desktop app of the type being talked about! You can use it with XAMPP and USBWebserver and the like, but they require a few extra clicks to start and stop the built in server.

Perhaps it is people reading the SQLite website and imagining that they have multiple steps to work through to get it running in its most basic way in a C / C++ app to be able to do anything. That's aimed at developers producing a complex bit of software. Just use PHP with all its build in functions for SQLite !