Getting started connecting to sqlite db

[27concepts]

Hi, I am lost as to how to get started here.

I have a sqlite db and I just want to query it. I am able to connect to it via DB Browser for SQLite, but no idea how to go about it using lua code.

I moved the sqlite folder in the same folder as my lua code and added local has_sqlite, sqlite = pcall(require, "sqlite") that I found in the other discussion. It doesn't work.

Help?

Thanks.

[kkharji]

Hey @27concepts, sorry to hear that.

If you installed sqlite.lua then it the access part should be dealt with. Now, if you have a database filled with data, I suggest you first back it up, just in case anything goes wrong, or possibly of being locked due to write ops (see using sqlite with firefox database)

There are two ways in which you can start communicating with database and all of theme are simple and straightforward :

1. quick and dirty

Open the db, execute statements and close it

local items = sqlite:with_open("/path/to/db", function(mydb) 
     -- see `: h sqlite_query_select` && `:h sqlite.db:select`
    return mydb:select("tablename", { where = {  key = "value" }})
    -- just evaluate a sql statement
   return db:eval("select * from tablename where x = ?", 1) -- 
end)

2. structured

This one is much more elegant, stable and super lazy, with auto closing

local mydb = sqlite {
   uri = "path/to/db",
   tablename = {
   -- ... keys and their types
  }
}

local tablename = mydb.tablename

tablename:count() -- number of elements

Now you can see :h sqlite.tbl.lua to see all the methods you can do with table object.

References:

1. Help docs :h sqlite.readme, the api is well documented and everything possible can be found there
2. Tests, sometimes the best way to know how something behave is to look at the tests. ..

Does this help?

[27concepts]

Thank you for replying!

Thing is, I am not sure I installed sqlite.lua correctly. What exactly needs to be done to "install" it? I downloaded the sqlite.lua-master.zip file from Github here and unzipped it in the folder of where my .lua file is.

Anything else needs to be done? How can I confirm it's installed correctly?

[kkharji]

Oh, hmmm sqlite requires luv and LuaJIT lua -v.

If you have luv and running on luajit then copying it should just work.

btw what kind of error you get from doing require'sqlite' and the code snippet above?

[27concepts]

Ok, then I didn't install anything regarding LuaJIT. I am looking for a solution that is "portable", that is, dependent solely on a .dll or something that doesn't require installation on another system.

[kkharji]

I suppose luv dependency can be removed, see how it is used https://github.com/kkharji/sqlite.lua/search?q=luv. If you have some time to replace them with built in method, I can merge it

• sqlite.lua/lua/sqlite/utils.lua

  Line 106 in b3de8f5

  expanded = luv.fs_realpath(path)

• sqlite.lua/lua/sqlite/defs.lua

  Line 18 in 56c5aac

  path, _ = luv.os_getenv "LIBSQLITE"

• sqlite.lua/lua/sqlite/helpers.lua

  Line 76 in ca8233f

  o.mtime = o.db.uri and (luv.fs_stat(o.db.uri) or { mtime = {} }).mtime.sec or nil

• sqlite.lua/lua/sqlite/tbl/cache.lua

  Line 65 in 78ff09f

  local stat = luv.fs_stat(self.db.uri)