Implementing a Simple SQLite‑like Database in Go

This article introduces the design and implementation of a minimal database using Go, inspired by SQLite. It explains the motivation for building a simple database to deepen understanding of database fundamentals such as memory‑disk storage, primary‑key uniqueness, indexing, and full‑table scans.

1. Goal of the Go implementation – To explore how data is stored in memory and on disk, how pages are written to disk, and how B‑tree structures manage data.

2. Choice of SQLite – SQLite is fully open‑source, has a simple C implementation, and serves as a reference for building a relational database in Go.

3. SQLite architecture overview – The front‑end parses SQL into virtual‑machine bytecode; the back‑end VM executes the bytecode, operating on tables and indexes stored as B‑trees. Pages are cached in memory and flushed to disk by a pager.

4. Core components and code

// run main – entry point for unit testing
func run() {
    table, err := dbOpen("./db.txt")
    if err != nil { panic(err) }
    for {
        printPrompt()
        inputBuffer, err := readInput()
        if err != nil { fmt.Println("read err", err) }
        if len(inputBuffer) != 0 && inputBuffer[0] == '.' {
            switch doMetaCommand(inputBuffer, table) {
            case metaCommandSuccess: continue
            case metaCommandUnRecongnizedCommand:
                fmt.Println("Unrecognized command", inputBuffer)
                continue
            }
        }
        statement := Statement{}
        switch prepareStatement(inputBuffer, &statement) {
        case prepareSuccess: break
        case prepareUnrecognizedStatement:
            fmt.Println("Unrecognized keyword at start of ", inputBuffer)
            continue
        default:
            fmt.Println("invalid input ", inputBuffer)
            continue
        }
        res := executeStatement(&statement, table)
        if res == ExecuteSuccess { fmt.Println("Executed"); continue }
        if res == ExecuteTableFull { fmt.Printf("Error: Table full.\n"); break }
        if res == EXECUTE_DUPLICATE_KEY { fmt.Printf("Error: Duplicate key.\n"); break }
    }
}

The doMetaCommand function handles special commands such as .exit , .btree , and .constants :

func doMetaCommand(input string, table *Table) metaCommandType {
    if input == ".exit" { dbClose(table); os.Exit(0); return metaCommandSuccess }
    if input == ".btree" { fmt.Printf("Tree:\n"); print_leaf_node(getPage(table.pager, 0)); return metaCommandSuccess }
    if input == ".constants" { fmt.Printf("Constants:\n"); print_constants(); return metaCommandSuccess }
    return metaCommandUnRecongnizedCommand
}

Parsing (the simplest SQL compiler) recognizes only insert and select statements:

func prepareStatement(input string, statement *Statement) PrepareType {
    if len(input) >= 6 && input[0:6] == "insert" {
        statement.statementType = statementInsert
        inputs := strings.Split(input, " ")
        if len(inputs) <= 1 { return prepareUnrecognizedStatement }
        id, err := strconv.ParseInt(inputs[1], 10, 64)
        if err != nil { return prepareUnrecognizedSynaErr }
        statement.rowToInsert.ID = int32(id)
        statement.rowToInsert.UserName = inputs[2]
        statement.rowToInsert.Email = inputs[3]
        return prepareSuccess
    }
    if len(input) >= 6 && input[0:6] == "select" {
        statement.statementType = statementSelect
        return prepareSuccess
    }
    return prepareUnrecognizedStatement
}

The virtual machine dispatches statements to the appropriate executor:

func executeStatement(statement *Statement, table *Table) executeResult {
    switch statement.statementType {
    case statementInsert:
        return executeInsert(statement, table)
    case statementSelect:
        return executeSelect(statement, table)
    default:
        fmt.Println("unknown statement")
    }
    return ExecuteSuccess
}

Data is serialized to a fixed‑size row before being written to disk:

// Serialize a row into a byte buffer
func serializeRow(row *Row, destination unsafe.Pointer) {
    ids := Uint32ToBytes(row.ID)
    q := (*[ROW_SIZE]byte)(destination)
    copy(q[0:ID_SIZE], ids)
    copy(q[ID_SIZE+1:ID_SIZE+USERNAME_SIZE], row.UserName)
    copy(q[ID_SIZE+USERNAME_SIZE+1:ROW_SIZE], row.Email)
}

Deserialization reads a row back from a memory page:

// Deserialize a row from a byte buffer
func deserializeRow(source unsafe.Pointer, rowDestination *Row) {
    ids := make([]byte, ID_SIZE, ID_SIZE)
    sourceByte := (*[ROW_SIZE]byte)(source)
    copy(ids[0:ID_SIZE], (*sourceByte)[0:ID_SIZE])
    rowDestination.ID = BytesToInt32(ids)
    userName := make([]byte, USERNAME_SIZE, USERNAME_SIZE)
    copy(userName[0:], (*sourceByte)[ID_SIZE+1:ID_SIZE+USERNAME_SIZE])
    rowDestination.UserName = string(getUseFulByte(userName))
    emailStoreByte := make([]byte, EMAIL_SIZE, EMAIL_SIZE)
    copy(emailStoreByte[0:], (*sourceByte)[1+ID_SIZE+USERNAME_SIZE:ROW_SIZE])
    rowDestination.Email = string(getUseFulByte(emailStoreByte))
}

The pager manages pages in memory and flushes them to disk page‑by‑page, reducing I/O:

// Flush a page to the file system
func pagerFlush(pager *Pager, pageNum, realNum uint32) error {
    if pager.pages[pageNum] == nil { return fmt.Errorf("pagerFlush null page") }
    offset, err := pager.osfile.Seek(int64(pageNum*PageSize), io.SeekStart)
    if err != nil { return fmt.Errorf("seek %v", err) }
    originByte := make([]byte, realNum)
    q := (*[PageSize]byte)(pager.pages[pageNum])
    copy(originByte[0:realNum], (*q)[0:realNum])
    bytesWritten, err := pager.osfile.WriteAt(originByte, offset)
    if err != nil { return fmt.Errorf("write %v", err) }
    fmt.Println("already written", bytesWritten)
    return nil
}

Closing the database writes all dirty pages back to disk:

func dbClose(table *Table) {
    for i := uint32(0); i < table.pager.numPages; i++ {
        if table.pager.pages[i] == nil { continue }
        pagerFlush(table.pager, i, PageSize)
    }
    defer table.pager.osfile.Close()
}

Page retrieval loads a page from disk on demand, using binary search within leaf nodes to locate keys. The cursor abstraction enables sequential traversal of rows.

5. Summary – The article demonstrates a Go‑based, SQLite‑inspired database that supports basic insert and select operations, page‑level disk I/O, and B‑tree storage. The current implementation is limited to a single 4 KB page; future work will extend multi‑page handling and improve capacity.