SmolFinance’s Storage Layer

As I continue working to move from a Go noob to a software craftsman—much like someone learning woodworking to craft their own furniture, this piece breaks down /db/store.go from first principles.

These breakdowns serve a dual purpose: documenting the inner workings of SmolFinance, and educating others. They’re written for anyone curious about building open-source software or deepening their understanding of Go. Understanding this file from the ground up means understanding how Go abstracts databases, how drivers register themselves, and how Go’s memory and type systems represent persistent data.

import ( "database/sql" _ "modernc.org/sqlite" "log" "os" "path/filepath" )

One of the cool things about Go is that database/sql is part of its standard library, it’s a built-in database interface, not something you have to implement yourself.

Think of database/sql as a universal power outlet, and each driver (SQLite, Postgres, MySQL, etc.) as a plug adapter. Soon, you’ll see how we use the sql.DB type to initialize and manage the database connection pool.

For first-principle understanding, remember:

the driver is the translator between your Go program and the database’s native language. In this case, modernc.org/sqlite translates Go’s database calls into SQLite commands.

The line:

_ "modernc.org/sqlite"

is called a blank import.

It runs the driver’s init() function automatically so that database/sql knows how to open SQLite connections. We never reference it directly, the init() function registers itself behind the scenes with database/sql.

Next:

os is used for interacting with the operating system—creating directories, reading files, handling permissions, etc.

path/filepath manages path joining and directory extraction in a cross-platform-safe way (so your code runs on macOS, Linux, and Windows without breaking).

These imports are crucial for SmolFinance because they ensure that directories exist before opening the database file, an essential part of the project’s local-first design philosophy.

type Store struct { DB *sql.DB }

This is our connection wrapper — a lightweight container for the live database.

As mentioned earlier, sql.DB is not a single connection; it’s a connection pool. You can think of it as the database’s context object: it opens connections lazily, manages concurrency, and reuses connections efficiently.

The Store struct represents SmolFinance’s open database session, serving as our abstraction layer. Through it, we’ll later expose higher-level methods like InsertTrade() or GetStats().

When you call:

sql.Open( "sqlite", "data/trades.db" )

Go creates a *sql.DB handle that manages a pool of SQLite connections behind the scenes. The Store simply owns this live database handle and provides convenient methods around it.

Initialization:

func Init (dbPath string ) (*Store, error ) { if dbPath == "" { dbPath = "./data/trades.db" }

This function is the entry point for setting up your local SQLite database. It determines the location of the SQLite file — the physical file on disk where all persistent data will live.

Init acts as our constructor for the database layer. While Go doesn’t have constructors in the object-oriented sense, this function plays that role: it returns a fully initialized and ready-to-use Store struct.

Right from the start, it ensures:

The database file and directory exist.

A connection is opened through Go’s database/sql subsystem.

The schema (tables) is created if missing.

A ready-to-use Store instance is returned to the caller.

The line:

if dbPath == "" {... }

checks whether the caller provided a custom file path. If not, it defaults to./data/trades.db, keeping the program self-contained — the database always has a predictable location by default.

if err:= os.MkdirAll(filepath.Dir(dbPath), 0755 ); err!= nil { return nil, err }

db, err:= sql.Open( "sqlite", dbPath) if err!= nil { return nil, err }

store:= &Store{DB: db}

if err:= store.applySchema(); err!= nil { return nil, err }

log.Printf( "Database initialized at %s\n", dbPath) return store, nil }

We first ensure that the directory structure exists. Go calls the underlying OS system call recursively to make sure all parent folders are created.

filepath.Dir(dbPath) → extracts the directory part (e.g. "./data" ).

os.MkdirAll → a system call wrapper that recursively creates directories if they don’t exist. If they already exist, it silently succeeds.

0755 → UNIX permission bits that allow the owner full access and others read/execute.

This is something I love about Go — before any SQL runs, the environment is made consistent. It follows a core Go principle: never assume state, create one.

SQLite stores all data in a single file. If./data/ doesn’t exist, sql.Open() would fail with “no such file or directory.” This line guarantees the path is safe before we touch the database.

db, err:= sql.Open( "sqlite", dbPath)

This is our bridge between the Go program and the SQLite driver.

"sqlite" is the key used in Go’s global driver registry.

dbPath is the data source name — for SQLite, it’s literally the filename of the database.

store:= &Store{DB: db}

This wraps the *sql.DB inside your own type so you can attach custom methods later (like InsertTrade, QueryStats, etc.). It turns the generic connection pool into SmolFinance’s database abstraction layer.

if err:= store.applySchema(); err!= nil { return nil, err }

Here we ensure the database has all the tables it needs. SQLite uses a single file ( trades.db ) for the entire database. When a new file is created, it’s completely empty — no schema, no tables. To safely run the app, your code must migrate it into a known structure.

applySchema() executes a SQL string with multiple CREATE TABLE IF NOT EXISTS statements, ensuring the persistence layer is ready.

log.Printf( "Database initialized at %s\n", dbPath) return store, nil

This signals successful initialization. The returned *Store is a live connection handle for all future database operations.

func (s *Store) applySchema() error { schema:= CREATE TABLE IF NOT EXISTS trades (...); CREATE TABLE IF NOT EXISTS notes (...); CREATE TABLE IF NOT EXISTS tags (...);

_, err:= s.DB.Exec(schema) return err }

This method applies the schema definition — it creates all required tables if they don’t exist.

In other words, the startup flow looks like this:

cmd/smolfinance.go ↓ db.Init() ↓ sql.Open("sqlite", "./data/trades.db") ↓ Store.applySchema() ↓ s.DB.Exec(schema) ↓ SQLite engine parses & executes SQL ↓ Returns success or error

And, lastly

func (s *Store) InsertTrade(t Trade) error { query:= INSERT INTO trades (broker, symbol, side, qty, price, timestamp, realized_pnl, fees, tags, notes) VALUES (?,?,?,?,?,?,?,?,?,?);

_, err:= s.DB.Exec(query, t.Broker, t.Symbol, t.Side, t.Qty, t.Price, t.Timestamp, t.RealizedPnL, t.Fees, t.Tags, t.Notes, ) return err }

This is where the database layer actually writes data to your persistent store. InsertTrade is a method on the database handle ( *Store ). It performs a single operation — add one trade record to the database.

It receives a Trade struct (a value type holding all the trade data in memory) and returns an error, allowing the caller to know if the operation succeeded.

_, err:= s.DB.Exec(query, t.Broker, t.Symbol, t.Side, t.Qty, t.Price, t.Timestamp, t.RealizedPnL, t.Fees, t.Tags, t.Notes)

Here’s the chain of events:

Exec() is called on s.DB (your *sql.DB connection pool).

The Go runtime looks up the registered driver ( "sqlite" ) and passes it the SQL query + arguments.

The driver prepares the SQL statement, binds your Go values to the? placeholders.

SQLite executes the compiled statement and writes the data to the trades table inside your.db file.

The driver returns a sql.Result (ignored here) and an error.

type Trade struct { ID int Broker string Symbol string Side string Qty float64 Price float64 Timestamp string RealizedPnL float64 Fees float64 Tags string Notes string…