Version of SQLite used in Android?

Question

What is the version of SQLite used in Android?

Reason: I'm wondering how to handle schema migrations. The newer SQLite versions support an "ALTER TABLE" SQL command which would save me having to copy data, drop the table, recreate table and re-insert data.

Transparency

You still might want to copy-drop-recreate-reinsert; SQLite's ALTER TABLE isn't very full-featured. – Jointure

Please choose the correct answer, 3.4.0 is not right – Allusion

Doesn't that depend on the version of Android you're running though? I might indeed have SQLite 3.4.x on my old myTouch running 1.6 OR I might have a higher version on my new G2 running 2.2. The true answer is to check for your particular handset. – Transparency

1.0.0 of android.arch.persistence:db and android.arch.persistence:db-framework shipped a few weeks ago. – Hugh

Answer 1

Here is a link to the official docs which include the main points in this answer: android.database.sqlite package-level javadoc

Kotlin code to get framework SQLite version (tip: just stick a breakpoint in your Activity onCreate() and use this code in Evaluate Expression...):

val version = android.database.sqlite.SQLiteDatabase.create(null).use {
    android.database.DatabaseUtils.stringForQuery(it, "SELECT sqlite_version()", null)
};
"Framework (API ${android.os.Build.VERSION.SDK_INT}) SQLite version: $version".also { println(it) }

Using the emulators (note, SQLite version on actual devices will be at least that specified):

API level*	Version	Name	SQLite	Notes
35	15	V	3.42.0	Developer Preview 1
34	14	U	3.39.2	RIGHT and FULL OUTER JOIN
33	13	T	3.32.2
32	12L	Sv2	3.32.2
31	12	S	3.32.2
30	11	R	3.28.0	window functions
29	10	Q	3.22.0
28	9	Pie	3.22.0
27	8.1	Oreo	3.19.4	see 3.19.3 and version control check-ins because 3.19.4 link does not exist
26	8.0	Oreo	3.18.2	O beta versions used 3.18.0
25	7.1.1	Nougat	3.9.2
24	7.0	Nougat	3.9.2
23	6.0	Marshmallow	3.8.10.2	M Preview 1 (SDK level 22) used 3.8.10
22	5.1.1	Lollipop	3.8.6.1	see 3.8.6 and version control check-ins because 3.8.6.1 link does not exist
21	5.0	Lollipop	3.8.6
20	4.4W.2	Android Wear	unknown	no emulator available, but probably either 3.7.11 or 3.8.4.3
19	4.4	KitKat	3.7.11
18	4.3	Jelly Bean	3.7.11
17	4.2	Jelly Bean	3.7.11
16**	4.1	Jelly Bean	3.7.11
15	4.0.3	Ice Cream Sandwich	3.7.4
14**	4.0	Ice Cream Sandwich	3.7.4
13	3.2	Honeycomb	3.7.4
12	3.1	Honeycomb	3.7.4
11**	3.0	Honeycomb	3.7.4
10	2.3.3	Gingerbread	3.6.22
9	2.3.1	Gingerbread	3.6.22
8**	2.2	Froyo	3.6.22
7	2.1	Eclair	3.5.9
4	1.6	Donut	3.5.9
3**	1.5	Cupcake	3.5.9

* Android API level links show where the android.database.sqlite package has changed. Where there is no link (e.g. API level 17), indicates no changes to that package.

** Broken SDK link, see here

Note: if you want your app to use the same version of SQLite across all Android versions, consider using Requery's 3rd party SQLite support library or SQLCipher (if you also want encryption).

Nicodemus

/system/bin/sh: sqlite3: not found This is on rooted Android 4.0.2 device – Ogre

Thanks mark. I'm confused a bit. While development how can i make sure my sqlite version remain consistent across all devices? i mean does targetSdkVersion or buildTarget has any influence on that? – Gride

Answer 2

Although the documentation gives 3.4.0 as reference number, if you execute the following sql, you'll notice that there is a much higher number of SQlite installed:

Cursor cursor = SQLiteDatabase.create(null).rawQuery("select sqlite_version() AS sqlite_version", null);
String sqliteVersion = "";
while(cursor.moveToNext()){
   sqliteVersion += cursor.getString(0);
}

This is just a piece of quick, dirty code to retrieve the sqlite version. For instance on a HTC Hero with Android 2.1, I get: 3.5.9.

On my Nexus One with Android 2.2, I even get 3.6.22.

Selective

I imagine 3.4.0 is given as a minimum version # - for portability you probably shouldn't assume its a higher version unless you have a really good reason to do so. – Transparency

sure, you're right. But if you have to use some advanced features which may improve performance on higher SQLite versions, you may use the code to query and eventually switch the kind of query depending on the deployed version :) – Selective

My Droid with a custom 2.2 ROM also reports 3.6.22 – Falconry

My Epic with pre-release 2.2 says 3.6.23 -- the numbers seem to be inching upwards. – Schramm

Nexus 7 with Android 4.2, gives 3.7.11 – Substructure

For quick check this can be done in one expression with DatabaseUtils.dumpCursorToString(openOrCreate...) in the Android Studio Evaluate window. – Celio

Answer 3

$ adb shell sqlite3 --version 
3.5.9

Same on ADP1 1.6 & 2.1 emulator.

Gavrilla

It gives me: sqlite3: permission denied – Intramolecular

Worked fine for me. I opened cmd prompt and navigated to android folder, where adb.exe was located. Then I typed in the two commands – Synergist

gives "/system/bin/sh: sqlite3: not found" on the Nexus 4 KitKat. – Hamulus

issuetracker.google.com/issues/142003176 – Nicodemus

Answer 4

A short overview of the Andorid APIs and the supported SQLite versions.

The overview is from the link in Mark's answer.

Grethel

Answer 5

In Room you can query

SELECT sqlite_version()

RG

Underwear