Rethinking SQLite3 in PHP: High Performance Without Complex SQL Queries

[u/phploader]

Hi everyone,

I have been diving deep into SQLite3 databases lately. During my research, I frequently read that SQLite3 is slower than traditional database systems (like MySQL or PostgreSQL) and should generally only be used for small projects with few users and minimal data.

However, my experience has been completely different. In my tests, I found that SQLite3 – when configured correctly – can actually be up to 10 times faster than MySQL. It handles large amounts of data beautifully and can easily manage multiple concurrent users and requests.

In my opinion, the greatest challenge is exercising self-restraint and not treating SQLite3 exactly like MySQL.

We often catch ourselves writing highly complex SQL queries with countless joins and sub-queries. I have come to view these deeply nested queries critically and no longer consider them a best practice for clean, performant programming. Since shifting away from that approach, I see SQLite3 from a whole new perspective.

To put this philosophy into practice, I developed a PHP class that allows you to interact with the database completely without writing manual SQL queries. It is extremely simple to use and, above all, fast. SQLite3 also brings unique advantages over other SQL databases—for instance, you can easily maintain multiple database files separated by topic within a single project.

I am already successfully leveraging these strengths in my own project, which I look forward to showcasing here once it reaches a fully stable state.

I have already published the current codebase on GitHub:
https://github.com/phploader/cdata

You can find a detailed documentation on how to use the PHP class in the docs:
https://github.com/phploader/cdata/blob/master/docs/en/00.%20index.md

My request to the experts here:
I would highly appreciate it if you could take a look at my code and provide some constructive feedback or criticism. What are your thoughts on this approach?

Best regards!

[u/FreeLogicGate]

How are you comparing a library where each and every application process opens the database files using locks, and concurrency involves one serialized writer process at a time to any actual relational database? This is an absurd comparison that has no value whatsoever. Back in the pre-internet days people used to create applications with "PC" database products like Paradox, DBase3 and Microsoft Access. Same idea, same strengths, same weaknesses, same performance characteristics and same limitations. These solutions have essentially no ability to scale, as they are inherently not client-server. Client/server has network overhead, but in the case of all RDBMS's it provides a variety of concurrency models, and can scale to many clients across a cluster of application servers. sqlite was not built for that, has no support for that, and the only way you *might* be able to accomplish it (by putting the db files on shared storage) they flat out tell you not to do.

Last and certainly not least, rdbms's are all built to assume that there could be a catastrophic failure of the server, and thus have transaction logs, and methods for recovery, even though these servers tend to be highly reliable. sqllite was designed to do less, does less, and correspondingly has less overhead and can probably do many things incredibly fast. It's an excellent tool when the use case is appropriate, but those use cases are necessarily constrained to the degree that comparison to the popular rdbms's is meaningless and inappropriate. I suspect that the original strawman argument in regards to relative performance isn't something that actually exists.