Debian Codegraph @

www/whentouse.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Appropriate Uses For SQLite</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 Appropriate Uses For SQLite
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#situations_where_sqlite_works_well">1. Situations Where SQLite Works Well</a></div>
126 <div class="fancy-toc1"><a href="#situations_where_a_client_server_rdbms_may_work_better">2. Situations Where A Client/Server RDBMS May Work Better</a></div>
127 <div class="fancy-toc1"><a href="#checklist_for_choosing_the_right_database_engine">3. Checklist For Choosing The Right Database Engine</a></div>
128 </div>
129 </details>
130 </div>
131
132
133
134
135 <p>
136 SQLite is not directly comparable to client/server SQL database engines such
137 as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to
138 solve a different problem.
139 </p>
140
141 <p>
142 Client/server SQL database engines strive to implement a shared repository
143 of enterprise data. They emphasize scalability, concurrency, centralization,
144 and control.
145 SQLite strives to provide local data storage for
146 individual applications and devices. SQLite emphasizes economy,
147 efficiency, reliability, independence, and simplicity.
148 </p>
149
150
151 <p>
152 SQLite does not compete with client/server databases.
153 SQLite competes with <a href="http://man.he.net/man3/fopen">fopen()</a>.
154 </p>
155
156 <h1 id="situations_where_sqlite_works_well"><span>1. </span>Situations Where SQLite Works Well</h1>
157
158 <ul>
159
160 <a name="appfileformat"></a>
161
162 <li><b>Embedded devices and the internet of things</b>
163
164 <p>Because an SQLite database requires no administration,
165 it works well in devices that must operate without expert human support.
166 SQLite is a good fit for use in
167 cellphones, set-top boxes, televisions, game consoles,
168 cameras, watches, kitchen appliances, thermostats, automobiles,
169 machine tools, airplanes, remote sensors, drones, medical devices,
170 and robots: the "internet of things".
171 </p>
172
173 <p>Client/server database engines are designed to live inside a
174 lovingly-attended datacenter at the core of the network.
175 SQLite works there too, but SQLite also thrives at the edge of the network,
176 fending for itself while providing fast and
177 reliable data services to applications that would otherwise
178 have dodgy connectivity.
179 </p></li>
180
181 <li><p><b>Application file format</b></p>
182
183 <p>
184 SQLite is often used as the on-disk file format
185 for desktop applications such as version control systems,
186 financial analysis tools, media cataloging and editing suites, CAD
187 packages, record keeping programs, and so forth. The traditional
188 File/Open operation calls sqlite3_open() to attach to the database
189 file. Updates happen automatically as application content is revised
190 so the File/Save menu option becomes superfluous. The File/Save_As
191 menu option can be implemented using the <a href="backup.html">backup API</a>.
192 </p>
193
194 <p>There are many benefits to this approach, including improved
195 performance, reduced cost and complexity, and
196 improved reliability. See technical notes
197 <a href="aff_short.html">"aff_short.html"</a> and
198 <a href="appfileformat.html">"appfileformat.html"</a> and
199 <a href="fasterthanfs.html">"fasterthanfs.html"</a> for more information.
200 This use case is closely related to the
201 <a href="#wireproto">data transfer format</a> and
202 <a href="#container">data container</a> use cases below.
203 </p></li>
204
205 <a name="website"></a>
206
207 <li><p><b>Websites</b></p>
208
209 <p>SQLite works great as the database engine for most low to
210 medium traffic websites (which is to say, most websites).
211 The amount of web traffic that SQLite can handle depends
212 on how heavily the website uses its database. Generally
213 speaking, any site that gets fewer than 100K hits/day should work
214 fine with SQLite.
215 The 100K hits/day figure is a conservative estimate, not a
216 hard upper bound.
217 SQLite has been demonstrated to work with 10 times that amount
218 of traffic.</p>
219
220 <p>The SQLite website (<a href="https://sqlite.org/">https://sqlite.org/</a>) uses SQLite itself,
221 of course, and as of this writing (2015) it handles about 400K to 500K
222 HTTP requests per day, about 15-20% of which are dynamic pages touching
223 the database. Dynamic content uses <a href="np1queryprob.html">about 200 SQL statements per webpage</a>.
224 This setup runs on a single VM that shares a physical server with 23 others
225 and yet still keeps the load average below 0.1 most of the time.
226
227 </p><p>See also: <a href="https://news.ycombinator.com/item?id=33975635">Hacker News discussion from 2022-12-13</a>.
228 </p></li>
229
230 <li><p><b>Data analysis</b></p>
231
232 <p>
233 People who understand SQL can employ the
234 <a href="cli.html">sqlite3 command-line shell</a> (or various third-party
235 SQLite access programs) to analyze large
236 datasets. Raw data can be imported from CSV files, then that
237 data can be sliced and diced to generate a myriad of summary
238 reports. More complex analysis can be done using simple scripts written
239 in Tcl or Python (both of which come with SQLite built-in) or in R or
240 other languages using readily available adaptors.
241 Possible uses include website log analysis, sports
242 statistics analysis, compilation of programming metrics, and
243 analysis of experimental results. Many bioinformatics researchers
244 use SQLite in this way.
245 </p>
246
247 <p>
248 The same thing can be done with an enterprise client/server
249 database, of course. The advantage of SQLite is
250 that it is easier to install and use and the resulting database
251 is a single file that can be written to a USB memory stick
252 or emailed to a colleague.
253 </p>
254 </li>
255
256 <li><p><b>Cache for enterprise data</b></p>
257
258 <p>
259 Many applications use SQLite as a cache of relevant content from
260 an enterprise RDBMS.
261 This reduces latency, since most queries now occur against the local
262 cache and avoid a network round-trip. It also reduces the load
263 on the network and on the central database server. And in many cases,
264 it means that the client-side application can continue operating during
265 network outages.
266 </p>
267 </li>
268
269 <a name="serversidedb"></a>
270
271 <li><p><b>Server-side database</b></p>
272
273 <p>
274 Systems designers
275 report success using SQLite as a data store on server applications
276 running in the datacenter, or in other words, using SQLite as the underlying
277 storage engine for an application-specific database server.</p>
278
279 <p>With this pattern, the overall system is still client/server:
280 clients send requests to the server and get back replies over the network.
281 But instead of sending generic SQL and getting back raw table content,
282 the client requests and server responses are high-level and
283 application-specific.
284 The server translates requests into multiple SQL queries, gathers the
285 results, does post-processing, filtering, and analysis, then constructs
286 a high-level reply containing only the essential information.</p>
287
288 <p>Developers report that SQLite is often faster than a client/server
289 SQL database engine in this scenario.
290 Database requests are serialized by the server, so concurrency is not
291 an issue. Concurrency is also improved by "database sharding":
292 using separate database files for different subdomains. For
293 example, the server might have a separate SQLite database for each
294 user, so that the server can handle hundreds or thousands of simultaneous
295 connections, but each SQLite database is only used by one connection.</p>
296 </li>
297
298 <a name="wireproto"></a>
299
300 <li><p><b>Data transfer format</b></p><p>
301
302 </p><p>Because an SQLite database is a single compact file in a
303 <a href="fileformat2.html">well-defined cross-platform format</a>, it is often used
304 as a container for transferring content from one system to another.
305 The sender gathers content into an SQLite database file, transfers
306 that one file to the receiver, then the receiver uses SQL to extract
307 the content as needed.
308
309 </p><p>An SQLite database facilitates data transfer between systems even
310 when the endpoints have different word sizes and/or byte orders.
311 The data can be a complex mix of large binary blobs, text, and small
312 numeric or boolean values. The data format can be easily extended
313 by adding new tables and/or columns, without breaking legacy receivers.
314 The SQL query language means that receivers are not required to parse
315 the entire transfer all at once, but can instead query the
316 received content as needed. The data format is "transparent" in the
317 sense that it is easily decoded for human viewing using
318 a variety of universally available, open-source tools, from multiple
319 vendors.
320 </p></li>
321
322 <a name="container"></a>
323
324 <li><p><b>File archive and/or data container</b></p>
325
326 <p>
327 The <a href="sqlar.html">SQLite Archive</a> idea shows how
328 SQLite can be used as a substitute for ZIP archives or Tarballs.
329 An archive of files stored in SQLite is only very slightly larger, and
330 in some cases actually smaller, than the equivalent ZIP archive.
331 And an SQLite archive features incremental and atomic updating
332 and the ability to store much richer metadata.
333 </p>
334
335 <p><a href="https://www.fossil-scm.org/">Fossil</a> version 2.5 and later offers
336 <a href="sqlar.html">SQLite Archive files</a> as a download format, in addition
337 to traditional tarball and ZIP archive.
338 The <a href="cli.html">sqlite3.exe command-line shell</a> version 3.22.0 and later will create,
339 list, or unpack an SQL archive using the
340 <a href="cli.html#sqlar">.archive command</a>.</p>
341
342 <p>
343 SQLite is a good solution for any situation that requires bundling
344 diverse content into a self-contained and self-describing package
345 for shipment across a network.
346 Content is encoded in a
347 <a href="fileformat2.html">well-defined, cross-platform, and stable file format</a>.
348 The encoding is efficient, and receivers can extract small subsets
349 of the content without having to read and parse the entire file.
350 </p>
351
352 <p>SQL archives are useful as the distribution format for software
353 or content updates that are broadcast to many clients. Variations
354 on this idea are used, for example, to transmit TV programming guides
355 to set-top boxes and to send over-the-air updates to vehicle navigation
356 systems.</p>
357 </li>
358
359 <li><p><b>Replacement for <i>ad hoc</i> disk files</b></p>
360
361 <p>Many programs use
362 <a href="http://man.he.net/man3/fopen">fopen()</a>,
363 <a href="http://man.he.net/man3/fread">fread()</a>, and
364 <a href="http://man.he.net/man3/fwrite">fwrite()</a> to create and
365 manage files of data in home-grown formats. SQLite works
366 particularly well as a
367 replacement for these <i>ad hoc</i> data files.
368 Contrary to intuition, SQLite can be <a href="fasterthanfs.html">faster than the filesystem</a>
369 for reading and writing content to disk.
370 </p></li>
371
372 <li><p><b>Internal or temporary databases</b></p>
373
374 <p>
375 For programs that have a lot of data that must be sifted and sorted
376 in diverse ways, it is often easier and quicker to load the data into
377 an in-memory SQLite database and use queries with joins and ORDER BY
378 clauses to extract the data in the form and order needed rather than
379 to try to code the same operations manually.
380 Using an SQL database internally in this way also gives the program
381 greater flexibility since new columns and indices can be added without
382 having to recode every query.
383 </p>
384 </li>
385
386 <li><p><b>Stand-in for an enterprise database during demos or testing</b></p>
387
388 <p>
389 Client applications typically use a generic database interface that allows
390 connections to various SQL database engines. It makes good sense to
391 include SQLite in the mix of supported databases and to statically
392 link the SQLite engine in with the client. That way the client program
393 can be used standalone with an SQLite data file for testing or for
394 demonstrations.
395 </p>
396 </li>
397
398 <li><p><b>Education and Training</b></p>
399
400 <p>
401 Because it is simple to setup and use (installation is trivial: just
402 copy the <b>sqlite3</b> or <b>sqlite3.exe</b> executable to the target machine
403 and run it) SQLite makes a good database engine for use in teaching SQL.
404 Students can easily create as many databases as they like and can
405 email databases to the instructor for comments or grading. For more
406 advanced students who are interested in studying how an RDBMS is
407 implemented, the modular and well-commented and documented SQLite code
408 can serve as a good basis.
409 </p>
410 </li>
411
412 <li><p><b>Experimental SQL language extensions</b></p>
413
414 <p>The simple, modular design of SQLite makes it a good platform for
415 prototyping new, experimental database language features or ideas.
416 </p>
417 </li>
418
419
420 </ul>
421
422 <h1 id="situations_where_a_client_server_rdbms_may_work_better"><span>2. </span>Situations Where A Client/Server RDBMS May Work Better</h1>
423
424 <ul>
425 <li><p><b>Client/Server Applications</b></p><p>
426
427 </p><p>If there are many client programs sending SQL to the same
428 database over a network, then use a client/server database
429 engine instead of SQLite. SQLite will work over a network filesystem,
430 but because of the latency associated with most network filesystems,
431 performance will not be great. Also, file locking logic is buggy in
432 many network filesystem implementations (on both Unix and Windows).
433 If file locking does not work correctly,
434 two or more clients might try to modify the
435 same part of the same database at the same time, resulting in
436 corruption. Because this problem results from bugs in
437 the underlying filesystem implementation, there is nothing SQLite
438 can do to prevent it.</p>
439
440 <p>A good rule of thumb is to avoid using SQLite
441 in situations where the same database will be accessed directly
442 (without an intervening application server) and simultaneously
443 from many computers over a network.</p>
444 </li>
445
446 <li><p><b>High-volume Websites</b></p>
447
448 <p>SQLite will normally work fine as the database backend to a website.
449 But if the website is write-intensive or is so busy that it requires
450 multiple servers, then consider using an enterprise-class client/server
451 database engine instead of SQLite.</p>
452 </li>
453
454 <li><p><b>Very large datasets</b></p>
455
456 <p>An SQLite database is limited in size to 281 terabytes
457 (2<sup><small>48</small></sup> bytes, 256 tibibytes).
458 And even if it could handle larger databases, SQLite stores the entire
459 database in a single disk file and many filesystems limit the maximum
460 size of files to something less than this. So if you are contemplating
461 databases of this magnitude, you would do well to consider using a
462 client/server database engine that spreads its content across multiple
463 disk files, and perhaps across multiple volumes.
464 </p>
465 </li>
466
467 <li><p><b>High Concurrency</b></p>
468
469 <p>
470 SQLite supports an unlimited number of simultaneous readers, but it
471 will only allow one writer at any instant in time.
472 For many situations, this is not a problem. Writers queue up. Each application
473 does its database work quickly and moves on, and no lock lasts for more
474 than a few dozen milliseconds. But there are some applications that require
475 more concurrency, and those applications may need to seek a different
476 solution.
477 </p>
478 </li>
479
480 </ul>
481
482 <a name="dbcklst"></a>
483
484 <h1 id="checklist_for_choosing_the_right_database_engine"><span>3. </span>Checklist For Choosing The Right Database Engine</h1>
485
486 <ol>
487 <li><p><b>Is the data separated from the application by a network?
488 &rarr; choose client/server</b></p>
489
490 <p>Relational database engines act as bandwidth-reducing data filters.
491 So it is best to keep the database engine and the data on
492 the same physical device so that the high-bandwidth engine-to-disk
493 link does not have to traverse the network, only the lower-bandwidth
494 application-to-engine link.
495
496 </p><p>But SQLite is built into the application. So if the data is on a
497 separate device from the application, it is required that the higher
498 bandwidth engine-to-disk link be across the network. This works, but
499 it is suboptimal. Hence, it is usually better to select a client/server
500 database engine when the data is on a separate device from the
501 application.
502
503 </p><p><em>Nota Bene:</em>
504 In this rule, "application" means the code that issues SQL statements.
505 If the "application" is an <a href="whentouse.html#serversidedb">application server</a> and
506 if the content resides on the same physical machine as the application server,
507 then SQLite might still be appropriate even though the end user is
508 another network hop away.</p>
509 </li>
510
511 <li><p><b>Many concurrent writers? &rarr; choose client/server</b></p>
512
513 <p>If many threads and/or processes need to write the
514 database at the same instant (and they cannot queue up and take turns)
515 then it is best to select a database engine that supports that
516 capability, which always means a client/server database engine.
517
518 </p><p>SQLite only supports one writer at a time per database file.
519 But in most cases, a write transaction only takes milliseconds and
520 so multiple writers can simply take turns. SQLite will handle
521 more write concurrency than many people suspect. Nevertheless,
522 client/server database systems, because they have a long-running
523 server process at hand to coordinate access, can usually handle
524 far more write concurrency than SQLite ever will.
525 </p></li>
526
527 <li><p><b>Big data? &rarr; choose client/server</b></p>
528
529 <p>If your data will grow to a size that you are uncomfortable
530 or unable to fit into a single disk file, then you should select
531 a solution other than SQLite. SQLite supports databases up to
532 281 terabytes in size, assuming you can find a disk drive and filesystem
533 that will support 281-terabyte files. Even so, when the size of the
534 content looks like it might creep into the terabyte range, it would
535 be good to consider a centralized client/server database.
536 </p></li>
537
538 <li><p><b>Otherwise &rarr; choose SQLite!</b></p>
539
540 <p>For device-local storage with low writer concurrency and less than a
541 terabyte of content, SQLite is almost always a better solution. SQLite
542 is fast and reliable and it requires no configuration or maintenance.
543 It keeps things simple. SQLite "just works".
544 </p></li>
545 </ol>
546 <p align="center"><small><i>This page was last updated on 2025-05-31 13:08:22Z </small></i></p>
547