Is it possible to access an SQLite database from JavaScript?

Question

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?

Solution 1

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.

Solution 2

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

Solution 3

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.

Solution 4

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

Solution 5

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

View more solutions

Comments

[Admin]

By file: scheme do you mean on the computer the browser is running on?

[Pal Szasz]

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.

[hanshenrik]

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

[Christoph]

FYI websql has been abandoned... Promote indexedDB instead.

[Christoph]

local storage is very slow and clumsy... you should use indexedDB instead. Nonetheless this is a working solution i guess.

[Christoph]

hehe, take a look into the second revision of your answer, there you can read it;)

[Pal Szasz]

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.

[Mrug]

You can go with some server side stuff, or try Node.JS for this codeforgeek.com/2014/07/node-sqlite-tutorial

[Perkins]

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.

[Abhee]

Can I use sql.js for accessing (insert, update, read) SQLite database which is on server side.

[Jeaf Gilbert]

@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?

[lovasoa]

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

[maxkoryukov]

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]

as you can see the author of the question has the sqlite DB, and you haven't provided examples and script for converting SQLITE => POUCHDB

[Anders Elmgren]

It's a JavaScript question about browser storage options such as indexedDb so suggesting to not use javascript isn't really helpful.

[SherylHohman]

In other words, this should be a Comment, not an Answer!