I 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.
SQLite is not a toy database
41–50 of 364 posts
Re: SQLite is not a toy database
#42> SQLite is serverless. Maybe if you use SQLite as a file format. But if you use it like an actual database (e.g. in a web application), I find that one is best off setting up a daemon thread to queue/batch transactions.
One of the disappointing developments of the past quarter century is the near demise of general purpose databases as an end user application. Yes, client/server models are useful when dealing with a large number of transactions. On the other hand, it is usually too complex to justify for personal or small office use.
I miss the days when databases were included in office suites or could be purchased as relatively inexpensive standalone applications since it was easy to create a database and associated forms, rather than depending upon domain specific applications that use a database as a file format but may be poorly suited for a particular scenario.
(Yes, the phrasing "file format" verses "actual database" rubbed me the wrong way.)
Edit: I realized that my post comes off as dismissive of SQLite, which is not entirely true. I use it as a database and appreciate it's role, but embedding it in an application is frequently more than a given application requires.
Re: SQLite is not a toy database
#43Don't forget about User-Defined Functions. https://www.sqlite.org/appfunc.html We just started enhancing our SQL dialect with new functions which are implemented in C# code. One of them is an aggregate and it is really incredible to see how it simplifies projections involving multiple rows. One huge benefit of SQLite's idea of UDFs is that you can actually set breakpoints and debug them as SQL is executing.
Application-defined functions are very useful.
One of my company's applications is already designed to work with different SQL systems and a new customer desperately wanted SQLite for a very special use case. As SQLite is quite simple and doesn't support many functions that are standard in SQL Server, MySQL, Oracle, etc., we used application-defined functions to implement all functions the application needs in C#. It's not very fast but also not slow and the customer is happy, which is what really counts.
Re: SQLite is not a toy database
#44I've built a complex CRM that handles 2.1 million USD in transactions every year. It is running sqlite with a simple in-memory lru cache (just a dict) that gets purged when a mutating query (INSERT, UPDATE or DELETE) is executed. It is very simple and more than fast enough. Friendly reminder that you shouldn't spend time fine tuning your horizontal autoscaler in k8s before making money.
How do you ensure data is not lost to oblivion if a catastrophic system failure occurs?
Re: SQLite is not a toy database
#45> not bothering with optimization (≈200 requests per page)
What amount of SQL queries per page render is considered sensible?
When I run more than 20 queries per request in my Rails apps (smallish internal tools for different companies) I get uneasy. I usually deploy the app on the same machine where the DB (not SQLite) runs, but I imagine if that weren't the case the app-DB roundtrips could soon dominate the whole thing.
Re: SQLite is not a toy database
#46SQLite is so robust, that I bet most websites could use it without really needing to move onto a client/server RDBMS.[1] I use MySQL, and I know PostgreSQL has a large marketshare now, but I wonder how much of either is really necessary when you think about traffic usage alone. I know at least in my use cases, neither seem necessary. [1]: https://sqlite.org/whentouse.html
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
The article addresses this. Basically, you can have any number of concurrent readers, but only a single writer. Writing and reading can happen concurrently just fine. So the question is -- how many users does a website need before having only a single concurrent writer becomes a bottleneck?
That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where [EDIT the number maximum number of concurrent users] is actually "1". And for many, that will be in the thousands or hundreds of thousands.
FWIW the webapp I use to help organize my community's conference has almost 0 cpu utilization with 50 users. Using sqlite rather than a separate database greatly simplifies administration and deployment.
Re: SQLite is not a toy database
#47I'd also like to add the possibility of using SQLite databases as an application file format: https://news.ycombinator.com/item?id=23508923 I had to work on a data import/export tool some time ago and SQLite has simplified the design a lot.
While this makes things easy, you should not do this for any application file format where you except your users to share files. This is because opening a SQLite database file makes the SQLite library execute any arbitrary code that may be stored in that file. [0] Therefore, SQLite is really only suitable for local-only file formats, such as configuration files, and not for files that users will e-mail to each other.
[0] https://media.ccc.de/v/36c3-10701-select_code_execution_from...
Re: SQLite is not a toy database
#48This 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)
Re: SQLite is not a toy database
#49My only "problem" with SQLite was that it was so fast, running locally, that it hid just how much my app was needlessly hitting the database. It was a surprise when I transitioned to a networked Postgres server and performance completely tanked due to my crappy code...
Re: SQLite is not a toy database
#50SQLite is so robust, that I bet most websites could use it without really needing to move onto a client/server RDBMS.[1] I use MySQL, and I know PostgreSQL has a large marketshare now, but I wonder how much of either is really necessary when you think about traffic usage alone. I know at least in my use cases, neither seem necessary. [1]: https://sqlite.org/whentouse.html
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
A nice fix for this might be to extract just the wire protocol from dqlite to make a network connected, but not Raft replicated, sqlite.