A Quick SQLite Primer

SQLite, as the name suggests, uses a dialect of SQL for queries (), data manipulation (INSERT, et al), and data definition (, et al). SQLite has a few places where it deviates from the SQL‑92 standard, no different than most SQL databases. The good news is that SQLite is so space‑efficient that the Android runtime can include all of SQLite, not some arbitrary subset to trim it down to size.

The biggest difference from other SQL databases you will encounter is probably the data typing. While you can specify the data types for columns in a statement, and while SQLite will use those as a hint, that is as far as it goes. You can put whatever data you want in whatever column you want. Put a string in an INTEGER column? Sure! No problem! Vice versa? Works too! SQLite refers to this as “manifest typing,” as described in the documentation:[18]

In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column.

In addition, there is a handful of standard SQL features not supported in SQLite, notably constraints, nested transactions, and , and some flavors of .

Beyond that, though, you get a full SQL system, complete with triggers, transactions, and the like. Stock SQL statements, like , work pretty much as you might expect.

If you are used to working with a major database, like Oracle, you may look upon SQLite as being a “toy” database. Please bear in mind that Oracle and SQLite are meant to solve different problems, and that you will not likely be seeing a full copy of Oracle on a phone any time soon.