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]
1 2 3 4 5 | { "DatabaseSettings" : { "DatabasePath" : "c:\\Tests\\TestDb.db" } } |
Now, we define the User class, the UserRepository interface and the UserRepository class:
[USER.CS]
1 2 3 4 5 6 7 8 | namespace TestSQLite; public class User { public int Id { get ; set ; } public string Username { get ; set ; } public string Password { get ; set ; } } |
[IUSERREPOSITORY.CS]
1 2 3 4 5 6 7 8 9 10 11 | 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]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | 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]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 | 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: