5 Reasons Why You Should Use SQLite in 2016

Top 5 reasons why you should use SQLite in 2016
What? Don’t you know SQLite yet? Well let me tell you, SQLite is an amazing database capable of doing real work in a real production environment. In this article, I will briefly outline 5 reasons why I think you should use SQLite in 2016.
1. Simple management. Have you managed a Postgres database before? There is quite a lot that you need to understand and learn in order to ensure the correct configuration of the database server (shared buffers, effective cache size, work mem, maintaining work mem, wal buffers...). Upgrading can be a scary process, and you may need to take the database offline, run a dedicated upgrade program, and then silently hope that it will still work when you backup it back. Furthermore, do you know where your Postgres database is actually stored? Can you point to a random place and say, "That's my database"? (As we all know, there are many cases where the needs of an application can only be met using Postgres (or MySQL, Oracle, SQL Server, etc.). But my purpose in saying this here, is not that. I just want to highlight managing a SQLite database The difference between other typical database servers. )
SQLite is easy to manage - because it is a single file (or in some cases a file + transaction log). The file format is very stable across many major versions, so assuming I have a SQLite database file starting with version 3.0.0 (in 2004), then I can also read it using the latest SQLite 3.10.0. If I want to put the database files on a USB stick, then I just copy the files, or better still, save the files in a dropbox folder. If I want to backup every night, then I just sync the database files to S3. If I wanted to share some data analysis I was doing with a colleague, I could send them a copy of the database file and they could use it. The database exists in the form of a single file and has a stable format, which is a feature of SQLite.
What’s more, SQLite is easy to configure. SQLite functionality can be managed in two ways: compilation flags and PRAGMA statements (runtime configuration). There is no such thing as a configuration file, you just build the library you want and then configure the runtime options when you create the database connection.
2. Continuously improving and rock-solid stability. SQLite is actively developed by some truly amazing software engineers. High-quality new features are being added at an astonishing rate. Just recently, SQLite added support for JSON data via the json1 extension. SQLite also released an improved version of the full-text search extension, which includes result ranking using the BM25 algorithm. In addition to adding new features, SQLite developers are also working to make the library more performant. In version 3.8.11, the release notes include this little blurb: SQLite now runs twice as fast as version 3.8.0 and three times as fast as version 3.3.9.
Despite so many changes and improvements, SQLite rarely introduces bugs. SQLite's test suite is widely regarded as one of the best in the industry, and a page on how SQLite is tested frequently appears on HackerNews as developers come across this impressive documentation.
3. Scalable and controllable. My personal favorite feature about SQLite is its scalability. Because SQLite is embedded through the application, it runs in the same address space and can execute application code on your behalf. Whether it is the Python standard library SQLite driver - pysqlite, or the optional driver aspw, both can provide APIs that define custom SQL functions, aggregate functions, and collations. aspw goes one step further and provides APIs that can also be used to define virtual tables and virtual file systems! Virtual tables, currently only supported by ASPW, allow you to define a table in code and then query it as if it were a normal SQL table, even though the backup data may be entirely dynamic. For example, I wrote a simple virtual table that lets you query Redis as if it were a SQL table.
You can also write a function with the same name, which is a function used to return 0...n rows of results. One example is a regular expression retrieval function that processes input and produces lines that match a token. I wrote a library, sqlite-vtfunc, that makes writing these types of functions very easy. Virtually every aspect of SQLite operates through your application.
4. As fast as lightning. SQLite is very fast. Since it runs on the same machine, there is no network burden when executing queries or reading results. And because it runs in the same address space, there is no wire protocol, serialization, or communication over unix sockets. SQLite can also run on mobile devices when resources are scarce and efficiency is critical. SQLite also supports a large number of compilation flags, allowing you to remove features you don't intend to use.
SQLite’s speed makes up for one of its biggest shortcomings, which is database file locking for writing. Incredibly fast writing of data, database locking only becomes an issue when there are a large number of concurrent writes being serviced.
5. WAL mode. Version 3.7.0 of SQLite adds a new logging method that utilizes write-ahead logging. While not really exciting news in and of itself, what this means to web application developers (or anyone dealing with concurrency) is that read services will no longer block write services, and vice versa. In other words, reading and writing can occur simultaneously. If there is no WAL mode, then in order to write to the database, the writing service needs exclusive access to the database, and the reading service cannot occur until the writing service ends. However, it's worth mentioning that even if you don't enable WAL mode, writes to the service typically occur in milliseconds. This time is so short that you will only notice a problem if you have high concurrency or if you write very long transactions.
Extra points for: BerkeleyDB. BerkeleyDB's SQLite integration can give application developers the even better performance they need for concurrent database access because instead of locking the entire database, BerkeleyDB only needs to lock individual pages. This allows BerkeleyDB to scale more efficiently under concurrent database loads, providing transactions that do not compete for the same page of data. BerkeleyDB also supports multi-version concurrency control (MVCC), which allows read operations to continue to occur on the data page and process the data through a write transaction.
Another benefit of BerkeleyDB is that it improves efficiency. In other words, BerkeleyDB can use fewer system resources and perform fewer system calls. You can read more details in this white paper and this brief technical overview. BerkeleyDB's SQL interface is a drop-in replacement for SQLite and supports the same API and functionality. BerkeleyDB provides some additional features such as replication (SQLite has a backup tool, but I don't think it is as powerful as BDB's), encryption, and of course all the features of BerkeleyDB itself.
A major disadvantage of using BerkeleyDB is that it is very sensitive to configuration values, and getting the correct page size, cache size, and other settings requires a lot of knowledge. Another drawback is the license - to read more about the BerkeleyDB license, check out Oracle's licensing page.
Finally, I hope you can give SQLite a try. Don't listen to the conventional wisdom from those who say it's not suitable for production, or that it's not suitable for use in web applications.
If you want to learn more, SQLite itself has a great documentation on when to use SQLite, which also covers a range of situations where another RDBMS would work better.
Brotherhood Gao Luofeng recruits disciples for free: http://www.hdb.com/party/lzcw-comm.html
Receive LAMP Brothers’ original PHP video tutorial CD/"Explain PHP in detail" free of charge. For details, please contact the official website customer service:
http://www.lampbrother.net
【Brothers IT Education】Learn PHP, Linux, HTML5, UI, Android and other video tutorials (courseware + notes + videos)!
Network disk tutorial download: http://pan.baidu.com/s/1sjOJiAL

The above introduces the five major reasons why you should use SQLite in 2016, including aspects of it. I hope it will be helpful to friends who are interested in PHP tutorials.