Search

How to Connect to SQLite with Lua

Step by step from installation to database connection.


How to Connect to SQLite with Lua


Connecting to a database in particular SQLite is essential to develop local and even online applications.

We also have a complete guide to using Lua for beginners at this link .

In this article we will see how to connect to SQLite using Lua using a step by step from installation to database connection.

For this we will use Ubuntu 21.04 as a reference, but you can change package names, directory paths according to your distribution or operating system.


Installation

1. Required packages

First of all let’s install the necessary dependencies

sudo apt update
sudo apt install sqlite3 libsqlite3-dev lua5.1 lua5.4 luarocks

Note: Even if you have already installed it, I recommend reinstalling, for this the parameter: --reinstall

Some package names may be different in your distro, use search in your package manager for the corresponding names.

2. Check that both are working normally:

Check command and version.

lua -v
#Lua 5.1.2 Copyright (C) 1994-2020 Lua.org, PUC-Rio
luarocks --version
#/usr/bin/luarocks 2.4.2
#LuaRocks main command-line interface

Change the interpreter to 5.1 by modifying your interpreter’s symlink

sudo ln -sf /usr/bin/lua5.1 /etc/alternatives/lua-interpreter

If you are on another Ubuntu distribution or version, create a symlink directly, eg sudo ln -sf /usr/bin/lua5.1 /usr/bin/lua


3. Now let’s install LuaSQL with Luarocks

sudo luarocks install luasql-sqlite3

Check if it was installed normally by listing with luarocks

luarocks list

Similar output will be:

Installed rocks:
----------------

luasql-sqlite3
   2.6.0-1 (installed) - /usr/local/lib/luarocks/rocks

That is, listed the luasql-sqlite3 normally and the sqlite3.so file is in the path: /usr/local/lib/luarocks/rocks


4. Now let’s make it possible for package.path to find the SQLite package installed by Luarocks

To do this, create a symbolic link with the command below:

sudo ln -s /usr/local/lib/lua /usr/lib/lua

NOTE

If when you run this command: ls /usr/local/lib/lua if the output is not 5.1 you will have to change the symbolic link of the lua command to the version that appears as we did in step 3, if more than one appears there will be no problem, but if the version number of your lua command does not exist you will only be able to use the interpreter for the corresponding version of those available when listing this cited directory.


Testing

To test we are going to use a test-ready database that I created and to get it just download it with wget with the command below:

wget -q https://git.io/litelua.db

Now let’s create the database.lua file with the code Lua below and then we will explain some parts of it:

local driver = require('luasql.sqlite3')
local env = driver.sqlite3()
local db = env:connect('./litelua.db')

local results = db:execute[[
  SELECT * FROM example;
]]

local id,mail,url,lang,name = results:fetch()
while id do
  print(id..' | '..mail..' | '..url..' | '..lang ..' | '..name)
  id,mail,url,lang,name = results:fetch()
end

results:close()

db:close()
env:close()

The output should return data from the fields of the informed table.

  • In db:execute you must enter the SQLite command you want, it can be: CREATE, UPDATE, DELETE
  • The variables have almost the same names as the columns, but this is not a rule.

Useful links


lua sqlite


Share



Comments