Using an external database to manage your data and more, Part 1

Hello everyone, today I’m going to be teaching you how to create, manage, maintain and control an external database to host your own game’s data, manipulate that data, and much more.

Now, in this tutorial I will cover different approaches and the approach that I personally use, obviously, each one is up to you.

Please note that I would label this as an intermediate level tutorial not meant for beginners and assumes you have an intermediate understanding of server management (Windows Server, Linux, etc). This is for people that want ultimate control over their data. Datastore is a good method for almost every case on Roblox, and is free, maintaining your own database will, generally, put that cost on you instead.

Yes I know there are other alternatives like Firestore, Datastore, Trello (please don’t use Trello for a database)

For the control software and management, I personally use PHP & Laravel Nova. I will show you how I take advantage of those frameworks for use on Roblox.

Step 1: Setting up the server with DigitalOcean

To start with I am going to use DigitalOcean, MySQL on Ubuntu 24.04 (a LEMP stack). I recommend using a Linux operating system as it’s a very popular choice for an option like this and using Windows Server is very expensive.

DigitalOcean is fairly inexpensive and easy to start with, as of writing this they include free credit coupons you can use to try out the service without having to pay. You can try another alternative like Heroku or doing this locally.

Once you create a droplet note your IP Address, you will need this for both connecting to it with HttpService on Roblox, and to work on the database itself

If you are using Mac you’ll need to open Terminal, if you’re on Windows open Command Prompt. Commands will be the same for both of them.

[insert terminal screnshot]

Alright now, let’s set up that database! First, let’s connect to the server.

* ssh root@your-ip-address

Obviously, replace your-ip-address with your server’s IP address. Make sure that you’re on the device where your SSH keys match the ones you used to create the droplet. If you decided to use a password instead of ssh then it should prompt you for the password to log in.

Step 2: Setting up the webserver -- with nginx.

In all of these examples, we will be using Nginx. A fast, efficient web server.

All software used in this procedure will be installed from Ubuntu’s package repositories.

Since we are just getting started, let’s go ahead and update everything.

* sudo apt update
* sudo apt install nginx

Now let’s have a check and make sure the webserver is operating as normal. Type your IP address for your droplet into your browser and it should take you to a page that looks like the one below.

http://your_ip_address

image

If you see the page above, you’ve successfully installed nginx.

Step 3: Setting up and configuring MySQL.

Now, let us move onto step 3! The reason we are here — installing MySQL Server.

Install MySQL by typing:

$ sudo apt install mysql-server

Now that we are installed, we need to finish configuring MySQL. We can start configuring by running the script:

$ sudo mysql_secure_installation

The script will ask if you would like to set up the validate password plugin, for the scope of this tutorial will assume that it is not enabled, however, this is up to your own discretion.

In MySQL versions above 5.7 will automatically enable the auth_socket plugin by default. You may have to switch it back to the MySQL native password. Run the following command:

$ sudo mysql

Now we will be running a command to swap the root user from auth socket to use the MySQL native password protocol.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Now, we are going to run FLUSH PRIVILEGES so we can update the grant tables and put your changes into effect.

mysql> FLUSH PRIVILEGES;

Alright enough of that now! Once you are done playing in the mysql terminal, run the exit command so we can get back to Linux.

mysql> exit

Alright, let’s just do a little check and make sure we can still authenticate with MySQL. Run the following command! $ mysql -u root -p and provide the password you setup above. If everything works out then you are finished with this portion!

Step 4: Installing PHP

For step 4, in this section, we will be using PHP. So let’s install PHP and configure nginx to process it.

Nginx does not come with a php processor so we will need to include the module php-fpm, which stands for FastCGI processing manager.

$ sudo apt install php-fpm php-mysql

Great! Assuming you got this far that means you have successfully installed the LEMP stack! Now all that leaves us to do is configure nginx to process our PHP requests.

This is going to be done with the server block (if you’ve used Apache, it’s similar to their virtual hosts). To do this we are going to need to create a new server block in the /etc/nginx/sites-available directory. In our tutorial it’s going to be named example.com, however, you can name it to whatever you’d like.

$ sudo nano /etc/nginx/sites-available/example.com

Creating a new block instead of overriding the default will allow you to revert to the default version should an issue happen.

Add the following content, which is just a modified version of the default server block.

server {
    listen 80;
    server_name server_domain_or_IP;
    root /var/www/sw_universe/public;

    add_header X-Frame-Options "SAMEORIGIN";
    add_header X-XSS-Protection "1; mode=block";
    add_header X-Content-Type-Options "nosniff";

    index index.html index.htm index.php;

    charset utf-8;

    location / {
        try_files $uri $uri/ /index.php?$query_string;
    }

    location = /favicon.ico { access_log off; log_not_found off; }
    location = /robots.txt  { access_log off; log_not_found off; }

    error_page 404 /index.php;

    location ~ \.php$ {
        fastcgi_pass unix:/var/run/php/php7.2-fpm.sock;
        fastcgi_index index.php;
        fastcgi_param SCRIPT_FILENAME $realpath_root$fastcgi_script_name;
        include fastcgi_params;
    }

    location ~ /\.(?!well-known).* {
        deny all;
    }

No we we need to save and close the file. After this we are going to be creating a symbolic link from sites-available to sites-enabled so that your block will be used instead of the default one.

$ sudo ln -s /etc/nginx/sites-available/example.com /etc/nginx/sites-enabled/

Now we will need to unlink the default server block, if any issues come up you can just run the command above but switch example.com with default.

$ sudo unlink /etc/nginx/sites-enabled/default

Aight, so we are going to check your configuration and make sure there are no syntax errors. If you encounter any errors make sure you did everything above correctly and try again.

$ sudo nginx -t
(please note you may get an error of a directory not existing, you may need to come back to this step once you finish the tutorial and everything should be setup for it.)

When you are ready then it’s time to refresh nginx so all our changes can be live.

$ sudo systemctl reload nginx

Awesome! Everything is configured.

Step 5: Preparing the database for use

Now we need to get our database up and running, for this I am going to be using MySQL Workbench but you are free to use the command line or any other program for MySQL data manipulation.

In this example, I will be using the database we currently employ at The Jedi Order’s Ilum which already has a lot of information stored. In MySQL Workbench you can create tables to keep your info. Think of each table as a DataStore container, you could have one for users, inventory, etc.

(If you don’t understand the program and need more help with creating tables, there is this helpful video made by TechGeekiArti which explains it for beginners)

Step 6: Setting up our framework: Laravel.

Okay great! So we got our database setup, we got our LEMP stack made, what’s next? Our visual interface, the main conduit and access point for our data in an easy to understand style. To accomplish this, I am going to be using Laravel Nova (paid) and Laravel Backpack (free for non-commercial) for this demonstration. First, we are going to need to install Laravel on our server.

To start off, we need to install Composer and some dependencies.

$ sudo apt install curl php-cli php-mbstring git unzip
$ cd ~
$ curl -sS https://getcomposer.org/installer -o composer-setup.php

After those commands are run, we will need to run the install script! Running the install script will download and install composer as a system command named composer in /usr/local/bin

$ sudo php composer-setup.php --install-dir=/usr/local/bin --filename=composer

If that runs without a problem, you should see the following output (or something similar):

All settings correct for using Composer
Downloading...

Composer (version 2.0.8) successfully installed to: /usr/local/bin/composer
Use it: php /usr/local/bin/composer

To test our installation, just run the command composer. If the command runs and displays a bunch of information and command-line arguments, then we are good to go!

Now for our PHP framework, Laravel. You will need to install a couple more dependencies for Laravel to run.

$ sudo apt install php-mbstring php-xml php-bcmath

For the purposes of this tutorial, we will be using the project name sw_universe, you can change this to whatever you want.

First, lets go to your user home page.

$ cd ~

Then, run this command. This command will create a laravel project into your user folder with the project name supplied.

$ composer create-project --prefer-dist laravel/laravel sw_universe

After running the command you should see an output similar to this.

Installing laravel/laravel (v8.5.9)
  - Installing laravel/laravel (v8.5.9): Downloading (100%)         
Created project in sw_universe
> @php -r "file_exists('.env') || copy('.env.example', '.env');"
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 80 installs, 0 updates, 0 removals
  - Installing symfony/polyfill-ctype (v1.16.0): Downloading (100%)         
  - Installing phpoption/phpoption (1.8.0): Downloading (100%)         
  - Installing vlucas/phpdotenv (v3.6.0): Downloading (100%)         
  - Installing symfony/css-selector (v4.3.2): Downloading (100%)     
...

Great! Laravel is now installed and your project is made – what’s next? Configuration! Running the create-project command will make you a .env file, or an environment file. This is what you will be using to configure the majority of your settings including database credentials, etc.

$ nano .env

There will be a lot of configuration options, most of which I won’t delve into except for the ones necessary in our application.

  • APP_NAME : Application name
  • APP_ENV : Current application environment.
  • APP_DEBUG : Whether or not to show debug information at client side.
  • APP_URL : Base URL for the application, used for generating links.
  • DB_DATABASE : Database name.
  • DB_USERNAME : Username to the database.
  • DB_PASSWORD : Password to the database.

Note that for the purpose of debugging and testing, you can leave APP_ENV set to development. HOWEVER! If you plan to use this for production, you will need to change it to production otherwise the contents of your environment file will be displayed publicly on the client-side for debug purposes. This would allow any actor who knew your domain name/IP Address to see the database login credentials and cause bad mojo. So let’s just change the environment before production.

APP_NAME=sw_universe
APP_ENV=development
APP_KEY=APPLICATION_UNIQUE_KEY_DONT_COPY_OR_EDIT
APP_DEBUG=true
APP_URL=http://domain_or_IP

LOG_CHANNEL=stack

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=swuniverse
DB_USERNAME=swuniverse
DB_PASSWORD=password

...

Adjust your variables as you see fit. When you are finished, close and save. Remember, if you are using nano, you can do that with CTRL+X , then Y and Enter to confirm.

Now we are going to move our project to a better spot.

$ sudo mv ~/sw_universe /var/www/sw_universe

(note if you had an issue with configuring nginx above, you may need to refresh it again)
$ sudo systemctl reload nginx

Everything up to this point works out? Then your website should look something like this.

If you encounter the error similar to: “The stream or file “/var/www/test/storage/logs/laravel.log” could not be opened in append mode: failed to open stream: Permission” denied then run these commands:

sudo chgrp -R www-data storage bootstrap/cache
sudo chmod -R ug+rwx storage bootstrap/cache

Now that Laravel is running, we will need to create some APIs so that our Roblox game server can contact and communicate with our database. For this tutorial I am going to be using Git and GitHub for version control and to help me edit files, this is optional but recommended. This tutorial assumes you know how to use the GitHub website and some git command lines. (If not, this doc created by GitHub will teach you how to get to this point)

First, let’s make sure we are in the correct directory!

$ cd /var/www/sw_universe

Let’s initialize git!

$ git init
$ git commit -m "first commit"
$ git branch -M main
$ git remote add origin https://github.com/your_username/your_repository.git
$ git push -u origin main

GitHub will prompt you to log in to upload the project. If everything worked right, your repository on GitHub should have your project in it. To edit it on your own machine you will need to clone the repository to your computer using git bash or GitHub desktop or by editing it directly from the website.

To start this, we are going to generate a model, controller, and resource.

$ php artisan make:model players -crm

You should see an output like this:

Model created successfully.
Created Migration: 2021_01_23_053629_create_players_table
Controller created successfully.

Now for the fun part. We are going to be editing our migration file! The migration file is how we create our database table and populate it. Another way you can do it is directly through MySQL Workbench, but using migrations is my preferred way of doing this.

I’m going to be using Visual Studio code to edit my files. Open up the project that you synced to GitHub and find your migration. It will be located in /database/migrations.

Using the example migration below will create a basic table for storing user information like visit count, username, user id, etc. You can use this information as a key for other information like player inventories and more.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreatePlayersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('players', function (Blueprint $table) {
            $table->id();
            $table->string('username');
            $table->timestamp('first_seen')->nullable();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('players');
    }
}

For way more in-depth options about different column types and whatnot, take a look at the Laravel documentation here.

Now save your migration and commit to GitHub. Once it’s on GitHub simply go to your server command line and run $ git pull so that way both branches are up to date with the changes. Once your project is up-to-date run $ php artisan migrate and your database is good to go with your changes!

Once that is done we will need to move over to our controller file, which will control what our API returns.

You can find your controller in /app/Http/Controllers/PlayerController.php in VSCode.

Using the example below is a rough version. In my example, I lock down the API from being executed from anything except the Roblox game server user-agent and an Authorization code. This authorization code is basically a password confirming that the request is coming from you and not a malicious actor.

<?php

namespace App\Http\Controllers;

use Illuminate\Foundation\Auth\Access\AuthorizesRequests;
use Illuminate\Foundation\Bus\DispatchesJobs;
use Illuminate\Foundation\Validation\ValidatesRequests;
use Illuminate\Routing\Controller as BaseController;
use Illuminate\Http\Request;
use App\Models\Player;

class PlayerController extends BaseController
{
    public function index()
    {
        return Player::all();
    }
 
    public function show(Request $request, $id)
    {
        if ($request->header('User-Agent') == "Roblox/Linux" && $request->header('Authorization') == "hellothere") {
            return Player::firstWhere('id', $id);
        } else {
            return response()->json([
                'message' => 'Access Denied',
           ], 403);
        }
    }

    public function store(Request $request)
    {

        if ($request->header('User-Agent') == "Roblox/Linux" && $request->header('Authorization') == "hellothere") {
                return Player::create($request->all());
        }else {
               return response()->json([
                'message' => 'Access Denied',
           ], 403);
        }
    }

    public function update(Request $request, $id)
    {
        if ($request->header('User-Agent') == "Roblox/Linux" && $request->header('Authorization') == "hellothere") {
                $player = Player::findOrFail($id);
                $player->update($request->all());
                return $player;
        }else {
               return response()->json([
                'message' => 'Access Denied',
           ], 403);
       }
    }
}

Great! Now that our controller is done then we just have to update the routes and, theoretically, we would be in business just like that!

Head over to /routes/api.php using your preferred code editor.

<?php

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Route;
use App\Http\Resources\PlayerResource as UserResource;

/*
|--------------------------------------------------------------------------
| API Routes
|--------------------------------------------------------------------------
|
| Here is where you can register API routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| is assigned the "api" middleware group. Enjoy building your API!
|
*/

 
Route::get('players', 'PlayerController@index');
Route::get('players/{id}', 'PlayerController@show');
Route::put('players/{id}', 'PlayerController@update');
Route::post('players', 'PlayerController@store');


Great! Now, let’s commit everything to GitHub and pull it back down to our server to make sure it’s all up to date.

Time to check to make sure everything is working! Head into Roblox Studio and make yourself a fresh new server script. In this script is a very basic interaction with the server. Just to get data and create new data. I would not use this in production, I suggest you make your own script for handling database interactions.

local HttpService = game:GetService("HttpService")

local function request(data)
	local response = HttpService:RequestAsync(data)

	-- Inspect the response table
	if response.Success then
		print("Status code:", response.StatusCode, response.StatusMessage)
		return response;
	else
		print("The request failed:", response.StatusCode, response.StatusMessage)
	end
end

game.Players.PlayerAdded:Connect(function(player)
	-- Check First Time
	local GetPlayerData = request({Url = "https://your_domain_or_ip_address/api/v1/player/"..player.userId, Method = "GET"})

	if GetPlayerData then
		if GetPlayerData.StatusCode == 200 and HttpService:JSONDecode(GetPlayerData.Body)["data"]["username"] then
			local Data = HttpService:JSONDecode(GetPlayerData.Body)


			if HttpService:JSONDecode(GetPlayerData.Body)["username"] then
				print(HttpService:JSONDecode(GetPlayerData.Body)["username"])
				print(GetPlayerData)
			end

			print(GetPlayerData.Body)
		end
	else
		warn("Creating a new account with the database.")
		print(request({
			Url = "https://your_domain_or_ip_address/api/players/",
			Method = "POST",
			Headers = {
				["Content-Type"] = "application/json"  -- When sending JSON, set this!
			},
			Body = HttpService:JSONEncode({id = player.userId, username = player.Name})
		}))
	end
end)

Put in your IP address or domain name and boom, run the game and you should receive an output similar to this!


(note: the extra data outputted is from my production server with more fields returned, yours will be whatever fields you add in your migration columns)

Okay, guys! This is the end of Part 1! Part 2 will show you how to setup software to manage your database and control your data. The sneak peek for Part 2 is below.

Part 2: Sneak Peek

Time for data management! What fun? We will be starting with Nova, It’s basically an admin panel that hooks up to your database and allows for all sorts of fun manipulation and control options! Note: Laravel Nova is not a free application. At the end of this section, I’ll be explaining how to use Backpack, another alternative.

Now, how do we install Nova? It’s pretty simple actually. Just pop open your composer.json file by running $ nano composer.json and amend the following:

"repositories": [
    {
        "type": "composer",
        "url": "https://nova.laravel.com"
    }
],

then we will need to add Nova to the composer requirements, like this:

"require": {
    "php": "^7.2.5",
    "fideloper/proxy": "^4.2",
    "laravel/framework": "^7.0",
    "laravel/nova": "~3.0"
},

now save your changes, you can do that with CTRL+X , then Y and Enter to confirm.

now we just need to update composer to accept our changes.

$ composer update

Nova creates a directory in /app/Nova which is basically like a nova-version of your Models.

An example to construct our database table is something like this

public function fields(Request $request)
    {
        return [
            Number::make('ID')->sortable()->rules('required', 'max:255'),

            Text::make('Username')
                ->sortable()
                ->rules('required', 'max:255'),

                Indicator::make('Status', function () {
                    $date = Carbon::parse($this->account_age);
                    $now = Carbon::now();
                    $diff = $date->diffInDays($now);

                    if ($diff < 14) {
                        return 'flagged';
                    } else {
                        return $diff;
                    }
                    
                })->labels([
                    'banned' => 'Banned',
                    'active' => 'Active',
                    'flagged' => 'Flagged',
                    'inactive' => 'Inactive',
                ])->colors([
                    'banned' => 'red',
                    'active' => 'green',
                    'flagged' => 'danger',
                    'inactive' => 'grey',
                ]),

    
            Boolean::make('Meditation'),
            Boolean::make('Levitation'),

            HasMany::make('Inventory'),

            HasMany::make('Crystal'),

            HasMany::make('AdminLog'),

            Text::make('Join Date', 'account_age')
                ->displayUsing(function($joinDate) {
                    if (strlen($joinDate) >=6 ) {
                        $today = Carbon::parse($joinDate); 
                        return $today->isoFormat('MMM Do, YYYY');
                    } else {
                        return null;
                    }
                })
            ->exceptOnForms()->sortable(),

            Text::make('Activity', 'updated_at')
                ->displayUsing(function($lastActive) {
                    return $lastActive->diffForHumans();
                })
            ->exceptOnForms()->sortable(),

            Text::make('First Seen', 'created_at')
                ->displayUsing(function($firstSeen) {
                    return $firstSeen->diffForHumans();
                })
            ->exceptOnForms()->sortable(),
   
        ];
    }

and that would show you something, like this! This example will not work for you without modifications. In the near future, when Part 2 comes out I plan on creating an open-source project on GitHub for everyone to follow along with.


Anything is possible with your imagination. This is the end of this tutorial, I plan on having Part 2 ready within the next week. What would you like to see me cover in the next Part? What do you think I could have done differently? Would you like to see a video series explaining and showing everything in-depth? Let me know below.

Part 2 will be amended to this post.

18 Likes

you can host mysql, or as in my case, mariadb, locally on your own hardware, making DigitalOcean technically an option to running this.

1 Like

Thanks for the turorial.

(Yes, it’s me.)

1 Like

If this is the case I will modify my post for local when I get home

1 Like

Great resource for people who want the ability to transfer and manage their data with ease. Probably won’t be using this because I don’t know how to use MySQL.

1 Like

Would it also be possible to incorporate this tutorial with other languages and frameworks? JavaScript and Node.js come to mind for creating an API with dedicated routing compared to PHP, which can be considered somewhat dated and not as lightweight as other web development languages.

1 Like

Awesome tutorial, but you should implement how to create a token,
because right now everybody could change data just by a post call if they have the adress.

How is the performance of the api? I dont like php that much because its slow when it gets to save thousands of data in seconds. So i thought about using express with mongodb

2 Likes

When’s part two lol… It’s been ages, and this topic seems so good… yet abandoned

1 Like

I thought I’d link some database services that I know of, there are infinite ways to setup a database so many ways to screw up long term setups. Learning about all the best types of databases available will help out quite a bit. Choose the option or depending on how large your vision is options that makes the most sense for you and your team.

The hierarchy for Roblox to Metaverse and beyond looks something like this.
Player/Gamer
Artist
Organizer
Developer
Manager
Entrepreneur
Data Engineer
There’s more but this will do for now.

Microsoft’s solution for any data related needs.

Amazon’s database platform capable of game, analytics, and blockchain tech.

Makes AWS much easier to scale and manage.

A lake house database option, mainly for use cases with AI and analytics. Integrates with Azure and AWS.

An application orientated option.

A decentralized node based storage option.

1 Like