Questions tagged [sqlite]

SQLite is an open-source software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world.

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.

SQLite is a relational database management system contained in a small (~350 KB) C programming library. In contrast to other database management systems, SQLite is not a separate process that is accessed from the client application, but an integral part of it.

SQLite is ACID-compliant and implements most of the SQL standard, using a dynamically and weakly typed SQL syntax that does not guarantee the domain integrity.

Making an MRE for SQLite questions on StackOverflow

Providing a minimal reproducible example for an SQLite-related question is most usefully and conveniently done by showing a few lines in SQLite syntax (i.e. some create table ... and insert ... which makes a tailored toy database with appropriate structure and sample data). Also consider making a db fiddle and sharing the link (there are multiple free such services out there; see, for example, this and this).

This way, potential answerers can easily recreate the database you used for demonstrating the problem and quickly and efficiently provide solution proposals that are supported by test runs and test output. Showing pictures of database viewers or table representations (even in ASCII art) does not provide the same benefits.

When seeking assistance with an SQL query, structure your query accordingly (see tips for asking a good SQL question).

If you already have created a database for demonstration purposes, consider using the .dump command of the SQLite commandline tool. It will automatically give you the lines for exactly recreating the database.

Getting familiar with the commandline tool also is a good way of avoiding all potential errors in whatever programming language is used to handle the database. With the commandline tool, you can inspect and analyse data and structure directly.

Mobile Apps

SQlite is commonly used to store data on Android, iOS, and Windows Phone apps since it has a simple implementation, easy to adapt, and quite fast.


Unlike client-server database management systems, the SQLite engine has no standalone processes with which the application program communicates. Instead, the SQLite library is linked in and thus becomes an integral part of the application program.

The application program uses SQLite's functionality through simple function calls, which reduce latency in database access: function calls within a single process are more efficient than inter-process communication. SQLite stores the entire database as a single cross-platform file on a host machine.



94030 questions
12 answers

Improve INSERT-per-second performance of SQLite

Optimizing SQLite is tricky. Bulk-insert performance of a C application can vary from 85 inserts per second to over 96,000 inserts per second! Background: We are using SQLite as part of a desktop application. We have large amounts of configuration…
Mike Willekes
  • 5,960
  • 10
  • 33
  • 33
18 answers

How can I list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file – once I have attached it with the ATTACH command on the sqlite3 command line tool?
  • 50,101
  • 39
  • 117
  • 168
31 answers

How do I check in SQLite whether a table exists?

How do I, reliably, check in SQLite, whether a particular user table exists? I am not asking for unreliable ways like checking if a "select *" on the table returned an error or not (is this even a good idea?). The reason is like this: In my program,…
  • 24,466
  • 7
  • 31
  • 35
9 answers

SQLite primary key on multiple columns

What is the syntax for specifying a primary key on more than 1 column in SQLite ?
Bogdan Gavril MSFT
  • 20,615
  • 10
  • 53
  • 74
10 answers

How can I avoid concurrency problems when using SQLite on Android?

What would be considered the best practices when executing queries on an SQLite database within an Android app? Is it safe to run inserts, deletes and select queries from an AsyncTask's doInBackground? Or should I use the UI Thread? I suppose that…
Vidar Vestnes
  • 42,644
  • 28
  • 86
  • 100
25 answers

Is it possible to insert multiple rows at a time in an SQLite database?

In MySQL you can insert multiple rows like this: INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); However, I am getting an error when I try to do…
  • 227,796
  • 193
  • 515
  • 708
19 answers

UPSERT *not* INSERT or REPLACE Insert Update stored proc on SQL Server Is there some clever way to do this in SQLite that I have not thought of? Basically I want to update three out of four columns if the record exists. If it does not exist, I…
Mike Trader
  • 8,564
  • 13
  • 55
  • 66
25 answers

How to get a list of column names on Sqlite3 database?

I want to migrate my iPhone app to a new database version. Since I don't have some version saved, I need to check if certain column names exist. This Stackoverflow entry suggests doing the select SELECT sql FROM sqlite_master WHERE tbl_name =…
  • 6,221
  • 5
  • 22
  • 18
17 answers

What 'additional configuration' is necessary to reference a .NET 2.0 mixed mode assembly in a .NET 4.0 project?

I have a project in which I'd like to use some of the .NET 4.0 features but a core requirement is that I can use the System.Data.SQLite framework which is compiled against 2.X. I see mention of this being possible such as the accepted answer here…
  • 17,253
  • 17
  • 63
  • 98
8 answers

Is there an SQLite equivalent to MySQL's DESCRIBE [table]?

I'm just getting started learning SQLite. It would be nice to be able to see the details for a table, like MySQL's DESCRIBE [table]. PRAGMA table_info [table] isn't good enough, as it only has basic information (for example, it doesn't show if a…
  • 28,056
  • 26
  • 104
  • 170
7 answers

How do I add a new column in between two columns?

I have a table with columns name, qty, rate. I need to add a new column COLNew in between the name and qty columns. How do I add a new column in between two columns?
  • 4,515
  • 8
  • 23
  • 16
8 answers

How to get Top 5 records in SqLite?

I have tried this which did not work. select top 5 * from [Table_Name]
  • 59,111
  • 42
  • 110
  • 159
8 answers

Store boolean value in SQLite

What is the type for a BOOL value in SQLite? I want to store in my table TRUE/FALSE values. I could create a column of INTEGER and store in it values 0 or 1, but it won't be the best way to implement BOOL type. Is there a way?
Ilya Suzdalnitski
  • 52,598
  • 51
  • 134
  • 168
9 answers

What are the performance characteristics of sqlite with very large database files?

2020 update, about 11 years after the question was posted and later closed, preventing newer answers. Almost everything written here is obsolete. Once upon a time sqlite was limited to the memory capacity or to 2 GB of storage (32 bits) or other…
  • 7,704
  • 5
  • 27
  • 25
8 answers

How to set Sqlite3 to be case insensitive when string comparing?

I want to select records from sqlite3 database by string matching. But if I use '=' in the where clause, I found that sqlite3 is case sensitive. Can anyone tell me how to use string comparing case-insensitive?
  • 4,051
  • 3
  • 23
  • 20
2 3
99 100