Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A future for SQL on the web

jlongster.com

Post reply on HN
Print view View original

11–20 of 227 posts

Re: A future for SQL on the web

#11

Post by rektide »

James is one of the world's great techno-adventurers, & getting to para-socially share in wild adventures like this makes living on Spaceship Earth more lovely & lively! James has also done cool projects like sweet.js macros, helped kick off Firefox devtool's transition to react (iirc), oh and lead the basically industry standard JS formatter Priettier project. I'm forgetting a dozen other things over the years but it's always been fun.

Just a heads-up, the File System Access API[1] is underway in Chrome, which potentially removes nearly all of the absurdity here. It has other benefits too. A web page using this could write a .sql file on to your drive, that other programs could then access. One of the other bright stars in my world is Karli Koss, who has an extensive personal data-extraction setup for a ridiculously colossal variety of services & devices[2]. A vast amount of this massive massive data-gathering framework is just reading sqlite databases of the various devices and apps. If the web can help participate more actively, can let apps write sql files to store state: so much the better I say. Help externalize your state beyond the browser, please!

[1] https://wicg.github.io/file-system-access/#api-filesystemwri... https://caniuse.com/native-filesystem-api

[2] https://beepb00p.xyz/myinfra.html

Re: A future for SQL on the web

#12

Post by devwastaken »

EvanAnderson wrote: post #4

This is funny and sad to me. We had SQLite in the browser[0]. I only did a little bit of work with it but it seemed actually pretty nice. It was torpedoed because it was SQL-based (and not trendy "key value" and "web scale"). There was the whole excuse that the specification was "whatever SQLite does" and, therefore, not suitable for being a standard. There would be worse things than SQLite upon which to base a stand…

No, SQLite was not safe to be used with arbitrary queries. There were multiple memory vulnerabilities that allowed escape of the browser sandbox.

https://www.sqlite.org/cves.html

Re: A future for SQL on the web

#13

Post by kall »

EvanAnderson wrote: post #4

This is funny and sad to me. We had SQLite in the browser[0]. I only did a little bit of work with it but it seemed actually pretty nice. It was torpedoed because it was SQL-based (and not trendy "key value" and "web scale"). There was the whole excuse that the specification was "whatever SQLite does" and, therefore, not suitable for being a standard. There would be worse things than SQLite upon which to base a stand…

The sad irony is that if HN is anything to go by, SQLite is super trendy now.

I feel like "just sqlite" was really a very practical idea and now, because it has already failed once, we can‘t really try it again, or can we?

Having the spec basically be "bundle one particular piece of software into the browser" might not be in the spirit of web standards, but on the other hand SQLite is so widely bundled into everything, does it really matter?

Re: A future for SQL on the web

#14

Post by eatonphil »

sql.js is pretty hard to use as is otherwise you run out of memory really quickly. I was trying to use it as the in-memory SQL flavor for an open source data ide [0] but my naive approach of `SELECT * FROM VALUES (...), ...` would run out of memory after only a few hundred rows.

I ended up switching to https://github.com/agershun/alasql which could handle up to 80MB of data or so. (I haven't yet tested on larger datasets so I don't know the actual limits.)

I don't think this is a fundamental limitation of sql.js as the linked article proves that you can implement custom paging for sql.js. But unless you do that (which I haven't spent the time to figure out how to do) then sql.js will run out of memory very quickly.

Just something to be aware of if you're investigating it.

If there's a high-level library that makes more effective use of memory with sql.js under the hood let me know.

Unlike absurd-sql I don't need the results to be permanent. I just wanted an in-memory SQL for joining, filtering, grouping data.

[0] https://github.com/multiprocessio/datastation

Re: A future for SQL on the web

#15

Post by shekhirin »

rektide wrote: post #11

James is one of the world's great techno-adventurers, & getting to para-socially share in wild adventures like this makes living on Spaceship Earth more lovely & lively! James has also done cool projects like sweet.js macros, helped kick off Firefox devtool's transition to react (iirc), oh and lead the basically industry standard JS formatter Priettier project. I'm forgetting a dozen other things over the years but i…

Several months ago I've made a proof-of-concept of exactly what you're talking about, feel free to check it out: https://shekhirin.com/sqlite-fs/.

I recommend downloading sample DB, writing some dummy query like "SELECT BILLINGCOUNTRY, COUNT(INVOICEID) FROM INVOICE GROUP BY 1 ORDER BY 2 DESC" and then pressing Execute.

I've been planning to write an extensive article about it and open sourcing the solution cleaning up the code a little bit, but still haven't got much time to do so.

Re: A future for SQL on the web

#16

Post by TehShrike »

EvanAnderson wrote: post #4

This is funny and sad to me. We had SQLite in the browser[0]. I only did a little bit of work with it but it seemed actually pretty nice. It was torpedoed because it was SQL-based (and not trendy "key value" and "web scale"). There was the whole excuse that the specification was "whatever SQLite does" and, therefore, not suitable for being a standard. There would be worse things than SQLite upon which to base a stand…

I used it back in the day and was quite sad when it disappeared. Nolan Lawson does a good job telling the sordid tale at https://nolanlawson.com/2014/04/26/web-sql-database-in-memor...

Re: A future for SQL on the web

#17

Post by TehShrike »

> Every [IndexedDB] library I looked at was messy and made performance even worse

Seconded – I was pretty dismayed when I saw the IndexedDB helper library landscape.

I ended up making https://github.com/TehShrike/small-indexeddb which is ~50 lines to make it less onerous to work directly with the IDBObjectStore.

Re: A future for SQL on the web

#18

Post by EMM_386 »

kall wrote: post #13
EvanAnderson wrote: post #4

This is funny and sad to me. We had SQLite in the browser[0]. I only did a little bit of work with it but it seemed actually pretty nice. It was torpedoed because it was SQL-based (and not trendy "key value" and "web scale"). There was the whole excuse that the specification was "whatever SQLite does" and, therefore, not suitable for being a standard. There would be worse things than SQLite upon which to base a stand…

The sad irony is that if HN is anything to go by, SQLite is super trendy now. I feel like "just sqlite" was really a very practical idea and now, because it has already failed once, we can‘t really try it again, or can we? Having the spec basically be "bundle one particular piece of software into the browser" might not be in the spirit of web standards, but on the other hand SQLite is so widely bundled into everythin…

> The sad irony is that if HN is anything to go by, SQLite is super trendy now.

If you look at the recent StackOverflow survey, the majority of the developers only have around 5 years of professional experience in the industry.

SQL has been around since the 1970s and is still around in force for reason. There's a good chance a lot of developers, especially the enormous number on the front end, do not have experience with SQL or are just getting into back-end work where they are exposed to it.

There have been a lot of SQLite articles recently, as well as node.js libraries to query SQL. I have a hunch this may be why.

Re: A future for SQL on the web

#19

Post by jlongster »

renke1 wrote: post #9

What would be the best way to do migrations with absurd-sql?

I just include a list of migration files in the app, iterate through them in startup and make sure they are all applied. It's pretty simple, but yeah you have to think about this if doing local apps

Re: A future for SQL on the web

#20

Post by infogulch »

devwastaken wrote: post #12
EvanAnderson wrote: post #4

This is funny and sad to me. We had SQLite in the browser[0]. I only did a little bit of work with it but it seemed actually pretty nice. It was torpedoed because it was SQL-based (and not trendy "key value" and "web scale"). There was the whole excuse that the specification was "whatever SQLite does" and, therefore, not suitable for being a standard. There would be worse things than SQLite upon which to base a stand…

No, SQLite was not safe to be used with arbitrary queries. There were multiple memory vulnerabilities that allowed escape of the browser sandbox. https://www.sqlite.org/cves.html

This article is about how to defend against potential websql vulns. https://www.sqlite.org/security.html
Post reply on HN