Is it the unix version of MS Access? It's sort of interesting to compare the two.
SQLite is cross-platform and actually ships inside all major operating systems.
SQLite is not a toy database
301–310 of 364 posts
Re: SQLite is not a toy database
#302Earlier quoted context omitted.
I would use SQLite on a single-machine web server. However, I prefer to build my web stuff as stateless machine images that connect to a separate database instance, because it lets me scale them independently. I should revisit this policy now that you can run a truly huge site off a 1U slot (I work for an alexa top10k, and our compute would fit comfortably in 1U); computers are so fast that vertical scaling is probab…
That's exactly what I am leaning to in my current job. Looking at the load of our servers I can comfortably put all our software on my gaming machine (which is mid-range!) -- with the DB included -- and I bet no request ever will be above 100ms. IMO a lot of organizations should start re-investing in on-premise as well. Having a mid-range AMD EPYC server can serve most businesses out there without ever having more th…
I don’t know about on-prem for serving external workloads; getting a reliable, redundant, high-speed connection can cost far more than paying for colocation at a DC that’ll do at least as good a job. If you’re lucky they’ll even install fire suppression systems.
Re: SQLite is not a toy database
#303Earlier quoted context omitted.
SQLite already has different operating modes, right? e.g. WAL is turned on and stays on, I think; It seems like you could at least make type-checking an opt-in mode
WAL is journaling mode set on the connection. It’s not a db level setting.
WAL mode will stick after subsequent "connections".
Re: SQLite is not a toy database
#304Earlier quoted context omitted.
It depends on what you consider high uptime. You can achieve 99.95% uptime with 4h of downtime a year. A lot of downtime occurs because of overly complicated systems so running a single process on a single server can give you relatively high uptime.
I just want to be able to deploy updates during business hours without downtime. Hard to do with a single process.
Most web servers support greaceful restarts, i.e. reloading code and configuration without downtime. Usually you just send a pre-specified and documented signal to the process to do it.
Re: SQLite is not a toy database
#305I wish SQLite had a PostgreSQL compatibility layer. Right now, to add SQLite support to a Go project (or anything without an ORM), you have to rework all your queries and migrations. It's probably an impossible ask, but having a compatibility flag within SQLite so it would accept PostgreSQL formatted queries would be extremely helpful.
[deleted]
Re: SQLite is not a toy database
#306Earlier quoted context omitted.
I don't fully get it -- this is an SQlite implementation in JavaScript, which is different from what you'd use in a native app. Unless you store it to localStorage or cookies, all variables disappear when you navigate away from the page. Can you write an offline HTML5 webapp with any of these libraries such that it can cerealize the entire database into a string and then store that to localStorage and reload the next…
Really you'd want to do this in to an indexeddb store rather than local storage. Less issues with size limits and string serialisation as you can chuck Blob instances straight into it. You'd have to handle loading/saving though
Is there some way by which the indexedDB store can stay synchronised with the server database running in the backend.
Something simple so that the front-end app developer can just focus on implementing business requirements.
Re: SQLite is not a toy database
#307Earlier quoted context omitted.
The primary reason for me is that Postgres has stronger default constraints. If you care about keeping your data logically consistent then Postgres has more of that out of the box. SQLite just makes the tradeoff to be simpler since often it doesn't matter. But don't make the mistake that it doesn't matter. Since PG helps avoid data problems and you might need to scale out web servers that is why Django for instance r…
Not sure why you were downvoted because that is a legitimate concern and it's my only beef with sqlite3. I will kill for an embedded PostgreSQL. If sqlite3 becomes that I'll absolutely pay for a license if they require it.
Check out DuckDB! It is designed for OLAP instead of OLTP, but it uses Postgres syntax and types! It's columnar and lightning fast for big queries.
Re: SQLite is not a toy database
#308With no sense of overstatement here, SQLite is one of my favorite creations in the entire world, so I have a bunch of links some of you might find interesting if you want to dig further: https://github.com/sql-js/sql.js - SQL.js lets you run SQLite within a Web page as it's just SQLite compiled to JS with Emscripten. https://litestream.io/blog/why-i-built-litestream/ - Litestream is a SQLite-powered streaming replica…
Using sql.js we have built online SQL course where the code is executed in the browser itself. https://academy.bigbinary.com/learn-sql
That’s pretty cool! Thanks for sharing this. My SQL knowledge has been a hodgepodge of random stuff for years, so maybe going through your course will help fill in the gaps.
Re: SQLite is not a toy database
#309Earlier quoted context omitted.
I just want to be able to deploy updates during business hours without downtime. Hard to do with a single process.
Most web servers support greaceful restarts, i.e. reloading code and configuration without downtime. Usually you just send a pre-specified and documented signal to the process to do it.
For a web server serving from the filesystem, sure. That doesn't help you deploy a new version of a web application you're making code changes to (which is the kind of thing that would need a database).
Re: SQLite is not a toy database
#310Earlier quoted context omitted.
SQLite is very limited because of its threading model, imo it's not usable outside of the single app model where you have a single user. https://sqlite.org/threadsafe.html https://sqlite.org/lockingv3.html
With a single thread & SQLite connection instance, I have been able to insert hundreds of thousands of rows per second when using NVMe drives. Note that WAL and synchronous flags must be set appropriately. Out of the box and using the standard "one connection per query" meme will handicap you to The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize…
> I have been able to insert hundreds of thousands of rows per second when using NVMe drives.
Thats... amazing! What is your setup like?