Database

Introduction

Most modern web applications interact with a database. Spark makes this simple by setting up Entity Framework for you, making querying and saving models to your database straight forward.

The only thing you have to worry about is setting up your configurations for which database type to use and its connection details.

Spark supports the following database types:

  • SQLite
  • PostgreSQL
  • MySQL
  • SqlServer

Configuration

By default, when a new Spark app is created, it comes with a SQLite already wired up. Databases configurations are setup in your .env file.

You can switch your database type by changing the DB_CONNECTION value to one of our other supported databases.

SQlite ENV Example

DB_CONNECTION=sqlite
DB_HOST=null
DB_PORT=null
DB_DATABASE=Spark.db
DB_USERNAME=root
DB_PASSWORD=

PostgreSQL ENV Example

DB_CONNECTION=postgres
DB_HOST=localhost
DB_PORT=5432
DB_DATABASE=your_db_name
DB_USERNAME=your_user
DB_PASSWORD=your_user_password

MySQL ENV Example

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=your_db_name
DB_USERNAME=your_user
DB_PASSWORD=your_user_password

SqlServer ENV Example

DB_CONNECTION=sqlserver
DB_HOST=localhost
# if using sqlexpress locally, this is what your DB_HOST will look like
# DB_HOST=localhost\SQLEXPRESS 
DB_PORT=1433
DB_DATABASE=your_db_name
DB_USERNAME=your_user
DB_PASSWORD=

# SQL Server Only
DB_TRUST_CERTIFICATE=true
DB_INTEGRATED_SECURITY=true

If you set DB_INTEGRATED_SECURITY to true, SQL Server will use your windows login to connect to the DB. This is great for local developement. If it is false, use a dedicated SQL Server user.

Entity Framework

Spark utilizes Entity Framework to make interacting with your database as simple as possible. If you’ve never used Entity Framework before, you can check out Microsofts documentation here.

Database Context

Entity Framework uses a Database Context class to manage your database connections, models, and allow you query the data in your database.

When a Spark app starts, a Database Context is created and added to your apps DI container via Sparks internal service registration. You can find the DatabaseContext.cs class in Application/Database folder.

Using the Database Context

Important!

Blazor Server cannot use the normal DatabaseContext class.

Instead, you must use a DB Context Factory because Blazor applications do not create a service scope that aligns with the desired DB Context lifetime and will result in errors in your Blazor app. You can read more about this here.

Here is an example of how to use a Database Context Factory in a components code behind file.

namespace MyApp.Pages.Developers.Index;

public partial class Index
{
    [Inject] public IDbContextFactory<DatabaseContext> Factory { get; set; }

    protected override void OnInitialized()
    {
        using var db = Factory.CreateDbContext();
        // query some data with db context
        var developers = db.Developers.ToList();
    }
}

You can inject the Database Context into your services or other registered classes like normal:

public class DeveloperService
{
    private readonly DatabaseContext _db;

    public DeveloperService(DatabaseContext db)
    {
        _db = db;
    }
}

Stay up to date with Spark on Twitter or our Newsletter

Looking for .NET jobs? Check out Dotnet Jobs for weekly job postings.