I've been switching my personal web apps from a Microsoft SQL Server datastore to SQLite. In doing this, I needed a quick and easy way to quickly create and recreate the SQLite database file during development. With a database as simple as SQLite, scripting was the easy answer.
Why Script?
While some .NET Core-based frameworks do support code-first database deployments, I find the separation of concerns between a database and application code to be important. Due to the simplistic nature of SQLite as a database, scripting tables, columns, constraints, etc. is exceedingly simple. Yes, it's a little more work up front, but I find it better to architect a strong data model outside of code, then model it in code using whatever tools are the most useful for a given project.
With scripts, changes during development can be deployed very quickly, repeatedly, and reliably without running application code. Similarly, deployments to production can be done in a very deliberate and calculated way.
Getting Started
For my simple database needs, one or two script files do the job. My basic approach is to:
- Set pragma
- Drop existing tables
- Create tables
- Insert initial data
- Insert test data for development (usually a separate file)
Here's a simple example of that approach:
CreateTables.sql
PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS Categories;
DROP TABLE IF EXISTS Users;
CREATE TABLE Users (
[ID] INTEGER NOT NULL PRIMARY KEY,
[FirstName] TEXT NOT NULL,
[LastName] TEXT NOT NULL,
[Email] TEXT NOT NULL,
[Active] INTEGER NOT NULL DEFAULT 1,
[CreateDate] TEXT NOT NULL DEFAULT CURRENT_DATE
);
CREATE TABLE Categories (
[ID] INTEGER PRIMARY KEY NOT NULL,
[Name] TEXT NOT NULL,
[UserID] INTEGER NOT NULL,
[Active] INTEGER NOT NULL DEFAULT 1,
[CreateDate] TEXT NOT NULL,
FOREIGN KEY (UserID) REFERENCES Users(ID)
);
InsertTestData.sql
-- USERS
INSERT INTO Users (ID, FirstName, LastName, Email)
VALUES (1, 'Brandon', 'Bruno', 'brandon@test.com');
SELECT * FROM Users;
-- CATEGORIES
INSERT INTO Categories (ID, Name, UserID, Active, CreateDate) VALUES (1, 'Groceries', 1, 1, '2024-01-01');
INSERT INTO Categories (ID, Name, UserID, Active, CreateDate) VALUES (2, 'Tech', 1, 1, '2024-01-01');
INSERT INTO Categories (ID, Name, UserID, Active, CreateDate) VALUES (3, 'Pet', 1, 1, '2024-01-01');
SELECT * FROM Categories;
The second script is only run when I need test data and never during production activities. Having this delineation between two files makes it easy to separate production and development activities.
Using each of these scripts is just one line on the terminal. For example:
sqlite3.exe MyDatabase.db < 10-CreateTables.sql