2014-02-17 12:00:00
Architecture
(This entry is part of a series. The audience: SQL Server developers. The topic: SQLite on mobile devices.)
No server
A big distinction between SQL Server and SQLite is visible in the fact that only one of them has the word 'server' in its name.
Much like SQL Server Compact (SQL CE), SQLite is designed to run in the same process as your app. It's an embedded database, not a server.
One file
Another way that SQLite is similar to SQL CE is that both of them store a database in a single file. But unlike SQL CE, the size of a SQLite database file is not limited to 4GB. SQLite databases of 50GB are fairly common (on desktops or servers, not mobile devices), and I've heard of people using databases much larger than that.
Open a "connection"
Even though there is no network between your app and your database file, SQLite still uses the terminology of a "connection" to describe the context in which your app interacts with a database. To begin using a database file, you "open" a connection. But you don't need an ODBC connection string or an ADO.NET connection string. Rather, what you need is a filename.
SQLite also supports the ability to have one connection be attached to more than one database, using the ATTACH command. This can be really handy when you want to break your data up into multiple files, while retaining the ability to do SELECTs with JOINs across those files.
Concurrency and threads
SQLite does support concurrent access to the database file. Multiple processes can access the same database file, or multiple threads within the same process. Like any other database, SQLite uses locking to keep everything straight. It supports a locking model where readers do not block writers and writers do not block readers.
This level of concurrency support is very important when developing a mobile app, where environments like iOS have strict rules about keeping the app responsive to the user by performing all blocking or intensive operations in a background thread.
No user logins
As a consequence of its no-server/embedded architecture, SQLite has no support for authentication or user logins. If an app has access to the database file, then it has access to everything inside it.
Summary
SQLite is an embedded SQL implementation which stores a database in a single file. It is a lot like what SQL Server Compact might be if it were cross-platform and open source.