Why would you do rapid prototyping with SQLite?

Why would you do rapid prototyping with SQLite?

2019-03-05 0 By Nordes

As someone who likes to do some hack and automate pretty much my daily routine, recently I changed a bit how I was making those prototype by using a super light no-dependent DB. I use it for caching my data and avoid hitting the official servers too often and give a better performance overall (UX).

In the past, I used a lot NodeJs, but found out that some things were missing in order to be really effective. Especially when you have to touch the Rest API’s (Express or alike), it’s becoming almost a hell for managing data contract, generating the Swagger API and etc. For most of my scenarios where I want something that can scaffold and be ready in one hour, I now tend to use more Dotnet Core. Since it now runs pretty much everywhere and that we can scaffold super quickly API’s and UI (creating some templates).

In fact, in order to become more efficient, I started to build a template project available on GitHub and also NuGet. It’s also the template I most likely use for the articles on this site. For information, the template project use Dotnet Core 2+ (2.2 as o now) and contains 2 projects template. One that is the most basic web application you could have with VueJS and the other one which contains all the ready to start pretty much anything (Swagger, VueJS, VeeValidate, etc.).

Why VueJS and not Angular or any others? That’s a legitimate question. I personally like to use VueJS for it’s simplicity, it’s easy to debug and to finish, it’s really easy to use (great documentation). Then added to that, I use instead of the mastodon of Bootstrap the super lightweight Picnic CSS. It contains all the thing I usually require (grid alike feature, buttons, tabs, etc.). Yes, NodeJS is not gone, it’s still part of the deal. I use WebPack (hot reloading) in order to have a nice lightweight front-end deployed in “production” cases. The hot-reload become really handy when you want to see your changes live.

To debug your API’s using REST calls become easy and you know how the pipeline goes and security is also part of the deal with Kestrel (Http/s).

Regarding the API’s call from the front-end, my personal choice is Axios since it’s also easy and in case I would build UT, the library is simple to mock.

Why Dotnet Core 2.2 (or higher)?

Dotnet Core is using the power of the C# and all the business logic. In case I have to use a database, instead of connecting to a big database, I will simply use EntityFramework Core code first approach with SQLite. Also, Dotnet Core create a simple way to have a well defined swagger API, creates API’s through controllers and also give possibilities to easily integrate with existing Authentication mechanisms. The syntax and data contracts are part of the language and for anyone will understand them. The serialization is usually using the camelCase, but in case you need something different, you simply have to change the data contract factory (Newtonsoft.Json library) to suits your needs.


I used to use SQL Express, PostgreSQL, etc. However, to be doing rapid prototyping without installing any DBMS, SQLite give you the power of a database in a single small file (Don’t forget to VACUUM ;)). I use the latest version where you have foreign keys and cascade delete availability. The only drawback when using SQLite is to access the data with a visualization tool. I recently found one tool which was worth using. It’s not so big to install and you can drag and drop your database in the tool for lazy people ;). The tool in question is SQLite Studio.

When doing prototyping, you want to take a minimum of time to setup an environment, and if you have to deploy let’s say on a Raspberry PI or docker. You might not want to have to install anything else than your application (self-contained). SQLite files are created wherever your project wants it to be. That way, you simply deploy your application. At the startup of the application, the migrations happens and everything is ready. In worst case, or best case, you simply download or upload the database from/to your PC.