HTML/JS as interface to local SQLite database

Question

I'm writing a pretty simple database application, which I want to be locally stored (as opposed to looking up a remote database). I like HTML/Javascript for designing interfaces, and they're cross-platform (everybody has a browser!), so I'd really like to write a webpage as a frontend. No client/server interaction should be involved - I just want the users to be able to interact with the database using a browser, instead of a native program.

However, the only way I can see to access databases from a browser is using something like WebSQL or IndexedDB. Both of these, however, abstract away the process of managing the database file itself, and store it away in user settings somewhere. I want to distribute the database file itself along with the app.

In short: is there a way to use HTML/Javascript to modify a local SQLite database file? Or is HTML not the tool I should be using for this sort of application?

EDIT: possibly relevant

Polarimeter

Javascript has no way to write to a local file. You could ship a js file to store values the first time however. Have you considered Adobe AIR on which you can develop using HTML/JS and have access to the filesystem? – Hardan

Haven't had much to do with AIR before, I will look it up. I want to reduce dependencies as much as possible, but don't think I'll be able to do this with pure HTML/JS. – Polarimeter

Answer 1

This is what I've ended up doing:

As referred to here, you can use Python to create a local web server. This tutorial gives a basic infrastructure for the server handler. I had to deal with some issues, possibly caused by Python 3 or by using Chrome to access my local page.

My GET handler function ended up looking like this:

def do_GET(self):
    try:
        self.send_response(200)
        self.send_header('Content-type', 'text/html')
        self.send_header('Access-Control-Allow-Origin', '*')
        self.end_headers()
        try:
            fn = GETHANDLERS[self.path[1:]]
            self.wfile.write(fn().encode("utf-8"))
        except KeyError:
            self.wfile.write(self.path.encode("utf-8"))
        return
    except:
        self.send_error(404, 'File Not Found: {0}'.format(self.path))

Where GETHANDLERS is a dictionary mapping URLs to functions - for example, if you visit http://localhost/my_func, the GETHANDLERS['my_func'] function is called. If no function exists for the URL, the URL is just echoed back.

I've implemented functions that manipulate a local SQLite database with the sqlite3 module. Here's an example of the query to list everything in the Categories table:

import sqlite3

def get_categories():
    con = sqlite3.connect('my.db')
    c = con.cursor()
    c.execute('''SELECT * FROM Categories;''')
    return [cat[0] for cat in c.fetchall()]

GETHANDLERS["categories"] = get_categories

To use the local app, it's necessary for the user to install Python, then run the server script before opening the webpage. I think this is pretty acceptable!

Polarimeter

Answer 2

If you really want just a client side application, you should really consider using HTML5 tehnologies(WebSQL, Indexed DB).A good reading is here: html5 doctor. You will need to export the database somewhere, or let the user export the database and when the user reloads the page the database should reconnect. example: export websql to a csv-file.

Rondure

I found lots of documentation on how to export a database, but is there a good way to import from CSV? Then, for example, the first time the app loads I could import all the data from local CSV into the browser's database. – Polarimeter

hmm you could try using this parser purbayubudi.wordpress.com/2008/11/09/… , but i would recommend exporting and importing your database using xml because is simpler w3schools.com/ajax/ajax_xmlfile.asp – Rondure

Answer 3

You could use the sqlite3 connector and connect to a local database file from a HTA Application.

Here is the connector (one has to register the dll as described on the page)

https://www.assembla.com/spaces/litex/documents

Check the HTA Demo App in the zip file (DLL is in \litex\bin folder, hta is in the script folder..)

Hoon