124

I have a set of HTML files and a SQLite database, which I would like to access from the browser, using the file:// scheme. Is it possible to access the database and create queries (and tables) using JavaScript?

dakab
  • 5,379
  • 9
  • 43
  • 67
Pal Szasz
  • 2,954
  • 3
  • 20
  • 18
  • 3
    By `file:` scheme do you mean on the computer the *browser* is running on? –  Nov 02 '12 at 09:57
  • 3
    Yes. Currently I have a tool which creates a report (a bunch of images, html files and an sqlite database). I can simply open this report locally (i.e. $ google-chrome report_out/index.html). I would like to make this more interactive, so the javascript would read the generated data from the database and create statistics out of it. – Pal Szasz Nov 02 '12 at 13:51
  • i believe it'd be possible to make a connection via a WebSocket proxy, but it'd take quite a bit of work to set up – hanshenrik Mar 06 '15 at 02:07

7 Answers7

54

Actually the answer is yes. Here is an example how you can do this: http://html5doctor.com/introducing-web-sql-databases/

The bad thing is that it's with very limited support by the browsers.

More information here HTML5 IndexedDB, Web SQL Database and browser wars

PS: As @Christoph said Web SQL is no longer in active maintenance and the Web Applications Working Group does not intend to maintain it further so look here https://developer.mozilla.org/en-US/docs/IndexedDB.

SQL.js

EDIT

As @clentfort said, you can access SQLite database with client-side JavaScript by using SQL.js.

maxkoryukov
  • 4,205
  • 5
  • 33
  • 54
Minko Gechev
  • 25,304
  • 9
  • 61
  • 68
  • 17
    FYI websql has been abandoned... Promote **[indexedDB](https://developer.mozilla.org/en-US/docs/IndexedDB)** instead. – Christoph Nov 02 '12 at 09:52
  • 3
    But is it possible to connect to the already existing database? I already have a bunch of data in it, which I would like to process with javascript. – Pal Szasz Nov 02 '12 at 13:47
  • You can go with some server side stuff, or try Node.JS for this https://codeforgeek.com/2014/07/node-sqlite-tutorial/ – Mrug Jun 09 '15 at 06:32
48

You could use SQL.js which is the SQLlite lib compiled to JavaScript and store the database in the local storage introduced in HTML5.

maxkoryukov
  • 4,205
  • 5
  • 33
  • 54
clentfort
  • 2,454
  • 17
  • 19
  • 10
    local storage is very slow and clumsy... you should use indexedDB instead. Nonetheless this is a working solution i guess. – Christoph Nov 02 '12 at 09:55
  • 3
    While localstorage isn't as nice as indexedDB, it is supported pretty much everywhere. SQL.js doesn't use localstorage directly (it's in memory), so you only have to read from/write to localstorage on startup/shutdown, you could even save SQL.js's state on a server. Good if you want the user to specifically save changes, bad if a user leaving without letting it save can break things. – Perkins Sep 21 '15 at 16:42
21

Up to date answer

My fork of sql.js has now be merged into the original version, on a dedicated repo.

The good documentation is also available on the original repo.

Original answer (outdated)

You should use the newer version of sql.js. It is a port of sqlite 3.8, has a good documentation and is actively maintained (by me). It supports prepared statements, and BLOB data type.

lovasoa
  • 6,419
  • 1
  • 35
  • 45
  • Can I use sql.js for accessing (insert, update, read) SQLite database which is on server side. – Abhee Jul 25 '17 at 15:05
  • @lovasoa If I use sql.js, can a fresh computer run my site and do the CRUD to its database (db stored in the same path with HTML folder) without doing any installations? – Jeaf Gilbert Feb 11 '19 at 16:05
  • 2
    @JeafGilbert No. sql.js operates exclusively in-memory, nothing is persisted. If you want to write the database file on your filesystem, you will have to write that logic yourself. – lovasoa Feb 11 '19 at 18:06
5

One of the most interesting features in HTML5 is the ability to store data locally and to allow the application to run offline. There are three different APIs that deal with these features and choosing one depends on what exactly you want to do with the data you're planning to store locally:

  1. Web storage: For basic local storage with key/value pairs
  2. Offline storage: Uses a manifest to cache entire files for offline use
  3. Web database: For relational database storage

For more reference see Introducing the HTML5 storage APIs

And how to use

http://cookbooks.adobe.com/post_Store_data_in_the_HTML5_SQLite_database-19115.html

Talha
  • 18,898
  • 8
  • 49
  • 66
2

What about using something like PouchDB? http://pouchdb.com/

theamoeba
  • 1,331
  • 13
  • 14
-5

It is best to code with python and flask. If you use WebSQL with JavaScript then it will only save the data for individual windows and not worldwide, as it is a browser cookie. Flask is a python web server and once you make a page with it you can import sqlite3. Another way is to use php, but the main point is that using JavaScript is a bad idea.

P.S. Actually you can try using loaclStorage i heard it can save data for as long as you want it to be saved.

Superman123
  • 11
  • 1
  • 3
  • 3
    It's a JavaScript question about browser storage options such as indexedDb so suggesting to not use javascript isn't really helpful. – Anders Elmgren Jun 24 '21 at 19:57
  • *[localStorage] can save data for as long as you want it to be saved.* Absolutely not - it will persist the data only until a cleanup is triggered ("delete local data" in the browser). This is a good place to store data that you want to persist between sessions but not difficult to recreate after having been wiped. – WoJ May 22 '23 at 18:50
-6

IMHO, the best way is to call Python using POST via AJAX and do everything you need to do with the DB within Python, then return the result to the javascript. json and sqlite support in Python is awesome and it's 100% built-in within even slightly recent versions of Python, so there is no "install this, install that" pain. In Python:

import sqlite3
import json

...that's all you need. It's part of every Python distribution.

@Sedrick Jefferson asked for examples, so (somewhat tardily) I have written up a stand-alone back-and-forth between Javascript and Python here.

fyngyrz
  • 2,458
  • 2
  • 36
  • 43
  • 3
    the question contains **"...which I would like to access from the browser..."**. so your answer (with `python`) is out of the area (at least today, when it is not that easy to run python from a browser) – maxkoryukov Mar 23 '20 at 08:44
  • Nonetheless, it's the best way to do it. :) – fyngyrz Aug 16 '22 at 11:52