How to use ROBLOX Lua + HttpService with SQL?

I'm quite bored so I'll actually have a go at this...

Introduction

The objective of this tutorial is to create a simple interface between Roblox and a SQL database. The app will be hosted on Heroku as it's free. The server will be written in Node.js so a knowledge of JavaScript is recommended.

The process is going to be as follows:

1. Create server in node.js with an endpoint for sending SQL requests to a Postgres database and deploy it. In practice, it's not recommended to expose an endpoint that executes SQL queries because it's a huge security risk. If you are going to create a professional application, you should plan different endpoints for different methods.

2. Create the connection in ROBLOX

Creating the node.js server

First, you should have a Heroku account. Create a new app and add a Postgres database to it. Connect the app to a GitHub repository and clone the repository to begin coding the app.

Once the repository is cloned create a file called server.js in its root and a file called package.json.

The file structure should look like

root/
-package.json
-server.js
-.gitignore/
    -.gitignore

Now to configure the server, we modify package.json so npm knows what dependencies our app needs and how to start it. Editing package.json

{
  "name": "robloxsql",
  "description": "Roblox SQL server example",
  "main": "server.js",
  "scripts": {
    "start": "node server.js"
  },
  "dependencies": {
    "express": "4.x.x",
    "pg": "7.x.x",
    "body-parser": "1.18.x"
  }
}

Express will be used to minimize boilerplate code, pg to connect to the Postgres database. Now editing server.js.

//Import requiered packages
const express = require('express');
const {Client} = require('pg');

//Create the conection to the postgres server
const client = new Client({
    connectionString: process.env.DATABASE_URL
});

client.connect();

//Create the express app
const bodyParser = require('body-parser');
const app = express();

// parse application/json
app.use(bodyParser.json());

//Handle a post request at /query
app.post('/query', (req, res) => {
    res.setHeader('Content-Type', 'application/json');
    console.log("Receiving request");
    if(req.body.query) {
        console.log(req.body.query);
        client.query(req.body.query, (err, r) => {
            if (err) throw err;
            rows = [];
            for(let row of r.rows){
                rows.push(row);
            }
            response = JSON.stringify(rows);
            console.log(response);
            res.end(response);
        });
    }
});

const port = process.env.PORT || 8080;

//Start listening
const server = app.listen(port, function () {
   console.log("App listening at ${host}")
});

Then in Heroku deploy the app.

Create the ROBLOX connection

Then querying the server is quite simple. Enable HttpService in the game settings and then

http = game:GetService("HttpService")


function sendQuery(sqlquery)
	local json = http:JSONEncode(
	{
		query = sqlquery
	})
	return http:JSONDecode(http:PostAsync("https://yourapp.herokuapp.com/query", json))
end

Then you can manipulate your SQL database like so:

sendQuery([[CREATE TABLE rpgplayers (
    name varchar(255),
    level int,
    money int
);]])

sendQuery([[INSERT INTO rpgplayers
VALUES ('user214', 1, 100);]])

sendQuery([[INSERT INTO rpgplayers
VALUES ('lombardo2', 34, 100045);]])

result = sendQuery([[SELECT * FROM rpgplayers]])

for _,row in pairs(results) do
	for k,v in pairs(row) do
		print(k,v)
	end
end

money 100 level 1 name user214 money 100045 level 34 name lombardo2

Note that you have to be very careful when doing queries as adding duplicates is very easy. That's also another reason why it's not recommended to be able to execute queries through an API endpoint. You should add an endpoint that adds a user explicitly to the database after checking for duplicates and stuff. Anyway, this is quite barebones and is just to give you an idea of how it is done.