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 luarocksNote: 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 interfaceChange the interpreter to 5.1 by modifying your interpreter’s symlink
sudo ln -sf /usr/bin/lua5.1 /etc/alternatives/lua-interpreterIf 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-sqlite3Check if it was installed normally by listing with luarocks
luarocks listSimilar output will be:
Installed rocks:
----------------
luasql-sqlite3
2.6.0-1 (installed) - /usr/local/lib/luarocks/rocksThat is, listed the luasql-sqlite3 normally and the
sqlite3.sofile 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/luaNOTE
If when you run this command:
ls /usr/local/lib/luaif the output is not 5.1 you will have to change the symbolic link of theluacommand 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 yourluacommand 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.dbNow 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:executeyou 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
- http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki
- https://keplerproject.github.io/luasql/manual.html
