From .NET & SQL Server to SQLite on Mobile

This is a series of articles about using SQLite when developing mobile apps. The target audience is folks who are coming from the world of SQL Server and .NET.

Table of Contents

Anything 'not written yet' is subject to change. Entries are listed in a somewhat logical order for their content, roughly proceeding from 'big picture stuff' to 'technical details', but I am not promising to actually write them in that order. :-)

  • Introduction

  • Microsoft apparently considers SQLite to be the replacement for SQL Server Compact.
  • Architecture

  • 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.
  • Feel

  • In a number of ways, you can tell that SQLite was not originally written specifically for Windows folks.
  • (not written yet) Community

  • The SQLite community, insofar as such a thing exists, is going to seem very different from the .NET community where you live now.
  • (not written yet) Visual Studio

  • People have done some things to make using SQLite with Visual Studio a more pleasant experience.
  • (not written yet) System.Data.SQLite

  • The SQLite team supports an ADO.NET-compatible API.
  • EF6 on Xamarin: Progress (or lack thereof)

  • Not ready for prime time.
  • (not written yet) SQLite-Net

  • SQLite-Net is a popular SQLite wrapper for C#, including a simple ORM.
  • (not written yet) Encryption

  • Keeping data on a mobile device is a risk, brecause people lose their phones and tablets. A SQLite database file can be encrypted using the SQLite Encryption Extension ($) or SQLCipher (open source).
  • Issues with sync and constraints

  • In a replicate-and-sync app, it would be really nice if the SQLite database on the mobile device had exactly the same constraints as the SQL Server database on the backend.
  • (not written yet) Language

  • The most jarring part of the transition to mobile app development is changing from C# to Objective-C or Java. Xamarin's tools can help with that.
  • (not written yet) Cars and Clocks

  • I won't apologize for my claim that you will be better off if you understand how things work 'under the hood'.
  • (not written yet) API

  • SQLite's primary API is in C, which is not the natural language for someone coming from the .NET world, nor is it the natural language for developing on mobile platforms.
  • (not written yet) Locks

  • More than anything else, the reason for the word Lite in SQLite is because of the simpler way it handles locking.
  • Data Types

  • At the SQL language level, the biggest difference with SQLite is the way it deals with data types.
  • Unicode

  • SQLite has no distinction between CHAR and NCHAR. Everything is Unicode.
  • (not written yet) Collations

  • SQLite supports several built-in collations, plus the ability to define new ones.
  • (not written yet) uniqueidentifier

  • SQLite doesn't have a uniqueidentifier type, but it does have ways of dealing with them.
  • (not written yet) IDENTITY

  • The equivalent of IDENTITY in SQLite is INTEGER PRIMARY KEY, which, believe it or not, is different from INT PRIMARY KEY.
  • (not written yet) Blobs

  • SQLite has excellent support for blobs.
  • (not written yet) Dates and Times

  • SQLite has no specific types for dates or times, but it has a number of functions for dealing with them in textual or numeric format.
  • (not written yet) Indexes

  • SQLite supports indexes, with generally fewer limitations than SQL Server.
  • UNIQUE NULL

  • SQL Server treats NULL as a value. SQLite treats it as the absence of a value.
  • (not written yet) Triggers

  • SQLite's support for triggers is surprisingly strong, but there are significant differences from SQL Server.
  • (not written yet) Views

  • SQLite has basic support for views, but they are read-only.
  • Foreign Keys

  • SQLite has excellent support for foreign keys, including the ability to defer them until commit time, which I wish that SQL Server could do.
  • (not written yet) Joins

  • SQLite supports inner join and left outer join.
  • (not written yet) Common Table Expressions

  • As of version 3.8.3, SQLite supports common table expressions and recursive queries.
  • (not written yet) ALTER TABLE

  • SQLite's support for ALTER TABLE is very limited.
  • (not written yet) Temporary tables

  • SQLite has good support for temporary tables.
  • (not written yet) SELECT INTO

  • SQLite can create a temp table from a SELECT result, but the syntax is different.
  • (not written yet) MERGE

  • SQLite does not support the MERGE statement, but it does have some similar features which will cover many related use cases.
  • (not written yet) Control Structures

  • SQLite doesn't support T-SQL stuff like conditionals or loops, but it has workarounds for some common cases.
  • (not written yet) PRAGMA

  • SQLite's PRAGMA statement is a way to access all kinds of configuration settings that are specific to SQLite.
  • (not written yet) EXPLAIN

  • SQLite's EXPLAIN statement can show you what its query planner intends to do.
  • (not written yet) Full Text Search

  • SQLite includes an excellent full text search engine.
  • (not written yet) Performance

  • In many cases, SQLite is significantly faster than SQL Server. But not always.