Simpler alternatives to SQL-like databases

[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 !

 

[sophiecentaur]

Are we risking cracking a small nut with a sledgehammer here? With so little data, why not use Google Sheetsl (or equivalent)? It's usable by a novice or several novices. It also makes sense and you can read the data on the sheet without knowing how to drive a particular language.