In this post, we will see how to create and manage a SQLite database using C#.
We will create a minimal API to use the database, implement a repository pattern to abstract database operations and finally, we will write unit tests to verify our code.
The SQLite database we will use is named TestDb and it contains a single table called TabUser, so defined:
CREATE TABLE TabUser (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Username TEXT(250),
Password TEXT(20)
);
First of all, we create an empty project and then, we will install five packages via NuGet that we will use in the project:
- Microsoft.AspNetCore.App: for building the minimal API
- System.Data.SQLite: to interact with the SQLite database
- xUnit: for unit testing.
- Moq: for mocking dependencies during testing.
- Microsoft.Extensions.Configuration: to handle configuration files
After the installation, we should have this result:
Then, we add a configuration file called appsettings.json, used to specify the path to the SQLite database file:
[APPSETTINGS.JSON]
{
"DatabaseSettings": {
"DatabasePath": "c:\\Tests\\TestDb.db"
}
}
Now, we define the User class, the UserRepository interface and the UserRepository class:
[USER.CS]
namespace TestSQLite;
public class User
{
public int Id { get; set; }
public string Username { get; set; }
public string Password { get; set; }
}
[IUSERREPOSITORY.CS]
namespace TestSQLite;
public interface IUserRepository
{
Task<List<User>> GetAllUsersAsync();
Task<User> GetUserByIdAsync(int id);
Task AddUserAsync(User user);
Task UpdateUserAsync(User user);
Task DeleteUserAsync(int id);
Task EnsureDatabaseCreatedAsync();
}
[USERREPOSITORY.CS]
using System.Data.SQLite;
namespace TestSQLite;
public class UserRepository: IUserRepository
{
private readonly string _connectionString;
// Constructor to initialize the connection string from configuration
public UserRepository(IConfiguration configuration)
{
string databasePath = configuration["DatabaseSettings:DatabasePath"];
_connectionString = $"Data Source={databasePath};Version=3;";
}
// Method to ensure the database and tables are created if they do not exist
public async Task EnsureDatabaseCreatedAsync()
{
try
{
var builder = new SQLiteConnectionStringBuilder(_connectionString);
string databaseFile = builder.DataSource;
// Check if the database file exists, and create it if necessary
if (!File.Exists(databaseFile))
{
SQLiteConnection.CreateFile(databaseFile);
}
// Open the connection and create the 'TabUser' table if it doesn't exist
await using var connection = new SQLiteConnection(_connectionString);
await connection.OpenAsync();
string createTableQuery = @"
CREATE TABLE IF NOT EXISTS TabUser (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Username TEXT(250),
Password TEXT(20)
);";
await using var command = new SQLiteCommand(createTableQuery, connection);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
// Method to get all users from the 'TabUser' table
public async Task<List<User>> GetAllUsersAsync()
{
try
{
var users = new List<User>();
await using var connection = new SQLiteConnection(_connectionString);
await connection.OpenAsync();
string query = "SELECT * FROM TabUser";
await using var command = new SQLiteCommand(query, connection);
await using var reader = await command.ExecuteReaderAsync();
// Read each row and add to the user list
while (await reader.ReadAsync())
{
users.Add(new User
{
Id = reader.GetInt32(0),
Username = reader.GetString(1),
Password = reader.GetString(2)
});
}
return users;
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
// Method to get a specific user by ID
public async Task<User> GetUserByIdAsync(int id)
{
try
{
await using var connection = new SQLiteConnection(_connectionString);
await connection.OpenAsync();
string query = "SELECT * FROM TabUser WHERE Id = @id";
await using var command = new SQLiteCommand(query, connection);
command.Parameters.AddWithValue("@id", id);
await using var reader = await command.ExecuteReaderAsync();
// Return the user if found
if (await reader.ReadAsync())
{
return new User
{
Id = reader.GetInt32(0),
Username = reader.GetString(1),
Password = reader.GetString(2)
};
}
return null; // Return null if user is not found
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
// Method to add a new user to the 'TabUser' table
public async Task AddUserAsync(User user)
{
try
{
await using var connection = new SQLiteConnection(_connectionString);
await connection.OpenAsync();
string query = "INSERT INTO TabUser (Username, Password) VALUES (@username, @password)";
await using var command = new SQLiteCommand(query, connection);
// Set parameter values to prevent SQL injection
command.Parameters.AddWithValue("@username", user.Username);
command.Parameters.AddWithValue("@password", user.Password);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
// Method to update an existing user's details
public async Task UpdateUserAsync(User user)
{
try
{
await using var connection = new SQLiteConnection(_connectionString);
await connection.OpenAsync();
string query = "UPDATE TabUser SET Username = @username, Password = @password WHERE Id = @id";
await using var command = new SQLiteCommand(query, connection);
// Set parameter values for the update
command.Parameters.AddWithValue("@username", user.Username);
command.Parameters.AddWithValue("@password", user.Password);
command.Parameters.AddWithValue("@id", user.Id);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
// Method to delete a user by ID
public async Task DeleteUserAsync(int id)
{
try
{
await using var connection = new SQLiteConnection(_connectionString);
await connection.OpenAsync();
string query = "DELETE FROM TabUser WHERE Id = @id";
await using var command = new SQLiteCommand(query, connection);
// Set the ID parameter for deletion
command.Parameters.AddWithValue("@id", id);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
}
Finally, in the Program.cs file, we define the Minimal API:
using TestSQLite;
var builder = WebApplication.CreateBuilder(args);
// Add configuration
builder.Configuration.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);
// Add services
builder.Services.AddSingleton<IUserRepository, UserRepository>();
var app = builder.Build();
// Ensure database and tables are created
using (var scope = app.Services.CreateScope())
{
var userRepository = scope.ServiceProvider.GetRequiredService<IUserRepository>();
await userRepository.EnsureDatabaseCreatedAsync();
}
// Map endpoints
app.MapGet("/users", async (IUserRepository userRepository) =>
{
// Endpoint to get all users
return await userRepository.GetAllUsersAsync();
});
app.MapGet("/users/{id}", async (int id, IUserRepository userRepository) =>
{
// Endpoint to get a user by ID
return await userRepository.GetUserByIdAsync(id);
});
app.MapPost("/users", async (User user, IUserRepository userRepository) =>
{
// Endpoint to add a new user
await userRepository.AddUserAsync(user);
return Results.Created($"/users/{user.Id}", user);
});
app.MapPut("/users/{id}", async (int id, User user, IUserRepository userRepository) =>
{
// Endpoint to update an existing user
user.Id = id;
await userRepository.UpdateUserAsync(user);
return Results.NoContent();
});
app.MapDelete("/users/{id}", async (int id, IUserRepository userRepository) =>
{
// Endpoint to delete a user by ID
await userRepository.DeleteUserAsync(id);
return Results.NoContent();
});
app.Run();
The last class that we have to define is the UserRepositoryTests class that, we will use to write all unit tests for UserRepository class:
[USERREPOSITORYTESTS.CS]
using Moq;
using Xunit;
namespace TestSQLite;
public class UserRepositoryTests
{
private readonly Mock<IUserRepository> _mockRepo;
public UserRepositoryTests()
{
_mockRepo = new Mock<IUserRepository>();
}
[Fact]
public async Task GetAllUsersAsync_ShouldReturnListOfUsers()
{
// Arrange
_mockRepo.Setup(repo => repo.GetAllUsersAsync()).ReturnsAsync(new List<User>
{
new User { Id = 1, Username = "Alice", Password = "password1" },
new User { Id = 2, Username = "Bob", Password = "password2" }
});
// Act
var users = await _mockRepo.Object.GetAllUsersAsync();
// Assert
Assert.NotNull(users);
Assert.Equal(2, users.Count);
}
[Fact]
public async Task GetUserByIdAsync_ShouldReturnUser_WhenUserExists()
{
// Arrange
var user = new User { Id = 1, Username = "Alice", Password = "password1" };
_mockRepo.Setup(repo => repo.GetUserByIdAsync(1)).ReturnsAsync(user);
// Act
var result = await _mockRepo.Object.GetUserByIdAsync(1);
// Assert
Assert.NotNull(result);
Assert.Equal("Alice", result.Username);
}
[Fact]
public async Task GetUserByIdAsync_ShouldReturnNull_WhenUserDoesNotExist()
{
// Arrange
_mockRepo.Setup(repo => repo.GetUserByIdAsync(99)).ReturnsAsync((User)null);
// Act
var result = await _mockRepo.Object.GetUserByIdAsync(99);
// Assert
Assert.Null(result);
}
[Fact]
public async Task AddUserAsync_ShouldAddUser()
{
// Arrange
var user = new User { Username = "Alice", Password = "password1" };
_mockRepo.Setup(repo => repo.AddUserAsync(user)).Returns(Task.CompletedTask);
// Act
await _mockRepo.Object.AddUserAsync(user);
// Assert
_mockRepo.Verify(repo => repo.AddUserAsync(user), Times.Once);
}
[Fact]
public async Task UpdateUserAsync_ShouldUpdateUser()
{
// Arrange
var user = new User { Id = 1, Username = "Alice", Password = "password1" };
_mockRepo.Setup(repo => repo.UpdateUserAsync(user)).Returns(Task.CompletedTask);
// Act
await _mockRepo.Object.UpdateUserAsync(user);
// Assert
_mockRepo.Verify(repo => repo.UpdateUserAsync(user), Times.Once);
}
[Fact]
public async Task DeleteUserAsync_ShouldDeleteUser()
{
// Arrange
var userId = 1;
_mockRepo.Setup(repo => repo.DeleteUserAsync(userId)).Returns(Task.CompletedTask);
// Act
await _mockRepo.Object.DeleteUserAsync(userId);
// Assert
_mockRepo.Verify(repo => repo.DeleteUserAsync(userId), Times.Once);
}
}
If we run the Unit Tests, this will be the result:
Finally, using a API client as Insomnia, we will check if all CRUD operations work fine:
INSERT:
SELECT:
UPDATE:
DELETE:
