1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Appropriate Uses For SQLite</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<h1 align="center">Appropriate Uses For SQLite</h1>
<p>
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.
</p>
<p>
Client/server SQL database engines strive to implement a shared repository
of enterprise data. They emphasize scalability, concurrency, centralization,
and control.
SQLite strives to provide local data storage for
individual applications and devices. SQLite emphasizes economy,
efficiency, reliability, independence, and simplicity.
</p>
<p>
SQLite does not compete with client/server databases.
SQLite competes with <a href="http://man.he.net/man3/fopen">fopen()</a>.
</p>
<h2>Situations Where SQLite Works Well</h2>
<ul>
<a name="appfileformat"></a>
<li><b>Embedded devices and the internet of things</b>
<p>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".
</p>
<p>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.
</li>
<li><p><b>Application file format</b></p>
<p>
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 <a href="backup.html">backup API</a>.
</p>
<p>There are many benefits to this approach, including improved
performance, reduced cost and complexity, and
improved reliability. See technical notes
<a href="aff_short.html">"aff_short.html"</a> and
<a href="appfileformat.html">"appfileformat.html"</a> and
<a href="fasterthanfs.html">"fasterthanfs.html"</a> for more information.
This use case is closely related to the
<a href="#wireproto">data transfer format</a> and
<a href="#container">data container</a> use cases below.
</li>
<a name="website"></a>
<li><p><b>Websites</b></p>
<p>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.</p>
<p>The SQLite website (<a href="https://www.sqlite.org/">https://www.sqlite.org/</a>) 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. Dynamic content uses <a href="np1queryprob.html">about 200 SQL statements per webpage</a>.
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.
<p>See also: <a href="https://news.ycombinator.com/item?id=33975635">Hacker New discussion from 2022-12-13</a>.
</li>
<li><p><b>Data analysis</b></p>
<p>
People who understand SQL can employ the
<a href="cli.html">sqlite3 command-line shell</a> (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.
</p>
<p>
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.
</p>
</li>
<li><p><b>Cache for enterprise data</b></p>
<p>
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.
</p>
</li>
<a name="serversidedb"></a>
<li><p><b>Server-side database</b></p>
<p>
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.</p>
<p>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.</p>
<p>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.</p>
</li>
<a name="wireproto"></a>
<li><p><b>Data transfer format</b><p>
<p>Because an SQLite database is a single compact file in a
<a href="fileformat2.html">well-defined cross-platform format</a>, it is often used
as a container for transferring content from one system to another.
The sender gathers content into an SQLite database file, transfers
that one file to the receiver, then the receiver uses SQL to extract
the content as needed.
<p>An SQLite database facilitates data transfer between systems even
when the endpoints have different word sizes and/or byte orders.
The data can be a complex mix of large binary blobs, text, and small
numeric or boolean values. The data format can be easily extended
by adding new tables and/or columns, without breaking legacy receivers.
The SQL query language means that receivers are not required to parse
the entire transfer all at once, but can instead query the
received content as needed. The data format is "transparent" in the
sense that it is easily decoded for human viewing using
a variety of universally available, open-source tools, from multiple
vendors.
</li>
<a name="container"></a>
<li><p><b>File archive and/or data container</b></p>
<p>
The <a href="sqlar.html">SQLite Archive</a> idea 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.
</p>
<p><a href="https://www.fossil-scm.org/">Fossil</a> version 2.5 and later offers
<a href="sqlar.html">SQLite Archive files</a> as a download format, in addition
to traditional tarball and ZIP archive.
The <a href="cli.html">sqlite3.exe command-line shell</a> version 3.22.0 and later will create,
list, or unpack an SQL archiving using the
<a href="cli.html#sqlar">.archive command</a>.</p>
<p>
SQLite is a good solution for any situation that requires bundling
diverse content into a self-contained and self-describing package
for shipment across a network.
Content is encoded in a
<a href="fileformat2.html">well-defined, cross-platform, and stable file format</a>.
The encoding is efficient, and receivers can extract small subsets
of the content without having to read and parse the entire file.
</p>
<p>SQL 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.</p>
</li>
<li><p><b>Replacement for <i>ad hoc</i> disk files</b></p>
<p>Many programs use
<a href="http://man.he.net/man3/fopen">fopen()</a>,
<a href="http://man.he.net/man3/fread">fread()</a>, and
<a href="http://man.he.net/man3/fwrite">fwrite()</a> to create and
manage files of data in home-grown formats. SQLite works
particularly well as a
replacement for these <i>ad hoc</i> data files.
Contrary to intuition, SQLite can be <a href="fasterthanfs.html">faster than the filesystem</a>
for reading and writing content to disk.
</li>
<li><p><b>Internal or temporary databases</b></p>
<p>
For programs that have a lot of data that must be sifted and sorted
in diverse ways, it is often easier and quicker to load the data into
an in-memory SQLite database and use queries with joins and ORDER BY
clauses to extract the data in the form and order needed rather than
to try to code the same operations manually.
Using an SQL database internally in this way also gives the program
greater flexibility since new columns and indices can be added without
having to recode every query.
</p>
</li>
<li><p><b>Stand-in for an enterprise database during demos or testing</b></p>
<p>
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.
</p>
</li>
<li><p><b>Education and Training</b></p>
<p>
Because it is simple to setup and use (installation is trivial: just
copy the <b>sqlite3</b> or <b>sqlite3.exe</b> 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.
</p>
</li>
<li><p><b>Experimental SQL language extensions</b></p>
<p>The simple, modular design of SQLite makes it a good platform for
prototyping new, experimental database language features or ideas.
</p>
</li>
</ul>
<h2>Situations Where A Client/Server RDBMS May Work Better</h2>
<ul>
<li><p><b>Client/Server Applications</b><p>
<p>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, file locking logic is buggy in
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
corruption. Because this problem results from bugs in
the underlying filesystem implementation, there is nothing SQLite
can do to prevent it.</p>
<p>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.</p>
</li>
<li><p><b>High-volume Websites</b></p>
<p>SQLite will normally work fine as the database backend to a website.
But if the website is write-intensive or is so busy that it requires
multiple servers, then consider using an enterprise-class client/server
database engine instead of SQLite.</p>
</li>
<li><p><b>Very large datasets</b></p>
<p>An SQLite database is limited in size to 281 terabytes
(2<sup><small>48</small></sup> bytes, 256 tibibytes).
And even if it could handle larger databases, SQLite stores the entire
database in a single disk file and many filesystems limit the maximum
size of files to something less than this. So if you are contemplating
databases of this magnitude, you would do well to consider using a
client/server database engine that spreads its content across multiple
disk files, and perhaps across multiple volumes.
</p>
</li>
<li><p><b>High Concurrency</b></p>
<p>
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. Writers 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
more concurrency, and those applications may need to seek a different
solution.
</p>
</li>
</ul>
<a name="dbcklst"></a>
<h2>Checklist For Choosing The Right Database Engine</h2>
<ol>
<li><p><b>Is the data separated from the application by a network?
→ choose client/server</b></p>
<p>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.
<p>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.
<p><em>Nota Bene:</em>
In this rule, "application" means the code that issues SQL statements.
If the "application" is an <a href="whentouse.html#serversidedb">application server</a> 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.</p>
</li>
<li><p><b>Many concurrent writers? → choose client/server</b></p>
<p>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.
<p>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 than 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.
</li>
<li><p><b>Big data? → choose client/server</b></p>
<p>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
281 terabytes in size, assuming you can find a disk drive and filesystem
that will support 281-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.
</li>
<li><p><b>Otherwise → choose SQLite!</b></p>
<p>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 things simple. SQLite "just works".
</li>
</ol>
|
