Repository: tami5/sql.nvim (lua)

Issue:
auto alter table schema
It's really annoying to have to call table drop or keep track of schema.ensure. In https://github.com/nvim-telescope/telescope-frecency.nvim/pull/35, I was trying to debug and understand why count field keep being nil, even though the schema has default=0. It turns out the calling files.schema() returned an old schema, which means that the new schema has not effect on pre-existing table. 

I'd like to compare the sql table schema and user provided schema and compare
between them, and if they are different. I'd like table module to auto alter.

The full table schema information as it exists in C side can be found with `db:schema("files", true)`, it output something like that 

~~~lua
{
  def = {},
  info = {
    count = { cid = 0, primary = false, required = false, type = "INTEGER" },
    id = { cid = 1, primary = true, required = false, type = "INTEGER" },
    path = { cid = 2, primary = false, required = false, type = "TEXT" }
  },
  req = {},
  types = { count = "INTEGER", id = "INTEGER", path = "TEXT" }
}
~~~

In telescope-frecency case, I wanted it to change to the following:
~~~lua
{
  def = { count = "0" },
  info = {
    count = { cid = 0, default = "0", primary = false, required = false, type = "integer" },
    id = { cid = 1, primary = true, required = true, type = "integer" },
    path = { cid = 2, primary = false, required = false, type = "string" }
  },
  req = {},
  types = { count = "integer", id = "integer", path = "string" }
}
~~~

Pull request description:
#### Purpose


closes #97. This such a cool feature as it enable the user to avoid dropping and recreating tables or even manually run alter.

Patch:
diff --git a/doc/sql.txt b/doc/sql.txt
index 66245e2e..8e88a11b 100644
--- a/doc/sql.txt
+++ b/doc/sql.txt
@@ -246,14 +246,13 @@ DB:drop({tbl_name})                                                *DB:drop()*
         `db:drop("todos")` drop table.
 
 
-DB:schema({tbl_name}, {info})                                    *DB:schema()*
+DB:schema({tbl_name})                                            *DB:schema()*
     Get {name} table schema, if table does not exist then return an empty
     table.
 
 
     Parameters: ~
-        {tbl_name} (string)   the table name.
-        {info}     (boolean)  whether to return table info. default false.
+        {tbl_name} (string)  the table name.
 
     Return: ~
         table list of keys or keys and their type.
diff --git a/lua/sql.lua b/lua/sql.lua
index b21207c1..904af247 100644
--- a/lua/sql.lua
+++ b/lua/sql.lua
@@ -28,11 +28,16 @@ DB.__index = DB
 ---@field where table: key and value
 ---@field values table: key and value to updated.
 
----return now date
----@todo: decide whether using os.time and epoch time would be better.
----@return string osdate
-local created = function()
-  return os.date "%Y-%m-%d %H:%M:%S"
+---Get a table schema, or execute a given function to get it
+---@param schema table|nil
+---@param self SQLDatabase
+local get_schema = function(tbl_name, self)
+  local schema = self.tbl_schemas[tbl_name]
+  if schema then
+    return schema
+  end
+  self.tbl_schemas[tbl_name] = self:schema(tbl_name)
+  return self.tbl_schemas[tbl_name]
 end
 
 ---Creates a new sql.nvim object, without creating a connection to uri.
@@ -66,12 +71,13 @@ function DB:open(uri, opts, noconn)
       closed = noconn and true or false,
       sqlite_opts = opts,
       modified = false,
-      created = not noconn and created() or nil,
+      created = not noconn and os.date "%Y-%m-%d %H:%M:%S" or nil,
+      tbl_schemas = {},
     }, self)
   else
     if self.closed or self.closed == nil then
       self.conn = clib.connect(self.uri, self.sqlite_opts)
-      self.created = created()
+      self.created = os.date "%Y-%m-%d %H:%M:%S"
       self.closed = false
     end
     return self
@@ -261,51 +267,26 @@ end
 ---@usage `db:drop("todos")` drop table.
 ---@return boolean
 function DB:drop(tbl_name)
+  self.tbl_schemas[tbl_name] = nil
   return self:eval(P.drop(tbl_name))
 end
 
 ---Get {name} table schema, if table does not exist then return an empty table.
 ---@param tbl_name string: the table name.
----@param info boolean: whether to return table info. default false.
 ---@return table list of keys or keys and their type.
-function DB:schema(tbl_name, info)
-  local sch = self:eval(string.format("pragma table_info(%s)", tbl_name))
-  if type(sch) == "boolean" then
-    return {}
-  end
-
-  local tbl_info = {}
-  local req = {}
-  local def = {}
-  local types = {}
-
-  for _, v in ipairs(sch) do
-    if v.notnull == 1 and v.pk == 0 then
-      req[v.name] = v
-    end
-    if v.dflt_value then
-      def[v.name] = v.dflt_value
-    end
-
-    tbl_info[v.name] = {
+function DB:schema(tbl_name)
+  local sch = self:eval(("pragma table_info(%s)"):format(tbl_name))
+  local schema = {}
+  for _, v in ipairs(type(sch) == "boolean" and {} or sch) do
+    schema[v.name] = {
+      cid = v.cid,
       required = v.notnull == 1,
       primary = v.pk == 1,
       type = v.type,
-      cid = v.cid,
       default = v.dflt_value,
     }
-
-    types[v.name] = v.type
   end
-
-  local obj = {
-    info = tbl_info,
-    req = req == {} and nil or req,
-    def = def == {} and nil or def,
-    types = types,
-  }
-
-  return info and obj or obj.types
+  return schema
 end
 
 ---Insert to lua table into sqlite database table.
@@ -317,11 +298,11 @@ end
 ---@usage `db:insert("todos", { title = "new todo" })` single item.
 ---@usage `db:insert("items", {  { name = "a"}, { name = "b" }, { name = "c" } })` insert multiple items.
 ---@todo handle inconflict case
-function DB:insert(tbl_name, rows)
+function DB:insert(tbl_name, rows, schema)
   a.is_sqltbl(self, tbl_name, "insert")
   local ret_vals = {}
-  local info = self:schema(tbl_name, true)
-  local items = P.pre_insert(rows, info)
+  schema = schema and schema or get_schema(tbl_name, self)
+  local items = P.pre_insert(rows, schema)
   local last_rowid
   clib.wrap_stmts(self.conn, function()
     for _, v in ipairs(items) do
@@ -351,36 +332,36 @@ end
 ---@usage `db:update("todos", { where = { id = "1" }, values = { action = "DONE" }})` update id 1 with the given keys
 ---@usage `db:update("todos", {{ where = { id = "1" }, values = { action = "DONE" }}, {...}, {...}})` multi updates.
 ---@usage `db:update("todos", { where = { project = "sql.nvim" }, values = { status = "later" } )` update multiple rows
-function DB:update(tbl_name, specs)
+function DB:update(tbl_name, specs, schema)
   a.is_sqltbl(self, tbl_name, "update")
-
-  local ret_vals = {}
   if not specs then
     return false
   end
-  local info = self:schema(tbl_name, true)
-  specs = u.is_nested(specs) and specs or { specs }
 
-  clib.wrap_stmts(self.conn, function()
+  return clib.wrap_stmts(self.conn, function()
+    specs = u.is_nested(specs) and specs or { specs }
+    schema = schema and schema or get_schema(tbl_name, self)
+
+    local ret_val = nil
     for _, v in ipairs(specs) do
       v.set = v.set and v.set or v.values
       if self:select(tbl_name, { where = v.where })[1] then
         local s = stmt:parse(self.conn, P.update(tbl_name, { set = v.set, where = v.where }))
-        s:bind(P.pre_insert(v.set, info)[1])
+        s:bind(P.pre_insert(v.set, schema)[1])
         s:step()
         s:reset()
         s:bind_clear()
         s:finalize()
         a.should_modify(self:status())
+        ret_val = true
       else
-        local res = self:insert(tbl_name, u.tbl_extend("keep", v.set, v.where))
-        table.insert(ret_vals, res)
+        ret_val = self:insert(tbl_name, u.tbl_extend("keep", v.set, v.where))
+        a.should_modify(self:status())
       end
     end
+    self.modified = true
+    return ret_val
   end)
-
-  self.modified = true
-  return true
 end
 
 ---Delete a {tbl_name} row/rows based on the {specs} given. if no spec was given,
@@ -424,17 +405,13 @@ end
 ---@usage `db:select("todos", { where = { status = {"later", "paused"} })` get row with status value of later or paused
 ---@usage `db:select("todos", { limit = 5 })` get 5 items from todos table
 ---@return table[]
-function DB:select(tbl_name, spec)
+function DB:select(tbl_name, spec, schema)
   a.is_sqltbl(self, tbl_name, "select")
-  spec = spec or {}
-  self.modified = false
-
-  local ret = {}
-  local types = self:schema(tbl_name)
-
-  self.modified = false
+  return clib.wrap_stmts(self.conn, function()
+    local ret = {}
+    schema = schema and schema or get_schema(tbl_name, self)
 
-  clib.wrap_stmts(self.conn, function()
+    spec = spec or {}
     spec.select = spec.keys and spec.keys or spec.select
 
     local s = stmt:parse(self.conn, P.select(tbl_name, spec))
@@ -442,10 +419,11 @@ function DB:select(tbl_name, spec)
       table.insert(ret, stmt.kv(s))
     end)
     stmt.reset(s)
-    stmt.finalize(s)
+    if stmt.finalize(s) then
+      self.modified = false
+    end
+    return P.post_select(ret, schema)
   end)
-
-  return P.post_select(ret, types)
 end
 
 ---Create new sql-table object.
diff --git a/lua/sql/assert.lua b/lua/sql/assert.lua
index 70db6dc0..18d2c8d5 100644
--- a/lua/sql/assert.lua
+++ b/lua/sql/assert.lua
@@ -1,4 +1,5 @@
 local M = {}
+local u = require "sql.utils"
 local clib = require "sql.defs"
 
 --- Functions for asseting and erroring out :D
@@ -10,6 +11,7 @@ local errors = {
   failed_ops = "operation failed, ERRMSG: %s",
   missing_req_key = "(insert) missing a required key: %s",
   missing_db_object = "'%s' db object is not set. please set it with `tbl.set_db(db)` and try again.",
+  outdated_schema = "`%s` does not exists in {`%s`}, schema is outdateset `self.db.tbl_schemas[table_name]` or reload",
 }
 
 for key, value in pairs(errors) do
@@ -60,6 +62,12 @@ M.missing_req_key = function(val, key)
   return false
 end
 
+M.should_have_column_def = function(column_def, k, schema)
+  if not column_def then
+    error(errors.outdated_schema:format(k, u.join(u.keys(schema), ", ")))
+  end
+end
+
 M.should_have_db_object = function(db, name)
   assert(db ~= nil, errors.missing_db_object:format(name))
   return true
diff --git a/lua/sql/defs.lua b/lua/sql/defs.lua
index 5b155645..10fd3f92 100644
--- a/lua/sql/defs.lua
+++ b/lua/sql/defs.lua
@@ -627,9 +627,9 @@ end
 ---@param fn func()
 M.wrap_stmts = function(conn_ptr, fn)
   M.exec_stmt(conn_ptr, "BEGIN")
-  fn()
+  local res = fn()
   M.exec_stmt(conn_ptr, "COMMIT")
-  return
+  return res
 end
 
 ---Get last error msg
diff --git a/lua/sql/parser.lua b/lua/sql/parser.lua
index 965512a9..84d560f9 100644
--- a/lua/sql/parser.lua
+++ b/lua/sql/parser.lua
@@ -19,10 +19,10 @@ M.sqlvalue = function(v)
   return type(v) == "boolean" and (v == true and 1 or 0) or (v == nil and "null" or v)
 end
 
-M.luavalue = function(v, schema_type)
-  if schema_type == "luatable" or schema_type == "json" then
+M.luavalue = function(v, key_type)
+  if key_type == "luatable" or key_type == "json" then
     return json.decode(v)
-  elseif schema_type == "boolean" then
+  elseif key_type == "boolean" then
     return v == 0 and false or true
   end
 
@@ -335,8 +335,10 @@ local opts_to_str = function(tbl)
     unique = function()
       return "unique"
     end,
-    nullable = function()
-      return "not null"
+    required = function(v)
+      if v then
+        return "not null"
+      end
     end,
     default = function(v)
       return "default " .. v
@@ -352,30 +354,32 @@ local opts_to_str = function(tbl)
     end,
   }
 
+  f.primary = f.pk
+
+  local res = {}
+
+  if type(tbl[1]) == "string" then
+    res[1] = tbl[1]
+  end
+
   local check = function(type)
-    if tbl[type] then
-      tbl[#tbl + 1] = f[type](tbl[type])
-      tbl[type] = nil
+    local v = tbl[type]
+    if v then
+      res[#res + 1] = f[type](v)
     end
   end
 
   check "type"
   check "unique"
-  check "nullable"
+  check "required"
   check "pk"
+  check "primary"
   check "default"
   check "reference"
   check "on_update"
-  check "on_detach"
-
-  for k, v in pairs(tbl) do
-    if type(k) ~= "number" then
-      tbl[k] = nil
-      tbl[#tbl + 1] = f[k](v)
-    end
-  end
+  check "on_delete"
 
-  return tconcat(tbl, " ")
+  return tconcat(res, " ")
 end
 
 ---Parse table create statement
@@ -389,16 +393,21 @@ M.create = function(tbl, defs)
   local items = {}
 
   tbl = defs.ensure and "if not exists " .. tbl or tbl
-  defs.ensure = nil
 
   for k, v in u.opairs(defs) do
-    local t = type(v)
-    if t == "boolean" then
-      tinsert(items, k .. " integer not null primary key")
-    elseif t ~= "table" then
-      tinsert(items, string.format("%s %s", k, v))
-    else
-      tinsert(items, ("%s %s"):format(k, opts_to_str(v)))
+    if k ~= "ensure" then
+      local t = type(v)
+      if t == "boolean" then
+        tinsert(items, k .. " integer not null primary key")
+      elseif t ~= "table" then
+        tinsert(items, string.format("%s %s", k, v))
+      else
+        if u.is_list(v) then
+          tinsert(items, ("%s %s"):format(k, tconcat(v, " ")))
+        else
+          tinsert(items, ("%s %s"):format(k, opts_to_str(v)))
+        end
+      end
     end
   end
   return ("create table %s(%s)"):format(tbl, tconcat(items, ", "))
@@ -421,11 +430,11 @@ M.pre_insert = function(rows, schema)
   local res = {}
   rows = u.is_nested(rows) and rows or { rows }
   for i, row in ipairs(rows) do
-    u.foreach(schema.req, function(k)
-      a.missing_req_key(row[k], k)
-    end)
     res[i] = u.map(row, function(v, k)
-      local is_json = schema.types[k] == "luatable" or schema.types[k] == "json"
+      local column_def = schema[k]
+      a.should_have_column_def(column_def, k, schema)
+      a.missing_req_key(v, column_def)
+      local is_json = column_def.type == "luatable" or column_def.type == "json"
       return is_json and json.encode(v) or M.sqlvalue(v)
     end)
   end
@@ -438,13 +447,18 @@ end
 ---@param schema table tbl schema
 ---@return table pre processed rows
 ---@TODO support boolean values.
-M.post_select = function(rows, types)
+M.post_select = function(rows, schema)
   local is_nested = u.is_nested(rows)
   rows = is_nested and rows or { rows }
 
   for _, row in ipairs(rows) do
     for k, v in pairs(row) do
-      row[k] = M.luavalue(v, types[k])
+      local info = schema[k]
+      if info then
+        row[k] = M.luavalue(v, info.type)
+      else
+        row[k] = v
+      end
     end
   end
 
diff --git a/lua/sql/table.lua b/lua/sql/table.lua
index a8bcf506..50b55a89 100644
--- a/lua/sql/table.lua
+++ b/lua/sql/table.lua
@@ -22,11 +22,15 @@ local run = function(func, o)
       o.has_content = o.tbl_exists and o.db:eval(countsmt)[1]["count(*)"] ~= 0 or 0
     end
 
-    if o.tbl_schema and next(o.tbl_schema) ~= nil and o.tbl_exists == false then
+    if o.tbl_exists == false and o.tbl_schema and next(o.tbl_schema) ~= nil then
       o.tbl_schema.ensure = u.if_nil(o.tbl_schema.ensure, true)
       o.db:create(o.name, o.tbl_schema)
     end
 
+    if type(o.db_schema) ~= "table" then
+      o.db_schema = o.db:schema(o.name)
+    end
+
     return func()
   end
 
@@ -173,7 +177,7 @@ function tbl:get(query)
   query = query or { query = { all = 1 } }
 
   return run(function()
-    local res = self.db:select(self.name, query)
+    local res = self.db:select(self.name, query, self.db_schema)
     return res
   end, self)
 end
@@ -201,7 +205,7 @@ function tbl:each(func, query)
   end
 
   return run(function()
-    local rows = self.db:select(self.name, query)
+    local rows = self.db:select(self.name, query, self.db_schema)
     if not rows then
       return false
     end
@@ -228,7 +232,7 @@ function tbl:map(func, query)
 
   return run(function()
     local res = {}
-    local rows = self.db:select(self.name, query)
+    local rows = self.db:select(self.name, query, self.db_schema)
     if not rows then
       return {}
     end
@@ -254,8 +258,9 @@ end
 ---@usage `local res = t1:sort({ where = {id = {32,12,35}}}, "age")` return rows sort by age
 ---@usage `local res = t1:sort({where = { ... }}, "age", function(a, b) return a > b end)` with custom function
 function tbl:sort(query, transform, comp)
+  query = query or { query = { all = 1 } }
   return run(function()
-    local res = self:get(query)
+    local res = self.db:select(self.name, query, self.db_schema)
     local f = transform or function(r)
       return r[u.keys(query.where)[1]]
     end
@@ -282,7 +287,7 @@ end
 ---@return integer: last inserted id
 function tbl:insert(rows)
   return run(function()
-    local succ, last_rowid = self.db:insert(self.name, rows)
+    local succ, last_rowid = self.db:insert(self.name, rows, self.db_schema)
     if succ then
       self.has_content = self:count() ~= 0 or false
     end
@@ -309,7 +314,7 @@ end
 ---@return boolean
 function tbl:update(specs)
   return run(function()
-    local succ = self.db:update(self.name, specs)
+    local succ = self.db:update(self.name, specs, self.db_schema)
     return succ
   end, self)
 end
@@ -322,7 +327,7 @@ end
 function tbl:replace(rows)
   return run(function()
     self.db:delete(self.name)
-    local succ = self.db:insert(self.name, rows)
+    local succ = self.db:insert(self.name, rows, self.db_schema)
     return succ
   end, self)
 end


Test patch:
diff --git a/test/auto/parser_spec.lua b/test/auto/parser_spec.lua
index ea3e03a1..2cc661b8 100644
--- a/test/auto/parser_spec.lua
+++ b/test/auto/parser_spec.lua
@@ -149,7 +149,7 @@ describe("parse", function()
     it("key-pair: nullable & unique", function()
       local defs = {
         id = true,
-        name = { "text", nullable = false, unique = true },
+        name = { "text", required = true, unique = true },
       }
       local passed = p.create("people", defs)
       local expected = "create table people(id integer not null primary key, name text unique not null)"
diff --git a/test/auto/sql_spec.lua b/test/auto/sql_spec.lua
index 19424e2b..e2e90d69 100644
--- a/test/auto/sql_spec.lua
+++ b/test/auto/sql_spec.lua
@@ -3,10 +3,11 @@ local curl = require "plenary.curl"
 local eq = assert.are.same
 local sql = require "sql"
 local u = require "sql.utils"
+local luv = require "luv"
 
 describe("sql", function()
   local path = "/tmp/db.sqlite3"
-  vim.loop.fs_unlink(path)
+  luv.fs_unlink(path)
 
   describe("sqlfunctions:", function()
     local s = sql.lib
@@ -53,7 +54,7 @@ describe("sql", function()
       eq(true, db:close(), "should close")
       eq(true, db:isclose(), "should close")
       eq(true, P.exists(P.new(tmp)), "It should created the file")
-      vim.loop.fs_unlink(tmp)
+      luv.fs_unlink(tmp)
     end)
     it("should accept pargma options", function()
       local tmp = "/tmp/db5.db"
@@ -63,7 +64,7 @@ describe("sql", function()
       db:open()
       eq("persist", db:eval("pragma journal_mode")[1].journal_mode)
       db:close()
-      vim.loop.fs_unlink(tmp)
+      luv.fs_unlink(tmp)
     end)
   end)
 
@@ -99,7 +100,7 @@ describe("sql", function()
 
       eq(true, db:close(), "It should close connection successfully.")
       eq(true, P.exists(P.new(path)), "File should still exists")
-      vim.loop.fs_unlink(path)
+      luv.fs_unlink(path)
     end)
 
     it("returns data and time of creation", function()
@@ -115,7 +116,7 @@ describe("sql", function()
       })
       eq("persist", db:eval("pragma journal_mode")[1].journal_mode)
       db:close()
-      vim.loop.fs_unlink(tmp)
+      luv.fs_unlink(tmp)
     end)
 
     it("reopen db object.", function()
@@ -132,7 +133,7 @@ describe("sql", function()
 
       eq(path, db.uri, "uri should be identical to db.uri")
       local res = db:eval "select * from todo"
-      eq(row, res[1], vim.loop.fs_unlink(path), "local row should equal db:eval result.")
+      eq(row, res[1], luv.fs_unlink(path), "local row should equal db:eval result.")
     end)
   end)
 
@@ -151,7 +152,7 @@ describe("sql", function()
       eq(true, db.closed, "should be closed.")
       eq("1", res[1].title, "should pass.")
 
-      vim.loop.fs_unlink(path)
+      luv.fs_unlink(path)
     end)
 
     it("works without initalizing sql objects. (via uri)", function()
@@ -373,6 +374,7 @@ describe("sql", function()
     it("serialize lua table in sql column", function()
       db:eval "drop table test"
       db:eval "create table test(id integer, data luatable)"
+      db.tbl_schemas.test = nil
       db:insert("test", { id = 1, data = { "list", "of", "lines" } })
 
       local res = db:eval [[select * from test]]
@@ -548,7 +550,7 @@ describe("sql", function()
     local posts, users
 
     it(".... pre", function()
-      if vim.loop.fs_stat "/tmp/posts" == nil then
+      if luv.fs_stat "/tmp/posts" == nil then
         curl.get("https://jsonplaceholder.typicode.com/posts", { output = "/tmp/posts" })
         curl.get("https://jsonplaceholder.typicode.com/users", { output = "/tmp/users" })
       end
@@ -680,11 +682,37 @@ describe("sql", function()
 
     it("gets a sql table schema", function()
       local sch = db:schema "test"
-      eq({ a = "text", b = "int", c = "int", d = "text" }, sch)
+      eq({
+        a = {
+          cid = 0,
+          primary = false,
+          required = false,
+          type = "text",
+        },
+        b = {
+          cid = 1,
+          primary = false,
+          required = false,
+          type = "int",
+        },
+        c = {
+          cid = 2,
+          primary = false,
+          required = true,
+          type = "int",
+        },
+        d = {
+          cid = 3,
+          default = "def",
+          primary = false,
+          required = false,
+          type = "text",
+        },
+      }, sch)
     end)
 
     it("gets a sql table schema info", function()
-      local sch = db:schema("test", true).info
+      local sch = db:schema "test"
       eq({
         a = {
           cid = 0,
@@ -732,10 +760,10 @@ describe("sql", function()
       eq(true, db:exists "test")
     end)
 
-    it("won't override the table schema if it exists", function()
+    it("skip overriding the table schema if it exists", function()
       db:create("test", { id = "not_a_type", ensure = true })
       local sch = db:schema "test"
-      eq("text", sch.title, "should not be nil")
+      eq("text", sch.title.type, "should exists and should be still text not be nil")
     end)
     it("auto enable foreign_keys on usage", function()
       db:create("test_keys", {
@@ -885,7 +913,7 @@ describe("sql", function()
       eq(3, db.st.count(), "should have inserted.")
     end)
 
-    vim.loop.fs_unlink(testrui)
-    vim.loop.fs_unlink(testrui2)
+    luv.fs_unlink(testrui)
+    luv.fs_unlink(testrui2)
   end)
 end)
diff --git a/test/auto/table_spec.lua b/test/auto/table_spec.lua
index cae0d633..2a79628c 100644
--- a/test/auto/table_spec.lua
+++ b/test/auto/table_spec.lua
@@ -1,5 +1,6 @@
 local sql = require "sql"
 local tbl = require "sql.table"
+local luv = require "luv"
 local eq = assert.are.same
 local demo = {
   { a = 1, b = "lsf", c = "de" },
@@ -12,7 +13,7 @@ local demo = {
 }
 
 local dbpath = "/tmp/tbl_methods_test.sql"
-vim.loop.fs_unlink(dbpath)
+luv.fs_unlink(dbpath)
 local db = sql.new(dbpath)
 
 local seed = function()
@@ -24,7 +25,7 @@ end
 
 local clean = function()
   db:close()
-  vim.loop.fs_unlink(dbpath)
+  luv.fs_unlink(dbpath)
 end
 
 describe("table", function()
@@ -53,38 +54,108 @@ describe("table", function()
         name = "text",
       },
     }
+    local detailed_schema = {
+      id = {
+        cid = 0,
+        primary = false,
+        required = false,
+        type = "int",
+      },
+      name = {
+        cid = 1,
+        primary = false,
+        required = false,
+        type = "text",
+      },
+    }
     local new = db:table("newtbl", opts)
 
     it("initalizes db with schema", function()
-      eq(opts.schema, new:schema(), "should be identical")
-      eq(opts.schema, new.tbl_schema, "should be identical")
+      eq(detailed_schema, new:schema(), "should be identical")
     end)
     it("should not rewrite schema.", function()
       local new2 = db:table "newtbl"
-      eq(opts.schema, new2:schema(), "should be identical")
-      eq(opts.schema, new2.tbl_schema, "should be identical")
+      eq(detailed_schema, new2:schema(), "should be identical")
+      eq(detailed_schema, new2.tbl_schema, "should be identical")
 
       local new3 = db:table("newtbl", { schema = { id = "string" } })
-      eq(opts.schema, new3:schema(), "should be identical")
-      eq(opts.schema, new3.tbl_schema, "should be identical")
+      eq(detailed_schema, new3:schema(), "should be identical")
+      eq(detailed_schema, new3.tbl_schema, "should be identical")
     end)
   end)
 
   describe(":schema", function()
     it("returns schema if self.tbl exists", function()
-      eq({ a = "integer", b = "text", c = "text" }, t1:schema())
+      eq({
+        a = {
+          cid = 0,
+          primary = false,
+          required = false,
+          type = "integer",
+        },
+        b = {
+          cid = 1,
+          primary = false,
+          required = false,
+          type = "text",
+        },
+        c = {
+          cid = 2,
+          primary = false,
+          required = false,
+          type = "text",
+        },
+      }, t1:schema())
     end)
     it("returns empty table if schema doesn't exists", function()
       eq({}, t2:schema())
     end)
     it("creates new table with schema", function()
-      local schema = { id = "int", a = "text", d = "text" }
+      local schema = {
+        a = {
+          cid = 0,
+          primary = false,
+          required = false,
+          type = "text",
+        },
+        d = {
+          cid = 1,
+          primary = false,
+          required = false,
+          type = "text",
+        },
+        id = {
+          cid = 2,
+          primary = false,
+          required = false,
+          type = "int",
+        },
+      }
       eq(true, t2:schema(schema), "Should return true")
       eq(schema, t2:schema(), "should return the schema.")
       eq(true, t2.tbl_exists, "should alter self.exists value")
     end)
     it("should drop and recreate the table if not schema.ensure", function()
-      local new = { id = "int", a = "text", f = "text" }
+      local new = {
+        a = {
+          cid = 0,
+          primary = false,
+          required = false,
+          type = "text",
+        },
+        f = {
+          cid = 1,
+          primary = false,
+          required = false,
+          type = "text",
+        },
+        id = {
+          cid = 2,
+          primary = false,
+          required = false,
+          type = "int",
+        },
+      }
       eq(true, t2:schema(new), "Should return true")
       eq(new, t2:schema(), "should return the schema.")
       eq(true, t2.tbl_exists, "should alter self.exists value")
@@ -570,12 +641,12 @@ describe("table", function()
           }
         end
       end
-      -- eq({}, getmetatable(some))
+
       eq("function", type(some.insert_or_update), "should be registered as function")
       some.insert_or_update "ff"
-      -- eq("ff", some.get({ name = "ff" }).name)
+      eq("ff", some.where({ name = "ff" }).name)
       some.insert_or_update "ff"
-      -- eq(2, some.where({ name = "ff" }).count)
+      eq(2, some.where({ name = "ff" }).count)
     end)
   end)
 end)