SQLite with a Repository Pattern

SQLite with a Repository Pattern

For its size vs performance SQLite is perfect for mobile applications and simple standalone projects. It’s ideal for solutions that need to keep track of relatively small amounts of data and want to make use of a relational database.

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

For the purpose of this article I will focus on my “Bunny App” which is an application that keeps track of Bunny Chows that my colleagues and I have enjoyed. It simply allows us to log the date and place we had a bunny and then rate the bunny with a simple “triple T” matrix being Temperature, Taste and Tomorrow…

Repository

In terms of the directory structures the most common sorting I have seen is to group classes by relevance or function within the business domain, this is a functional approach. That is to say anything to do with a user can be kept in a “Users” folder, anything to do with a financial account can be kept in a “debtors” or “creditors” folder. This is really open to preference so I’m pretty sure each developer will have their own idea on a “functional approach”.

Although setting up all these folders can be cumbersome its useful when new developers join. Maintenance is relatively low and simple providing you understand the business model.

As my Bunny App is very simple I have kept my folder structure to the bare minimum but still allowing for possible database migrations.

Schema Folder

This contains the schema of the database that our ORM (Object Relational Mapper) will map the database columns to. I am fan of Dapper for its speed and simplicity so have used this ORM in my Bunny.Repository project. The schema properties are generally represented by built in primitive types although none primitive such as DateTime are very common.

Schema folder

Interface Folder

Each class file represents the interface (or contract) that repository must fulfill. Although Hungarian Notation has been widely deprecated I still prefix my class files with “I” as this is the way I was taught and although change is good, this is a last vested syntax I still enjoy.

Schema folder

Implementation Folder

I like to group my implementations by database type, this is simply a sub folder within “Implementation” as the database name, in my example this is “SQLite” – there could be other folders such as “MySQL”, “PostgreSQL” or “MSSQL” to represent Microsoft SQL. The idea is that should Business decide to migrate to a new database your code is not heavily tied (or closely coupled) to that database. In the route of “Implementation” is IBaseContext.cs which caters for most of the database events:

  • Open; Check the connection state and open it if its closed
  • Select; To return a single object
  • SelectList; to return a list of objects
  • Insert; to persist an object and return the new database auto Id
  • InsertBulk; to persist a list of data and return void
  • Update; to persist an update to the database, returns void
  • Delete; to delete a record from the database returns void
  • ExecuteNonQuery; I used this to drop, create and seed the database, generally I don’t use it for anything else or even include it in this interface. Its sometimes better to version your database with tools such as flyway or liquibase but this depends on your business mandate for auditing and accountability.

Then per database I have a ‘DBContext’ to cater for connection strings and database connections. In my example I have SQLiteContext.cs which implements system’s IDisposable and my custom IBaseContext.

SQL Lite Context

This will then create BunnyDb.db in the given path, for the example above it will be in the application executing directory.

Bin Folder

I like to then keep the repository methods the same as these base methods as this keeps things tidy and simple to understand.

Score Repository

It is a bit of extra work but I then manually remove the word “Implementation” from the repository’s namespace, ie: in the class file ‘UserRepository.cs’. This makes resolving the namespace simpler. Ie: Bunny.Repository.Implementation.SQLite is changed to Bunny.Repository.SQLite

Namespace Fix

SQLite Folder

Each class file implements its corresponding Interface. If the database instance is installed on the operating system of a dedicated server then it’s a good idea to have the SQL commands stored as stored procedures, this will keep maintenance in one place and be language agnostic. However for SQLite this doesn’t make sense so I have kept the database command text inside the repository class files.

Command Text

The connection string should also be passed to the DbContext in its constructor or read from configuration, however for SQLite this is not necessary as there is no authentication needed and in my case there is no reason for the .db file name to change.

SQL Lite Context

DBeaver

DBeaver is a universal SQL Client that can be used to view your SQLite database file. Below is a screenshot visually showing the database my Bunny.Tests.CreateRepositoryTest create_structure/seed_db methods created.

DBeaver is a free multi-platform database tool for developers, SQL programmers, database administrators and analysts. Supports all popular databases: MySQL, PostgreSQL, MariaDB, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Derby, etc.

DBeaver

Our applications can now make use of the repository classes as shown in my test cases.

Integration Tests

References