SQLite with Apache using Lua

13 Jun, 2020

It is possible to integrate SQLite with Apache using Lua.

Update your /etc/apache2/conf-available/demo.conf file to add some DBD configuration and a lua handler in the secure virtualhost:

DBDriver sqlite3
DBDParams /var/www/db/hello.sqlite
DBDPrepareSQL "SELECT 'james'" james


<Files "*.lua">
  SetHandler lua-script
  Options +Includes
  AddType text/html .lua
  AddOutputFilter INCLUDES .lua
</Files>

Now make sure the SQLite driver is installed (it should be already):

sudo apt-get install -y libaprutil1-dbd-sqlite3

Create the directory for the database:

sudo mkdir -p /var/www/db
sudo chown www-data:www-data /var/www/db

Create a handler :

cat << EOF | sudo tee /var/www/html/hello.lua > /dev/null
function handle(r)
    local database, err = r:dbacquire("mod_dbd")
    if not err then
        local statement, errmsg = database:prepared(r, "james")
        if not errmsg then
            local results, errmsg = statement:select()
            if not err then
                local rows = results(0) -- fetch all rows synchronously
                for k, row in pairs(rows) do
                    r:puts( string.format("Name: %s<br/>", row[1]))
                end
            else
                r:puts("Database query error: " .. err)
            end
        end
        database:close()
    else
        r:puts("Could not connect to the database: " .. err)
    end
end
EOF
sudo chmod a+x /var/www/html/hello.lua
sudo chmod w-r /var/www/html/hello.lua

Restart:

sudo a2enmod dbd lua
systemctl restart apache2

Now visit /hello.lua and you'll see the word james. What's happening here is that Lua is using the query named james that us defined by the DBDPrepareSQL config option in the demo.conf file. Apache is managing the database connections efficiently and running the query. In this case the SELECT 'james' statement just returns the word james which is what Lua receives and outputs.

Comments

Be the first to comment.

Add Comment

Copyright James Gardner 1996-2020 All Rights Reserved. Admin.