341–350 of 364 posts
Earlier quoted context omitted.
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.
Earlier quoted context omitted.
It's truly a shame that Web SQL was opposed by Mozilla. The web would be a better place if a tool as powerful as SQLite was available by default on billions of devices.
Can you share more details? Why Mozilla opposed SQLite?
More interesting commentary here https://softwareengineering.stackexchange.com/questions/2202...
> There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access. No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.) SQLite isn't a toy DBMS, it's an extremely capable embedded DBMS. An embedded DBMS is geared towards serving a si…
Actually, I think it's network access that is the issue. SQLite isn't really designed to have 4-5 network connected apps using it as a shared data store. In most shared hosting setups, database is treated as a network service, so you can have 200 different sites leveraging the same db server... and sometimes the db server isn't on the same hardware as the http server.
Earlier quoted context omitted.
> Friendly reminder that you shouldn't spend time fine tuning your horizontal autoscaler in k8s Oh, but most companies need this! The biggest feat of microservices (which require ways to manage them, like k8s) was to provide the ability for companies to ship their organizational chart to production. If you don't need to ship your org chart and you can focus on designing a product, then you can go a long way without o…
I think this org chart issue has been overstated. If you have two services that are completely orthogonal, then combining them into a single application for deployment and operation purposes can be limiting. Developers of all people should understand the benefits of decoupling. There are a lot of downsides that come with jamming a whole lot of unrelated functionality into the same deployment unit. It's similar to the…
This is an absolutely enormous trade-off between logical modularity and run time operational complexity.
If your devs aren't good enough to enforce modular design in a single application, what makes you think they're good enough to handle complex distributed systems?
Earlier quoted context omitted.
When I was working on database services there were actually many applications with more writes than reads. A common example is applications where you want to keep the user's state saved frequently, but you only need to reload it when the application is restarted. A lot of games work this way. So there can be a write every few seconds or every minute for every active user, but you only need to do a read a few times at…
Right; lots of modern document software, for instance, basically saves continuously; so a Google Docs-style application could have such a load. But if you have a write transaction every second, but each transaction only takes 1ms, that's still ~1000 concurrent users before you start to get noticeable lag. Remember, the person I was replying to claimed SQLite was " not useable outside of the model... where you have a…
Earlier quoted context omitted.
It also doesn't have strong/static typing (it's dynamically typed) so you have to typecheck your inputs or do type coercion upon read. And it doesn't have a native date type. Date handling has to be handled at the application layer. It can be tricky to do massive time-series calculations or date-based aggregations. You can use integers or text types to represent dates, but this open-endedness means you can't share yo…
Datetimes really are the achilles heel of SQLite. The JSON extension library is amazing and works well. If SQLite were to grow a first-rate RFC 3339 library, one which could read from tz when available and do the things which strftime cant, acting on your choice of Unix timestamp and valid RFC 3339 date string, this would be a real boon to the ecosystem. I haven't found typechecking inputs to be a real barrier. Sure,…
OTOH, as an embedded database with freeform advisory typing and easy extensibility (for functions, etc.) in most host languages, it's not hard at all to get whatever you need for datetimes if you are using a host language that has a decent datetime library itself (and, as a bonus, you then don't have to worry about subtle differences between manipulations of datetimes through SQL and manipulations through other mechanisms in the app.)
This is a great introductory guide! I like SQLite a lot, but I found SQLite's recursive CTE implementation to be somewhat limited: https://dercuano.github.io/notes/why-html-is-not-a-programmi... Has that improved? (sorry about the embarrassing arrogant pedant attitude in that note, but it's too late to fix it now)
It has! Search for "2020-12-01 (3.34.0)" here: https://www.sqlite.org/draft/changes.html The 3.35 release added more good stuff for CTEs, as well as a RETURNING clause and a much more flexible UPSERT.
I think it's still the case that SQLite doesn't permit the use of recursive CTEs in subqueries.
Earlier quoted context omitted.
It has! Search for "2020-12-01 (3.34.0)" here: https://www.sqlite.org/draft/changes.html The 3.35 release added more good stuff for CTEs, as well as a RETURNING clause and a much more flexible UPSERT.
After discussing it with you, I think it would be bad to make SQLite's recursive CTE support more powerful; making SQL Turing-complete is a clear case of Turing overreach. I think it's still the case that SQLite doesn't permit the use of recursive CTEs in subqueries.
I have make a copy of the Collatz Conjecture CTE that you linked to and was going to see if I could get it to work in SQLite for the next release cycle. I don't (yet) see any reason why it shouldn't work to have the recursive reference down inside a subquery, as long as there is only one recursive reference. No promises. We'll see how it goes.
With 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…
Wow! The sql.js bundle is only 8kb. This is a hidden gem for sure. Thanks for pointing it out! EDIT: It's actually 1.2MB. Thanks for pointing it out :)