Start typing to search packages!
rsql
By @rustyspottedcatt
Roblox
Mirrored from WallyrSQL
SQL for Roblox DataStores, player profiles and in-memory tables.
[image: rSQL 2.0]
local rSQL = require(ReplicatedStorage.rSQL)
local db = rSQL.datastore(DataStoreService:GetDataStore("Game"))
db:exec([[
CREATE TABLE IF NOT EXISTS Players (
UserId INTEGER PRIMARY KEY,
Name TEXT NOT NULL,
Coins INTEGER NOT NULL DEFAULT 0
)
]])
db:exec("INSERT INTO Players (UserId, Name) VALUES (?, ?)", { player.UserId, player.Name })
db:exec("UPDATE Players SET Coins = Coins + ? WHERE UserId = ?", { 50, player.UserId })
local top = db:exec("SELECT Name, Coins FROM Players ORDER BY Coins DESC LIMIT 10").rows
2.0 is a rewrite. The 1.x code had a bug that wiped tables, stored every number as a string and kept each table in a single 4 MB DataStore key, so I started over with a real parser, typed columns and sharded storage. Old scripts and old saved data still work. See what changed.
Try it in Studio
You need Rokit and Roblox Studio.
rokit install
rojo build dev.project.json -o rSQL-Playground.rbxl
Open the place and press Play. Walk onto the yellow pad to earn coins; the board behind it runs SELECT ... ORDER BY Coins DESC LIMIT 5 every two seconds. With "Enable Studio Access to API Services" on, it uses a real DataStore, otherwise an in-memory database.
The playground also has three examples that are off until you enable them: a loot table, a ProfileStore inventory, and the unmodified 1.x quick start.
To run the tests inside Studio, build test.project.json instead and press Run (F8):
rojo build test.project.json -o rSQL-Tests.rbxl
The last line in Output is [rSQL tests] PASS ... or FAIL ....
What you can do with it
[image: Queries against a DataStore]
Three kinds of storage, one API:
| Use it for | Where rows live | |
|---|---|---|
rSQL.datastore(store) | data shared by every server: leaderboards, guilds, world state | a DataStore, split over several keys |
rSQL.profile(profile) | one player's data, with ProfileService, ProfileStore or DataKeep | inside profile.Data, saved by your profile library |
rSQL.memory() | game data you query a lot: items, loot tables, shops | a Luau table, not saved |
And the SQL you'd expect:
SELECTwithWHERE,JOIN/LEFT JOIN,GROUP BY,HAVING,ORDER BY,LIMIT/OFFSET,DISTINCTCOUNT,SUM,AVG,MIN,MAX, plusLOWER,UPPER,LENGTH,ROUND,COALESCEand a few moreAND/OR/NOT,LIKE,IN,BETWEEN,IS NULL, and math in expressions (SET Coins = Coins + 10)INSERTwith several rows at once,UPDATE,DELETECREATE TABLEwithPRIMARY KEY,AUTOINCREMENT,NOT NULL,UNIQUE,DEFAULTALTER TABLE(add, drop or rename columns),DROP TABLE,TRUNCATEEXPLAIN,SHOW TABLES,DESCRIBE?and:nameparameters, so player input never becomes SQL
Columns have types (INTEGER, REAL, TEXT, BOOLEAN, JSON, BLOB), so a number goes in and a number comes out.
How it stores tables in a DataStore
[image: Data read per lookup]
1.x kept a whole table under one DataStore key, so every query downloaded every row and a table could never grow past 4 MB. 2.0 splits each table over several keys (8 by default) by a hash of the primary key. Looking up one player reads one of them. Every write goes through UpdateAsync on just the shards it touches, so two servers changing different players don't step on each other.
[image: Rows per shard]
[image: Requests per statement]
The trade-off: a query that filters on a non-key column still has to read every shard, and a statement that changes rows in several shards commits one shard at a time. docs/advanced-usage.md has the details.
Errors you can act on
[image: Error messages]
What changed from 1.x
[image: 1.x vs 2.0]
Your 1.x code keeps running:
local connection = rSQL:connect(DataStoreService:GetDataStore("PlayerDatastore"), {
allowCreate = true,
allowInsert = true,
allowSelect = true,
allowUpdate = true,
}):expect()
connection:query("SELECT * FROM Players WHERE ID = '1'"):andThen(print)
Tables saved by 1.x load as they are, and tables created through the 1.x API keep the 1.x layout so old and new servers can share them during an update. docs/migration.md maps every 1.x call to the new one.
How it's built
[image: Architecture]
src/
├── init public API: datastore / profile / memory / open, plus the 1.x API
├── Database handles: exec, query, prepare, permissions, statement cache, stats
├── SQL/ lexer, parser, syntax tree, errors with positions
├── Engine/ planner, executor, compiled expressions, schemas, types
├── Storage/ DataStore (sharded and 1.x layout), profile/memory tables
├── Promise small promise compatible with what 1.x scripts used
├── Legacy the 1.x API on top of the new engine
└── Platform, Util runtime detection, hashing, JSON, logging
docs/architecture.md walks through how a query runs and why the storage looks the way it does.
Testing
- 91 tests cover the lexer and parser, every statement on the in-memory engine, the DataStore layouts (sharding, retries, size limits, resharding, 1.x data), profiles, the 1.x API and the promise.
- The same suite runs three ways: under Lune, from the Rojo-built place with Roblox's
requirerules (lune run scripts/emulate-studio), and inside Studio withtest.project.json. - The DataStore tests use a stand-in that copies values like real serialization, enforces the size limit and can throw throttling errors on demand.
src/passes strict type checking with luau-lsp.
Development
rokit install
lune run tests/run # all tests
lune run scripts/emulate-studio # same suite from the built Roblox place
lune run scripts/rsql # SQL shell (add -- --store dev.db.json to keep data)
lune run bench/run # benchmarks -> docs/figures/data/bench.json
lune run scripts/figures && swift tools/render-figures.swift # rebuild docs/img (macOS)
stylua src tests scripts bench examples && selene src tests
luau-lsp analyze --platform=standard --definitions=types/globals.d.luau --ignore="src/**/*.server.luau" src
See CONTRIBUTING.md.
Docs
- Getting started
- SQL reference
- DataStores, profiles and production use
- Recipes
- Architecture
- Benchmarks
- Moving from 1.x
License
MIT. See LICENSE.
Package Details
Install command (Click to copy)
Version
2.0.1
License
MIT
Safe for commercial use
Automated license review — not legal advice.
