Editing SQLite (section)

==Features==
SQLite implements most of the [[SQL-92]] standard for SQL, but lacks some features. For example, it only partially provides [[database trigger|triggers]] and cannot write to [[view (database)|views]] (however, it provides INSTEAD OF triggers that provide this functionality). Its support of [[Data definition language#ALTER statement|ALTER TABLE]] statements is limited.<ref>{{Cite web|url=https://sqlite.org/changes.html|title=Release History of SQLite|access-date=2021-03-22|archive-date=2021-03-16|archive-url=https://web.archive.org/web/20210316043517/https://sqlite.org/changes.html|url-status=live}}</ref>

SQLite uses an unusual [[type system]] for an SQL-compatible DBMS: instead of assigning a [[SQL data types|type]] to a column as in most SQL database systems, types are assigned to individual values; in language terms it is ''dynamically typed''. Moreover, it is ''weakly typed'' in some of the same ways that [[Perl]] is: one can insert a [[string (computer science)|string]] into an [[Integer (computer science)|integer]] column (although SQLite will try to convert the string to an integer first, if the column's preferred type is integer). This adds flexibility to columns, especially when bound to a dynamically typed scripting language. However, the technique is not portable to other SQL products. A common criticism is that SQLite's type system lacks the [[data integrity]] mechanism provided by statically typed columns, although it can be emulated with constraints like {{code|2=sql|1=CHECK(typeof(x)='integer')}}.<ref name="Owens06" /> In 2021, support for static typing was added through STRICT tables, which enforce datatype constraints for columns.<ref>{{Cite web |title=STRICT Tables |url=https://sqlite.org/stricttables.html |access-date=January 24, 2025 |website=SQLite}}</ref>

Tables normally include a hidden ''rowid'' index column, which provides faster access.<ref>{{cite web |title=SQL As Understood By SQLite |url=https://sqlite.org/lang_createtable.html#rowid |website=SQLite |access-date=21 May 2018 |quote=Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value. |archive-date=21 May 2018 |archive-url=https://web.archive.org/web/20180521104530/https://sqlite.org/lang_createtable.html#rowid |url-status=live }}</ref> If a table includes an INTEGER PRIMARY KEY column, SQLite will typically optimize it by treating it as an alias for the ''rowid'', causing the contents to be stored as a [[strictly typed]] 64-bit signed integer and changing its behavior to be somewhat like an auto-incrementing column. SQLite includes an option to create a table without a rowid column, which can save disk space and improve lookup speed. WITHOUT ROWID tables are required to have a primary key.<ref>{{Cite web |title=Clustered Indexes and the WITHOUT ROWID Optimization |url=https://sqlite.org/withoutrowid.html |access-date=January 24, 2025 |website=SQLite}}</ref>

SQLite supports foreign key constraints,<ref>{{cite book |last=Karwin |first=Bill |editor-last=Carter |editor-first=Jacquelyn |date=May 2010 |title=SQL Antipatterns: Avoiding the Pitfalls of Database Programming |publisher=The Pragmatic Bookshelf |isbn=978-1-934356-55-5 |page=70 |quote=Sometimes you're forced to use a database brand that doesn't support foreign key constraints (for example MySQL's MyISAM storage engine or SQLite prior to version 3.6.19).}}</ref><ref>{{Cite web|url=https://sqlite.org/releaselog/3_6_19.html|title=SQLite Release 3.6.19 On 2009-10-14|website=sqlite.org|access-date=2020-10-15|archive-date=2020-10-29|archive-url=https://web.archive.org/web/20201029060401/http://sqlite.org/releaselog/3_6_19.html|url-status=live}}</ref> although they are disabled by default and must be manually enabled with a PRAGMA statement.<ref>{{Cite web |title=SQLite Foreign Key Support |url=https://sqlite.org/foreignkeys.html |access-date=January 24, 2025 |website=SQLite}}</ref>

[[Stored procedure]]s are not supported; this is an explicit choice by the developers to favor simplicity, as the typical use case of SQLite is to be embedded inside a host application that can define its own procedures around the database.<ref>Source: developers' comments on [https://sqlite.org/forum/info/78a60bdeec7c1ee9 SQLite forum] {{Webarchive|url=https://web.archive.org/web/20230401220416/https://sqlite.org/forum/info/78a60bdeec7c1ee9 |date=2023-04-01 }}</ref>

SQLite does not have full [[Unicode]] support by default for backwards compatibility and due to the size of the Unicode tables, which are larger than the SQLite library.<ref>{{Cite web |title=Quirks, Caveats, and Gotchas In SQLite |url=https://sqlite.org/quirks.html |access-date=January 24, 2025 |website=SQLite}}</ref> Full support for [[Unicode]] case-conversions can be enabled through an optional extension.<ref>{{cite web |title = Case-insensitive matching of Unicode characters does not work |url = https://sqlite.org/faq.html#q18 |website = SQLite Frequently Asked Questions |access-date = 2015-09-03 |archive-date = 2015-09-05 |archive-url = https://web.archive.org/web/20150905054749/https://sqlite.org/faq.html#q18 |url-status = live }}</ref>

SQLite supports [[full-text search]] through its FTS5 loadable extension, which allows users to efficiently search for a keyword in a large number of documents similar to how [[search engine]]s search webpages.<ref>{{Cite web |title=SQLite FTS5 Extension |url=https://sqlite.org/fts5.html |access-date=January 24, 2025 |website=SQLite}}</ref>

SQLite includes support for working with [[JSON]] through its ''json1'' extension, which is enabled by default since 2021. SQLite's JSON functions can handle JSON5 syntax since 2023. In 2024, SQLite added support for JSONB, a binary serialization of SQLite's internal representation of JSON. Using JSONB allows applications to avoid having to parse the JSON text each time it is processed and saves a small amount of disk space.<ref>{{Cite web |title=JSON Functions And Operators |url=https://sqlite.org/json1.html |access-date=January 24, 2025 |website=SQLite}}</ref>

The maximum supported size for an SQLite database file is 281 terabytes.<ref>{{cite web|url=https://sqlite.org/limits.html|title=Limits In SQLite|website=SQLite.org|access-date=2022-09-19|archive-date=2021-11-07|archive-url=https://web.archive.org/web/20211107064937/https://sqlite.org/limits.html|url-status=live}}</ref>