Working with SQLite and Visual Studio 2013

SQLite is nice project to store low amount of data in applications. Babel windows desktop project uses it to store client data on client computer. SQLite is lightweight database with excellent portability link. 

Advantage of this solution is that we have SQL available. Selection, projection, insertions, transactions etc. are ready to use. Of course there is gap between objects and relational database. SQLite is relational so we need to use some ORM framework which provides an easy access to database from code. Babel windows desktop client uses sqlite-net. This library allows Code-First approach.

Code-First creates database schema by defining model as classes in code, which will be used as entities. Standard data anotations on properties in these entity classes can define constraints for columns:

public class Stock
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [MaxLength(8)]
    public string Symbol { get; set; }
}

When there is database, there is also need to solve migrations. If we release new version of program, we also might need to update database schema. Sqllite-net ORM doesn't provide migrations. This must be done by our code. We use SQLite Expert To write and debug migration SQL script. It is Free for personal and commercial use! This standalone program is not integrated to visual studio and allows us to see data in database, export DDL for table, run custom SQL commands on live database. This great tool miss just one functionality, but very important to write context for unit test especially for testing database schema migrations. This function is to export insert scripts for data, which ale already in database.

For that we use Visual Studio extension SQLite Toolbox. This extension will adds panel, where we can connect to database and use its functions. To export mentioned insert scripts navigate to SQL Server Compact Toolbx panel -> Right click on database connection -> Script Database -> Script database data -> check tables which are relevant -> select file to save SQL script

Warning!

SQLite Toolbox generates wrong names for named constraints and indexes in DDL scripts. So be careful using this export. Next DDL exports are from same database

SQLite Toolbox export:

CREATE INDEX [Address_ContactId_Address] ON [Address] ([ContactId] ASC);

SQLite Expert export:

CREATE INDEX "Address_ContactId" on "Address"("ContactId");