A small test application or any small application that uses only a small amount of data might be able to use SQLite instead of the full Microsoft SQL Server or other DB. One advantage of using SQL Server is that Azure makes it easy to setup, and other is that it works well with Visual Studio and other MS tools. The largest advantage is that it will handle large amounts of data and under a heavy load. The downside to Azure is that there is a cost. And if you’re not using Azure, SQL Server becomes more difficult to maintain, typically requiring a separate computer. So, what do you do if you just want to keep things simple and free?
SQLite is basically a database without a server, storing everything it needs in a file. This makes it easy to deploy but it would be a bad idea if you plan to give it heavy use or lots of data.
The caveat to using SQLite with the Entity Framework is that code-first is not supported. EF cannot make SQLite create the database with the currently-available libraries on NuGet. (A commercial option is available, however.) If you want to use a more database-first approach, that should work with EF.
I considered migrating the db for my small program over to SQLite, as it’s really not much data, but since I’m currently using code-first, I’d rather not make that change. And with the ease of Azure, I feel like I see where Microsoft is going with Azure – make it so easy to deploy to Azure that you’ll just want to do it that way as the default option.
The other database alternative I’ve been thinking about lately is just storing in flat files. This might not be good for normal transactional processing, where multiple people could make simultaneous changes (depending on the design), but would work well for something like a blog, where you just need to serve up contents of a file. (Although, in this case, you can take the next step and use Jekyll or another static site generator to create a static website for even less overhead.) If your folder structure hierarchy matches what you typically would use in your program, it might be efficient to directly access the requested data.