(Note: The current page is a brief summary of why SQLite makes +a good application file format. The topic is considered at greater +detail in a [application file-format | separate technical note].)
+ ++SQLite has been used with great success as the on-disk file format +for desktop applications such as version control systems, +financial analysis tools, media cataloging and editing suites, CAD +packages, record keeping programs, and so forth. The traditional +File/Open operation calls sqlite3_open() to attach to the database +file. Updates happen automatically as application content is revised +so the File/Save menu option becomes superfluous. The File/Save_As +menu option can be implemented using the [backup API]. +
+ ++There are many advantages to using SQLite as an application file format, +including: +
+ ++SQLite allows database files to have any desired filename extension, so +an application can choose a custom filename extension for its own use, if +desired. The [application_id pragma] can be used to set an "Application ID" +integer in the database file so that tools like +[http://www.darwinsys.com/file/ | file(1)] can determine that the file +is associated with your application and is not just a generic +SQL database.
Index: pages/capi3ref.in ================================================================== --- pages/capi3ref.in +++ pages/capi3ref.in @@ -1,9 +1,6 @@These pages define the C-language interface to SQLite.
+This is not a tutorial. These -pages are designed to be precise, not easy to read. -For a tutorial introduction see -[quickstart | SQLite In 3 Minutes Or Less] and/or -the [cintro | Introduction To The SQLite C/C++ Interface]. +
These pages are intended to be precise and detailed specification. +For a tutorial introduction, see instead: +
This version of the C-language interface reference is -broken down into small pages for easy viewing. The -same content is also available as a -single large HTML file -for those who prefer that format.
- -The content on these pages is extracted from comments -in the source code.
- -The interface is broken down into three categories:
+The SQLite interface elements can be grouped into three categories:
List Of Objects. This is a list of all abstract objects and datatypes used by the SQLite library. There are couple dozen objects in total, but - the three most important objects are: - A database connection object [sqlite3], - prepared statement object [sqlite3_stmt], and the 64-bit integer - type [sqlite3_int64].
List Of Constants. This is a list of numeric constants used by SQLite and represented by #defines in the sqlite3.h header file. These constants - are things such as numeric return parameters from - various interfaces (ex: [SQLITE_OK] or flags passed + are things such as numeric [result codes] from + various interfaces (ex: [SQLITE_OK]) or flags passed into functions to control behavior (ex: [SQLITE_OPEN_READONLY]).
List Of Functions.
This is a list of all functions and methods operating on the
@@ -287,27 +279,22 @@
This page defined the C-language interface to SQLite.
- -This is not a tutorial. These -pages are designed to be precise, not easy to read. -For a tutorial introduction see -[quickstart | SQLite In 3 Minutes Or Less] and/or -the [cintro | Introduction To The SQLite C/C++ Interface]. -
- -This page contains all C-language interface information -in a single HTML file. The same information is also -available broken out into -lots of small pages -for easier viewing, if you prefer.
- -This document is created by a script which scans comments -in the source code files.
+This page is intended to be a precise and detailed specification. +For a tutorial introductions, see instead: +
Note: Objects marked with "[experimental | exp]" -are [experimental] and objects marked with -"[deprecated | (obs)]" are [deprecated].
+Other lists: Constants and -Functions.} +Functions and +Result Codes.} hd_close_aux hd_enable_main 1 hd_putsnl {
Note: Constants marked with "[experimental | (exp)]" -are [experimental] and constants marked with -"[deprecated | (obs)]" are [deprecated]
+Also available: [error codes|list of error codes]
Other lists: Objects and -Functions.
} +Functions and +Result Codes.} hd_enable_main 1 hd_close_aux hd_putsnl {Note: Functions marked with "[experimental | (exp)]" are [experimental] and functions marked with [deprecated | (obs)] are [deprecated].
Other lists: Constants and -Objects.
} +Objects and +Result Codes} hd_enable_main 1 hd_close_aux hd_putsnl {- This article provides an overview to the C/C++ interface - to SQLite. -
+The following two objects and eight methods comprise the essential +elements of the SQLite interface: + +
| [sqlite3] | + | The database connection object. Created by +[sqlite3_open()] and destroyed by [sqlite3_close()]. | +
| [sqlite3_stmt] | + | The prepared statement object. Created by +[sqlite3_prepare()] and destroyed by [sqlite3_finalize()]. | +
| [sqlite3_open()] | + | Open a connection to a new or existing SQLite database. +The constructor for [sqlite3]. | +
| [sqlite3_prepare()] | + | Compile SQL text into +byte-code that will do the work of querying or updating the database. +The constructor for [sqlite3_stmt]. | +
| [sqlite3_bind_int|sqlite3_bind()] | + | Store application data into +[parameters] of the original SQL. | +
| [sqlite3_step()] | + | Advance an [sqlite3_stmt] to the next result row or to completion. | +
| [sqlite3_column_int|sqlite3_column()] | + | Column values in the current result row for an [sqlite3_stmt]. | +
| [sqlite3_finalize()] | + | Destructor for [sqlite3_stmt]. | +
| [sqlite3_close()] | + | Destructor for [sqlite3]. | +
| [sqlite3_exec()] | + | A wrapper function that does [sqlite3_prepare()], [sqlite3_step()], +[sqlite3_column_int|sqlite3_column()], and [sqlite3_finalize()] for +a string of one or more SQL statements. | +
Early versions of SQLite were very easy to learn since they only - supported 5 C/C++ interfaces. But as SQLite has grown in capability, + supported five C/C++ interfaces. But as SQLite has grown in capability, new C/C++ interfaces have been added so that now there are over 200 distinct APIs. This can be overwhelming to a new programmer. Fortunately, most of the C/C++ interfaces in SQLite are very specialized and never need to be considered. Despite having so many - entry points, the core API is still relatively simple and easy to code to. + entry points, the core API is still simple to learn and easy to code to. This article aims to provide all of the background information needed to easily understand how SQLite works.
A separate document, [capi3ref | The SQLite C/C++ Interface], provides detailed - specifications for all of the various C/C++ APIs for SQLite. Once + specifications for all C/C++ APIs for SQLite. Once the reader understands the basic principles of operation for SQLite, [capi3ref | that document] should be used as a reference guide. This article is intended as introduction only and is neither a complete nor authoritative reference for the SQLite API. Index: pages/compile.in ================================================================== --- pages/compile.in +++ pages/compile.in @@ -526,11 +526,11 @@
Index: pages/faq.in ================================================================== --- pages/faq.in +++ pages/faq.in @@ -522,14 +522,14 @@
That said, there are a number of things that external programs or bugs in your hardware or OS can do to corrupt a database file. See How To Corrupt An SQLite Database File for further information. -
Your can use PRAGMA integrity_check +
You can use PRAGMA integrity_check to do a thorough but time intensive test of the database integrity.
-Your can use PRAGMA quick_check to do a faster +
You can use PRAGMA quick_check to do a faster but less thorough test of the database integrity.
Depending how badly your database is corrupted, you may be able to recover some of the data by using the CLI to dump the schema and contents to a file and then recreate. Unfortunately, once humpty-dumpty falls off Index: pages/features.in ================================================================== --- pages/features.in +++ pages/features.in @@ -6,12 +6,14 @@
Database For The Internet Of Things. +SQLite is popular choice for the database engine in cellphones, +PDAs, MP3 players, set-top boxes, and other electronic gadgets. +SQLite has a small code footprint, makes efficient use of memory, +disk space, and disk bandwidth, is highly reliable, and requires +no maintenance from a Database Administrator.
Application File Format. Rather than using fopen() to write XML, JSON, CSV, or some proprietary format into disk files used by your application, use an SQLite database. You'll avoid having to write and troubleshoot a parser, your data will be more easily accessible and cross-platform, and your updates will be transactional. ([application file-format | more...])
Database For Gadgets. -SQLite is popular choice for the database engine in cellphones, -PDAs, MP3 players, set-top boxes, and other electronic gadgets. -SQLite has a small code footprint, makes efficient use of memory, -disk space, and disk bandwidth, is highly reliable, and requires -no maintenance from a Database Administrator.
Website Database. Because it requires no configuration and stores information in ordinary disk files, SQLite is a popular choice as the database to back small to medium-sized websites.
Stand-in For An Enterprise RDBMS. SQLite is often used as a surrogate for an enterprise RDBMS for demonstration purposes or for testing. SQLite is fast and requires no setup, which takes a lot of the hassle out of testing and which makes demos perky and easy to launch.
SQLite is built from over one hundred of files of C code and script +
SQLite is built from over one hundred files of C code and script spread across multiple directories. The implementation of SQLite is pure ANSI-C, but many of the C-language source code files are either generated or transformed by auxiliary C programs and AWK, SED, and TCL scripts prior to being incorporated into the finished SQLite library. Building the necessary C programs and transforming and/or creating the Index: pages/index.in ================================================================== --- pages/index.in +++ pages/index.in @@ -70,21 +70,24 @@
Index: pages/lang.in ================================================================== --- pages/lang.in +++ pages/lang.in @@ -201,13 +201,20 @@ sequence of operations. The steps to make arbitrary changes to the schema design of some table X are as follows:
+If foreign key constraints are enabled, +disable them using [PRAGMA foreign_keys | PRAGMA foreign_keys=OFF]. + +
+Start a transaction. +
Remember the format of all indexes and triggers associated with table X. -This information will be needed in step 7 below. One way to do this is +This information will be needed in step 8 below. One way to do this is to run a query like the following: SELECT type, sql FROM sqlite_master WHERE tbl_name='X'.
Use [CREATE TABLE] to construct a new table "new_X" that is in the desired @@ -216,38 +223,38 @@
Transfer content from X into new_X using a statement like: INSERT INTO new_X SELECT ... FROM X. -
-If foreign key constraints are enabled, -disable them using [PRAGMA foreign_keys | PRAGMA foreign_keys=OFF]. -
Drop the old table X: [DROP TABLE | DROP TABLE X].
Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.
Use [CREATE INDEX] and [CREATE TRIGGER] to reconstruct indexes and triggers associated with table X. Perhaps use the old format of the triggers and -indexes saved from step 1 above as a guide, making changes as appropriate +indexes saved from step 3 above as a guide, making changes as appropriate for the alteration. -
-If foreign key constraints were originally enabled (prior to -step 4) then run [PRAGMA foreign_key_check] to verify that the schema -change did not break any foreign key constraints, and run -[PRAGMA foreign_keys | PRAGMA foreign_keys=ON] to re-enable foreign key -constraints. -
If any views refer to table X in a way that is affected by the schema change, then drop those views using [DROP VIEW] and recreate them with whatever changes are necessary to accommodate the schema change using [CREATE VIEW]. +
+If foreign key constraints were originally enabled +then run [PRAGMA foreign_key_check] to verify that the schema +change did not break any foreign key constraints. + + +
+Commit the transaction started in step 2. + +
+If foreign keys constraints were originally enabled, reenable them now.
The procedure above is completely general and will work even if the schema change causes the information stored in the table to change. So the full procedure above is appropriate for dropping a column, @@ -260,12 +267,14 @@ CHECK or FOREIGN KEY or NOT NULL constraints, renaming columns, or adding or removing or changing default values on a column.
Start a transaction. +
Run [PRAGMA schema_version] to determine the current schema -version number. This number will be needed for step 5 below. +version number. This number will be needed for step 6 below.
Activate schema editing using [PRAGMA writable_schema | PRAGMA writable_schema=ON].
Run an [UPDATE] statement to change the definition of table X @@ -296,16 +305,14 @@
Disable schema editing using [PRAGMA writable_schema | PRAGMA writable_schema=OFF].
(Optional) Run [PRAGMA integrity_check] to verify that the schema changes did not damage the database. + +
Commit the transaction started on step 1 above.
It is important that both of the above procedures be run from within -a transaction to prevent other processes from accessing the database file -while the schema change is only partially complete. -
If some future version of SQLite adds new ALTER TABLE capabilities,
those capabilities will very likely use one of the two procedures
outlined above.
^For all of these errors, SQLite attempts to undo just the one statement
it was working on and leave changes from prior statements within the
Index: pages/news.in
==================================================================
--- pages/news.in
+++ pages/news.in
@@ -16,10 +16,23 @@
regsub -all {[Tt]icket #(\d+)} $txt \
{\0} txt
hd_resolve " The 3.8.8.3 patch release fixes an obscure problem in the SQLite code
+ generator that can cause incorrect results when the qualifying expression
+ of a [partial index] is used inside the ON clause of a LEFT JOIN.
+ This problem has been in the code since support for partial indexes
+ was first added in version 3.8.0. However, it is difficult to imagine
+ a valid reason to every put the qualifying constraint inside the ON
+ clause of a LEFT JOIN, and so this issue has never come up before.
+
+ Any applications that is vulnerable to this bug would have encountered
+ problems already. Hence, upgrading from the previous release is optional.
+}
newsitem {2015-01-30} {Release 3.8.8.2} {
The 3.8.8.2 patch release fixes a single minor problem: It ensures
that the [sqlite3_wal_checkpoint(TRUNCATE)] operation will always truncate
the [write-ahead log] even if log had already been reset and contained
Index: pages/queryplanner.in
==================================================================
--- pages/queryplanner.in
+++ pages/queryplanner.in
@@ -195,11 +195,11 @@
In the previous query the fruit='Peach' constraint narrowed the result
down to a single row. But the same technique works even if multiple
-rows are obtained. Suppose we looked up the price of Oranges instead
+rows are obtained. Suppose we looked up the price of Oranges instead of
Peaches:
-On of the distinctive features of
+One of the distinctive features of
SQLite is that a database consists of a single disk file.
This simplifies the use of SQLite since moving or backing up a
database is a simple as copying a single file. It also makes
SQLite appropriate for use as an
application file format.
Index: pages/whentouse.in
==================================================================
--- pages/whentouse.in
+++ pages/whentouse.in
@@ -1,126 +1,179 @@
-SQLite is not directly comparable to other SQL database engines such
-as Oracle, PostgreSQL, MySQL, or SQL Server since SQLite is trying to
-solve a very different problem.
+SQLite is not directly comparable to client/server SQL database engines such
+as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to
+solve a different problem.
-Other SQL database engines strive to implement a shared repository
+Client/server SQL database engines strive to implement a shared repository
of enterprise data. They emphasis scalability, concurrency, centralization,
and control.
-
-SQLite, on the other hand, strives to provide local data storage for
+SQLite strives to provide local data storage for
individual applications and devices. SQLite emphasizes economy,
efficiency, reliability, independence, and simplicity.
-SQLite is not designed to compete with
-[http://www.oracle.com/database/index.html | Oracle].
-SQLite is designed to compete with [http://man.he.net/man3/fopen | fopen()].
+SQLite does not compete with client/server databases.
+SQLite competes with [http://man.he.net/man3/fopen | fopen()].
Application File Format Because an SQLite database requires no administration,
+it works well in devices that must operate without expert human support.
+SQLite is a good fit for use in
+cellphones, set-top boxes, televisions, game consoles,
+cameras, watches, kitchen appliances, thermostats, automobiles,
+machine tools, airplanes, remote sensors, drones, medical devices,
+and robots: the "internet of things".
+ Client/server database engines are designed to live inside a
+lovingly-attended datacenter at the core of the network.
+SQLite works there too, but SQLite also thrives at the edge of the network,
+fending for itself while providing fast and
+reliable data services to applications that would otherwise
+have dodgy connectivity.
+ (Further details [application file-format | here].) Application file format
-SQLite has been used with great success as the on-disk file format
+SQLite is often used as the on-disk file format
for desktop applications such as version control systems,
financial analysis tools, media cataloging and editing suites, CAD
packages, record keeping programs, and so forth. The traditional
File/Open operation calls sqlite3_open() to attach to the database
file. Updates happen automatically as application content is revised
so the File/Save menu option becomes superfluous. The File/Save_As
menu option can be implemented using the [backup API].
-There are many advantages to using SQLite as an application file format,
-including:
-
-SQLite allows database files to have any desired filename extension, so
-an application can choose a custom filename extension for its own use, if
-desired. The [application_id pragma] can be used to set an "Application ID"
-integer in the database file so that tools like
-[http://www.darwinsys.com/file/ | file(1)] can determine that the file
-is associated with your application and is not just a generic
-SQL database. Embedded devices and applications Because an SQLite database requires little or no administration,
-SQLite is a good choice for devices or services that must work
-unattended and without human support. SQLite is a good fit for
-use in cellphones, PDAs, set-top boxes, and/or appliances. It also
-works well as an embedded database in downloadable consumer applications.
- There are many benefits to this approach, including improved
+application performance, reduced cost and complexity, and
+improved reliability. See technical notes
+[file-format benefits|here] and
+[application file-format|here] for details.
Websites SQLite usually will work great as the database engine for low to
-medium traffic websites (which is to say, 99.9% of all websites).
-The amount of web traffic that SQLite can handle depends, of course,
+ SQLite works great as the database engine for most low to
+medium traffic websites (which is to say, most websites).
+The amount of web traffic that SQLite can handle depends
on how heavily the website uses its database. Generally
speaking, any site that gets fewer than 100K hits/day should work
fine with SQLite.
The 100K hits/day figure is a conservative estimate, not a
hard upper bound.
SQLite has been demonstrated to work with 10 times that amount
of traffic. The SQLite website ([https://www.sqlite.org/]) uses SQLite itself,
+of course, and as of this writing (2015) it handles about 400K to 500K
+HTTP requests per day, about 15-20% of which are dynamic pages touching
+the database. Each dynamic page does roughly 200 SQL statements.
+This setup runs on a single VM that shares a physical server with 23 others
+and yet still keeps the load average below 0.1 most of the time.
+ Data analysis
+People who understand SQL can employ the
+[command-line shell|sqlite3 command-line shell] (or various third-party
+SQLite access programs) to analyze large
+datasets. Raw data can be imported from CSV files, then that
+data can be sliced and diced to generate a myriad of summary
+reports. More complex analysis can be done using simple scripts written
+in Tcl or Python (both of which come with SQLite built-in) or in R or
+other languages using readily available adaptors.
+Possible uses include website log analysis, sports
+statistics analysis, compilation of programming metrics, and
+analysis of experimental results. Many bioinformatics researchers
+use SQLite in this way.
+
+The same thing can be done with an enterprise client/server
+database, of course. The advantage of SQLite is
+that it is easier to install and use and the resulting database
+is a single file that can be written to a USB memory stick
+or emailed to a colleague.
+ Cache for enterprise data
+Many applications use SQLite as a cache of relevant content from
+an enterprise RDBMS.
+This reduces latency, since most queries now occur against the local
+cache and avoid a network round-trip. It also reduces the load
+on the network and on the central database server. And in many cases,
+it means that the client-side application can continue operating during
+network outages.
+ Server-side database
+Systems designers
+report success using SQLite as a data store on server applications
+running in the datacenter, or in other words, using SQLite as the underlying
+storage engine for an application-specific database server. With this pattern, the overall system is still client/server:
+clients send requests to the server and get back replies over the network.
+But instead of sending generic SQL and getting back raw table content,
+the client requests and server responses are high-level and
+application-specific.
+The server translates requests into multiple SQL queries, gathers the
+results, does post-processing, filtering, and analysis, then constructs
+a high-level reply containing only the essential information. Developers report that SQLite is often faster than a client/server
+SQL database engine in this scenario.
+Database requests are serialized by the server, so concurrency is not
+an issue. Concurrency is also improved by "database sharding":
+using separate database files for different subdomains. For
+example, the server might have a separate SQLite database for each
+user, so that the server can handle hundreds or thousands of simultaneous
+connections, but each SQLite database is only used by one connection. File archives
+The [https://www.sqlite.org/sqlar|SQLite Archiver] project shows how
+SQLite can be used as a substitute for ZIP archives or Tarballs.
+An archive of files stored in SQLite is only very slightly larger, and
+in some cases actually smaller, than the equivalent ZIP archive.
+And an SQLite archive features incremental and atomic updating
+and the ability to store much richer metadata.
+ SQLite archives are useful as the distribution format for software
+or content updates that are broadcast to many clients. Variations
+on this idea are used, for example, to transmit TV programming guides
+to set-top boxes and to send over-the-air updates to vehicle navigation
+systems. Replacement for ad hoc disk files Many programs use
@@ -144,59 +197,33 @@
greater flexibility since new columns and indices can be added without
having to recode every query.
Command-line dataset analysis tool
-Experienced SQL users can employ
-the command-line sqlite3 program to analyze miscellaneous
-datasets. Raw data can be imported from CSV files, then that
-data can be sliced and diced to generate a myriad of summary
-reports. Possible uses include website log analysis, sports
-statistics analysis, compilation of programming metrics, and
-analysis of experimental results.
-
-You can also do the same thing with an enterprise client/server
-database, of course. The advantages to using SQLite in this situation
-are that SQLite is much easier to set up and the resulting database
-is a single file that you can store on a floppy disk or flash-memory stick
-or email to a colleague.
- Stand-in for an enterprise database during demos or testing
-If you are writing a client application for an enterprise database engine,
-it makes sense to use a generic database backend that allows you to connect
-to many different kinds of SQL database engines. It makes even better
-sense to
-go ahead and include SQLite in the mix of supported databases and to statically
+Client applications typically use a generic database interface that allows
+connections to various SQL database engines. It makes good sense to
+include SQLite in the mix of supported databases and to statically
link the SQLite engine in with the client. That way the client program
can be used standalone with an SQLite data file for testing or for
demonstrations.
Database Pedagogy Education and Training
Because it is simple to setup and use (installation is trivial: just
copy the sqlite3 or sqlite3.exe executable to the target machine
and run it) SQLite makes a good database engine for use in teaching SQL.
Students can easily create as many databases as they like and can
email databases to the instructor for comments or grading. For more
advanced students who are interested in studying how an RDBMS is
implemented, the modular and well-commented and documented SQLite code
-can serve as a good basis. This is not to say that SQLite is an accurate
-model of how other database engines are implemented, but rather a student who
-understands how SQLite works can more quickly comprehend the operational
-principles of other systems.
+can serve as a good basis.
Experimental SQL language extensions Client/Server Applications
- If you have many client programs accessing a common database
-over a network, you should consider using a client/server database
+ If there are many client programs sending SQL to the same
+database over a network, then use a client/server database
engine instead of SQLite. SQLite will work over a network filesystem,
but because of the latency associated with most network filesystems,
-performance will not be great. Also, the file locking logic of
-many network filesystems implementation contains bugs (on both Unix
-and Windows). If file locking does not work like it should,
-it might be possible for two or more client programs to modify the
+performance will not be great. Also, file locking logic is buggy
+many network filesystem implementations (on both Unix and Windows).
+If file locking does not work correctly,
+two or more clients might try to modify the
same part of the same database at the same time, resulting in
-database corruption. Because this problem results from bugs in
+corruption. Because this problem results from bugs in
the underlying filesystem implementation, there is nothing SQLite
can do to prevent it. A good rule of thumb is that you should avoid using SQLite
-in situations where the same database will be accessed simultaneously
-from many computers over a network filesystem. A good rule of thumb is to avoid using SQLite
+in situations where the same database will be accessed directly
+(without an intervening application server) and simultaneously
+from many computers over a network. High-volume Websites SQLite will normally work fine as the database backend to a website.
-But if you website is so busy that you are thinking of splitting the
-database component off onto a separate machine, then you should
-definitely consider using an enterprise-class client/server database
-engine instead of SQLite.
)^
$txt
"
hd_puts "
"
}
+
+newsitem {2015-02-25} {Release 3.8.8.3} {
+1.4 Multiple Result Rows
SQLite's Use Of Temporary Disk Files
1.0 Introduction
Appropriate Uses For SQLite
+Appropriate Uses For SQLite
Situations Where SQLite Works Well
-
-
-
-Situations Where Another RDBMS May Work Better
+Situations Where A Client/Server RDBMS May Work Better
Very large datasets
An SQLite database is limited in size to 140 terabytes @@ -256,14 +283,80 @@
High Concurrency
SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. -For many situations, this is not a problem. Each application +For many situations, this is not a problem. Writer queue up. Each application does its database work quickly and moves on, and no lock lasts for more -than a few dozen milliseconds. But there are some applications that require +than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.
Is the data separated from the application by a network? + → choose client/server
+ +Relational database engines act as bandwidth-reducing data filters. +So it is best to keep the database engine and the data on +the same physical device so that the high-bandwidth engine-to-disk +link does not have to traverse the network, only the lower-bandwidth +application-to-engine link. + +
But SQLite is built into the application. So if the data is on a +separate device from the application, it is required that the higher +bandwidth engine-to-disk link be across the network. This works, but +it is suboptimal. Hence, it is usually better to select a client/server +database engine when the data is on a separate device from the +application. + +
Nota Bene: +In this rule, "application" means the code that issues SQL statements. +If the "application" is an [server-side database|application server] and +if the content resides on the same physical machine as the application server, +then SQLite might still be appropriate even though the end user is +another network hop away.
+Many concurrent writers? → choose client/server
+ +If many threads and/or processes need to write the +database at the same instant (and they cannot queue up and take turns) +then it is best to select a database engine that supports that +capability, which always means a client/server database engine. + +
SQLite only supports one writer at a time per database file. +But in most cases, a write transaction only takes milliseconds and +so multiple writers can simply take turns. SQLite will handle +more write concurrency that many people suspect. Nevertheless, +client/server database systems, because they have a long-running +server process at hand to coordinate access, can usually handle +far more write concurrency than SQLite ever will. +
Big data? → choose client/server
+ +If your data will grow to a size that you are uncomfortable +or unable to fit into a single disk file, then you should select +a solution other than SQLite. SQLite supports databases up to +140 terabytes in size, assuming you can find a disk drive and filesystem +that will support 140-terabyte files. Even so, when the size of the +content looks like it might creep into the terabyte range, it would +be good to consider a centralized client/server database. +
Otherwise → choose SQLite!
+ +For device-local storage with low writer concurrency and less than a +terabyte of content, SQLite is almost always a better solution. SQLite +is fast and reliable and it requires no configuration or maintenance. +It keeps thing simple. SQLite "just works". +