Appropriate Uses For sqlite

sqlite is different from most other sql database engines in that its primary design goal is to be simple:

• Simple to administer
• Simple to operate
• Simple to embed in a larger program
• Simple to maintain and customize

Many people like sqlite because it is small and fast. But those qualities are just happy accidents. Users also find that sqlite is very reliable. Reliability is a consequence of simplicity. With less complication,there is less to go wrong. So,yes,sqlite is small,fast,and reliable,but first and foremost,sqlite strives to be simple.

Simplicity in a database engine can be either a strength or a weakness,depending on what you are trying to do. In order to achieve simplicity,sqlite has had to sacrifice other characteristics that some people find useful,such as high concurrency,fine-grained access control,a rich set of built-in functions,stored procedures,esoteric sql language features,XML and/or Java extensions,tera- or peta-byte scalability,and so forth. If you need some of these features and do not mind the added complexity that they bring,then sqlite is probably not the database for you. sqlite is not intended to be an enterprise database engine. It is not designed to compete with Oracle or Postgresql.

The basic rule of thumb for when it is appropriate to use sqlite is this: Use sqlite in situations where simplicity of administration,implementation,and maintenance are more important than the countless complex features that enterprise database engines provide. As it turns out,situations where simplicity is the better choice are more common than many people realize.

Another way to look at sqlite is this: sqlite is not designed to replaceOracle. It is designed to replacefopen().

Situations Where sqlite Works Well

• Application File Format

  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 atomically as application content is revised so the File/Save menu option become superfluous. The File/Save_As menu option can be implemented using thebackup API.

  There are many advantages to using sqlite as an application file format,including:

  1. There is no file parsing and generating code to write and debug.
  2. Content can be accessed and updated using powerful sql queries,greatly reducing the complexity of the application code.
  3. Extending the file format for new capabilities in later releases is a simple as adding new tables or new columns to existing tables.
  4. Diverse content which might otherwise be stored as a "pile-of-files" can be encapsulated into a single disk file.
  5. The content can be viewed using third-party tools.
  6. The application file is portable across all operating systems,32-bit and 64-bit and big- and little-endian architectures.
  7. The application only has to load as much data as it needs,rather than reading the entire application file and holding a complete parse in memory. Startup time and memory consumption are reduced.
  8. Small edits only overwrite the parts of the file that change,not the entire file,thus improving performance and reducing wear on SSD drives.
  9. Content is updated continously and atomically so that there is no work lost in the event of a power failure or crash.
  10. Applications can leverage thefull-text searchandRTREEcapablities that are built into sqlite.
  11. Performance problems can often be resolved usingCREATE INDEXrather than redesigning,rewriting,and retesting application code.
  12. A federation of programs,perhaps written in different programming languages,can all access the same application file with no compatibility concerns.
  13. Multiple processes can attach to the same application file and can read and write without interfering with each another.
  14. Cross-session undo/redo can be implemented using triggers.
  15. In many common cases,loading content from an sqlite database is faster than loading content out of individual files. SeeInternal Versus External BLOBsfor additional information.
  16. Content stored in an sqlite database is more likely to be recoverable decades in the future,long after all traces of the original application have been lost. Data lives longer than code.

  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. Theapplication_id pragmacan be used to set an "Application ID" integer in the database file so that tools likefile(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.

• 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,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.

• Replacement forad hocdisk files

  Many programs usefopen(),fread(),andfwrite()to create and manage files of data in home-grown formats. sqlite works particularly well as a replacement for thesead hocdata files.

• Internal or temporary databases

  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.

• Command-line dataset analysis tool

  Experienced sql users can employ the command-linesqlite3program 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 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

  Because it is simple to setup and use (installation is trivial: just copy thesqlite3orsqlite3.exeexecutable 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.

• Experimental sql language extensions

  The simple,modular design of sqlite makes it a good platform for prototyping new,experimental database language features or ideas.

Situations Where Another RDBMS May Work Better

• Client/Server Applications

  If you have many client programs accessing a common database over a network,you should consider using 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 same part of the same database at the same time,resulting in database 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.

• 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.

• Very large datasets

  An sqlite database is limited in size to 140 terabytes (2⁴⁷bytes,128 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.

• 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 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.