Full SQLite3 server

This is similar to the SQLite3 database I made, however this allows you to execute any query (which allows you to create tables with several columns)
This does require SQL knowledge, however depending on your needs you may need only the basics. If you want to create tables when the server starts up, you can add entries into the startupQueries array.
Example: CREATE TABLE IF NOT EXISTS bannedusers (username VARCHAR(50) NOT NULL, userid INT NOT NULL, reason VARCHAR(300) NOT NULL)

Setting up the project on glitch and in roblox is very easy.
Full tutorial posted on the github page.
Please report any and all bugs to me. Thanks.

The module itself (located in roblox directory of github page) only has one method, not including the configuration, which is Query.

Return results for all function can be found here.

sql:Query(string Query)
This is method returns a statement object for the query you specify. It is highly recommended to use prepared statements otherwise you are vulnerable to sql injection if you handle any user input.
Example of prepared statement: SELECT * FROM table WHERE key=?
The parameter (the question mark) can be specified via Statement:Bind()

Statement object

Statement:Bind(… Parameters)
Bind parameters to the values passed to this function.
statement:Bind("One", "Two", "Three", "Four", "Five")
You can save the statement object if you’ll need to use it several times, and you can just rebind the parameters.

Statement:Get(… Parameters)
Internally, if any parameters are passed it will call Statement:Bind()
Receive values from a query. Values are stored in second return value as an array.
This can only be run on queries that return data.

Statement:Run(… Parameters)
Internally, if any parameters are passed it will call Statement:Bind()
Execute a query without receiving results.
This can only be run on queries that return no data.

I tried my best to make the apis as simple as possible, if you don’t understand something which feel free to ask me. This is for users more experienced with SQL.

15 Likes

I’ve released an update to allow several databases to be easily setup on different paths, along with a new module to make this easier. Everything will function the same on the server. Setting up is the same as befor, and multiple databases is 100% optional.

To use multiple databases, make sure you copy multiServer.js and server.js to your project folder. By default, server.js will be used. To enable multiServer.js you can change the useMultiServer variable to true.

Configuring multiServer.js
multiServer.js has three variables (PORT, default_ApiToken and default_StartupQueries) PORT will be the port your server will listen on. default_ApiToken ad default_StartupQueries will only be used if you do not specify them in each individual database setup.

Setting up multiple databases
If you scroll down, you’ll see some comments showing how to use the registerDatabase function (which is used to setup each database)
registerDatabase(string Path, string databaseFile[, optional string ApiToken, optional array StartupQueries]);

registerDatabase("databaseName", "./databaseFile.db", "ApiToken", [ 
   -- startupQueries
]);

Using multiServer.js from roblox
I have created a new module to use multiple databases in a single module.
To create a new database, require the module and use the following code

local sql = require(...);
local database1 = sql:CreateDatabase("databaseName", "ApiToken"); -- these should match the settings specified above
local database2 = sql:CreateDatabase("databaseName2", "ApiToken2"); -- these should match the settings specified above

After using CreateDatabase, it will function exactly like the other module. (Documentation above)

4 Likes