如何在Go二进制文件中捆绑SQLite数据库? [英] How to bundle an SQLite database in a Go binary?

问题描述

我尝试使用go-bindata和packr，但是这些软件包没有显示如何将SQLite数据库文件打包为二进制文件.

I am try to use go-bindata and packr, but those packages do not show how to pack an SQLite database file in to a binary file.

我不需要以任何方式更新数据库，我只想在启动时从数据库中读取数据.

I don't need to update the database in any way, I just want to read the data from it on startup.

如何将SQLite数据库文件嵌入Go二进制文件中?

How can I embed an SQLite database file in a Go binary file?

推荐答案

SQLite驱动程序无法从内存(例如，从字节片)读取数据库文件.但是您可以将数据写入一个临时文件，然后打开它:

The SQLite driver can't read a database file from memory (e.g. from a byte slice). But you can write the data to a temporary file, and open that:

//go:generate go run gen.go

package main

import (
    "database/sql"
    "fmt"
    "io/ioutil"
    "log"
    "os"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    // Create temporary file for database.
    tmpDB, err := ioutil.TempFile("", "db*.sqlite3")
    if err != nil {
        log.Fatal(err)
    }
    // Remove this file after on exit.
    defer func() {
        err := os.Remove(tmpDB.Name())
        if err != nil {
            log.Print(err)
        }
    }()

    // Write database to file.
    _, err = tmpDB.Write(sqlDB)
    if err != nil {
        log.Print(err)
    }
    err = tmpDB.Close()
    if err != nil {
        log.Print(err)
    }

    // Open DB.
    db, err := sql.Open("sqlite3", tmpDB.Name()+"?mode=ro")
    if err != nil {
        log.Fatal(err)
    }

    // Make sure it's loaded correct.
    rows, err := db.Query("select * from test")
    if err != nil {
        log.Fatal(err)
    }

    for rows.Next() {
        var c string
        err := rows.Scan(&c)
        if err != nil {
            log.Fatal(err)
        }
        fmt.Println(c)
    }
}

您可以使用类似以下内容将数据库写入 db.go :

And you can write the database to db.go with something like:

// +build generate

package main

import (
    "fmt"
    "io/ioutil"
    "log"
    "os"
    "strings"
)

func main() {
    // Read source database file.
    d, err := ioutil.ReadFile("source.sqlite3")
    if err != nil {
        log.Fatal(err)
    }

    fp, err := os.Create("db.go")
    if err != nil {
        log.Fatal(err)
    }

    _, err = fmt.Fprintf(fp, "// Code generated by gen.go; DO NOT EDIT.\n\n"+
        "package main\n\n"+
        "var sqlDB = %s\n", asbyte(d))
    if err != nil {
        log.Fatal(err)
    }
}

// Write any data as byte array.
func asbyte(s []byte) string {
    var b strings.Builder
    for i, c := range s {
        if i%19 == 0 {
            b.WriteString("\n\t\t")
        }
        b.WriteString(fmt.Sprintf("%#x, ", c))
    }
    return "[]byte{" + b.String() + "}"
}

如果愿意，也可以使用go-bindata或packr，但我看不出有什么优势.

You can also use go-bindata or packr for that if you prefer, but I don't really see an advantage.

另一种方法是使用内存数据库，该数据库可能会更快，具体取决于您要执行的操作.

An alternative way is to use a memory database, which may be faster depending on what you want to do.

1. 将您想要的Go二进制文件中的SQL模式和行作为字符串嵌入.
2. 在程序启动时打开一个新的内存数据库( sql.Open("sqlite3"，:memory:`)"，然后创建架构并插入行.

1. Embed the SQL schema and rows you want in your Go binary as strings.
2. Open a new memory database when your program starts (sql.Open("sqlite3",:memory:`) and create the schema and insert the rows.

此方法无法访问磁盘，因此以较慢的启动时间为代价(肯定是基准！)来查询它可能会快一点.

There is no disk access with this method, so querying it will probably be a bit faster at the expensive of slower startup times (benchmark to be sure!)

这篇关于如何在Go二进制文件中捆绑SQLite数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！