DEV Community: Arnab Santra

The Git Filesystem - Recreating the Content-Addressable Database

Arnab Santra — Sat, 23 May 2026 18:50:27 +0000

You've used Git every single day for years. You've resolved merge conflicts at midnight, force-pushed to the wrong branch, lost an afternoon to a detached HEAD. You know git rebase -i well enough to teach it. You've explained pull requests to interns.

And yet — if someone put a gun to your head and asked "what actually happens when you run git commit?" — you'd say something like "it... saves a snapshot?" and hope they move on.

Don't feel bad. Most developers interact with Git the same way they interact with their car engine: turn the key, go. The abstraction holds until the day it doesn't — until git reflog saves your job or a corrupted object wrecks a deploy and you're staring at .git/objects with no idea what you're looking at.

This series is about closing that gap.

Not "here are some advanced Git commands" — there are plenty of cheat sheets for that. This is about understanding the machine underneath. The object store. The pack format. The wire protocol. The actual bytes on disk.

By the end of this post, you'll be able to write a valid Git blob object in Go — one that git cat-file accepts without complaint — without calling a single Git command. Not as a party trick. As proof that you now understand the primitive Git is built on.

Stop thinking of Git as a history tracker. It's a content-addressable filesystem with a thin VCS wrapper bolted on top.

When Linus Torvalds wrote the first version of Git in 2005, he didn't start by designing branches or merges. He started by designing an object store. The VCS layer came after. That design decision explains everything that's confusing about Git — and everything that's elegant about it.

This post tears apart .git/objects, explains exactly what's on disk, and walks through a Go implementation that writes a real, valid Git object from scratch. No magic. No waving hands at "Git uses SHA-1."

The `.git` Directory Is the Repository

Clone a repo, delete every file except .git, and you've lost nothing. The working tree is just a checked-out view. The actual data — every version of every file ever committed — lives in .git/objects.

.git/
├── HEAD                 # pointer to current branch
├── config               # repo-local config
├── objects/             # THE database
│   ├── 4b/
│   │   └── 825dc642cb6eb9a060e54bf8d69288fbee4904
│   ├── info/
│   └── pack/            # packed objects (covered in Post 2)
└── refs/
    ├── heads/           # branch tips
    └── tags/

That file at 4b/825dc6... is the empty tree object. It exists in every Git repository ever initialized. The name IS its content — hash the empty tree structure and you'll always get 4b825dc642cb6eb9a060e54bf8d69288fbee4904. That's the contract.

Four Object Types, One Storage Format

Git has exactly four object types. Everything in your repository decomposes into these:

Type	What it stores
`blob`	File contents (no filename, no permissions)
`tree`	Directory listing (filenames + modes + child object hashes)
`commit`	Snapshot pointer (tree hash + parent + author + message)
`tag`	Named pointer (usually to a commit)

A critical insight: blobs don't know their filename. The tree that references them knows the filename. This is why renaming a file in Git is "free" — the blob object is reused, only the tree changes. And that's why two files with identical contents — anywhere in the repo, across any branch — share a single blob.

The Wire Format of a Loose Object

Every loose object follows the same binary format before being zlib-compressed:

[type] SP [size] NUL [content bytes]

For a file containing hello\n:

blob 6\0hello\n

That's it. The blob type string, a space, the byte count of the content (not the header), a null byte, then the raw content. Hash this entire sequence with SHA-1 and you get the object's identity. Write it zlib-compressed to .git/objects/[first-2-hex-chars]/[remaining-38-hex-chars].

Let's verify this with raw shell commands before implementing it in Go:

# Write "hello\n" as a blob
$ echo "hello" | git hash-object -w --stdin
ce013625030ba8dba906f756967f9e9ca394464a

# Confirm the file exists
$ xxd .git/objects/ce/013625030ba8dba906f756967f9e9ca394464a | head -3
00000000: 789c cb 48 cd c9 c9 57 30 b4 65 00 00 0b 27 02 a7  x..H..W0.e...'..

# Decompress and inspect
$ python3 -c "
import zlib
with open('.git/objects/ce/013625030ba8dba906f756967f9e9ca394464a', 'rb') as f:
    print(repr(zlib.decompress(f.read())))
"
b'blob 6\x00hello\n'

There's the header: blob 6\x00. Exactly as described.

Why Content-Addressability Matters

The SHA-1 hash of an object is its address. This has radical consequences:

Deduplication is automatic. If 10,000 commits all include an unchanged README.md, there's exactly one blob object for it. The trees in those commits all point to the same hash. No dedup logic required — identical content produces identical hashes.

Corruption is detectable. Flip one bit in any object file and git fsck will catch it immediately. The stored filename no longer matches the hash of the decompressed content.

Caching is trivially correct. If you have object abc123, you have it forever, unchanged. There's no cache invalidation problem. Object identity is content identity.

This is why git clone is trustworthy across mirrors — you don't have to trust the mirror, only the hash.

⚠️ The SHA-1 → SHA-256 Transition

In 2017, Google's SHAttered attack produced two PDFs with the same SHA-1 hash. For Git, the risk isn't casual: it was demonstrated that it's essentially possible to create two Git repositories with the same head commit hash but different contents — say, a benign source and a backdoored one.

Git has been transitioning to SHA-256. Git 2.29 (2020) introduced experimental support for SHA-256 via the sha256 object format. Repositories can now be initialized with git init --object-format=sha256. Git 2.51 further progressed this transition, and backward compatibility remains critical since Git is used by millions of developers. The tricky part: Torvalds noted that "Git doesn't actually just hash the data — it prepends a type/length field to it. That usually tends to make collision attacks much harder, because you either have to make the resulting size the same too, or you have to be able to also edit the size field in the header."

Building It: A Go Implementation

Enough theory. Here's a self-contained Go program that writes a valid Git blob object — one that git cat-file can read back:

package main

import (
    "compress/zlib"
    "crypto/sha1"
    "fmt"
    "os"
    "path/filepath"
)

// GitObject represents a raw Git object before storage.
type GitObject struct {
    Type    string
    Content []byte
}

// Header returns the [type] SP [size] NUL prefix Git prepends to all objects.
func (o *GitObject) Header() []byte {
    return []byte(fmt.Sprintf("%s %d\x00", o.Type, len(o.Content)))
}

// Store computes the SHA-1 hash of header+content, zlib-compresses the full
// payload, and writes it to the correct nested path under .git/objects/.
func (o *GitObject) Store(gitDir string) (string, error) {
    // Step 1: build the full store payload = header + raw content
    header := o.Header()
    payload := append(header, o.Content...)

    // Step 2: SHA-1 hash the payload — this IS the object's identity
    sum := sha1.Sum(payload)
    hash := fmt.Sprintf("%x", sum)

    // Step 3: derive the on-disk path: objects/[2-char]/[38-char]
    objDir := filepath.Join(gitDir, "objects", hash[:2])
    objPath := filepath.Join(objDir, hash[2:])

    // Step 4: skip if object already exists (content-addressable = idempotent)
    if _, err := os.Stat(objPath); err == nil {
        return hash, nil
    }

    if err := os.MkdirAll(objDir, 0755); err != nil {
        return "", fmt.Errorf("mkdir: %w", err)
    }

    // Step 5: write zlib-compressed payload to disk
    f, err := os.OpenFile(objPath, os.O_WRONLY|os.O_CREATE|os.O_EXCL, 0444)
    if err != nil {
        return "", fmt.Errorf("create object file: %w", err)
    }
    defer f.Close()

    w := zlib.NewWriter(f)
    if _, err := w.Write(payload); err != nil {
        return "", fmt.Errorf("compress write: %w", err)
    }
    if err := w.Close(); err != nil {
        return "", fmt.Errorf("compress close: %w", err)
    }

    return hash, nil
}

// ReadObject reads and decompresses a Git object, returning its type, size,
// and raw content. The inverse of Store().
func ReadObject(gitDir, hash string) (objType string, content []byte, err error) {
    path := filepath.Join(gitDir, "objects", hash[:2], hash[2:])

    f, err := os.Open(path)
    if err != nil {
        return "", nil, fmt.Errorf("open: %w", err)
    }
    defer f.Close()

    r, err := zlib.NewReader(f)
    if err != nil {
        return "", nil, fmt.Errorf("zlib: %w", err)
    }
    defer r.Close()

    var raw []byte
    buf := make([]byte, 4096)
    for {
        n, readErr := r.Read(buf)
        raw = append(raw, buf[:n]...)
        if readErr != nil {
            break
        }
    }

    // Parse header: find the null byte separating header from content
    nullIdx := -1
    for i, b := range raw {
        if b == 0x00 {
            nullIdx = i
            break
        }
    }
    if nullIdx == -1 {
        return "", nil, fmt.Errorf("malformed object: no null byte in header")
    }

    // Header format: "[type] [size]"
    var size int
    header := string(raw[:nullIdx])
    fmt.Sscanf(header, "%s %d", &objType, &size)

    return objType, raw[nullIdx+1:], nil
}

func main() {
    content := []byte("hello, git internals\n")

    obj := &GitObject{
        Type:    "blob",
        Content: content,
    }

    hash, err := obj.Store(".git")
    if err != nil {
        fmt.Fprintf(os.Stderr, "error: %v\n", err)
        os.Exit(1)
    }

    fmt.Printf("wrote blob: %s\n", hash)
    fmt.Printf("path: .git/objects/%s/%s\n", hash[:2], hash[2:])

    // Round-trip: read it back
    objType, readContent, err := ReadObject(".git", hash)
    if err != nil {
        fmt.Fprintf(os.Stderr, "read error: %v\n", err)
        os.Exit(1)
    }

    fmt.Printf("read back: type=%s content=%q\n", objType, readContent)

    // Cross-verify with git cat-file
    fmt.Printf("\nVerify with: git cat-file -p %s\n", hash)
    fmt.Printf("Type check:  git cat-file -t %s\n", hash)
}

Run this in any Git repo directory and then verify:

$ go run main.go
wrote blob: a97d3ee72059abfabb3cd99748d61e36bcc2b2c5
path: .git/objects/a9/7d3ee72059abfabb3cd99748d61e36bcc2b2c5
read back: type=blob content="hello, git internals\n"

Verify with: git cat-file -p a97d3ee72059abfabb3cd99748d61e36bcc2b2c5
$ git cat-file -p a97d3ee72059abfabb3cd99748d61e36bcc2b2c5
hello, git internals

Git accepts the object as legitimate — because it is legitimate. Same format, same hash algorithm. The Store function is exactly what git hash-object -w does internally.

The Two-Character Directory Split

Why objects/a9/7d3ee7... instead of objects/a97d3ee7...?

Filesystems degrade when directories contain too many entries. Old ext2 used linear lookups; even modern filesystems show measurable slowdown with hundreds of thousands of entries in a single directory. By splitting on the first two hex characters, Git caps any single directory at 256 entries (one per two-hex-char prefix). A repository with a million objects distributes them across at most 256 directories of ~3,900 objects each.

It's a pragmatic workaround for filesystem limitations, not an algorithmic requirement. The SHA-256 transition keeps the same split (objects/[2]/[62]), just with longer filenames.

What a Tree Object Actually Looks Like

Trees deserve their own dissection. The format is binary, not the text format of blobs and commits:

[mode] SP [filename] NUL [20-byte-raw-SHA-1]
[mode] SP [filename] NUL [20-byte-raw-SHA-1]
...

Notice: raw bytes, not hex. Each entry is a mode string (like 100644 for a regular file, 040000 for a subdirectory, 100755 for executable), the filename, a null byte, and then 20 raw bytes of the referenced object's SHA-1.

You can inspect this manually:

# Get the tree hash of HEAD
$ git cat-file -p HEAD
tree 9ab456...
...

# Inspect the tree (git pretty-prints it)
$ git cat-file -p 9ab456
100644 blob ce013625030ba8dba906f756967f9e9ca394464a    README.md
040000 tree 4b825dc642cb6eb9a060e54bf8d69288fbee4904    src/

# But the raw bytes are not ASCII — decode them yourself:
$ python3 -c "
import zlib
with open('.git/objects/9a/b456...', 'rb') as f:
    raw = zlib.decompress(f.read())
# skip header (find null byte)
null = raw.index(b'\x00')
entries = raw[null+1:]
# each entry: mode SP name NUL 20-bytes
i = 0
while i < len(entries):
    space = entries.index(b' ', i)
    mode = entries[i:space].decode()
    null2 = entries.index(b'\x00', space)
    name = entries[space+1:null2].decode()
    sha = entries[null2+1:null2+21].hex()
    print(f'{mode} {name} -> {sha}')
    i = null2 + 21
"

Plumbing Commands You Should Know

Git ships with "porcelain" (user-facing) and "plumbing" (low-level) commands. When you're working at this level, you want plumbing:

# Hash content without writing (dry run)
$ echo "test" | git hash-object --stdin

# Hash AND write to object store
$ echo "test" | git hash-object -w --stdin

# Inspect object type
$ git cat-file -t <hash>

# Print object content (pretty-printed for trees)
$ git cat-file -p <hash>

# Print raw content size
$ git cat-file -s <hash>

# Verify object database integrity
$ git fsck --unreachable

# Show all objects in the store
$ git rev-list --objects --all

# Manually trigger loose → packed object consolidation
$ git gc

The git cat-file -p output is what feeds into higher-level operations. Everything git does at the porcelain level eventually bottoms out in reads and writes of these four object types.

The Immutability Guarantee

One last thing worth internalizing: Git objects are immutable by construction. Once written, they never change. git commit --amend doesn't modify the old commit — it writes a new commit object and updates the branch reference to point to it. The old commit still exists in .git/objects until garbage-collected.

This is why git reflog can recover "lost" commits. They're not lost — the objects are still on disk. Only the references have been updated. git gc eventually removes unreachable objects, but by default only after they've been unreachable for 30 days.

The content-addressable store is the engine. Branches, tags, the index, the staging area — all of it is built on top of this one primitive: a compressed, hashed, immutable object store.

Next Up

In Post 2: Pack Files and Delta Compression, we dissect how Git takes thousands of loose objects and compresses them into a single binary packfile using delta encoding — storing only the differences between similar objects. For a file that's 10 MB with changes generating 100 KB deltas each time, storing 100 versions as loose objects costs ~1 GB. Pack files with delta compression reduce that to roughly 10 MB. We'll implement a pack file reader and inspect the binary format byte by byte.

How I Built the Most Overengineered TUI Weather App

Arnab Santra — Mon, 09 Feb 2026 16:48:53 +0000

Why Would Someone Need a Weather App?

They don't.

But I had free time, a desire to learn Go, and an unexplainable need to make simple things complicated.

I believe the most effective way of learning is by building, so here I am, using a sophisticated relational database for storing a 5-minute weather cache.

I had previously built simple web servers with Go and net/http, and most YouTube tutorials focus on that.

The Idea

This was my first list of requirements:

The app should be instant/fast
The user can search for their location in the terminal
The user can select from multiple locations within the TUI
The fetched weather should not be boring to look at.

The Components

I envisioned the app having three major components:

The backend: Responsible for fetching the location (latitude, longitude), and the recent weather.
The UI: This will be our TUI frontend and will communicate with the backend.
The Database: I realized that continuously calling a public API isn't ideal. Therefore, we needed to cache the city and weather information.

The Tech Stack

This project uses a lot of powerful (yet questionable for a simple weather app) set of tools:

Go - The core language.
Bubble Tea - Used to build the interactive terminal UI using The Elm Architecture.
Lip Gloss - Handles styling, layout, and colors in the TUI.
SQLite - A lightweight, file-based database used for caching weather data and storing city information.
sqlc - Generates type-safe Go code from raw SQL queries, keeping the database layer clean and maintainable.
Goose - Manages database migrations and schema changes.
OpenWeather API - Provides geocoding and weather data

Note: You can find the full source code in my GitHub

The Backend

This backend is pretty straightforward. You call the API, get the result, format the response, and return it while storing the result in the database. There are multiple public APIs for this task. I think openweathermap.org is the most popular, and it's the one I'm using.

Then again, it is a public API. Though this one has a generous free tier, I don't like the idea of thousands of API calls per day for something as trivial as weather.
The solution is caching. I simply search the DB for the latest record. Each record includes a timestamp. I compare that timestamp with the current time. If the record is older than a set threshold (5 minutes in this case), we call the API; otherwise, we return the cached record.

Calling the API itself is pretty straightforward, and the same code can be used for both weather and city information.

client.go

func FetchAndDecode[T any](client *http.Client, req *http.Request) (*T, error) {
    response, err := client.Do(req)
    if err != nil {
        return nil, err
    }

    defer response.Body.Close()

    if response.StatusCode < 200 || response.StatusCode >= 300 {
        body, _ := io.ReadAll(response.Body)
        return nil, fmt.Errorf("API Error: status=%d, body=%s", response.StatusCode, body)
    }

    var result T
    if err := json.NewDecoder(response.Body).Decode(&result); err != nil {
        return nil, err
    }

    return &result, nil
}

This function uses generics [T any] to handle both the response payload and the return type. It executes the request req using the provided client, attempts to decode the JSON response into type T, and finally returning a pointer to the result and along with the error (if any).

Now, we can reuse this same function to fetch both the weather and city information without writing redundant decoding logic.

client.go

// Fetches weahter for a specific coordinates
func (c *WeatherClient) FetchWeather(ctx context.Context, lat, lon float64) (*WeatherResponse, error) {
    weatherUrl, err := url.Parse(c.WeatherURL)
    if err != nil {
        return nil, err
    }

    query := weatherUrl.Query()
    query.Set("lat", fmt.Sprintf("%f", lat))
    query.Set("lon", fmt.Sprintf("%f", lon))
    query.Set("appid", c.APIKey)
    query.Set("units", "metric")

    weatherUrl.RawQuery = query.Encode()
    req, err := http.NewRequestWithContext(ctx, "GET", weatherUrl.String(), nil)
    if err != nil {
        return nil, fmt.Errorf("Error forming the weather request: %s", err)
    }

    return FetchAndDecode[WeatherResponse](c.HTTPClient, req)
}

// Fetches city information (such as coordinates) for a city name
func (c *WeatherClient) FetchGeocoding(ctx context.Context, cityName string, limit int) ([]City, error) {
    geocoderUrl, err := url.Parse(fmt.Sprintf("%s/direct", c.GeocoderURL))
    if err != nil {
        return nil, err
    }

    query := geocoderUrl.Query()
    query.Set("q", cityName)
    query.Set("limit", fmt.Sprintf("%d", limit))
    query.Set("appid", c.APIKey)
    geocoderUrl.RawQuery = query.Encode()

    req, err := http.NewRequestWithContext(ctx, "GET", geocoderUrl.String(), nil)
    if err != nil {
        return nil, fmt.Errorf("Error forming the geocoding request: %s", err)
    }

    cities, err := FetchAndDecode[[]City](c.HTTPClient, req)
    if err != nil {
        return nil, err
    }

    if len(*cities) == 0 {
        return nil, fmt.Errorf("no cities found with name: %s", cityName)
    }

    return *cities, nil
}

The openweathermap.org API expects latitude and longitude as query params.
This means we need resolve the coordinates of a city before we can actually as for the weather. This process is called Geocoding.

In this step, we pass the city name as a query parameter and receive one or more matching cities as results, with their coordinates and other metadata. We then store those results in the following struct:

types.go

type City struct {
    Name    string  `json:"name"`
    Country string  `json:"country"`
    Lat     float64 `json:"lat"`
    Lon     float64 `json:"lon"`
    Id      int     `json:"id"`
}

The openweathermap.org API returns a massive JSON payload with dozens of fields. For a TUI, we don't need everything. I’ve simplified the structures to focus on the essentials

types.go

type Coordinates struct {
    Lat float64 `json:"lat"`
    Lon float64 `json:"lon"`
}

type BasicWeather struct {
    Type string `json:"main"`
    Desc string `json:"description"`
    Icon string `json:"icon"`
    Id   int    `json:"id"`
}

type MainWeather struct {
    Temp        float64 `json:"temp"`
    FeelsLike   float64 `json:"feels_like"`
    TempMin     float64 `json:"temp_min"`
    TempMax     float64 `json:"temp_max"`
    Pressure    int     `json:"pressure"`
    Humidity    int     `json:"humidity"`
    SeaLevel    int     `json:"sea_level"`
    GroundLevel int     `json:"grnd_level"`
}

type Wind struct {
    Speed float64 `json:"speed"`
    Gust  float64 `json:"gust"`
    Deg   int     `json:"deg"`
}

type WeatherSys struct {
    Country string `json:"country"`
    Sunrise int64  `json:"sunrise"`
    Sunset  int64  `json:"sunset"`
    Type    int    `json:"type"`
    Id      int    `json:"id"`
}

type Location struct {
    Name  string      `json:"name"`
    Coord Coordinates `json:"coord"`
    Id    int         `json:"id"`
}

type WeatherResponse struct {
    Weather  []BasicWeather `json:"weather"`
    Main     MainWeather    `json:"main"`
    Sys      WeatherSys     `json:"sys"`
    Wind     Wind           `json:"wind"`
    Coord    Coordinates    `json:"coord"`
    Rain     float64        `json:"rain.1h"`
    Base     string         `json:"base"`
    Name     string         `json:"name"`
    DT       int64          `json:"dt"`
    COD      int            `json:"cod"`
    ID       int            `json:"id"`
    Clouds   int            `json:"clouds.all"`
    Timezone int            `json:"timezone"`
    Vis      int            `json:"visibility"`
}

Note: I might have skipped one or two fields, the sole reason being nothing but my laziness. You can take a look at the full response schema here.

The Weather Service

With this let's create high-level functions to fetch the weather and cities. Because we have a caching mechanism in-place with SQLite , it's better to have a weatherSevice struct to handle both the DB connection and env variables.

type WeatherService struct {
    DB     *database.Queries
    Client *WeatherClient
}

Then we add the methods.

// for weather
func (s *WeatherService) GetWeather(ctx context.Context, loc Location) (*WeatherResponse, error) {
    var w *WeatherResponse
    if loc.Coord.Lat == 0 && loc.Coord.Lon == 0 {
        cities, err := s.ResolveCity(ctx, loc.Name)
        if err != nil {
            return nil, err
        }
        loc.Coord = Coordinates{Lat: cities[0].Lat, Lon: cities[0].Lon}
    }

    cacheParams := database.GetFreshWeatherByCoordsParams{
        Lat:       sql.NullFloat64{Float64: loc.Coord.Lat - EPSILON, Valid: true},
        Lat_2:     sql.NullFloat64{Float64: loc.Coord.Lat + EPSILON, Valid: true},
        Lon:       sql.NullFloat64{Float64: loc.Coord.Lon - EPSILON, Valid: true},
        Lon_2:     sql.NullFloat64{Float64: loc.Coord.Lon + EPSILON, Valid: true},
        FetchedAt: sql.NullInt64{Int64: time.Now().Add(-10 * time.Minute).Unix(), Valid: true},
    }

    if cached, err := s.DB.GetFreshWeatherByCoords(ctx, cacheParams); err == nil {
        cachedWeatherRes := WeatherCacheToResponse(cached)
        return &cachedWeatherRes, nil
    }

    w, err := s.Client.FetchWeather(ctx, loc.Coord.Lat, loc.Coord.Lon)
    if err != nil {
        return nil, err
    }

    func() {
        if err := s.DB.InsertWeather(ctx, w.ToDBWeather()); err != nil {
            log.Printf("Failed to cache the response: %s", err)
        }
    }()

    return w, nil
}

// for city
func (s *WeatherService) ResolveCity(ctx context.Context, name string) ([]City, error) {
    var cities []City
    query := name + "%"
    dbCities, err := s.DB.FuzzYFindCity(ctx, query)

    log.Printf("db queried")

    if err != nil {
        log.Printf("fuzzyfind Error: %v", err)
    }

    if err == nil && len(dbCities) > 0 {
        for _, dbCity := range dbCities {
            cities = append(cities, City{
                Id:      int(dbCity.ID),
                Name:    dbCity.Name,
                Country: dbCity.Country,
                Lat:     dbCity.Lat,
                Lon:     dbCity.Lon,
            })
        }
        return cities, nil
    }

    cities, err = s.Client.FetchGeocoding(ctx, name, 1)
    log.Printf("api queried")
    if err != nil || len(cities) == 0 {
        log.Printf("city '%s' not found locally or via API", name)
        return nil, fmt.Errorf("city '%s' not found locally or via API", name)
    }

    return cities, nil
}

Let's also create a function to initialize the service.

func NewWeatherService(conn *sql.DB, client *WeatherClient) *WeatherService {
    return &WeatherService{
        DB:     database.New(conn),
        Client: client,
    }
}

The Database (Caching with SQLite)

Most people would use a simple JSON file or an in-memory map for caching. I chose SQLite . Why? Because I wanted full-text indexing on 200,000 cities and the ability to perform fuzzy searches without hitting an external API every time the user types a letter.

SQLite is simple, single-file based, relational yet surprisingly powerful database. The whole point of using a database in an weather app is speed. This is achieved with denormalized schemas and lots of indexing.

The most difficult part of designing any application, in my opinion, is the database schema. For this one, I just added simple tables to store:

API responses
City information

CREATE TABLE cities (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT NOT NULL,
    lat REAL NOT NULL,
    lon REAL NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE weather_cache (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    -- Location
    city_id INTEGER,
    city_name TEXT,
    country TEXT,
    lat REAL,
    lon REAL,

    -- Weather summary
    weather_main TEXT,
    weather_desc TEXT,
    weather_icon TEXT,

    -- Temperature
    temp REAL,
    feels_like REAL,
    temp_min REAL,
    temp_max REAL,
    humidity INTEGER,
    pressure INTEGER,

    -- Wind
    wind_speed REAL,
    wind_deg INTEGER,
    wind_gust REAL,

    -- Rain (nullable)
    rain_1h REAL,

    -- Clouds
    cloudiness INTEGER,

    -- Visibility
    visibility INTEGER,

    -- Time
    weather_time INTEGER,   -- `dt` from API
    fetched_at INTEGER,    -- when WE fetched it (unix time)
    timezone INTEGER
);

Searching the cities with city names was a core feature, so I added a simple index on city name.

-- v1
CREATE INDEX city_name_idx ON cities (name);

Then I realized I needed more of a "fuzzy find" approach.

-- v2
CREATE INDEX city_name_idx ON cities (name COLLATE NOCASE);

This enables case-insensitive searches, which works well for city lookups.

For weather data, queries often involve multiple columns. For example, I might need to fetch the latest weather information for a city based on cities name, country or even coordinated. To support this efficiently, I added indexes on all relevant columns.

CREATE INDEX idx_weather_cid ON weather_cache(city_id);
CREATE INDEX idx_weather_city ON weather_cache(city_name, country);
CREATE INDEX idx_weather_coords ON weather_cache(lat, lon);
CREATE INDEX idx_weather_fetched ON weather_cache(fetched_at);

That's it.

Migrations with `goose`

For table creation and schema migrations, I went with a command line tool called goose.

# instaltion command
go install github.com/pressly/goose/v3/cmd/goose@latest

To use goose , I have put all the migration code in to a directory called sql\schema

Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a---          17-01-2026    23:47            272 001_cities.sql
-a---          17-01-2026    23:52            117 002_index_cities.sql
-a---          18-01-2026    13:25            851 003_weather_cache.sql
-a---          18-01-2026    13:28            483 004_index_weather_cache.sql
-a---          18-01-2026    21:04            193 005_index_cities.sql
-a---          24-01-2026    17:46            726 006_add_cols_weather_cache.sql

Each migration is prefixed with a sequence number (001, 002 etc.) so goose can keep track of which migrations have already been applied.

001_cities.sql

-- +goose Up

CREATE TABLE cities (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT NOT NULL,
    lat REAL NOT NULL,
    lon REAL NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- +goose Down
DROP TABLE cities;

The comment -- +goose Up marks the SQL to be executed during an up migration. Similarly, -- +goose Down defines how to revert the changes.
-- +goose Down is added before the command for down migration.

Important: Always include both **Up* and Down migrations.
The Down migration should fully undo the changes made by the Up migration*

To execute the migrations, we just have to run the following command
from sql\schema directory:

goose sqlite3 ../../small.db up

Here small.db is the SQLite DB file. Basically we have to provide the relative path to it.

Type Safe queries with `sqlc`

If you've worked with SQL before, you probably already know there are several ways we can execute SQL queries from a programming language, such as:

Database Driver: This is a low level approach where you install a DB-specific driver for your language or choice. Then you open a connection, send raw SQL strings, and finally closing the connection. This approach is fast, but it gets messy quickly and the DX is usually not great.
Query Builders:
These are built on top of database drivers. You use a library that lets you build queries using method chaining or function calls, and the library generates the SQL under the hood.

Example:

db.select('*').from('users').where('age', '>', 18)

ORMs *(Object-Relational Mappers)*: This might be highest level of abstraction, where:
- Tables <-> classes
- Roes <-> objects The SQL is mostly hidden. Here the DX is great but performance can degrade, and you have almost no control over the generated SQL .

sqlctakes a different approach. We write raw SQL queries in separate files and sqlc generates type-safe Go methods from them.

Example:

sql\queries\cities.sql

-- name: CreateCity :one
INSERT INTO cities (id, name, country, lat, lon)
VALUES (?, ?, ?, ?, ?)
RETURNING *;

-- name: DeleteCity :exec
DELETE FROM cities
WHERE id = ?;

-- name: FindCity :many
SELECT *
FROM cities
WHERE LOWER(name)=LOWER(?);


-- name: FindCityWithID :one
SELECT *
FROM cities
WHERE id = ?;

-- name: FuzzYFindCity :many
SELECT *
FROM cities
WHERE name like ?;

Here also, we add special comments to instruct sqlc. The syntax is

-- name: <FunctionName> : <return type>

Where the return type can be :one, :many, or :exec

Similarly, for weather data:

sql\queries\weather_cache.sql

-- name: GetLatestWeatherByCity :one
SELECT *
FROM weather_cache
WHERE city_name = ?
ORDER BY fetched_at DESC
LIMIT 1;

-- name: GetFreshWeatherByCity :one
SELECT *
FROM weather_cache
WHERE city_name = ?
  AND fetched_at >= ?
ORDER BY fetched_at DESC
LIMIT 1;

-- name: GetLatestWeatherByCoords :one
SELECT *
FROM weather_cache
WHERE lat >= ?
  AND lat <= ?
  AND lon >= ?
  AND lon <= ?
  AND fetched_at >= ?
ORDER BY fetched_at DESC
LIMIT 1;
.
.
.

Configuring `sqlc`

To specify details such as SQL engine, target language, and the schema and queries directories, we create a configuration file at the root of our project called sqlc.yaml.

sqlc.yaml

version: "2"
sql:
  - schema: "sql/schema"
    queries: "sql/queries"
    engine: "sqlite"
    gen:
      go:
        out: "internal/database"

Then let's install sqlc if not already installed:

go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest

Then we run the following command at the project root:

sqlc generate

This command validates the schema and queries, then generates the corresponding Go code in the internal/database directory, as specified in sqlc.yaml.

Now, that we are mostly done with DB.
Let's move on to the interesting part - the TUI.

Making a TUI with `Bubble Tea`

Bubble Tea is a pretty popular framework for building terminal apps. I had wanted to use it for a long time. So let's get started.

How Does Bubble Tea render UI ?

Without diving deep into the internals of Bubble Tea, let's just say it uses The Elm Architecture .

This architecture has three core components:

Model - the state of you application
View - a way to render the UI based on the current Model or state.
Update - a way to update the Model / state based of messages (or events).

That's it.

The Model

The first step is to design the model which represents the state of the application. This model holds all the application data (such as current city, current weather, searching, error etc.)

type StateModel struct {
    service *weather.WeatherService // holds http clients, db connection etc

    textInput         textinput.Model  // a text input component provied by `bubble tea`
    searchResults     list.Model   // a list component from `bubble tea`
    curItem           *weather.City  // pointer to current city
    curWeather        *weather.WeatherResponse  // pointer to current weather
    isFilterOpen      bool  // indicates if filtering is on
    isFetchingWeather bool  
    keys              *itemsKeyMap  // pointer to keyboard shotcuts
    help              help.Model  // pointer to help component
    err               error
    debounceId        int  // used in debouncing
    width             int  
    height            int  // both height an width is used for terminal size

    logging bool
}

Now, we create the initial model:

func NewModel(service *weather.WeatherService) StateModel {
    newSearchResults := list.New(nil, list.NewDefaultDelegate(), 0, 0)
    ti := textinput.New()
    ti.Placeholder = "Search for a city"
    ti.CharLimit = 50
    ti.Width = 40

    newModel := StateModel{
        service:           service,
        textInput:         ti,
        searchResults:     newSearchResults,
        curItem:           nil,
        curWeather:        nil,
        isFilterOpen:      false,
        isFetchingWeather: false,
        debounceId:        0,
        err:               nil,
        keys:              newItemsKeyMap(),
        help:              help.New(),
    }
    newModel.searchResults.Title = "Find Cities"
    newModel.searchResults.SetShowFilter(false)
    newModel.searchResults.SetShowHelp(false)
    newModel.searchResults.SetFilteringEnabled(false)

    return newModel
}

The `Init()` method

The Init() method runs when the UI is first rendered.
In this case, there’s nothing we need to do at startup, so we simply return nil

package ui

import tea "github.com/charmbracelet/bubbletea"

func (curM StateModel) Init() tea.Cmd {
    return nil
}

We need two additional methods:

Update
View

If either of these three methods ( Init(), Update(), View() ) of our StateModel struct is missing, the application will fail to compile.

The `View()` method

This method is responsible for displaying the UI based on the current model state.

func (curM StateModel) View() string {
    var content string

    helpView := curM.renderContextualHelp()
    height := max(curM.height-lipgloss.Height(helpView), 0)

    if curM.err != nil {
        content = windowStyle.
            Width(curM.width).
            Height(height).
            Render(errorStyle.Render(fmt.Sprintf("❌ Error: %v", curM.err)))
    } else if curM.isFilterOpen {
        searchContent := lipgloss.JoinVertical(lipgloss.Left,
            titleStyle.Render("🌤️ Weather Search"),
            curM.textInput.View(),
            "",
            curM.searchResults.View(),
        )
        content = windowStyle.
            Width(curM.width).
            Height(height).
            Render(searchContent)
    } else if curM.curItem != nil && curM.curWeather != nil {
        content = renderWeather(curM.curWeather, curM.width, height)
    } else {
        content = windowStyle.
            Width(curM.width).
            Height(height).
            Render("Loading...")
    }

    return lipgloss.JoinVertical(lipgloss.Left, content, helpView)
}

There are two main states, either the filter for the cities is open or closed. When the filter is not open we check if we have a city and weather and render it. The full working code can be accessed from github. We are using lipgloss for colors and styling.

The `Update()` method

The Update() method has the following signature:

func (curM StateModel) Update(msg tea.Msg) (tea.Model, tea.Cmd)

It take a tea.Msg as an argument, which is like an event. Whenever something changes ( like terminal resize, key press, mouse input, etc.) Bubble Tea emits a message ( tea.Msg ) that is handled in this Update() method.

If you look closely you can see that Update() method returns a

an updated tea.Model
tea.Cmd, which tells Bubble Tea to perform an action asynchronously.

Because commands run asynchronously, heavy or blocking work should never be done directly inside Update():

func (curM StateModel) Update(msg tea.Msg) (tea.Model, tea.Cmd) {
    var cmd tea.Cmd = nil
    switch msg := msg.(type) {

    case tea.WindowSizeMsg:  // window is resized
        curM.width, curM.height = msg.Width, msg.Height
        curM.searchResults.SetSize(msg.Width-4, msg.Height-8)

    case citySearchResultMsg:  // handles city search results
        curM.searchResults.SetItems(msg.locs)

    case weatherSearchResultMsg:  // handle weather search results
        curM.curWeather = msg.weather
        curM.isFetchingWeather = false

    case debouncedMsg:  // handle debounced requests
        if curM.debounceId != msg.id {
            return curM, nil
        }
        return curM, curM.performLocationSearch()

    case errorMsg:
        curM.err = msg
        return curM, tea.Quit

    case tea.KeyMsg:  // core logic. a key msg can be navigating keys
                      // or keypresses when user searches for a city
                      // quit or open/close filter view commands
        switch {

        case key.Matches(msg, curM.keys.quit) && !curM.textInput.Focused():
            return curM, tea.Quit

        case key.Matches(msg, curM.keys.toggleFilter):
            if !curM.isFilterOpen {
                curM.isFilterOpen = true
                return curM, curM.textInput.Focus()
            }

            if !curM.textInput.Focused() {
                return curM, curM.textInput.Focus()
            }
            curM.isFilterOpen = false
            curM.textInput.Blur()
            return curM, nil

        case key.Matches(msg, curM.keys.choose):
            if i, ok := curM.searchResults.SelectedItem().(weather.City); ok {
                curM.curItem = &i
                curM.isFilterOpen = false
                curM.textInput.Blur()
                curM.isFetchingWeather = true
                cmd = curM.performWeatherSearch()
            }
            return curM, cmd
        case key.Matches(msg, curM.keys.back):
            if curM.textInput.Focused() {
                curM.textInput.Blur()
                return curM, nil
            }

            if curM.isFilterOpen {
                curM.isFilterOpen = false
                return curM, nil
            }

            return curM, tea.Quit

        default:
            if !curM.textInput.Focused() {
                curM.searchResults, cmd = curM.searchResults.Update(msg)
                return curM, cmd
            }

            curM.textInput, cmd = curM.textInput.Update(msg)
            return curM, tea.Batch(cmd, curM.debouncedSearch())
        }

    }

    return curM, cmd

}

As you can see in this code snippet we are handling the filtering and results ourselves.

When the text changes in the filter's text input. The textInput.Model registers a tea.keyMsg.
We debounce the search request. After certain delay, we perform the search and register a citySearchResultMsg which contains the city results.
Finally based on this results we update the list on the TUI.

Fetching the weather is handled similarly.
When the weather data is received, we update our model, close the filter view, and render the weather beautifully in the terminal.

This is basically it.

Some Small Details

Here we are actually caching the weather responses. If the weather is already available in the cache, that cache version is show, instead of making another API call.
I downloaded a huge list of city information containing almost 200000 cities. Then I put them into our SQLite table. When the app searches for a city, it first looks up this local table, if not found only then it calls the openweathermap.org API to get the location coordinates.
While building the binaries for a release version, I compressed the DB in a .gz file which contains all the tables and the city information.

Conclusion

There you have it, the most complicated weather app no one asked for, which I built anyway. I learned, GO backend, how TUI's work, migrations, effective debouncing, how to build a release binary, create GitHub assets and many more.
Maybe the real learning is the fun I made along the way !!!