SQLite Setup

This is a simple post about how to add and use SQLite to your project.  We will be adding Sqlite to a console app to start with.  

We will cover:

1. Why SQLite
2. Add Sqlite
3. Adding the tools needed
4. Create database
5. Making schema changes
6. Apply schema changes

Why SQLite

SQLite is a free lightweight single-file database that requires no engine.  For its size, it is a great choice for mobile, desktop, and small to medium web applications (SQLite).  If you are running a large web application or need to support complex joins or queries, this is not the database you want to use.

SQLite has an entity framework provider, so all data access is the same as any entity framework application.  Another strong point was using it.

For the Blazor applications that I blog about, it is a perfect choice.

Defined Project

We will be building a simple app to showcase SQLite.  Our model will be a Blog Post:

        public class Post

        {

            public int Id { get; set; }

            [Required]

            public string Title { get; set; }

            public string Content { get; set; }

            public string Author { get; set; }

        }

We are using data annotations to define our schema.  As with any entity framework project you can do the same with the modeling on the context.

We only have the Post as the data set in the context:

        public class BlogContext : DbContext

        {

            public DbSet<Post> Posts { get; set; }

            protected override void OnConfiguring(DbContextOptionsBuilder options)

                => options.UseSqlite("Data Source=Blogs.db");

        }

 

We are setting the location of the DB. file as the data source.  You would normally put this in app settings or key vault depending on your deployment.

Add SQLite and Tools

You will need to add the following Nuget packages:

• Microsoft.EntityFrameworkCore.Sqlite
• Microsoft.EntityFrameworkCore.Tools

It is very important that you install the tool.  They are required to run the commands for db creation and migrations.

Create database

Without getting to the workings of the entity framework, we will step through creating the database.  These commands can be done either in the VS package console or at the command line.

1. Create the initial migration (Add-Migration "tag")
1. Tag = the name you gave the migration in case you need to reference it later
2. If you get an error on this step try executing just ef.
3. If that fails, reinstall the tools from above again.
2. Update-Database
1. The location of the physical file will be what is set as the data source.
2. If you just use what is defined above, it will be located with your source files.
3. You just need to make sure that the running application has access to the location,

Making schema changes

Anytime you make a change to the model, adding, removing properties, or adding new data annotations, you will need to create a new migration.  To do this just execute:

    Add-Migration MigrationName

Apply schema changes

You need to be aware of which db you are applying the migrations to.  For example, if you keep a master db in source control, you would apply the migration to that one, but then you would need to apply it to the "production" version.

There are a couple of cool ways to apply the migrations.

Client Side

From within your application, you can simply run on start-up:

           using var db = new BlogContext();

            db.Database.Migrate();

This will apply any new schema changes.  You could wrap this in an update process as well.  This works very well for client DBS, like on the desktop or in a mobile application.

Server deploy

For a database file running in a production environment or with multiple servers hitting it, running the above command will not work and could make the database unstable.  

For the server side, you can run the command:

        script-migration => from the package manager console

This command will generate SQL scripts that you can apply to the production DB.

Viewing Data

At this point, we have our database and it has some data in it.  How do we actually see the data or run queries against it?

There are 2 tools available to view the data.  These are very similar to the SQL Explorer for MSSQL.

Extension for Visual Studio (SQLite and SQL Server Compact Toolbox).  Once installed, you can find it under the Tools on the Visual Studio Menu.

The second tool available is a desktop client that looks a lot more like SQL Explorer.  It is called SQLite Browser.

Once installed you can open it like any desktop application.

Summary

There were a few gotchas trying to learn SQLite, but I am pretty excited about SQLite now.  I will be using it in a lot of upcoming projects.  For the full picture please download the source code provided and take a look.  

[source]