12 ms·
SQLite 3.32
- nattaylor 6y agoA few nice little conveniences like IFF(). I like reading SQLite released because they seem good at avoiding adding cruft. (The refusal to implement JSONB comes to mind.) Now if only I could get my shared web host to upgrade to a recent version...
- jventura 6y agoIf you have ssh access to your web host, you may be able to upgrade it yourself. I needed something more recent for django 2.2 and had to download the latest sqlite, compile it, put the lib in some folder and add the lib to .bashrc so that python3 could use it (ld_include_flags or something like that). Look for it on google, it’s possible to do it..
- simonw 6y agoMy Google skills are failing me here, can you provide any more details? I'm very interested in knowing tricks to upgrade the SQLite version used by Python.
- jventura 6y agoI’m not in my computer now but I’ll look for a link..
- icegreentea2 6y agoYou set the LD_LIBRARY_PATH environment variable (https://unix.stackexchange.com/a/24833 https://unix.stackexchange.com/a/24833). Specifically, you'll need to recompile libsqlite3, put it somewhere, and then set LD_LIBRARY_PATH before invoking Python. You can do that globally in your shell by modifying your .bashrc or similar file. Or if you're super brave, you just replace the libsqlite3.so that Python is pointing to (really depends on your use case).
- oefrha 6y agoGood to see a ternary function iif() added. Case expressions are usually pretty painful and/or unreadable when using query builders.
- iagovar 6y agoAs a non-dev intruder I have to say that I love SQLite. I do a lot of data-analysis and it makes everything easy, from fast SQL Wizardry to sharing the DB just coping a file! Just how amazing is that?! It must sound naive to some of you, but the first time stumbled upn sqlite I was so excited!
- dragonshed 6y agoI totally agree. I'm a front-end dev that can wing backend from time to time, and I use SQLite as much as possible. On multiple projects now I've run into complications due to complexity or environments, and adding a simplified local development backend with sqlite kept down time to a minimum. SQLite is awesome.
- jventura 6y agoMost of my web apps’ databases are an SQLite file. It’s more than enough for the ammount of traffic they serve and the db files are easy to set up and backup..
- eska 6y agoI also always wondered why sqlite isn't used more in websites. Especially if you split heavy write workloads to a separate database file it scales quite far.
- qes 6y agoThere's no reasonable way to share a SQLite database between processes on separate machines or VMs.. What website doesn't use at least 2 instances for HA? How do you make sure you don't lose data in a SQLite DB?
- presumably 6y agoWhat’s not reasonable about TiDB?
- samtho 6y ago
- dtf 6y agoWhile reading the documentation for iff(), I noticed the command line function edit(), which is pretty cool. UPDATE docs SET body=edit(body) WHERE name='report-15'; UPDATE pics SET img=edit(img,'gimp') WHERE id='pic-1542';
- thibran 6y agoIf you like edit, you might like :memory: too :) https://sqlite.org/inmemorydb.html https://sqlite.org/inmemorydb.html
- edwinyzh 6y agoI also didn't know about it before! Cool
- combatentropy 6y agohttps://sqlite.org/cli.html#the_edit_sql_function https://sqlite.org/cli.html#the_edit_sql_function
- bob1029 6y agoFor our B2B application, we've been using SQLite as the exclusive means for reading and writing important bytes to/from disk for over 3 years now. We still have not encountered a scenario that has caused us to consider switching to a different solution. Every discussion that has come up regarding high availability or horizontal scaling ended at "build a business-level abstraction for coordination between nodes, with each node owning an independent SQLite datastore". We have yet to go down this path, but we have a really good picture of how it will work for our application now. For the single-node-only case, there is literally zero reason to use anything but SQLite if you have full autonomy over your data and do not have near term plans to move to a massive netflix-scale architecture. Performance is absolutely not an argument, as properly implemented SQLite will make localhost calls to Postgres, SQL Server, Oracle, et. al. look like a joke. You cannot get much faster than an in-process database engine without losing certain durability guarantees (and you can even turn these off with SQLite if you dare to go faster).
- Kaze404 6y agoI often connect to production databases in read only users to do various data analysis. Is this something you can do with SQLite (besides maybe SSHing into the machine)? If not, how do you get around it (if it ever even comes up)?
- bob1029 6y agoWe have a few paths for this type of thing. One is to simply zip up the entire database and send it across the wire. This is most applicable for local development and QA testing scenarios. Another is to have something in the business application and relevant tooling that allows for programmatic querying of the data we need to look into. We also have some techniques where we do ETL of the data range we care about from 1 SQLite db to another, then pull down the consolidated db for analysis.
- hobs 6y agoI love sqlite, but just a wonder on how big you are going? I regularly see 50TB total of databases on SQL Server, and scaling up to thousands of clients.
- boksiora 6y agomy favorite db format
- ha470 6y agoWhile I love SQLite as much as the next person (and the performance and reliability is really quite remarkable), I can’t understand all the effusive praise when you can’t do basic things like dropping columns. How do people get around this? Do you just leave columns in forever? Or go through the dance of recreating tables every time you need to drop a column?
- eli 6y agoDon’t many MySQL backends also recreate the whole table when you drop a column? They just hide it from you better.
- faceplanted 6y agoPretty sure they must, row based storage on disk would practically require it just to not completely waste all of the space you've just gained from deleting the column by leaving a gap on every single row.
- calpaterson 6y agoAdding a nullable column is constant time (ie: basically instant) in postgres and innodb, maybe also in other systems.
- HelloNurse 6y agoIf adding a nullable column is free, it probably means that the DBMS is able to distinguish multiple layouts for the same table: existing rows in which the new column doesn't actually exist and is treated as NULL, and newly written rows in which there is space for the new column. But dropping a column is different: even if the DBMS performs a similar smart trick (ignoring the value of the dropped column that is contained in old rows) space is still wasted, and it can only be reclaimed by rewriting old files.
- calpaterson 6y ago
- trashburger 6y ago>Increase the default upper bound on the number of parameters from 999 to 32766. I don't want to know the use case for this. Keep rocking on, SQLite. It's the first tool I reach for when prototyping anything that needs a DB.
- oefrha 6y agoSimple. Bulk insert with a 999-parameter limit is just painful; if each entry has 9 columns, you can’t even insert 112 rows at once. In practice distros already compile with higher default; e.g. Debian compiles with -DSQLITE_MAX_VARIABLE_NUMBER=250000, still way higher than this new default.
- abraae 6y agoWhat's the point? Inserting batches of 1000 rows at once, or even 10k rows at once is hardly any faster overall than using batches of 100 rows, assuming there are no delays in presenting the batches to the DB.
- Carpetsmoker 6y agoIt's just easier: I won't have to split queries with 1,500 parameters in two because of some limit.
- dtf 6y agoFor instance, you might want to update a large subset of rows via WHERE id IN (?,?,?,...) instead of WHERE id IN (SELECT ...)
- deleted 6y ago[deleted]
- zubairq 6y agoThanks so much for SQLite. Amazing and stable database. Yazz Pilot (https://github.com/zubairq/pilot https://github.com/zubairq/pilot) is built on it
- devwastaken 6y agoAre there resources for good practices on database formatting? I feel that what I make 'works', but I'd be curious on what experienced databases look like. For example I have an app that you upload files through. Files can be local to the server or on s3 and have metadata. I end up making a new table for the API points. Like a table for listing files/directories. A table for local files and a table for s3 files. Then a table for the metadata, and a table for the kind of file it is, etc. It works, but it feels like a heavy hammer.
- vbezhenar 6y agoYou might want to check out Codd books. He invented relational model after all and his books cover database design.
- cptnapalm 6y agoRecommendations for learning SQL with SQLite? I've recently started doing the Khan Academy videos, and am liking them, but I'd like more practice problems and explanatory text.
- fibers 6y agotry importing this data and play around with it https://www.percona.com/blog/2019/01/24/a-quick-look-into-tidb-performance-on-a-single-server/ https://www.percona.com/blog/2019/01/24/a-quick-look-into-ti...
- justinclift 6y agoSome of these may be useful: https://github.com/sqlitebrowser/sqlitebrowser/wiki/Tutorials https://github.com/sqlitebrowser/sqlitebrowser/wiki/Tutorial... https://github.com/sqlitebrowser/sqlitebrowser/wiki/Video-tutorials https://github.com/sqlitebrowser/sqlitebrowser/wiki/Video-tu... One of our developers (Manuel) started putting together lists of tutorials and video's for SQLite + DB Browser for SQLite a while back. There are probably more we've missed, and contributions to those pages (etc) are welcome. :)
- hn_1234 6y agois SQLite used for big data storage ? What are high end use cases than small data points which I mostly use it for. excuse me if its a dumb question
- mkl 6y agoMaybe this will help? https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- emadda 6y agoI’ve been using SQLite on GCP for a few small projects and it seems to work well. I use docker volumes to write to disk. I pass the disk directory to my process via a CLI arg. When running on a VM these disk writes are replicated between zones (this is default for regional GCP disks). So you get zero config high availability (if you can tolerate down time during a reboot).
- rhencke 6y agoYou might find DQLite of interest. https://dqlite.io/ https://dqlite.io/
- emadda 6y agoThanks I have seen this, but would prefer to use the data center provided replication at the disk level as I do not need to have real time failover (I just need to make sure I can recover data in case of a single zone failure). Also incremental disk snapshots are nice to have.
- pachico 6y agoI have running in production a SQLite powered service for the free Geonames gazetteer. It's a read only service so it fits perfectly and providing really good performance. I also use it to work with data coming in CSV format. What a great piece of software!
- me551ah 6y agoWhere can you use sqlite? Embedded: Yes Raspberry Pi: Yes Mobile Apps : Yes Desktop Apps: Yes Microservices: Yes Big Monolith : Yes Browsers. : No
- goutham2688 6y agocheck this out for browsers https://github.com/sql-js/sql.js https://github.com/sql-js/sql.js
- stephen82 6y agoDid you mean whether browsers use it or not? If that is the case, if not all, the majority of them already use it for ages now; else, please clarify what you mean.
- why-el 6y agoOne of the great things one can learn from SQLite is the degree to which they unit (and integration) test their source code. It's honestly the best unit test document I have read in my career to date: https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html.
- ardy42 6y agoIIRC, some company wanted to use SQLite on an airplane, so they paid the devs enough to bring the test suite up FAA standards. IIRC, they have code coverage of every machine instruction.
- why-el 6y agoYep, I think (with 90% certainty) that Richard Hipp, the creator of SQLite, mentioned this in a Youtube Talk, but sadly I can't recall which one. :(
- justinclift 6y agoThis seems to be it: https://youtu.be/Jib2AmRb_rk?t=675 https://youtu.be/Jib2AmRb_rk?t=675
- why-el 6y agoYep, that's the one. Thanks.
- SQLite 6y agoThat was my business plan: Do the intense testing required for avionics, then sell the test cases to aviation manufacturers. That plan didn't work out - I've never sold the tests to any aviation manufacturer; not one. But the TH3 test harness has had side benefits that I did not anticipate, not the least of which is that it allows us to maintain a complex code base that is run on billions of devices with just a few developers.
- RivieraKid 6y agoIs it reasonable to assume that in most current deployments of PostgreSQL or MySQL, SQLite would be at least an equally good choice? I was recently choosing a database for a medium-size website and SQLite seemed like an obvious choice. One thing I was worried about was that the database locks for each write - but this is apparently not true anymore with write-ahead log.
- deleted 6y ago[deleted]
- therealdrag0 6y ago> “Most current deployments” I doubt it but we’re both guessing. Personally I’ve never worked on a professional project that had all readers/writers on a single computer. So in my bubble SQLite is not an option.
- duskwuff 6y agoDepends on the environment. SQLite will scale out reasonably well so long as it's only needed on one machine. As soon as you need a network-accessible database, traditional database servers start looking like a better option.
- justinmeiners 6y agoYes, most wordpress or joomla sites come to mind. There is typically only one application communicating with it, the user doesn't typically doesn't admin the database directly (and if they did they want a file), medium traffic load (hundreds per second), and most of the queries are reads, with the occasional content update. As soon as you get into privilege levels or heavy loads, then those others make more sense.
- Carpetsmoker 6y agoI ran some performance/reliability benchmarks on the product I'm working on (which supports SQLite and PostgreSQL), and SQLite was about 30% faster than PostgreSQL. This won't hold true for all use cases; one table now has 11 million rows, and I'm not sure how well SQLite would perform on that. The benchmark was very simple anyway, and it's mostly a read-only where users don't update/insert new stuff. Would be interesting to re-test all of this.
- RivieraKid 6y agoOne possible disadvantage of SQLite is that it only allows one writer at a time (but writes don't block readers with write-ahead log enabled). I'm really curious about whether Postgres performs better at concurrent writing, couldn't find any benchmarks. In theory, disk writes are always sequential, so I'm skeptical Postgres would do substantially better.
- therealdrag0 6y agoSQLite isn’t a db-server like most other mainstream databases. It’s more of a db-file; almost an excel file. This means it’s usecases are quite different and perf comparisons don’t make sense.
- justinclift 6y ago> I'm really curious about whether Postgres performs better at concurrent writing ... Very much so. PostgreSQL easily handles lots of concurrent writing. It's a use case where PostgreSQL is much better than SQLite. :)
- RivieraKid 6y agoI don't believe this without a benchmark.
- justinclift 6y agoGenerally that's a good approach. :) In this case though, it seems a bit weird. SQLite is widely known to be for single writer workloads, whereas PostgreSQL is similarly widely known for being extremely good in concurrent usage scenarios. Those are the things they're each designed for. eg: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html (the "High-volume Websites", "High Concurrency", and "Many concurrent writers?" pieces) Feel free to run benchmarks to demonstrate this to your own satisfaction though. :)
- RivieraKid 6y ago
- wenc 6y agoSQLite is great but its decision in not having a standard datetime/timestamp datatype -- a standard in all other relational databases -- has always struck me as a surprising omission, but in retrospect I kind of understand why. Datetimes are undeniably difficult. So sqlite leaves the datetime storage decision to the user: either TEXT, REAL or INTEGER [1]. This means certain datetime optimizations are not available, depending on what the user chooses. If one needs to ETL data with datetimes, a priori knowledge of the datetime type a file is encoded in is needed. In that sense, sqlite really is a "file-format with a query language" rather than a "small database". [1] https://stackoverflow.com/questions/17227110/how-do-datetime-values-work-in-sqlite https://stackoverflow.com/questions/17227110/how-do-datetime...
- deleted 6y ago[deleted]
- combatentropy 6y ago"SQLite does not compete with client/server databases. SQLite competes with fopen()." --- https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- zeroimpl 6y agoWhy is it “iif” instead of “if”? I don’t recall “if” being a keyword in SQL