Lua - Database Access

Hello, aspiring programmers! Today, we're going to embark on an exciting journey into the world of database access using Lua. As your friendly neighborhood computer science teacher, I'm here to guide you through this adventure step by step. Don't worry if you're new to programming – we'll start from the basics and work our way up. So, grab your virtual hardhats, and let's dig into some data!

MySQL db Setup

Before we can start playing with databases in Lua, we need to set up our playground. Think of it as preparing your kitchen before cooking a delicious meal. In our case, we'll be using MySQL as our database management system.

First, make sure you have MySQL installed on your computer. If not, head over to the MySQL website and download the appropriate version for your operating system.
Once installed, create a new database for our experiments. You can do this using the MySQL command line or a graphical tool like MySQL Workbench.

CREATE DATABASE lua_test;

Great! Now we have our sandbox ready for some Lua magic.

Importing MySQL

To use MySQL with Lua, we need a special tool called LuaSQL. It's like a translator that helps Lua talk to MySQL. Let's import it:

local luasql = require "luasql.mysql"

This line is like telling Lua, "Hey, we're going to need your MySQL skills for this task!"

Setting up Connection

Now that we've introduced Lua to MySQL, let's establish a connection. It's like dialing a phone number to reach our database:

local env = luasql.mysql()
local con = env:connect("lua_test", "username", "password", "localhost", 3306)

Here's what's happening:

We create an environment (env) for MySQL.
We use this environment to connect to our database, providing the database name, username, password, host, and port.

Execute Function

The execute function is our way of sending commands to the database. It's like a waiter taking our order at a restaurant:

function execute(con, sql)
    local cur = con:execute(sql)
    if cur then
        return cur:fetch()
    end
    return nil
end

This function takes our connection and an SQL command, executes it, and returns the result.

Create Table Example

Let's create our first table! Imagine we're building a simple library system:

local sql = [[
    CREATE TABLE books (
        id INT PRIMARY KEY AUTO_INCREMENT,
        title VARCHAR(100),
        author VARCHAR(50),
        year INT
    )
]]
execute(con, sql)

This creates a table named 'books' with columns for id, title, author, and year.

Insert Statement Example

Now, let's add some books to our library:

local sql = [[
    INSERT INTO books (title, author, year)
    VALUES ('The Lua Programming Language', 'Roberto Ierusalimschy', 2016)
]]
execute(con, sql)

This adds a book to our table. It's like filling out a library card for a new book.

Update Statement Example

Oops! We made a mistake. Let's update the year of our book:

local sql = [[
    UPDATE books
    SET year = 2017
    WHERE title = 'The Lua Programming Language'
]]
execute(con, sql)

This corrects the publication year of our book.

Delete Statement Example

Let's remove a book from our library:

local sql = [[
    DELETE FROM books
    WHERE title = 'The Lua Programming Language'
]]
execute(con, sql)

This removes the specified book from our table.

Select Statement Example

Now, let's see what books we have in our library:

local sql = "SELECT * FROM books"
local cur = con:execute(sql)
local row = cur:fetch({}, "a")
while row do
    print(string.format("%s by %s (%d)", row.title, row.author, row.year))
    row = cur:fetch(row, "a")
end

This retrieves all books and prints their details.

A Complete Example

Let's put it all together in a complete example:

local luasql = require "luasql.mysql"

local env = luasql.mysql()
local con = env:connect("lua_test", "username", "password", "localhost", 3306)

function execute(con, sql)
    local cur = con:execute(sql)
    if cur then
        return cur:fetch()
    end
    return nil
end

-- Create table
execute(con, [[
    CREATE TABLE IF NOT EXISTS books (
        id INT PRIMARY KEY AUTO_INCREMENT,
        title VARCHAR(100),
        author VARCHAR(50),
        year INT
    )
]])

-- Insert a book
execute(con, [[
    INSERT INTO books (title, author, year)
    VALUES ('The Lua Programming Language', 'Roberto Ierusalimschy', 2017)
]])

-- Select and print all books
local cur = con:execute("SELECT * FROM books")
local row = cur:fetch({}, "a")
while row do
    print(string.format("%s by %s (%d)", row.title, row.author, row.year))
    row = cur:fetch(row, "a")
end

con:close()
env:close()

This script creates a table, inserts a book, and then prints all books in the table.

Performing Transactions

Transactions are like protective bubbles around a series of database operations. They ensure that either all operations succeed, or none of them do. It's like an "all or nothing" approach.

Start Transaction

To start a transaction:

con:execute("START TRANSACTION")

Rollback Transaction

If something goes wrong, we can undo all changes:

con:execute("ROLLBACK")

Commit Transaction

If everything goes well, we can save all changes:

con:execute("COMMIT")

Importing SQLite

SQLite is another popular database system, especially for smaller applications. Let's see how to use it with Lua:

local luasql = require "luasql.sqlite3"

Setting Up Connection

Connecting to SQLite is a bit different from MySQL:

local env = luasql.sqlite3()
local con = env:connect("test.db")

This creates or opens a file named "test.db" as our database.

Execute Function

The execute function for SQLite is similar to MySQL:

function execute(con, sql)
    local cur = con:execute(sql)
    if cur then
        return cur:fetch()
    end
    return nil
end

Create Table Example

Let's create a simple table in SQLite:

local sql = [[
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        age INTEGER
    )
]]
execute(con, sql)

Insert Statement Example

Now, let's add a user:

local sql = [[
    INSERT INTO users (name, age)
    VALUES ('Alice', 30)
]]
execute(con, sql)

Select Statement Example

Let's retrieve our users:

local sql = "SELECT * FROM users"
local cur = con:execute(sql)
local row = cur:fetch({}, "a")
while row do
    print(string.format("%s is %d years old", row.name, row.age))
    row = cur:fetch(row, "a")
end

A Complete Example

Here's a complete example using SQLite:

local luasql = require "luasql.sqlite3"

local env = luasql.sqlite3()
local con = env:connect("test.db")

function execute(con, sql)
    local cur = con:execute(sql)
    if cur then
        return cur:fetch()
    end
    return nil
end

-- Create table
execute(con, [[
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        age INTEGER
    )
]])

-- Insert a user
execute(con, [[
    INSERT INTO users (name, age)
    VALUES ('Alice', 30)
]])

-- Select and print all users
local cur = con:execute("SELECT * FROM users")
local row = cur:fetch({}, "a")
while row do
    print(string.format("%s is %d years old", row.name, row.age))
    row = cur:fetch(row, "a")
end

con:close()
env:close()

This script creates a table, inserts a user, and then prints all users in the table.

And there you have it, folks! You've just taken your first steps into the world of database access with Lua. Remember, practice makes perfect, so don't be afraid to experiment with these examples. Who knows? You might just create the next big database-driven application! Happy coding!

Method	Description
`require "luasql.mysql"`	Imports MySQL module
`env:connect()`	Establishes database connection
`con:execute()`	Executes SQL statements
`cur:fetch()`	Retrieves result rows
`START TRANSACTION`	Begins a transaction
`ROLLBACK`	Undoes changes in a transaction
`COMMIT`	Saves changes in a transaction
`require "luasql.sqlite3"`	Imports SQLite module

Credits: Image by storyset

Previous Tutorial:

Lua - Web Programming

Next Tutorial:

Lua - Game Programing