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

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? — Florian Margaine, May 06 '12 at 11:49
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. — Daniel Buckmaster, May 06 '12 at 13:30

score 4 · Accepted Answer · edited May 23 '17 at 12:30

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!

score 3 · Answer 2 · edited May 23 '17 at 12:30

3

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.

edited May 23 '17 at 12:30

Community

1
1

answered May 06 '12 at 12:00

Placinta Salaru Alin

88
1
8

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. – Daniel Buckmaster May 06 '12 at 13:29
hmm you could try using this parser http://purbayubudi.wordpress.com/2008/11/09/csv-parser-using-javascript/ , but i would recommend exporting and importing your database using xml because is simpler http://www.w3schools.com/ajax/ajax_xmlfile.asp – Placinta Salaru Alin May 06 '12 at 21:14

score 1 · Answer 3 · answered Jan 18 '13 at 00:04

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..)

HTML/JS as interface to local SQLite database

3 Answers3