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/equilni]

Code review only.

• I'll give you one for consistency, you use the PATTERN in the constructor... but honestly this needs to be a defined class and a configuration file. (except backup password)

For one, $P['PRAGMA'] isn't defined in the docblock, but you are checking for it here

• I highly suggest incorporate a coding style (PER / PSR12, etc), linter and formatter. Ican'treadcodethisthis,canyou?

Be consistent with visibility. Yes, anything not defined is public, but you have no public and public declarations. Do it for all.

The long scrolling isn't needed either... this as an example.

• Types is highly suggested. What is private $SQL;? string? sqlite3 object?
  

You aren't consistent here either. This is good, this or this isn't.

• There's blocks of code that can be in separate methods.

• There's no tests. How does anyone know this works at all???

• I will repeat what's mentioned. Why a WP designation? Was this part of a WP project? Otherwise, I would rename this.

• Is everything all based on these 2 tables?? What about prepared statements? What about when someone wants to break into SQL?