10 ms·

Sqlite 3.30.0

393 points by QuadrupleA 7y ago

nattaylor 7y ago
I use FILTER once in a while on a postgres database I work with, so I am happy to see it added to SQLite
hardwaresofton 7y ago
Glad to see SQLite on then front page -- it's one of the silent workhorses of the modern programming/database world. It is the most widely deployed database[0]. If you haven't given it a look/aren't interested in it since it seems to be a "toy" database (often the "test" or "local" db for frameworks like rails or django), you owe it to yourself to see what it can really do. Easy to use full text search[1][2], CTEs[3], JSON support via extension[4] (also the extension system is worth looking at[5]) and much much more. There are certainly things that SQLite does not do, and that's well documented too[6]. If all this doesn't convince you to use SQLite, it's also one of the most well documented large C codebases that is fantastic to learn from. I'd go as far as to say that many modern startups could get pretty far with SQLite + aggressive caching before even bringing in a big database like Postgres (though with recent deployment/ops advancements it's easier than ever to run postgres). [0]: https://sqlite.org/mostdeployed.html https://sqlite.org/mostdeployed.html [1]: https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html [2]: https://sqlite.org/fts3.html https://sqlite.org/fts3.html [3]: https://sqlite.org/lang_with.html https://sqlite.org/lang_with.html [4]: https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html [5]: https://www.sqlite.org/loadext.html https://www.sqlite.org/loadext.html [6]: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- dmarlow 7y ago
  Apparently clustering too https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
  - minxomat 7y ago
    Hardly, that is more of a hack. There is however a distributed, clustered version of sqlite: Bloomberg's Comdb2. It has the same scaling factors as e.g. percona, can be Geo-replicated (multi-roomed) and has many advanced timing and financial data types. Plus SQL functions can be written in Lua. Oh, and it has super granular serialization guarantees, up to and including full linearizibility. By using Sqlite as the SQL frontend, it retains features other NewSQL engines struggle with (recursive CTE, deep optimization, compatibility with existing queries etc)
    - e12e 7y ago
      Wow, thats: https://github.com/bloomberg/comdb2 https://github.com/bloomberg/comdb2 Looks interesting. Any experience using it in real projects?
      - minxomat 7y ago
        Yeah it works well. I also sometimes push code into the db for faster execution (sorry on mobile, can't detail now). Haven't tested out Geo replication though, but it's pretty straightforward. Clock drift maximum and all that.
  - hardwaresofton 7y ago
    Also there is dqlite[0]. I really do think that SQLite can go even farther than people think, but the exploration makes less and less sense the better and easier to run options like postgres become. That said, I'm glad projects like dqlite and rqlite exist. There are also previous threads discussing them but here's one[1]. [0]: https://github.com/CanonicalLtd/dqlite https://github.com/CanonicalLtd/dqlite [1]: https://news.ycombinator.com/item?id=20836331 https://news.ycombinator.com/item?id=20836331
    - speedplane 7y ago
      > I really do think that SQLite can go even farther than people think I know different databases support different features (e.g., atomic transactions, ACID compliance, etc.), but it seems like the big differentiator is size, namely: 1. Can your database live within a single computer's RAM? 2. Can your database live within a single computer? 3. Can your database live across a cluster of computers in a single data center? 4. Can your database live across a cluster of computers spread across the planet? Once you pick your category from the above, there are still choices to be made, but the "best" options narrow down significantly.
      - hardwaresofton 7y ago
        I'd argue that feature set (transactions, ACID, etc) are a bigger differentiator because they drive/change application development -- this can have drastic effects on application features and complexity whether immediately or down the road. I agree that the possible second biggest (if not the first) differentiator is size though -- despite the amount of choices out there, once you start narrowing down answering those questions, many options fall away very quickly. I'd also add on that the only real way to scale a database is to shard. There are a bunch of tricks you can do to improve throughput and those are always welcome, and sometimes I'll see articles that proclaim "we just changed to a system with 256GB of RAM to fix our scaling problem!" -- but these improvements don't get past the basic premise that once your data does not fit on one computer/harddrive you need to start figuring out a way for multiple to coordinate. You need to shard, and you need to organize your data in such a way that it can shard, then you need to start worrying about data access patterns. There's a lot of space before something like this is necessary (you really do have to have a lot of data for it to not fit on one machine these days), but I think people don't see this chain coming fast/often enough.
- dmarlow 7y ago
  I only wish I could do some basic things without having to create temporary tables and copy data around.
  - gigatexal 7y ago
    Yeah that’s likely the one thing that keeps it from being perfect.
  - hardwaresofton 7y ago
    I'm not sure exactly what the use case is but another awesome tool that's recently been making the rounds is datasette[0], it provides an easy to use data exploration point for SQLite databases. There's also a PyCon 2019 talk[1]. [0]: https://datasette.readthedocs.io/en/stable https://datasette.readthedocs.io/en/stable [1]: https://www.youtube.com/watch?v=pTr1uLQTJNE https://www.youtube.com/watch?v=pTr1uLQTJNE
- kragen 7y ago
  I only learned this week that it supports recursive CTEs (although apparently you can't refer to them in subqueries yet?) and only yesterday that you can do this: >>> import sqlite3 >>> con = sqlite3.connect(':memory:') >>> cur = con.cursor() >>> q = '''select x, sum(a * power(x, e)) ... from (select -2 as x union select -1 union select 0 union select 1 union select 2) xt, ... (select 1 as a, 2 as e union select 4, 1 union select 2, 0) et ... group by x''' >>> cur.execute(q).fetchall() Traceback (most recent call last): File "<stdin>", line 4, in <module> sqlite3.OperationalError: no such function: power >>> con.create_function('power', 2, lambda base, power: base**power) >>> cur.execute(q).fetchall() [(-2, -2), (-1, -1), (0, 2), (1, 7), (2, 14)] That is freaking cool. Not evaluating polynomials in SQL, I mean, but defining a user-defined function for SQL in one line of Python.
  - emj 7y ago
    That is cool; Being able to call python code from SQL is very cool. Is it very slow?
    - kyberias 7y ago
      Being able to call python code from SQL is super scary!
      - sbierwagen 7y ago
        What attacks do you believe it enables?
    - oefrha 7y ago
      It simply uses the sqlite3_create_function API, so it’s as fast/slow as your Python function. You can even use Cython if pure Python isn’t fast enough. I use UDFs quite a bit and the performance is fine without extra tweaks most of the time, but for something like pow I’d use the math extension instead which would be way more performant. Btw, user-defined scalar functions aren’t remotely as sexy as user-defined window functions.
- steve_adams_86 7y ago
  Checkfront, a booking management system, got by on sqlite up until maybe 2 years ago - it was the solution for 5 or more years! These days it runs on postgres but it went very far without it.
- Scarbutt 7y ago
  But why?(for a server) Postgres can run in a 256MB VPS(if one can find one these days) and has more features that you might need later. Installing is as easy as 'apt-get install postgresql', create user, create database, done.
  - xyzzy_plugh 7y ago
    Furthermore, postgres has great replication, and a read-replica is trivial to set up. sqlite is amazing (for any embedded database, absolutely) but postgres can't be beat as a go to for server side.
    - cyphar 7y ago
      To paraphrase the SQLite site: "SQLite isn't competing with [PostgreSQL]; it's competing with fopen(3)." The primary purpose is to replace custom (and fragile) single-file databases with something that is far more resilient and provides uniformity in implementation. The fact that it is a suprisingly good SQL database (to the point where folks are actively arguing that much more fully-fledged databases are better in some aspects) is incidental.
  - hardwaresofton 7y ago
    SQLite is even easier to start with -- I'm not trying to say Postgres is not the right choice, but it is certainly a more complex choice. Postgres is a fantastic database, but most of the time getting started with and using SQLite will be easier. For example -- you'd never have to secure firewall rules to a SQLite instance. Bad defaults (unlikely but possible) or some bad post-install configuration and you've got an exposed server. I want to emphasize that my point about SQLite being viable for startups was with the assumption of a low-write high read workload -- most of the time queries won't even hit the DB but instead be served by some caching layer. It's also really easy to scale read only versions of your applications horizontally if all you've got to do is ship/swap out DB files (again at this point it might make more sense to look into a bigger DB and just scaling vertically).
  - throwaway_bad 7y ago
    I have a small blog (using Ghost) that uses sqlite as the main database and it's incredibly easy to maintain. It's just a single file. For example if you want to back up, you just copy the file or add it to git. If you want to do a diff you just do sqldiff on two files. It is as easy as working with json or csv files, except with atomic writes and better querying. From their site: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html >SQLite does not compete with client/server databases. SQLite competes with fopen().
- saagarjha 7y ago
  > If you haven't given it a look/aren't interested in it since it seems to be a "toy" database As you mentioned, it ships in every major browser, most OSes, and a number of extremely popular instances of consumer software. If that's still a "toy" I don't know what isn't…
- erik_seaberg 7y ago
  sqlite can be handy but it's not a substitute for testing against a staging instance of another database platform. It ignores a lot of mistakes in column types and size limits and sometimes even foreign keys. If anything, I would want tests to be more strict than prod.
  - hardwaresofton 7y ago
    Your point is correct, but I'm afraid you might not have understood what I meant -- I meant that people should be considering using SQLite in production. Agreed that testing against copies of production or staging instances that are as close as possible to production is important, but what I'm suggesting is that you could ride SQLite all the way into production if you wanted to. I'd love to hear more about the ignoring column types/size limit/other issues that you've run into though, have you written about them anywhere or is there an SO post? I haven't run into anything super egregious though I've had some problems with FKs before.
    - ris 7y ago
      Do a GROUP BY, and then try referencing a term which hasn't been either aggregated or mentioned in the GROUP BY clause. The result should be "abort! this query can't make sense!". The result (last I tried) is instead "sure, here's some arbitrary value from this group".
  - marmaduke 7y ago
    It seeks to replace fopen() not postgres.
  - justinclift 7y ago
    Foreign keys are supported with SQLite version 3.6.19 onward (~late 2009), though not enabled by default. https://sqlite.org/foreignkeys.html https://sqlite.org/foreignkeys.html If you can add "PRAGMA foreign_keys = ON;" to your testing script SQL calls - somewhere soon after connecting to SQLite - that enables it.
- siscia 7y ago
  Indeed, I believe as well that SQLite is underused in a lot of situations. Which is mostly the reason why I created RediSQL[1], to allow a more widespread use of it also in "modern"/"cloud native" application where you want to just connect to a database via the network, ideally through common API like the Redis one. It has the great benefit, that if you create your database in memory is crazy fast, and it retains the persistency properties of Redis, which are good enough in most cases. [1] https://RediSQL.com https://RediSQL.com
  - jeff_redradish 7y ago
    Just FYI, on Chrome a 'Subscribe to the mail list' pop-up appears, and cannot be dismissed by clicking the X. Hitting Escape works.
    - siscia 7y ago
      Thanks so much! Sorry for the annoyance!
      - philips 7y ago
        Safari on iPadOS as well.
- ngrilly 7y ago
  > I'd go as far as to say that many modern startups could get pretty far with SQLite + aggressive caching before even bringing in a big database like Postgres Modern startups need their services to be available, and this is difficult to achieve with SQLite because you can't run long database migrations without blocking writes during the migration. Personally, I'd use SQLite more often if there was a good solution to this (and also DROP COLUMN).
  - hardwaresofton 7y ago
    My statement was definitely a reach, but I think there is a bunch of space in the life of an early startup for lack of highly available services. I've found startups to prioritize time-to-market (or time to PoC) over technical purity -- there's usually lots of unplanned outages early on that often have nothing to do with the database or are a result of bad application code/misunderstood requirements as well, so I'm not fully buying the availability emphasis, but a few things that I think could counteract it: - I'm not sure I agree that the delay of the blocking write from a migration is so long as to cause significant (if any) damage to the business. - If SQLite makes development and deployment easier (I think it does), this becomes a tradeoff between a deployment downtime/partial operation window likely on the order of milliseconds and hours of time spent setting up/managing the alternatives. With really robust systems like Postgres out there this is a pretty weak argument on my part, but there is at least something there. - As I've also mentioned elsewhere, a high read-to-write workload would be less affected by this problem. If your startup produces aggregated business analytics dashboards for manufacturing plant stakeholders for example, your data set could be completely static. - WAL mode[0] improves SQLite's resiliency to concurrent readers/writers. - In the extreme case, you could treat SQLite as a hybrid row & JSON document store, and manage your own schemas (I do not endorse this approach), but you'd still be saving some time because you can do stuff like make use of SQLite's FTS search features, and you'd have access to other relational features that you might not get with other document stores. All this said, SQLite is not for lots of concurrent writers, and they so themselves[1], but I think it can absolutely be done. More to the point, I think that SQLite can help you get something out and easily deployable, and is not hard to move off of later. [0]: https://sqlite.org/wal.html https://sqlite.org/wal.html [1]: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- beatgammit 7y ago
  We used SQLite for a while until we were limited by IO on our crappy SD cards for our embedded product and wanted a database server with more tuning options. Before that point, it was a very capable database, she I wouldn't hesitate to use it for pretty much any product that doesn't need high performance, cross thread access (basically any crud app) or the ability to run in a cluster/dedicated server (e.g. high storage requirements). It's fast, simple, and easy. Perhaps my favorite feature is that backups are just a copy (in fact, when I'm debugging an app, I often copy the database into a temp directory and go to town). I totally agree that SQLite is a great first option for startups. If your database will fit on your filesystem (gigabytes, not terabytes), it'll probably do really well. It's really easy to switch to something like Postgres later, and it's really nice to not have to worry about the complexities of a database server until you actually need them (e.g. you're considering hiring dedicated IT/DBA staff instead of just developers). And there's a good chance you'll never need anything other than SQLite.
- bob1029 7y ago
  I am glad to see other SQLite advocates out here. >Many modern startups could get pretty far with SQLite + aggressive caching Not only did we get "pretty far", we got to what is effectively a final destination for this generation of our product. We don't even need "aggressive caching" beyond what the filesystem provides to us by way of accessing the SQLite db file. Today, we have a single SQLite database persisting stateful data for an application that is used concurrently by 100+ users. I have benchmarked 512KB state update writes in a multithreaded scenario at >500 per second. 64KB updates exceed 1000 per second. It took us a lot of time and understanding to get to this point. At the end of the day with SQLite, less is always more. The first attempts we did involved connection pools, queues and all kinds of other fancy over-engineering in an attempt to extract more performance. We finally ended up with just the following: - Enable Write Ahead Logging - Set Synchronous to Normal - Use a single SQLiteConnection Justification for a single connection: We are using .NET with application-level locking and an RWLS where appropriate. This is in contrast to relying on the database to push these transactional scopes to disk. For us, losing an in-flight transaction tracked in memory is not catastrophic so we can tolerate this 1-100ms danger zone for the huge upside we get in throughput. This is a great example of something you can only do with SQLite. No hosted database solution offers this degree of exclusivity over your data store from an application perspective. One other application note to keep in mind is that SQLite (by default) operates in serialized mode, so the above approach is consistent regardless of the fact that we are hitting a single connection from multiple threads at once. The usages of RWLS are required in our case simply because we need to do multiple dependent sequential operations (select+modify+update) or insertion of new items (insert+last insert row id from connection). It is hypothetically possible to avoid locking altogether (aside from the implicit SQLite serialization) if you can structure your database such that you perform all operations in a single command - E.g.: Use application-generated keys upon insertion rather than waiting for the DB to generate one. Also, backups and restores are super trivial as mentioned elsewhere in this discussion. I have considered writing a backup agent directly into our software that is transactionally-aware. E.g.: During a backup request, I can take out a write lock on the RWLS for the SQLiteConnection while copying the database to a backup destination. Alternatively, since the DB lives on the same machine as the application, you can just take a single VM snapshot throughout the day, but this won't be as consistent a backup (tolerable for our use case though). The biggest advantage we see with using SQLite as our principal data store is that it has allowed us to completely avoid operational annoyances regarding how we actually ship our stack to various environments. .NET Self-Contained Deployments + SQLite = one bin folder that "just works". We were able to build custom deployment tooling because of the simplicity of this model, rather than relying on off-the-shelf tech that doesn't quite fit 100% into our use case.
insulanian 7y ago
We have started a project with SQLite as the data store, but ended up replacing it with Firebird embedded due to two main reasons: - Lost decimal places on a simple database round-trip. - Issues with accessing the DB from multiple threads.
- gigatexal 7y ago
  Yeah... many readers but only one writer at a given time. We got around that by doing a SQLite db per user.
  - ramraj07 7y ago
    Once conceived of a plan to store a lot of data per user and thought of the same idea. How do you store the dbs? Keeping it on say s3 means there's a multisecond (or longer) load time when a user logs in and you need to load the DB to the hard drive right? (I'm thinking a GB or more data per user).I considered an ec2 instance with a multi terabyte ebs attached, that can then effectively store a thousand users: data. Are there any other possibilities?
    - gigatexal 7y ago
      Our DBs are tiny for most users. We run s3 (minio rook ceph on k8s) locally so the network latency is a cluster latency. I figure you could just throw hardware at it like you mentioned. Move them to nvme backed S3 if needed. And our use case is only ever load, do a read or write, and then save. So they DBs aren’t open for very long. And with S3 compression you could save on download time but pay a decompress cost. This approach has its downsides don’t get me wrong but it scales nicely but forget running aggregates across the databases at least not for a real-time result.
      - xyzzy_plugh 7y ago
        Put sqlite in redis.
        7y ago
ISO-morphism 7y ago
I remember reading once upon a time about the sqlite file format being a good candidate for data transfer between systems/machines, as it's standardized, stable, and infinitely queryable. Has anyone here had any experience actually using it as such? What are your takeaways?
- mroos 7y ago
  We have been using SQLite as a distributed object container for the last few years as part of an industry spec. <http://www1.semi.org/en/ritdb-interplanetary-database-manufacturing> http://www1.semi.org/en/ritdb-interplanetary-database-manufa.... Has been working quite well on windows, OS X and Linux. Also java, r, c, js, python clients. We are using MQTT as the transport.
  - pdimitar 7y ago
    Your link gives 404.
    - bpicolo 7y ago
      Remove the >
- beagle3 7y ago
  Yes, it works very well. If you have only one thread/process, use it as is; if you need multiple readers and one writer (who do not block each other), use WAL mode; if you need simultaneous multiple readers and writers, use Postgres. If you are using SQLite, be aware that the “copy the file to move it to a different system” comes with a caveat - a regular Unix “cp” may get the file in an inconsistent state if it was modified while copying, or in the middle of a transaction. Iirc “rsync” if used to copy, will verify that the file was not modified while copying and restart the copy if it did (a number of times and then error out). But that’s still not enough - you may need to also copy the shm file (in wal mode) and the journal file - and you need to do that atomically. Which is harder still. The right way to copy an SQLite file from place to place us: Any way you like, if it was properly committed and closed last time it was used, and is not currently open by any other process; Or creating a backup (e.g. with the sqlite3 command), which will be a nonblocking operation if in WAL more - and then you can ship the backup whichever way you want.
deleted 7y ago
[deleted]
rootw0rm 7y ago
SQLite kicks ass. Oh, the horrible things I've attempted to make it do...
tlamponi 7y ago
SQLite is the most stable small DB there is, maybe even the most stable one at all. Our CTO made a cluster wide distributed configuration filesystem[0], started about 9 years ago. For that a local backing store was required, and a a small DB seemed to fit the bill as it should allow to worry less about data safety, when it's really written out, and it could be a general interface which could be swapped out rather easily if required one day. He checked out lots of the then available options, sorry I only remember BerkleyDB and SQLite but there were more. Anyway, only SQLite never crashed, had corrupt data when doing weird things with it and was still very fast, just great. Almost 10 years later we still use it, never a single issue with SQLite as the cause - really impressive IMO. [0]: https://git.proxmox.com/?p=pve-cluster.git;a=tree;f=data/src;h=67601f78bf53763092ead57e55ceb1a2428891a2;hb=refs/heads/master https://git.proxmox.com/?p=pve-cluster.git;a=tree;f=data/src...
shafte 7y ago
Aside from its merits as a database, SQLite is also a great example of good documentation[0], thorough testing[1], and focused development. It's something that all library maintainers should study and try to learn from. [0] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html [1] https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html
jimnotgym 7y ago
I used to do a fair amount of ad hoc analysis with large (more than Excel can handle is large enough) csv files. Sqlite3 was my saviour. It was the work of minutes to have the data in a db and be chopping out the bits I needed.
- speedplane 7y ago
  > I used to do a fair amount of ad hoc analysis with large (more than Excel can handle is large enough) csv files. Sqlite3 was my saviour. This was probably the right decision 5 years ago, but I'm not sure Sqllite is the proper solution for "ad hoc" analysis now. Excel now has plugins to handle very large datasources. And of course, the company leading this charge here is Tableau, with many others doing something similar. Sqllite can still do all the manipulation that these others can, but the others can also be used by non developers and come with pretty graphs and pictures. For large CSV analysis on an ad hoc basis, SQLlite doesn't stack up well to the competition.
  - snthpy 7y ago
    As someone who has to use Excel a lot, I'm very interested in what your typical workflow looks like.
    - speedplane 7y ago
      > As someone who has to use Excel a lot, I'm very interested in what your typical workflow looks like. It's pretty standard Excel stuff: summing columns, averaging, functions combining multiple columns, pivot tables. The significant difference is that I routinely deal with 20k+ rows, and occasionally 100k+ rows. For simple stuff, standard Excel starts breaking down with 10k+ rows, and it even struggles with 1k rows with more complicated manipulation.
  - e12e 7y ago
    I'm not sure - but if you're in the MS world, you might want to just import that csv file into PowerBI?
- 7y ago
etaioinshrdlu 7y ago
So, I've heard SQLite described as "Postgres for people not paying attention", because it ignores data type errors. A lot of people in the HN community like types. Me included. I really don't want my database to ignore type errors. I basically want Postgres but embedded like a library. I cannot think of any reason why that couldn't work. Perhaps just no one has built it yet.
- js8 7y ago
  I think it's a design choice. Ignoring types lets you focus on other features, in case of SQLite, things like crash resistance and reliability. In other words, typechecking is also a cost for language designer. Even SQL itself has not particularly strong type system. Perhaps you're looking for something else entirely.
  - justinclift 7y ago
    Interestingly, it's amazing to me that many SQLite libraries in the Go ecosystem don't support these mixed/flexible data types in tables. If you read back a set of values and one of the fields in a row doesn't match the data type given by the column, the libraries will error out. One library that works correctly is github.com/gwenn/gosqlite, which checks the data type of each field of the result set (per row), so handles things gracefully.
  - suyjuris 7y ago
    SQLite originated as a TCL extension, so it would make sense for it to share TCL's (notoriously lax) philosophy regarding types.
- zokier 7y ago
  I have recently been thinking if it were possible to marry pg query engine with sqlite storage layer and c api. Idea being that you could get all the rich types etc from pg with the embeddability of sqlite.
- e12e 7y ago
  Oh, that's a bit scary. Both for use as a file format (in case of attacker supplied db file) and more general. I see that even with "INTEGER" affinity, I can insert and read back text to columns :/ sqlite :memory: CREATE TABLE test( x INTEGER, y TEXT); INSERT INTO test VALUES("a", "b"); SELECT * FROM test; a|b https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
0x76 7y ago
I would definitely use it more if it played nicer having it's DB on a nfs share. Although I don't know the underlying cause so it may be infeasible.
fauigerzigerk 7y ago
I would be using Sqlite a lot more if it had a decimal type for monetary calculations.
- nosezinc 7y ago
  Coming in sqlite4...
  - ngrilly 7y ago
    Nope. > SQLite4 was an experimental rewrite of SQLite that was active from 2012 through 2014. All development work on SQLite4 has ended. Lessons learned from SQLite4 have been folded into the main SQLite3 product. SQLite4 was never released. There are no plans to revive it. You should be using SQLite3. Source: https://sqlite.org/src4/doc/trunk/www/ https://sqlite.org/src4/doc/trunk/www/
alkonaut 7y ago
I wish SQLite would move to github with issue tracking. Digging trough their issues to find if my particular issue is reported is not as pleasant as with github issues. Specifically I’m now wondering if a case-insensitive replace() string function is already a feature request or perhaps even implemented in a recent version.
silvestrov 7y ago
I'm really hoping we one day will get ALTER TABLE x DROP COLUMN y.
- foxhop 7y ago
  Me too, my only issues I have running SQLite3 in production is database migrations where I need to drop or change a column
rcarmo 7y ago
Every time I have to set up a Java runtime on Windows and click through Oracle's boast of it running on 3 (US) billion devices (which pops up on the installer) I think of how SQLite probably is at least a thousand billion in front :) Great to see FILTER on aggregates, solves a small pain I have with my home automation stats :)
mikece 7y ago
Something else I appreciate about SQLite: it proves that under the ultimate free software distribution scheme -- public domain -- corporate users contribute improvements back to the project.
no_wizard 7y ago
How does SQLite compare to say BerkeleyDB or LevelDB, which I think compete in this same arena no? I’ve used SQLite and like it a lot but I’ve recently started using these two for some pet projects and found them easier to work with and in theory they should scale easier if I understand their strengths
- zzzcpan 7y ago
  It depends on your use case. LevelDB is way too sloppy about memory allocation, threads and I/O, which may not be acceptable for an embeddable database. I got to the point once where every daemon on every node using LevelDB had to be restarted daily from cron.

393 points by QuadrupleA 7y ago

I use FILTER once in a while on a postgres database I work with, so I am happy to see it added to SQLite

Glad to see SQLite on then front page -- it's one of the silent workhorses of the modern programming/database world. It is the most widely deployed database[0]. If you haven't given it a look/aren't interested in it since it seems to be a "toy" database (often the "test" or "local" db for frameworks like rails or django), you owe it to yourself to see what it can really do. Easy to use full text search[1][2], CTEs[3], JSON support via extension[4] (also the extension system is worth looking at[5]) and much much more. There are certainly things that SQLite does not do, and that's well documented too[6]. If all this doesn't convince you to use SQLite, it's also one of the most well documented large C codebases that is fantastic to learn from. I'd go as far as to say that many modern startups could get pretty far with SQLite + aggressive caching before even bringing in a big database like Postgres (though with recent deployment/ops advancements it's easier than ever to run postgres). [0]: https://sqlite.org/mostdeployed.html https://sqlite.org/mostdeployed.html [1]: https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html [2]: https://sqlite.org/fts3.html https://sqlite.org/fts3.html [3]: https://sqlite.org/lang_with.html https://sqlite.org/lang_with.html [4]: https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html [5]: https://www.sqlite.org/loadext.html https://www.sqlite.org/loadext.html [6]: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

dmarlow 7y ago
Apparently clustering too https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
- minxomat 7y ago
  Hardly, that is more of a hack. There is however a distributed, clustered version of sqlite: Bloomberg's Comdb2. It has the same scaling factors as e.g. percona, can be Geo-replicated (multi-roomed) and has many advanced timing and financial data types. Plus SQL functions can be written in Lua. Oh, and it has super granular serialization guarantees, up to and including full linearizibility. By using Sqlite as the SQL frontend, it retains features other NewSQL engines struggle with (recursive CTE, deep optimization, compatibility with existing queries etc)
  - e12e 7y ago
    Wow, thats: https://github.com/bloomberg/comdb2 https://github.com/bloomberg/comdb2 Looks interesting. Any experience using it in real projects?
    - minxomat 7y ago
      Yeah it works well. I also sometimes push code into the db for faster execution (sorry on mobile, can't detail now). Haven't tested out Geo replication though, but it's pretty straightforward. Clock drift maximum and all that.
- hardwaresofton 7y ago
  Also there is dqlite[0]. I really do think that SQLite can go even farther than people think, but the exploration makes less and less sense the better and easier to run options like postgres become. That said, I'm glad projects like dqlite and rqlite exist. There are also previous threads discussing them but here's one[1]. [0]: https://github.com/CanonicalLtd/dqlite https://github.com/CanonicalLtd/dqlite [1]: https://news.ycombinator.com/item?id=20836331 https://news.ycombinator.com/item?id=20836331
  - speedplane 7y ago
    > I really do think that SQLite can go even farther than people think I know different databases support different features (e.g., atomic transactions, ACID compliance, etc.), but it seems like the big differentiator is size, namely: 1. Can your database live within a single computer's RAM? 2. Can your database live within a single computer? 3. Can your database live across a cluster of computers in a single data center? 4. Can your database live across a cluster of computers spread across the planet? Once you pick your category from the above, there are still choices to be made, but the "best" options narrow down significantly.
    - hardwaresofton 7y ago
      I'd argue that feature set (transactions, ACID, etc) are a bigger differentiator because they drive/change application development -- this can have drastic effects on application features and complexity whether immediately or down the road. I agree that the possible second biggest (if not the first) differentiator is size though -- despite the amount of choices out there, once you start narrowing down answering those questions, many options fall away very quickly. I'd also add on that the only real way to scale a database is to shard. There are a bunch of tricks you can do to improve throughput and those are always welcome, and sometimes I'll see articles that proclaim "we just changed to a system with 256GB of RAM to fix our scaling problem!" -- but these improvements don't get past the basic premise that once your data does not fit on one computer/harddrive you need to start figuring out a way for multiple to coordinate. You need to shard, and you need to organize your data in such a way that it can shard, then you need to start worrying about data access patterns. There's a lot of space before something like this is necessary (you really do have to have a lot of data for it to not fit on one machine these days), but I think people don't see this chain coming fast/often enough.
dmarlow 7y ago
I only wish I could do some basic things without having to create temporary tables and copy data around.
- gigatexal 7y ago
  Yeah that’s likely the one thing that keeps it from being perfect.
- hardwaresofton 7y ago
  I'm not sure exactly what the use case is but another awesome tool that's recently been making the rounds is datasette[0], it provides an easy to use data exploration point for SQLite databases. There's also a PyCon 2019 talk[1]. [0]: https://datasette.readthedocs.io/en/stable https://datasette.readthedocs.io/en/stable [1]: https://www.youtube.com/watch?v=pTr1uLQTJNE https://www.youtube.com/watch?v=pTr1uLQTJNE
kragen 7y ago
I only learned this week that it supports recursive CTEs (although apparently you can't refer to them in subqueries yet?) and only yesterday that you can do this: >>> import sqlite3 >>> con = sqlite3.connect(':memory:') >>> cur = con.cursor() >>> q = '''select x, sum(a * power(x, e)) ... from (select -2 as x union select -1 union select 0 union select 1 union select 2) xt, ... (select 1 as a, 2 as e union select 4, 1 union select 2, 0) et ... group by x''' >>> cur.execute(q).fetchall() Traceback (most recent call last): File "<stdin>", line 4, in <module> sqlite3.OperationalError: no such function: power >>> con.create_function('power', 2, lambda base, power: base**power) >>> cur.execute(q).fetchall() [(-2, -2), (-1, -1), (0, 2), (1, 7), (2, 14)] That is freaking cool. Not evaluating polynomials in SQL, I mean, but defining a user-defined function for SQL in one line of Python.
- emj 7y ago
  That is cool; Being able to call python code from SQL is very cool. Is it very slow?
  - kyberias 7y ago
    Being able to call python code from SQL is super scary!
    - sbierwagen 7y ago
      What attacks do you believe it enables?
  - oefrha 7y ago
    It simply uses the sqlite3_create_function API, so it’s as fast/slow as your Python function. You can even use Cython if pure Python isn’t fast enough. I use UDFs quite a bit and the performance is fine without extra tweaks most of the time, but for something like pow I’d use the math extension instead which would be way more performant. Btw, user-defined scalar functions aren’t remotely as sexy as user-defined window functions.
steve_adams_86 7y ago
Checkfront, a booking management system, got by on sqlite up until maybe 2 years ago - it was the solution for 5 or more years! These days it runs on postgres but it went very far without it.
Scarbutt 7y ago
But why?(for a server) Postgres can run in a 256MB VPS(if one can find one these days) and has more features that you might need later. Installing is as easy as 'apt-get install postgresql', create user, create database, done.
- xyzzy_plugh 7y ago
  Furthermore, postgres has great replication, and a read-replica is trivial to set up. sqlite is amazing (for any embedded database, absolutely) but postgres can't be beat as a go to for server side.
  - cyphar 7y ago
    To paraphrase the SQLite site: "SQLite isn't competing with [PostgreSQL]; it's competing with fopen(3)." The primary purpose is to replace custom (and fragile) single-file databases with something that is far more resilient and provides uniformity in implementation. The fact that it is a suprisingly good SQL database (to the point where folks are actively arguing that much more fully-fledged databases are better in some aspects) is incidental.
- hardwaresofton 7y ago
  SQLite is even easier to start with -- I'm not trying to say Postgres is not the right choice, but it is certainly a more complex choice. Postgres is a fantastic database, but most of the time getting started with and using SQLite will be easier. For example -- you'd never have to secure firewall rules to a SQLite instance. Bad defaults (unlikely but possible) or some bad post-install configuration and you've got an exposed server. I want to emphasize that my point about SQLite being viable for startups was with the assumption of a low-write high read workload -- most of the time queries won't even hit the DB but instead be served by some caching layer. It's also really easy to scale read only versions of your applications horizontally if all you've got to do is ship/swap out DB files (again at this point it might make more sense to look into a bigger DB and just scaling vertically).
- throwaway_bad 7y ago
  I have a small blog (using Ghost) that uses sqlite as the main database and it's incredibly easy to maintain. It's just a single file. For example if you want to back up, you just copy the file or add it to git. If you want to do a diff you just do sqldiff on two files. It is as easy as working with json or csv files, except with atomic writes and better querying. From their site: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html >SQLite does not compete with client/server databases. SQLite competes with fopen().
saagarjha 7y ago
> If you haven't given it a look/aren't interested in it since it seems to be a "toy" database As you mentioned, it ships in every major browser, most OSes, and a number of extremely popular instances of consumer software. If that's still a "toy" I don't know what isn't…
erik_seaberg 7y ago
sqlite can be handy but it's not a substitute for testing against a staging instance of another database platform. It ignores a lot of mistakes in column types and size limits and sometimes even foreign keys. If anything, I would want tests to be more strict than prod.
- hardwaresofton 7y ago
  Your point is correct, but I'm afraid you might not have understood what I meant -- I meant that people should be considering using SQLite in production. Agreed that testing against copies of production or staging instances that are as close as possible to production is important, but what I'm suggesting is that you could ride SQLite all the way into production if you wanted to. I'd love to hear more about the ignoring column types/size limit/other issues that you've run into though, have you written about them anywhere or is there an SO post? I haven't run into anything super egregious though I've had some problems with FKs before.
  - ris 7y ago
    Do a GROUP BY, and then try referencing a term which hasn't been either aggregated or mentioned in the GROUP BY clause. The result should be "abort! this query can't make sense!". The result (last I tried) is instead "sure, here's some arbitrary value from this group".
- marmaduke 7y ago
  It seeks to replace fopen() not postgres.
- justinclift 7y ago
  Foreign keys are supported with SQLite version 3.6.19 onward (~late 2009), though not enabled by default. https://sqlite.org/foreignkeys.html https://sqlite.org/foreignkeys.html If you can add "PRAGMA foreign_keys = ON;" to your testing script SQL calls - somewhere soon after connecting to SQLite - that enables it.
siscia 7y ago
Indeed, I believe as well that SQLite is underused in a lot of situations. Which is mostly the reason why I created RediSQL[1], to allow a more widespread use of it also in "modern"/"cloud native" application where you want to just connect to a database via the network, ideally through common API like the Redis one. It has the great benefit, that if you create your database in memory is crazy fast, and it retains the persistency properties of Redis, which are good enough in most cases. [1] https://RediSQL.com https://RediSQL.com
- jeff_redradish 7y ago
  Just FYI, on Chrome a 'Subscribe to the mail list' pop-up appears, and cannot be dismissed by clicking the X. Hitting Escape works.
  - siscia 7y ago
    Thanks so much! Sorry for the annoyance!
    - philips 7y ago
      Safari on iPadOS as well.
ngrilly 7y ago
> I'd go as far as to say that many modern startups could get pretty far with SQLite + aggressive caching before even bringing in a big database like Postgres Modern startups need their services to be available, and this is difficult to achieve with SQLite because you can't run long database migrations without blocking writes during the migration. Personally, I'd use SQLite more often if there was a good solution to this (and also DROP COLUMN).
- hardwaresofton 7y ago
  My statement was definitely a reach, but I think there is a bunch of space in the life of an early startup for lack of highly available services. I've found startups to prioritize time-to-market (or time to PoC) over technical purity -- there's usually lots of unplanned outages early on that often have nothing to do with the database or are a result of bad application code/misunderstood requirements as well, so I'm not fully buying the availability emphasis, but a few things that I think could counteract it: - I'm not sure I agree that the delay of the blocking write from a migration is so long as to cause significant (if any) damage to the business. - If SQLite makes development and deployment easier (I think it does), this becomes a tradeoff between a deployment downtime/partial operation window likely on the order of milliseconds and hours of time spent setting up/managing the alternatives. With really robust systems like Postgres out there this is a pretty weak argument on my part, but there is at least something there. - As I've also mentioned elsewhere, a high read-to-write workload would be less affected by this problem. If your startup produces aggregated business analytics dashboards for manufacturing plant stakeholders for example, your data set could be completely static. - WAL mode[0] improves SQLite's resiliency to concurrent readers/writers. - In the extreme case, you could treat SQLite as a hybrid row & JSON document store, and manage your own schemas (I do not endorse this approach), but you'd still be saving some time because you can do stuff like make use of SQLite's FTS search features, and you'd have access to other relational features that you might not get with other document stores. All this said, SQLite is not for lots of concurrent writers, and they so themselves[1], but I think it can absolutely be done. More to the point, I think that SQLite can help you get something out and easily deployable, and is not hard to move off of later. [0]: https://sqlite.org/wal.html https://sqlite.org/wal.html [1]: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
beatgammit 7y ago
We used SQLite for a while until we were limited by IO on our crappy SD cards for our embedded product and wanted a database server with more tuning options. Before that point, it was a very capable database, she I wouldn't hesitate to use it for pretty much any product that doesn't need high performance, cross thread access (basically any crud app) or the ability to run in a cluster/dedicated server (e.g. high storage requirements). It's fast, simple, and easy. Perhaps my favorite feature is that backups are just a copy (in fact, when I'm debugging an app, I often copy the database into a temp directory and go to town). I totally agree that SQLite is a great first option for startups. If your database will fit on your filesystem (gigabytes, not terabytes), it'll probably do really well. It's really easy to switch to something like Postgres later, and it's really nice to not have to worry about the complexities of a database server until you actually need them (e.g. you're considering hiring dedicated IT/DBA staff instead of just developers). And there's a good chance you'll never need anything other than SQLite.
bob1029 7y ago
I am glad to see other SQLite advocates out here. >Many modern startups could get pretty far with SQLite + aggressive caching Not only did we get "pretty far", we got to what is effectively a final destination for this generation of our product. We don't even need "aggressive caching" beyond what the filesystem provides to us by way of accessing the SQLite db file. Today, we have a single SQLite database persisting stateful data for an application that is used concurrently by 100+ users. I have benchmarked 512KB state update writes in a multithreaded scenario at >500 per second. 64KB updates exceed 1000 per second. It took us a lot of time and understanding to get to this point. At the end of the day with SQLite, less is always more. The first attempts we did involved connection pools, queues and all kinds of other fancy over-engineering in an attempt to extract more performance. We finally ended up with just the following: - Enable Write Ahead Logging - Set Synchronous to Normal - Use a single SQLiteConnection Justification for a single connection: We are using .NET with application-level locking and an RWLS where appropriate. This is in contrast to relying on the database to push these transactional scopes to disk. For us, losing an in-flight transaction tracked in memory is not catastrophic so we can tolerate this 1-100ms danger zone for the huge upside we get in throughput. This is a great example of something you can only do with SQLite. No hosted database solution offers this degree of exclusivity over your data store from an application perspective. One other application note to keep in mind is that SQLite (by default) operates in serialized mode, so the above approach is consistent regardless of the fact that we are hitting a single connection from multiple threads at once. The usages of RWLS are required in our case simply because we need to do multiple dependent sequential operations (select+modify+update) or insertion of new items (insert+last insert row id from connection). It is hypothetically possible to avoid locking altogether (aside from the implicit SQLite serialization) if you can structure your database such that you perform all operations in a single command - E.g.: Use application-generated keys upon insertion rather than waiting for the DB to generate one. Also, backups and restores are super trivial as mentioned elsewhere in this discussion. I have considered writing a backup agent directly into our software that is transactionally-aware. E.g.: During a backup request, I can take out a write lock on the RWLS for the SQLiteConnection while copying the database to a backup destination. Alternatively, since the DB lives on the same machine as the application, you can just take a single VM snapshot throughout the day, but this won't be as consistent a backup (tolerable for our use case though). The biggest advantage we see with using SQLite as our principal data store is that it has allowed us to completely avoid operational annoyances regarding how we actually ship our stack to various environments. .NET Self-Contained Deployments + SQLite = one bin folder that "just works". We were able to build custom deployment tooling because of the simplicity of this model, rather than relying on off-the-shelf tech that doesn't quite fit 100% into our use case.

insulanian 7y ago

We have started a project with SQLite as the data store, but ended up replacing it with Firebird embedded due to two main reasons: - Lost decimal places on a simple database round-trip. - Issues with accessing the DB from multiple threads.

gigatexal 7y ago
Yeah... many readers but only one writer at a given time. We got around that by doing a SQLite db per user.
- ramraj07 7y ago
  Once conceived of a plan to store a lot of data per user and thought of the same idea. How do you store the dbs? Keeping it on say s3 means there's a multisecond (or longer) load time when a user logs in and you need to load the DB to the hard drive right? (I'm thinking a GB or more data per user).I considered an ec2 instance with a multi terabyte ebs attached, that can then effectively store a thousand users: data. Are there any other possibilities?
  - gigatexal 7y ago
    Our DBs are tiny for most users. We run s3 (minio rook ceph on k8s) locally so the network latency is a cluster latency. I figure you could just throw hardware at it like you mentioned. Move them to nvme backed S3 if needed. And our use case is only ever load, do a read or write, and then save. So they DBs aren’t open for very long. And with S3 compression you could save on download time but pay a decompress cost. This approach has its downsides don’t get me wrong but it scales nicely but forget running aggregates across the databases at least not for a real-time result.
    - xyzzy_plugh 7y ago
      Put sqlite in redis.
      - 7y ago

ISO-morphism 7y ago

I remember reading once upon a time about the sqlite file format being a good candidate for data transfer between systems/machines, as it's standardized, stable, and infinitely queryable. Has anyone here had any experience actually using it as such? What are your takeaways?

mroos 7y ago
We have been using SQLite as a distributed object container for the last few years as part of an industry spec. <http://www1.semi.org/en/ritdb-interplanetary-database-manufacturing> http://www1.semi.org/en/ritdb-interplanetary-database-manufa.... Has been working quite well on windows, OS X and Linux. Also java, r, c, js, python clients. We are using MQTT as the transport.
- pdimitar 7y ago
  Your link gives 404.
  - bpicolo 7y ago
    Remove the >
beagle3 7y ago
Yes, it works very well. If you have only one thread/process, use it as is; if you need multiple readers and one writer (who do not block each other), use WAL mode; if you need simultaneous multiple readers and writers, use Postgres. If you are using SQLite, be aware that the “copy the file to move it to a different system” comes with a caveat - a regular Unix “cp” may get the file in an inconsistent state if it was modified while copying, or in the middle of a transaction. Iirc “rsync” if used to copy, will verify that the file was not modified while copying and restart the copy if it did (a number of times and then error out). But that’s still not enough - you may need to also copy the shm file (in wal mode) and the journal file - and you need to do that atomically. Which is harder still. The right way to copy an SQLite file from place to place us: Any way you like, if it was properly committed and closed last time it was used, and is not currently open by any other process; Or creating a backup (e.g. with the sqlite3 command), which will be a nonblocking operation if in WAL more - and then you can ship the backup whichever way you want.

deleted 7y ago

[deleted]

rootw0rm 7y ago

SQLite kicks ass. Oh, the horrible things I've attempted to make it do...

tlamponi 7y ago

SQLite is the most stable small DB there is, maybe even the most stable one at all. Our CTO made a cluster wide distributed configuration filesystem[0], started about 9 years ago. For that a local backing store was required, and a a small DB seemed to fit the bill as it should allow to worry less about data safety, when it's really written out, and it could be a general interface which could be swapped out rather easily if required one day. He checked out lots of the then available options, sorry I only remember BerkleyDB and SQLite but there were more. Anyway, only SQLite never crashed, had corrupt data when doing weird things with it and was still very fast, just great. Almost 10 years later we still use it, never a single issue with SQLite as the cause - really impressive IMO. [0]: https://git.proxmox.com/?p=pve-cluster.git;a=tree;f=data/src;h=67601f78bf53763092ead57e55ceb1a2428891a2;hb=refs/heads/master https://git.proxmox.com/?p=pve-cluster.git;a=tree;f=data/src...

shafte 7y ago

Aside from its merits as a database, SQLite is also a great example of good documentation[0], thorough testing[1], and focused development. It's something that all library maintainers should study and try to learn from. [0] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html [1] https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html

jimnotgym 7y ago

I used to do a fair amount of ad hoc analysis with large (more than Excel can handle is large enough) csv files. Sqlite3 was my saviour. It was the work of minutes to have the data in a db and be chopping out the bits I needed.

speedplane 7y ago
> I used to do a fair amount of ad hoc analysis with large (more than Excel can handle is large enough) csv files. Sqlite3 was my saviour. This was probably the right decision 5 years ago, but I'm not sure Sqllite is the proper solution for "ad hoc" analysis now. Excel now has plugins to handle very large datasources. And of course, the company leading this charge here is Tableau, with many others doing something similar. Sqllite can still do all the manipulation that these others can, but the others can also be used by non developers and come with pretty graphs and pictures. For large CSV analysis on an ad hoc basis, SQLlite doesn't stack up well to the competition.
- snthpy 7y ago
  As someone who has to use Excel a lot, I'm very interested in what your typical workflow looks like.
  - speedplane 7y ago
    > As someone who has to use Excel a lot, I'm very interested in what your typical workflow looks like. It's pretty standard Excel stuff: summing columns, averaging, functions combining multiple columns, pivot tables. The significant difference is that I routinely deal with 20k+ rows, and occasionally 100k+ rows. For simple stuff, standard Excel starts breaking down with 10k+ rows, and it even struggles with 1k rows with more complicated manipulation.
- e12e 7y ago
  I'm not sure - but if you're in the MS world, you might want to just import that csv file into PowerBI?
7y ago

etaioinshrdlu 7y ago

So, I've heard SQLite described as "Postgres for people not paying attention", because it ignores data type errors. A lot of people in the HN community like types. Me included. I really don't want my database to ignore type errors. I basically want Postgres but embedded like a library. I cannot think of any reason why that couldn't work. Perhaps just no one has built it yet.

js8 7y ago
I think it's a design choice. Ignoring types lets you focus on other features, in case of SQLite, things like crash resistance and reliability. In other words, typechecking is also a cost for language designer. Even SQL itself has not particularly strong type system. Perhaps you're looking for something else entirely.
- justinclift 7y ago
  Interestingly, it's amazing to me that many SQLite libraries in the Go ecosystem don't support these mixed/flexible data types in tables. If you read back a set of values and one of the fields in a row doesn't match the data type given by the column, the libraries will error out. One library that works correctly is github.com/gwenn/gosqlite, which checks the data type of each field of the result set (per row), so handles things gracefully.
- suyjuris 7y ago
  SQLite originated as a TCL extension, so it would make sense for it to share TCL's (notoriously lax) philosophy regarding types.
zokier 7y ago
I have recently been thinking if it were possible to marry pg query engine with sqlite storage layer and c api. Idea being that you could get all the rich types etc from pg with the embeddability of sqlite.
e12e 7y ago
Oh, that's a bit scary. Both for use as a file format (in case of attacker supplied db file) and more general. I see that even with "INTEGER" affinity, I can insert and read back text to columns :/ sqlite :memory: CREATE TABLE test( x INTEGER, y TEXT); INSERT INTO test VALUES("a", "b"); SELECT * FROM test; a|b https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html

0x76 7y ago

I would definitely use it more if it played nicer having it's DB on a nfs share. Although I don't know the underlying cause so it may be infeasible.

fauigerzigerk 7y ago

I would be using Sqlite a lot more if it had a decimal type for monetary calculations.

nosezinc 7y ago
Coming in sqlite4...
- ngrilly 7y ago
  Nope. > SQLite4 was an experimental rewrite of SQLite that was active from 2012 through 2014. All development work on SQLite4 has ended. Lessons learned from SQLite4 have been folded into the main SQLite3 product. SQLite4 was never released. There are no plans to revive it. You should be using SQLite3. Source: https://sqlite.org/src4/doc/trunk/www/ https://sqlite.org/src4/doc/trunk/www/

alkonaut 7y ago

I wish SQLite would move to github with issue tracking. Digging trough their issues to find if my particular issue is reported is not as pleasant as with github issues. Specifically I’m now wondering if a case-insensitive replace() string function is already a feature request or perhaps even implemented in a recent version.

silvestrov 7y ago

I'm really hoping we one day will get ALTER TABLE x DROP COLUMN y.

foxhop 7y ago
Me too, my only issues I have running SQLite3 in production is database migrations where I need to drop or change a column

rcarmo 7y ago

Every time I have to set up a Java runtime on Windows and click through Oracle's boast of it running on 3 (US) billion devices (which pops up on the installer) I think of how SQLite probably is at least a thousand billion in front :) Great to see FILTER on aggregates, solves a small pain I have with my home automation stats :)

mikece 7y ago

Something else I appreciate about SQLite: it proves that under the ultimate free software distribution scheme -- public domain -- corporate users contribute improvements back to the project.

no_wizard 7y ago

How does SQLite compare to say BerkeleyDB or LevelDB, which I think compete in this same arena no? I’ve used SQLite and like it a lot but I’ve recently started using these two for some pet projects and found them easier to work with and in theory they should scale easier if I understand their strengths

zzzcpan 7y ago
It depends on your use case. LevelDB is way too sloppy about memory allocation, threads and I/O, which may not be acceptable for an embeddable database. I got to the point once where every daemon on every node using LevelDB had to be restarted daily from cron.