9 ms·

SuperSQLite: SQLite library for Python (2018)

135 points by jkldotio 7y ago

Gys 7y ago
Its supports 'Remote Streaming over HTTP' without explaining what that means. Maybe someone here knows?
- niea_11 7y ago
  From what i saw in the source code, it's a feature that lets sqlite open a database file stored on a web server. There is a class in the code HTTPVFS [1] that proxies sqlite's filesystem operations to http requests. [1]:https://github.com/plasticityai/supersqlite/blob/01e54bbb829a9441627c416342b1f1e50f94333e/supersqlite/__init__.py#L1203 https://github.com/plasticityai/supersqlite/blob/01e54bbb829...
- egorfine 7y ago
  Got me, too
- patelajay285 7y ago
  I'm the author, yes @niea_11's comment is right. We add a HTTP Virtual File System. So you can stream the SQLite files over a static HTTP Server like Amazon S3 using HTTP Range Headers to do it efficiently. No dynamic web software needed.
trollied 7y ago
The title would be better as "SuperSQLite: a supercharged SQLite library for Python".
johnisgood 7y ago
> SQLite is extremely reliable and durable for large amounts of data (up to 140TB). It is considered one of the most well-engineered and well-tested software solutions today, with 711x more test code than implementation code. I keep seeing this statement. Why is it considered one of the most well-engineered software?
- hanche 7y ago
  A small (?) part of the answer is likely to be found in the massive amount of testing: https://sqlite.org/testing.html https://sqlite.org/testing.html
- tempguy9999 7y ago
  https://sqlite.org/about.html https://sqlite.org/about.html The bit about testing.
- platz 7y ago
  I would like not to have to deal with SQLITE_BUSY errors for once. It even throws when trying to obtain a connection. It got so bad I had to put a mutex around obtaining a sqlite connection.
  - breakingcups 7y ago
    It's possible you are using it wrong.
    - firebacon 7y ago
      The less flippant explanation is that SQLite can only handle a single writer at any time and when you try to access it with two concurrent writers (or a concurrent reader and writer in some modes) it will by default return a "BUSY" error instead of blocking. So, if you're were getting unexpected "BUSY" erorrs than, yes, you would be using it incorrectly. However, AIUI, you are always expected to see some amount of BUSY errors during normal, concurrent operation and have to deal with them explicitly. So the fact that you're seeing BUSY errors alone doesn't mean you're doing anything wrong... To use SQLite correctly from multiple processes, you have to do one of two things: - Add explicit code to retry on BUSY errors everywhere you do SQL queries - Serialize all access the database, e.g. by using a mutex GP appears to have chosen the second option.
- PudgePacket 7y ago
  Not a direct answer but it's certainly the most used, which correlates highly with getting a lot of dev attention :) https://sqlite.org/mostdeployed.html https://sqlite.org/mostdeployed.html
coleifer 7y ago
Looks like it just bundles a full build of the sqlite shared library, along with some kind of gross hacks. It bundles pysqlite2, which notably is missing a ton of features and improvements from upstream. I'll just compile my own, thanks...
- patelajay285 7y ago
  Hi @coleifer, I'll disagree, I'm the author. I don't think they are gross hacks, just a more sensible default configuration. In fact, I used a lot of your advice from your blog post https://charlesleifer.com/blog/going-fast-with-sqlite-and-python/ https://charlesleifer.com/blog/going-fast-with-sqlite-and-py... while making this library. Fair enough if you want to compile your own version, but there's a lot of people and Python devs who don't know how / won't go through the effort and that's what this project is for. It's also a work in progress and not meant to be released yet. Shoot me an e-mail ajay@plasticityai.com if you have suggestions for how to improve it.
  - coleifer 7y ago
    The advice in my post that you linked can be supplemented by this: http://charlesleifer.com/blog/compiling-sqlite-for-use-with-python-applications/ http://charlesleifer.com/blog/compiling-sqlite-for-use-with-... Describes several handy ways to compile sqlite for use with python (2 or 3), as a system-wide shared library or compiled directly into the python module. This can also be applied to sqlcipher, etc. If you don't know how to compile sqlite, I'd argue that you have no business trying to use it's more advanced features. How can you tune or optimize something you don't understand? Furthermore, your library is stale. Anyone using it is dependent on you to merge in upstream changes constantly. And based on what I saw, it's already well out of date.
    - patelajay285 7y ago
      Yes, I've seen that post as well. Thanks! It's good advice. >> If you don't know how to compile sqlite, I'd argue that you have no business trying to use it's more advanced features. How can you tune or optimize something you don't understand? I'm not sure I agree with that, some people understand the advanced features but have a miserable time with compilers and compiling something like SQLite. There's a lot of great engineers with Python and SQL expertise that just don't know how to compile a C project. That might sound contradictory, but it's just something a lot of engineers still don't do day to day since people are using a lot of scripted languages (Node/Python/Ruby). For example, there's probably a lot of people who want to use the JSON1 or FTS5 modules with SQLite in Python. That doesn't require advanced knowledge, but requires them to re-compile SQLite! >> Furthermore, your library is stale. Anyone using it is dependent on you to merge in upstream changes constantly. And based on what I saw, it's already well out of date. It is stale, I agree. But it's still a WIP. This was posted here early. My plan is to pull in changes from the upstream sqlite module with the latest CPython 2.7 tag and latest CPython 3 tag in the source.
dest 7y ago
Interesting pick from one of the links in the article: "SQLite has fantastic write performance as well. By default SQLite uses database-level locking (minimal concurrency), and there is an “out of the box” option to enable WAL mode to get fantastic read concurrency — as shown by this test. But lesser known is that there is a branch of SQLite that has page locking, which enables for fantastic concurrent write performance." https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
- cheez 7y ago
  uh.... concurrent writes? southparkejaculation.gif Unfortunately, I use SQLite through Python so I'm stuck with the system version :-(
- pdw 7y ago
  I knew Oracle offered something like that (implemented by grafting the SQLite frontend/VM on top of Berkeley DB). But it'd be really cool to see page-level locking appear in standard SQLite version.
- nbevans 7y ago
  In case anyone is curious "BEGIN CONCURRENT" is what is being discussed here (page level locks). There is also a WAL2 mode which is basically the WAL mode operating in a A/B hot swap mode - to facilitate checkpointing without holding up writes. SQLite has progressed a lot in the last few years. It is no longer advisable to mock it over SQLITE_BUSY et al; you will come unstuck on the Internet very quickly ;-)
- marktangotango 7y ago
  When you have a write-heavy workload with multiple servers that need to write concurrently to a shared database (backend to a website), you would probably want to choose something that has a client-server model instead like PostgreSQL It's easy to get really stellar concurrent performance out of SQLite using a many reader, single writer model (ie many threads, single process). In testing we did it easily surpassed Postgres.
nabdab 7y ago
This looks like a dead project that just bundles together the built-in sqlite3 And another wrapper library APSW which itself is more active than this project. Why is this on hn?
- airstrike 7y ago
  > Why is this on hn? Because anyone can submit links?
- typon 7y ago
  We need comments like yours and let the down votes do the rest.
- stedaniels 7y ago
  No commits in 9 months, "looks like a dead project".
  - deleted 7y ago
    [deleted]
- patelajay285 7y ago
  Hi, I'm the author! It's not a dead project, it's just not released yet, someone seems to have submitted it early :). Will post it back here when it's ready for prime time.
mtw 7y ago
How accurate is this statement? "SQLite is faster than nearly every other database". It links to a page that's decades old.
tony 7y ago
Any features in this library you'd like to see standard library's sqlite3 [1]? Maybe a PEP [2, python enhancement proposal] could do it. [1] https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html [2] https://www.python.org/dev/peps/pep-0001/ https://www.python.org/dev/peps/pep-0001/
- nerdponx 7y ago
  In the standard library? Probably nothing. But if someone published an alternative APSW wheel with JSON1, ICU, and FTS5 enabled, I'd be happy.
  - rogerbinns 7y ago
    I'm the APSW author. The binary builds for Windows are distributed with those extensions all compiled in, although my doc needs some updating. It is also only a single flag for other platforms to include all extensions during compilation. What can I do?
    - no_wizard 7y ago
      Whats the hold back to getting it via pip install via a wheel? I'm curious (happy APSW user here). Its not well elaborated from what I could find. This is something I would love to help with but not sure where to start, as I'm not sure where in the process of doing the builds it falls down. Would Cython help, perhaps?
      - rogerbinns 7y ago
        Cython wouldn't help. The reason[1] is in the docs. I already do Windows builds[2] - 21, becoming 23 in next release so that isn't a difficulty. The problem is tying all this together in a way that is useful, with sensible defaults and appropriate tools. APSW supports python versions going back 16 years! I welcome discussion at python-sqlite[3] [1] https://rogerbinns.github.io/apsw/download.html#easy-install-pip-pypi https://rogerbinns.github.io/apsw/download.html#easy-install... [2] https://rogerbinns.github.io/apsw/download.html#source-and-binaries https://rogerbinns.github.io/apsw/download.html#source-and-b... [3] https://groups.google.com/forum/#!forum/python-sqlite https://groups.google.com/forum/#!forum/python-sqlite
ddorian43 7y ago
Title is misleading. It needs to add something like `python-library`. I thought they supercharged sqlite itself.
- patelajay285 7y ago
  Hi I'm the author, see my parent comment in the thread. This is still a WIP. You're right that it's mostly a Python library, but when it's released, it will actually be released with pre-built native static SQLite libraries that can be linked into any C application, so it is actually supercharged SQLite itself :).
jgalt212 7y ago
> SQLite is extremely reliable and durable for large amounts of data (up to 140TB). This has not been our experience. Our experience is that it sort of bogs down around 12GB in file size. linux box, ext3/4, spinning disk (not SSD)
- patelajay285 7y ago
  Hi @jgalt, I'm the author of this library, and we've been using it to serve extremely large files over ~130GB at Plasticity (YCS17) for AI model data! Shoot me an email ajay@plasticityai.com and if you'd like to chat, but it should remain extremely responsive even at that file size.
  - jgalt212 7y ago
    Thanks may take you up on that after running some new tests. It was years ago we had performance issues (approx 3-4), so will see if time has healed all wounds.
d_burfoot 7y ago
I've been toying with the idea of using SQLite as a data exchange format instead of JSON or XML. I can't stand navigating through complex JSON trees, I want to just use SQL queries. Has anyone else tried this?
- sametmax 7y ago
  Works well if your data is big and doesn't have to be read on a web browser.
patelajay285 7y ago
Hi, I'm the author and founder at Plasticity (YCS17) and built this for some internal use cases our startup. This is actually a Work-in-Progress and not meant to be released yet, but it seems like someone has found it online. I will re-submit this here when it is ready as a Show HN given the interest, shoot me an e-mail at ajay@plasticityai.com if you would like to be kept up to date!

135 points by jkldotio 7y ago

Gys 7y ago

Its supports 'Remote Streaming over HTTP' without explaining what that means. Maybe someone here knows?

niea_11 7y ago
From what i saw in the source code, it's a feature that lets sqlite open a database file stored on a web server. There is a class in the code HTTPVFS [1] that proxies sqlite's filesystem operations to http requests. [1]:https://github.com/plasticityai/supersqlite/blob/01e54bbb829a9441627c416342b1f1e50f94333e/supersqlite/__init__.py#L1203 https://github.com/plasticityai/supersqlite/blob/01e54bbb829...
egorfine 7y ago
Got me, too
patelajay285 7y ago
I'm the author, yes @niea_11's comment is right. We add a HTTP Virtual File System. So you can stream the SQLite files over a static HTTP Server like Amazon S3 using HTTP Range Headers to do it efficiently. No dynamic web software needed.

trollied 7y ago

The title would be better as "SuperSQLite: a supercharged SQLite library for Python".

johnisgood 7y ago

> SQLite is extremely reliable and durable for large amounts of data (up to 140TB). It is considered one of the most well-engineered and well-tested software solutions today, with 711x more test code than implementation code. I keep seeing this statement. Why is it considered one of the most well-engineered software?

hanche 7y ago
A small (?) part of the answer is likely to be found in the massive amount of testing: https://sqlite.org/testing.html https://sqlite.org/testing.html
tempguy9999 7y ago
https://sqlite.org/about.html https://sqlite.org/about.html The bit about testing.
platz 7y ago
I would like not to have to deal with SQLITE_BUSY errors for once. It even throws when trying to obtain a connection. It got so bad I had to put a mutex around obtaining a sqlite connection.
- breakingcups 7y ago
  It's possible you are using it wrong.
  - firebacon 7y ago
    The less flippant explanation is that SQLite can only handle a single writer at any time and when you try to access it with two concurrent writers (or a concurrent reader and writer in some modes) it will by default return a "BUSY" error instead of blocking. So, if you're were getting unexpected "BUSY" erorrs than, yes, you would be using it incorrectly. However, AIUI, you are always expected to see some amount of BUSY errors during normal, concurrent operation and have to deal with them explicitly. So the fact that you're seeing BUSY errors alone doesn't mean you're doing anything wrong... To use SQLite correctly from multiple processes, you have to do one of two things: - Add explicit code to retry on BUSY errors everywhere you do SQL queries - Serialize all access the database, e.g. by using a mutex GP appears to have chosen the second option.
PudgePacket 7y ago
Not a direct answer but it's certainly the most used, which correlates highly with getting a lot of dev attention :) https://sqlite.org/mostdeployed.html https://sqlite.org/mostdeployed.html

coleifer 7y ago

Looks like it just bundles a full build of the sqlite shared library, along with some kind of gross hacks. It bundles pysqlite2, which notably is missing a ton of features and improvements from upstream. I'll just compile my own, thanks...

patelajay285 7y ago
Hi @coleifer, I'll disagree, I'm the author. I don't think they are gross hacks, just a more sensible default configuration. In fact, I used a lot of your advice from your blog post https://charlesleifer.com/blog/going-fast-with-sqlite-and-python/ https://charlesleifer.com/blog/going-fast-with-sqlite-and-py... while making this library. Fair enough if you want to compile your own version, but there's a lot of people and Python devs who don't know how / won't go through the effort and that's what this project is for. It's also a work in progress and not meant to be released yet. Shoot me an e-mail ajay@plasticityai.com if you have suggestions for how to improve it.
- coleifer 7y ago
  The advice in my post that you linked can be supplemented by this: http://charlesleifer.com/blog/compiling-sqlite-for-use-with-python-applications/ http://charlesleifer.com/blog/compiling-sqlite-for-use-with-... Describes several handy ways to compile sqlite for use with python (2 or 3), as a system-wide shared library or compiled directly into the python module. This can also be applied to sqlcipher, etc. If you don't know how to compile sqlite, I'd argue that you have no business trying to use it's more advanced features. How can you tune or optimize something you don't understand? Furthermore, your library is stale. Anyone using it is dependent on you to merge in upstream changes constantly. And based on what I saw, it's already well out of date.
  - patelajay285 7y ago
    Yes, I've seen that post as well. Thanks! It's good advice. >> If you don't know how to compile sqlite, I'd argue that you have no business trying to use it's more advanced features. How can you tune or optimize something you don't understand? I'm not sure I agree with that, some people understand the advanced features but have a miserable time with compilers and compiling something like SQLite. There's a lot of great engineers with Python and SQL expertise that just don't know how to compile a C project. That might sound contradictory, but it's just something a lot of engineers still don't do day to day since people are using a lot of scripted languages (Node/Python/Ruby). For example, there's probably a lot of people who want to use the JSON1 or FTS5 modules with SQLite in Python. That doesn't require advanced knowledge, but requires them to re-compile SQLite! >> Furthermore, your library is stale. Anyone using it is dependent on you to merge in upstream changes constantly. And based on what I saw, it's already well out of date. It is stale, I agree. But it's still a WIP. This was posted here early. My plan is to pull in changes from the upstream sqlite module with the latest CPython 2.7 tag and latest CPython 3 tag in the source.

dest 7y ago

Interesting pick from one of the links in the article: "SQLite has fantastic write performance as well. By default SQLite uses database-level locking (minimal concurrency), and there is an “out of the box” option to enable WAL mode to get fantastic read concurrency — as shown by this test. But lesser known is that there is a branch of SQLite that has page locking, which enables for fantastic concurrent write performance." https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

cheez 7y ago
uh.... concurrent writes? southparkejaculation.gif Unfortunately, I use SQLite through Python so I'm stuck with the system version :-(
pdw 7y ago
I knew Oracle offered something like that (implemented by grafting the SQLite frontend/VM on top of Berkeley DB). But it'd be really cool to see page-level locking appear in standard SQLite version.
nbevans 7y ago
In case anyone is curious "BEGIN CONCURRENT" is what is being discussed here (page level locks). There is also a WAL2 mode which is basically the WAL mode operating in a A/B hot swap mode - to facilitate checkpointing without holding up writes. SQLite has progressed a lot in the last few years. It is no longer advisable to mock it over SQLITE_BUSY et al; you will come unstuck on the Internet very quickly ;-)
marktangotango 7y ago
When you have a write-heavy workload with multiple servers that need to write concurrently to a shared database (backend to a website), you would probably want to choose something that has a client-server model instead like PostgreSQL It's easy to get really stellar concurrent performance out of SQLite using a many reader, single writer model (ie many threads, single process). In testing we did it easily surpassed Postgres.

nabdab 7y ago

This looks like a dead project that just bundles together the built-in sqlite3 And another wrapper library APSW which itself is more active than this project. Why is this on hn?

airstrike 7y ago
> Why is this on hn? Because anyone can submit links?
typon 7y ago
We need comments like yours and let the down votes do the rest.
stedaniels 7y ago
No commits in 9 months, "looks like a dead project".
- deleted 7y ago
  [deleted]
patelajay285 7y ago
Hi, I'm the author! It's not a dead project, it's just not released yet, someone seems to have submitted it early :). Will post it back here when it's ready for prime time.

mtw 7y ago

How accurate is this statement? "SQLite is faster than nearly every other database". It links to a page that's decades old.

tony 7y ago

Any features in this library you'd like to see standard library's sqlite3 [1]? Maybe a PEP [2, python enhancement proposal] could do it. [1] https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html [2] https://www.python.org/dev/peps/pep-0001/ https://www.python.org/dev/peps/pep-0001/

nerdponx 7y ago
In the standard library? Probably nothing. But if someone published an alternative APSW wheel with JSON1, ICU, and FTS5 enabled, I'd be happy.
- rogerbinns 7y ago
  I'm the APSW author. The binary builds for Windows are distributed with those extensions all compiled in, although my doc needs some updating. It is also only a single flag for other platforms to include all extensions during compilation. What can I do?
  - no_wizard 7y ago
    Whats the hold back to getting it via pip install via a wheel? I'm curious (happy APSW user here). Its not well elaborated from what I could find. This is something I would love to help with but not sure where to start, as I'm not sure where in the process of doing the builds it falls down. Would Cython help, perhaps?
    - rogerbinns 7y ago
      Cython wouldn't help. The reason[1] is in the docs. I already do Windows builds[2] - 21, becoming 23 in next release so that isn't a difficulty. The problem is tying all this together in a way that is useful, with sensible defaults and appropriate tools. APSW supports python versions going back 16 years! I welcome discussion at python-sqlite[3] [1] https://rogerbinns.github.io/apsw/download.html#easy-install-pip-pypi https://rogerbinns.github.io/apsw/download.html#easy-install... [2] https://rogerbinns.github.io/apsw/download.html#source-and-binaries https://rogerbinns.github.io/apsw/download.html#source-and-b... [3] https://groups.google.com/forum/#!forum/python-sqlite https://groups.google.com/forum/#!forum/python-sqlite

ddorian43 7y ago

Title is misleading. It needs to add something like `python-library`. I thought they supercharged sqlite itself.

patelajay285 7y ago
Hi I'm the author, see my parent comment in the thread. This is still a WIP. You're right that it's mostly a Python library, but when it's released, it will actually be released with pre-built native static SQLite libraries that can be linked into any C application, so it is actually supercharged SQLite itself :).

jgalt212 7y ago

> SQLite is extremely reliable and durable for large amounts of data (up to 140TB). This has not been our experience. Our experience is that it sort of bogs down around 12GB in file size. linux box, ext3/4, spinning disk (not SSD)

patelajay285 7y ago
Hi @jgalt, I'm the author of this library, and we've been using it to serve extremely large files over ~130GB at Plasticity (YCS17) for AI model data! Shoot me an email ajay@plasticityai.com and if you'd like to chat, but it should remain extremely responsive even at that file size.
- jgalt212 7y ago
  Thanks may take you up on that after running some new tests. It was years ago we had performance issues (approx 3-4), so will see if time has healed all wounds.

d_burfoot 7y ago

I've been toying with the idea of using SQLite as a data exchange format instead of JSON or XML. I can't stand navigating through complex JSON trees, I want to just use SQL queries. Has anyone else tried this?

sametmax 7y ago
Works well if your data is big and doesn't have to be read on a web browser.

patelajay285 7y ago

Hi, I'm the author and founder at Plasticity (YCS17) and built this for some internal use cases our startup. This is actually a Work-in-Progress and not meant to be released yet, but it seems like someone has found it online. I will re-submit this here when it is ready as a Show HN given the interest, shoot me an e-mail at ajay@plasticityai.com if you would like to be kept up to date!