{"id":"rustyspottedcatt/rsql","name":"rsql","scope":"rustyspottedcatt","platform":"roblox","description":"SQL for Roblox DataStores, player profiles and in-memory tables.","version":"2.0.1","latest":"2.0.1","versions":["2.0.1"],"license":"MIT","licenseRating":"safe","licenseCaveats":[],"licenseVerified":true,"dependencies":{},"integrity":"b9a33d91147ff427d100a269eb0344f47658a4884202db33dde821d8f05fd435","likes":0,"downloads":0,"install":"forest install rustyspottedcatt/rsql","url":"https://forest.dev/p/roblox/rustyspottedcatt/rsql","files":"https://api.forest.dev/ai/package/roblox/rustyspottedcatt/rsql/files","readme":"# rSQL\n\nSQL for Roblox DataStores, player profiles and in-memory tables.\n\n[![CI](https://github.com/rustyspottedcatt/rSQL/actions/workflows/ci.yml/badge.svg)](https://github.com/rustyspottedcatt/rSQL/actions/workflows/ci.yml)\n[![Luau strict](https://img.shields.io/badge/Luau-strict-00a2ff)](https://luau.org)\n[![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](LICENSE)\n\n![rSQL 2.0](docs/img/01-banner.png)\n\n```lua\nlocal rSQL = require(ReplicatedStorage.rSQL)\nlocal db = rSQL.datastore(DataStoreService:GetDataStore(\"Game\"))\n\ndb:exec([[\n\tCREATE TABLE IF NOT EXISTS Players (\n\t\tUserId INTEGER PRIMARY KEY,\n\t\tName   TEXT NOT NULL,\n\t\tCoins  INTEGER NOT NULL DEFAULT 0\n\t)\n]])\n\ndb:exec(\"INSERT INTO Players (UserId, Name) VALUES (?, ?)\", { player.UserId, player.Name })\ndb:exec(\"UPDATE Players SET Coins = Coins + ? WHERE UserId = ?\", { 50, player.UserId })\n\nlocal top = db:exec(\"SELECT Name, Coins FROM Players ORDER BY Coins DESC LIMIT 10\").rows\n```\n\n2.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](#what-changed-from-1x).\n\n## Try it in Studio\n\nYou need [Rokit](https://github.com/rojo-rbx/rokit) and Roblox Studio.\n\n```bash\nrokit install\nrojo build dev.project.json -o rSQL-Playground.rbxl\n```\n\nOpen 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.\n\nThe 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.\n\nTo run the tests inside Studio, build `test.project.json` instead and press Run (F8):\n\n```bash\nrojo build test.project.json -o rSQL-Tests.rbxl\n```\n\nThe last line in Output is `[rSQL tests] PASS ...` or `FAIL ...`.\n\n## What you can do with it\n\n![Queries against a DataStore](docs/img/03-queries.png)\n\nThree kinds of storage, one API:\n\n| | Use it for | Where rows live |\n|---|---|---|\n| `rSQL.datastore(store)` | data shared by every server: leaderboards, guilds, world state | a DataStore, split over several keys |\n| `rSQL.profile(profile)` | one player's data, with ProfileService, ProfileStore or DataKeep | inside `profile.Data`, saved by your profile library |\n| `rSQL.memory()` | game data you query a lot: items, loot tables, shops | a Luau table, not saved |\n\nAnd the SQL you'd expect:\n\n- `SELECT` with `WHERE`, `JOIN` / `LEFT JOIN`, `GROUP BY`, `HAVING`, `ORDER BY`, `LIMIT` / `OFFSET`, `DISTINCT`\n- `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, plus `LOWER`, `UPPER`, `LENGTH`, `ROUND`, `COALESCE` and a few more\n- `AND` / `OR` / `NOT`, `LIKE`, `IN`, `BETWEEN`, `IS NULL`, and math in expressions (`SET Coins = Coins + 10`)\n- `INSERT` with several rows at once, `UPDATE`, `DELETE`\n- `CREATE TABLE` with `PRIMARY KEY`, `AUTOINCREMENT`, `NOT NULL`, `UNIQUE`, `DEFAULT`\n- `ALTER TABLE` (add, drop or rename columns), `DROP TABLE`, `TRUNCATE`\n- `EXPLAIN`, `SHOW TABLES`, `DESCRIBE`\n- `?` and `:name` parameters, so player input never becomes SQL\n\nColumns have types (`INTEGER`, `REAL`, `TEXT`, `BOOLEAN`, `JSON`, `BLOB`), so a number goes in and a number comes out.\n\n## How it stores tables in a DataStore\n\n![Data read per lookup](docs/img/04-bytes-per-lookup.png)\n\n1.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.\n\n![Rows per shard](docs/img/06-shards.png)\n\n![Requests per statement](docs/img/05-requests.png)\n\nThe 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](docs/advanced-usage.md) has the details.\n\n## Errors you can act on\n\n![Error messages](docs/img/07-errors.png)\n\n## What changed from 1.x\n\n![1.x vs 2.0](docs/img/08-what-changed.png)\n\nYour 1.x code keeps running:\n\n```lua\nlocal connection = rSQL:connect(DataStoreService:GetDataStore(\"PlayerDatastore\"), {\n\tallowCreate = true,\n\tallowInsert = true,\n\tallowSelect = true,\n\tallowUpdate = true,\n}):expect()\n\nconnection:query(\"SELECT * FROM Players WHERE ID = '1'\"):andThen(print)\n```\n\nTables 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](docs/migration.md) maps every 1.x call to the new one.\n\n## How it's built\n\n![Architecture](docs/img/02-architecture.png)\n\n```\nsrc/\n├── init            public API: datastore / profile / memory / open, plus the 1.x API\n├── Database        handles: exec, query, prepare, permissions, statement cache, stats\n├── SQL/            lexer, parser, syntax tree, errors with positions\n├── Engine/         planner, executor, compiled expressions, schemas, types\n├── Storage/        DataStore (sharded and 1.x layout), profile/memory tables\n├── Promise         small promise compatible with what 1.x scripts used\n├── Legacy          the 1.x API on top of the new engine\n└── Platform, Util  runtime detection, hashing, JSON, logging\n```\n\n[docs/architecture.md](docs/architecture.md) walks through how a query runs and why the storage looks the way it does.\n\n## Testing\n\n- 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.\n- The same suite runs three ways: under Lune, from the Rojo-built place with Roblox's `require` rules (`lune run scripts/emulate-studio`), and inside Studio with `test.project.json`.\n- The DataStore tests use a stand-in that copies values like real serialization, enforces the size limit and can throw throttling errors on demand.\n- `src/` passes strict type checking with luau-lsp.\n\n## Development\n\n```bash\nrokit install\nlune run tests/run                      # all tests\nlune run scripts/emulate-studio         # same suite from the built Roblox place\nlune run scripts/rsql                   # SQL shell (add -- --store dev.db.json to keep data)\nlune run bench/run                      # benchmarks -> docs/figures/data/bench.json\nlune run scripts/figures && swift tools/render-figures.swift   # rebuild docs/img (macOS)\nstylua src tests scripts bench examples && selene src tests\nluau-lsp analyze --platform=standard --definitions=types/globals.d.luau --ignore=\"src/**/*.server.luau\" src\n```\n\nSee [CONTRIBUTING.md](CONTRIBUTING.md).\n\n## Docs\n\n- [Getting started](docs/getting-started.md)\n- [SQL reference](docs/basic-usage.md)\n- [DataStores, profiles and production use](docs/advanced-usage.md)\n- [Recipes](docs/templates.md)\n- [Architecture](docs/architecture.md)\n- [Benchmarks](docs/benchmarks.md)\n- [Moving from 1.x](docs/migration.md)\n\n## License\n\nMIT. See [LICENSE](LICENSE).\n","readmeTruncated":false}