# Example of how LuaLaTeX accesses PostgreSQL database under Ubuntu

Sometimes we need to insert data from the database into the document, which will be very troublesome without the help of the program. Fortunately, LuaLaTeX can call the Lua program, through which we can access the database. In fact, our documents can be connected to various computer systems through Lua program.

Although LuaLaTeX is not as good as other LaTeX Chinese characters, the powerful blessing of Lua program greatly expands the functions of LaTeX. For friends who can program, it is strongly recommended to use LuaLaTeX when using LaTeX.

The following is a list of methods and steps for LuaLaTeX to access PostgreSQL database under Ubuntu. It's actually very simple.

environment
- Ubuntu 20.04
- Lua 5.3
- PostgreSQL 12.8

### preparation in advance

##### 1. Install the PostgreSQL database

If the method is not clear, please see my other blog Simple installation and related settings for PostgreSQL Ubuntu 20.04 . I won't repeat it here.

##### 2. Create database and related tables, etc

In this example, the database test for testing is created, and the table roster is created in this database. Three pieces of data have been added to the roster table for testing.

##### 3. Install latex

To save trouble, install them all directly:

sudo apt install texlive-full


### Key components

After the previous preparations are completed, if we immediately use the lualatex command to compile the latex document containing the code for connecting to the PostgreSQL database, the following errors will occur:

As can be seen from the figure, there is a lack of luasql.so link library. Now let's install:

sudo apt install lua-sql-postgres


After installation, it cannot be used directly, because the name and location are not in the default search of lualatex, and need to be modified. First create the directory: / usr/local/lib/lua/5.3 /, and then execute the following command:

sudo cp /usr/lib/x86_64-linux-gnu/liblua5.3-sql-postgres.so.2 /usr/local/lib/lua/5.3/luasql.so


Note that liblua5.3-sql-postgres.so.2 may not be in the above directory. If not, please use the find command to find it yourself.

After doing the above work, execute the lualatex command to pass.

### Sample code

##### 1. test1.lua
--[[
@file test1.lua
@brief LuaLaTeX visit PostgreSQL Database sample file
@copyright MIT
@author Lu Wei
@version 1.0.0
@date 2021-10-16
--]]

-- CSDN，Walking among the stars, Lu Wei's blog
-- definition visitPG class
VisitPG = {
code = "",
}

function VisitPG:new(o, str)
o = o or {}
setmetatable(o, self)
self.__index = self
self.code = str
return o
end

-- @brief visit PostgreSQL database
function VisitPG:Visit()
local luasql = require 'luasql.postgres'
local envv = luasql.postgres()
local con = assert (envv:connect('test', 'starry', 'password123', "127.0.0.1", 5432))

local cur = assert(con:execute"SELECT * from roster")

local row = cur:fetch({}, "a")
local str1 = ""
while row do
str1 = str1 .. row.id .. "; " .. row.name .. "; " .. row.age .. "; " .. row.id .. "; " .. "\\\\"
row = cur:fetch(row, "a")
end

cur:close()
con:close()
envv:close()

return str1
end

--[[
local luasql = require "luasql.postgres"
local env = luasql.postgres()
local con = assert (env:connect('test', 'starry', 'belighT928s', "127.0.0.1", 5432))

local cur = assert (con:execute"SELECT * from roster")

local row = cur:fetch ({}, "a")
while row do
print(string.format("%20s  %-15d %-15d", row.name, row.age, row.id))
row = cur:fetch (row, "a")
end

cur:close()
con:close()
env:close()
]]--


The code is very simple. Don't explain it. Remember the final finishing work. The bottom comment in the code is for independent lua command testing.

##### 2. test1.tex
% CSDN，Walking among the stars, Lu Wei's blog
\documentclass{article}

\usepackage{ctex}% Chinese support
\usepackage{geometry}% For page setup
\usepackage{luacode}% lua support

% Set to A4 Paper, moderate margin mode (refer to Yongzhong) office)
\geometry{
width = 210mm,
height = 297mm,
left = 19.1mm,
right = 19.1mm,
top = 25.4mm,
bottom = 25.4mm
}

\begin{document}
\begin{center}
\huge LuaLaTeX visit PostgreSQL Database example

\luaexec{
require("test1")
local pg = VisitPG:new(nil, "")
tex.sprint(pg:Visit())
}
\end{center}
\end{document}


The pdf documents generated after compilation are as follows:

Although it can be implemented with tools such as lualocks, it is troublesome and is not recommended here.

After establishing a connection with the database, there are actually many things that can be done, which are not limited to simply reading data for display. The connection methods with other databases will not be introduced for the time being, but will be discussed when I use them.

Posted on Mon, 18 Oct 2021 15:50:13 -0400 by ChemicalBliss