"no such table" error with in-memory sqlite

Question

I am using in-memory sqlite as follows.

func init() {
    global.ConductorConfig = readConfig()
    log.Println(utils.GetCurrentDir() + global.ConductorConfig.DbFile)
    //db = sqlx.MustConnect("sqlite3", utils.GetCurrentDir()+global.ConductorConfig.DbFile)
    db = sqlx.MustConnect("sqlite3", ":memory:")
    db.Exec(schema)
    task:=model.Task{}
    SaveTask(&task)
    db.MapperFunc(func(s string) string {
        return s
    })
}

in my main func, I create the table

if global.ConductorConfig.DevMode {
    db.CreateTables()
}
go job.HeartbeatJob()
go job.TaskClearJob()
app.Action = func(c *cli.Context) error {
    ListenAndServe()
    return nil
}

Then I go 'no such table' in http handler function.

existed, err := db.GetAgentByServerName(agent.ServerName)
if err != nil {
    c.JSON(http.StatusBadRequest, err)
    log.Println("[conductor] error occurred when get agent by server name: " + err.Error())
    return err
}


func GetAgentByServerName(name string) (*model.Agent, error) {
    agent := &model.Agent{}
    err := db.Get(agent, "select * from agent where ServerName=$1", name)

    if err == sql.ErrNoRows {
        err = nil
        agent = nil
    }

    return agent, err
}

When I start the program, I got

error occurred when get agent by server name: no such table: agent

db schema (attributes omitted)

var schema = `
DROP TABLE IF EXISTS agent;
CREATE TABLE agent (
    Id                  INTEGER PRIMARY KEY,
);

DROP TABLE IF EXISTS task;
CREATE TABLE task (
    Id                  INTEGER PRIMARY KEY,
);

I can not share all the code, here is a minimal example to reproduce the same error.

    package main

    import (
        "github.com/jmoiron/sqlx"
        _ "github.com/mattn/go-sqlite3"
        "log"
    )

    var db *sqlx.DB

    func init() {
        db = sqlx.MustConnect("sqlite3", ":memory:")
        db.Exec("CREATE TABLE agent (Id  INTEGER PRIMARY KEY,);")
    }

    func main()  {
        _, err:=db.Exec("insert into agent values (1)")
        if err!=nil{
            log.Println(err)
        }

}

Answer 1

:memory: will open a separate database for each connection. use file::memory:?cache=shared instead.

Update: I also run into some lock issues using the above code, it seems to me that use :memory: with db.DB().SetMaxOpenConns(1) in tests is a better solution.

Answer 2

Maybe this would help someone

I had the same issue with 'no such table' and found an exact explanation:

If sql.Rows is not closed before next query then driver returns no such table on next query. Issue exists only for in-memory storage, same test for file backed db runs fine.

After that, I found the part of code where I had forgotten to do rows.Close(), fixed that and now it works with no issues.

Original answer: https://github.com/mattn/go-sqlite3/issues/511#issuecomment-396290916

Answer 3

If you used the same schema for creating table the one you posted here then the table is never created because the schema has syntax error and it should be like this

var schema = `
  DROP TABLE IF EXISTS agent;
  CREATE TABLE agent (
    Id                  INTEGER PRIMARY KEY
 );

  DROP TABLE IF EXISTS task;
  CREATE TABLE task (
    Id                  INTEGER PRIMARY KEY
);
`

And I'm guessing your code is same as example code, so in the init function you have skipped the error checking for db.Exec(schema) (which you should never do that for any function in Go). As the error is not checked and it got an error for executing the schema so the tables are never created but the program continues, correct your schema and check the error then try it again. Try this in you init function:

_, err := db.Exec(schema)

if err != nil {
    log.Fatal(err)
}