nalgeon / sqlime

Online SQLite playground

Let's write some code!

Hi! I'm Anton, Golang developer and SQLite enthusiast. I work on open source projects (see below) and blog about programming.

Currently working on codapi: interactive code examples for documentation, education and fun. Give it a try!

I've also written a book about the subset of SQL that helps with data analysis - SQL Window Functions Explained. It's a clear and visual introduction to the subject, taking the reader step-by-step from the basics to advanced topics.

Other (mini)books: Curl by example • Git by example • Grep by example

★ Subscribe to follow updates

sqlime's Issues

Sqlitewasm

You could use SQLite wasm.
It uses the file system api of the browser which is now even available in iOS safari

https://www.phoronix.com/news/SQLite-3.40-Released

Failed to save database to GitHub: got 500 status code if try save data

I tried to save data, but got error

Getting json response from query?

Hi, I like your project. It's simple and easy to use and explore data. I was wondering, would be possible to allow query result to return pure JSON instead of html table? It would be nice to make it consumable from a REST client.

Thanks,
Zemian

feat: improve query editor

• Add SQLITE syntax highlighting
• Allow tabs to add space/tabs in the editor, not focus the run button

Really cool project 😄 thank you for open sourcing—it’s one of the few examples I’ve found with SQLITE WASM

Feature Request: Clear data if zero rows returns

It would be good to show empty table if 0 rows found.

Example:

1. Try demo db
2. Run query select * from employees;. See 10 rows.
3. Run query select * from employees WHERE name = 'test';. You still see 10 rows.

Perhaps the query output should always be cleared before execution.

Support connections to remotely hosted sqlite databases

If a web hosted database is large, the user may not want to download it into memory. The phiresky/sql.js-httpvfs fork of sql.js "provide[s] a read-only HTTP-Range-request based virtual file system for SQLite" (see also: Hosting SQLite databases on Github Pages (or any static file hoster)).

This means that queries can be run against an arbitrarily large remotely hosted sqlite database without taking the hit of having to download the whole database.

It would be useful if SQLime could offer a similar facility in the form a connection to a remote database.

When sharing a query, if the remote URL rather than the database export could be saved to the gist (eg #6 ), then on loading from a gist, the user could be prompted to as to whether they want to download the database into memory, or set up a remote connection to it.

Dot commands are given as SQL errors

First, thank you for the awesome integration with SQLite's WASM.

Second, I'm trying to understand where to place this issue.

The main hiccup is the WASM interpreter seems to not like SQLite's dot commands, e.g.

sqlite> .tables

SQLite3Error: sqlite result code 1: near ".": syntax error

Is there any way to use the dot commands within the extension?

Using multiple databases when creating an html document with interactive sql examples?

Hey thanks for making this awesome javascript widget to create interactive sql examples.

My question whether it is possible to use multiple databases when creating an html document with interactive sql examples? That is, would the following work,

<pre class="employees">select
  rank() over w as "rank",
  name, department, salary
from employees
window w as (order by salary desc)
order by "rank", id;</pre>

<pre class="demo">select * from employees limit 10;</pre>

<sqlime-db name="employees" path="./employees.sql"></sqlime-db>
<sqlime-examples db="employees" selector="pre.employees" editable></sqlime-examples>

<sqlime-db name="demo" path="./demo.db"></sqlime-db>
<sqlime-examples db="demo" selector="pre.demo" editable></sqlime-examples>

Note: both employees.sql and demo.db are taken from this repo

Support larger >1GB sqlite databases

ArrayBuffer limitations

Currently, SQLite databases are loaded using arraybuffers:

• response.arrayBuffer();

• function loadDbFromArrayBuffer(buf) { … }

Arraybuffers have limited sizes:

• Chrome 2145386496 bytes is 2.145386496 gb
• Safari 4294967296 bytes is 4.294967296 gb
• Firefox 8589934592 bytes is 8.589934592 gb

String limitations

Or by loading text:

• return response.text();

Strings have limited sizes:

• Chrome 1073741776 bytes is 1.073741776 gb
• Safari 4294967294 bytes is 4.294967294 gb
• Firefox 2147483644 bytes is 2.147483644 gb

Solutions

Streams API / Web Streams API

To handle larger files, it is possible to use Web Streams:

• With fetch

  // request
  await fetch(url, {
    body: new ReadableStream()
  })
  // response
  const readable = (await fetch(url)).body

• With the File System Access API

  const writable = await fileHandle.createWritable();
  const readable = (await fileHandle.getFile()).stream();

WebAssembly.Memory

A workaround to creating a larger array buffer is using new WebAssembly.Memory

However, I think using the Streams API is a better fit.

Other

I was looking at the implementation:

function loadDbFromArrayBuffer(buf) {
    const bytes = new Uint8Array(buf);
    const p = sqlite3.wasm.allocFromTypedArray(bytes);
    const db = new sqlite3.oo1.DB();
    sqlite3.capi.sqlite3_deserialize(
        db.pointer,
        "main",
        p,
        bytes.length,
        bytes.length,
        sqlite3.capi.SQLITE_DESERIALIZE_FREEONCLOSE
    );
    return db;
}

And I’m thinking that if OPFS VFS support is active, then we can stream the db file to the OPFS VFS, and then open the DB from the file we created.

Using OPFS requires cross-origin isolation (COOP and COEP), which can be added to GitHub pages with coi-serviceworker.

Support embedding into another application

I'm thinking about looking at embedding SQLime into another application so that people teaching SQL can embed SQLime into teaching materials where the embedded display preloads some SQL, but then allows the students to practice querying it. This integration would take care of storing the preloaded materials and things like remembering the SQL entered by the students.

If this can be made generic would these sorts of changes be useful to be included back in a PR or do you have any thoughts about this more generally?

select query with the duplicate column names does not work correctly

We have query:

select sales.dat, calendar.dat
from sales
left join calendar
on sales.dat = calendar.dat
order by 1 nulls first, 2 nulls first

But we see only 1 column

Example from DBeaver (sqlite db engine)

"Canned" queries for idiot-proofing the I/F . .

People,

I want to convert my "pre-Kindle (paper) books" and "watched Netflix videos" DBs from Ruby on Rails or Jekyll web sites and just give my siblings direct access to the DBs - but it will have to be an "idiot-proof" I/F. I am thinking of supplying a short list of "canned" queries on the web page like:

[Run] select * from books
[Run] select id, title, author from books
[Run] Enter your own query here
[Run] Enter your search text here

etc . . this should not be too hard?

Thanks,
Phil.

No message if gist response 403

Steps for reproduce

0. Go to https://sqlime.org/
1. Create API TOKEN without gist scope`
2. Add it to settings
3. Try save snippet
4. infinity saving...
5. In devtools response from gists

{
  "message": "Resource not accessible by personal access token",
  "documentation_url": "https://docs.github.com/rest/reference/gists#create-a-gist"
}

Expected behaviour

User should see message if TOKEN is invalid

Feature Request: Take DB URL to load in the host url

It would be handy if we can use https://sqlime.org?db=https%3A%2F%2Fraw.githubusercontent.com%2Fnalgeon%2Fsqlime%2Fmain%2Femployees.db to load the db at the url specified on start

Not obvious when running SQL if it's updated.

When experimenting with SQL in the browser after clicking "Run" the results are updated pretty much instantly for me. This is great, but it means it's sometimes not obvious that the results have been updated (eg if the result is still the same). It's great that it's fast, however I wonder if adding a tiny pause ( & maybe spinner display) and only then setting the output (eg 100ms), so there is a clue that the results have been refreshed, but it doesn't get in the way of quickly iterating on the SQL.

Run only selected query from the editor

Hello, Currently when we hit run button all the text in editor is evaluated and executed.
If only selected query is executed it will help all of users maintain short history and run queries faster.

Failed to save database to GitHub: got 500 status code if try save data

I tried to save data, but got error

Saving a link to web hosted database rather than the downloaded database

This a really handy app - thanks for sharing. :-)

I note that if a user wants to query a web hosted SQLite database, the whole database is downloaded into the browser and then queried in-memory.

When a Github credentialled user shares a query, both the query and the database seem to be saved as a (private) gist. If the user saves a lot of queries, this is quite wasteful in terms of resource (network, gist storage).

It would be convenient in such cases if the user could:

• choose to save either the full db or the remote URL to the gist;
• when loading from the gist, chec to see if the full db or the remote URL is specified:
  • if the full db / sql code is provided, load that, as is currently the case;
  • if a remote URL is specified for the database, check to see if the current loaded database has been loaded from the same remote URL; if it has, retain the current database and just load the query in the gist; if the current loaded database has a different source to the remote specified URL, replace the database with a connection to a new database downloaded from the remote URL.