ShortSQL

# ShortSQL ### A real SQL database, inside one shortcut.      > **Tables, joins, encryption and backups on your iPhone.** > No app to install. No server. No account. Your data never leaves your iCloud Drive. --- ## At a glance | | | |:--|:--| | **Real SQL** | `JOIN`, `GROUP BY`, subqueries, `UNION`, transactions and 60+ functions | | **Encrypted databases** | AES-256-GCM with a key only you know. Change it or turn it on any time | | **Private databases** | Hidden from the console, for the shortcut that owns them | | **Backup & restore** | Every database in one `.ssql` file. Move to a new iPhone in seconds | | **History** | Your last 20 queries, one tap to run again | | **Beautiful results** | A clean table page in light or dark, or plain text | | **Developer API** | One Run Shortcut action gives *your* shortcut a database | | **Fully offline** | Everything runs on the device. Internet is only used to tell you about updates | --- ## How it works Run **ShortSQL**. That's the whole setup.    > **Tip:** leave the prompt empty to open the menu. Type `USE shop` to switch database, `KEY secret` to unlock one, or `exit` to close. ### Everything in the menu | Option | What it does | |:--|:--| | **Browse a table** | See its rows, no SQL needed | | **Describe a table** | Columns, types, keys and defaults | | **History** | Rerun one of your recent queries | | **Switch database** | Open, create, or delete several at once | | **Enter key · Encryption** | Unlock, encrypt later, change the key or remove it | | **Backup & restore** | Save one or all databases, or bring them back | | **Settings** | Page or text, appearance, start-up, rows shown. Every change is confirmed | | **Help** | A full SQL reference, built in | | **What's new** | Release notes for every version | | **Developers** | API guide, a copy-ready JSON template, the ShortSQL Starter | | **Support** | Discord and Telegram | --- ## Real SQL. Not a toy. ```sql CREATE TABLE expenses ( id INT PRIMARY KEY AUTO_INCREMENT, item VARCHAR(80) NOT NULL, amount DECIMAL(10,2) NOT NULL, tag TEXT DEFAULT 'other', spent DATE DEFAULT (CURRENT_DATE) ); -- Where did my money go this month? SELECT tag, COUNT(*) AS times, SUM(amount) AS total FROM expenses WHERE spent >= DATE_FORMAT(CURDATE(), '%Y-%m-01') GROUP BY tag ORDER BY total DESC; ``` ### Everything that's supported - **Databases:** `CREATE` (plain, `ENCRYPTED`, `PRIVATE`), `ALTER DATABASE … KEY / DECRYPT / PRIVATE / PUBLIC`, `SHOW DATABASES`, `DROP` - **Tables:** `CREATE` (incl. `AS SELECT` and `LIKE`), `ALTER` (add, drop, rename, modify, change columns, rename table), `DROP`, `TRUNCATE`, `RENAME`, `DESCRIBE`, `SHOW TABLES`, `SHOW CREATE TABLE`, `SHOW INDEX` - **Writing:** `INSERT` (multi-row, `SELECT`, `SET`), `INSERT IGNORE`, `REPLACE`, `ON DUPLICATE KEY UPDATE`, `UPDATE` and `DELETE` with `ORDER BY` / `LIMIT` - **Reading:** `JOIN`, `LEFT` / `RIGHT` / `CROSS JOIN`, `GROUP BY`, `HAVING`, `DISTINCT`, `ORDER BY`, `LIMIT` / `OFFSET`, subqueries (`IN`, `EXISTS`, scalar, derived tables), `UNION` / `UNION ALL` - **Expressions:** `CASE`, `CAST`, `BETWEEN`, `LIKE`, `REGEXP`, `IS NULL`, `<=>`, `DIV`, `MOD`, `INTERVAL` - **Constraints:** `PRIMARY KEY`, `AUTO_INCREMENT`, `UNIQUE`, `NOT NULL`, `DEFAULT` - **Transactions:** `BEGIN` / `COMMIT` / `ROLLBACK`; any `;` script is saved only if every statement succeeds - **Functions:** text (`CONCAT`, `SUBSTRING`, `REPLACE`, `LPAD`…), numbers (`ROUND`, `ABS`, `POWER`…), dates (`NOW`, `DATE_ADD`, `DATEDIFF`, `DATE_FORMAT`…), logic (`IF`, `IFNULL`, `COALESCE`…), totals (`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `GROUP_CONCAT`) > Simple `SELECT`, `COUNT`, `UPDATE` and `DELETE` run as a few native actions, so big tables stay quick. --- ## Your data, locked down - **`CREATE DATABASE vault ENCRYPTED`** encrypts every table with **AES-256-GCM**. The key is stretched with **PBKDF2 (100,000 rounds)**, so guessing it is slow. - **Change your mind later:** `ALTER DATABASE vault KEY 'new key'` re-encrypts everything. It works on unencrypted databases too. - **`PRIVATE`** databases never show up in the console list. - Everything lives in **iCloud Drive › Shortcuts › ShortSQL**. Nothing is uploaded, ever. > **Note:** a lost key cannot be recovered. That's what makes it encryption. --- ## Never lose a thing **Menu › Backup & restore** saves one database, or all of them, into a single `.ssql` file. Keep it in Files, AirDrop it to a new iPhone, restore it there. Encrypted databases **stay encrypted** inside the backup. --- ## For shortcut developers Stop juggling text files. Give your shortcut a real database in **three actions**: 1. **Text** with a JSON request (or a **Dictionary**) 2. **Run Shortcut** → `ShortSQL` 3. **Get Dictionary from Input** → read `rows`, `affected` or `error` ```json { "db": "myapp", "query": "SELECT id, title FROM notes WHERE done = ?", "params": [0] } ``` **Reply:** ```json { "ok": true, "count": 2, "rows": [ { "id": "1", "title": "Buy milk" }, { "id": "2", "title": "Call mom" } ] } ``` **Bulk insert:** many rows, one call, all or nothing: ```json { "db": "myapp", "table": "notes", "rows": [ { "title": "Buy milk" }, { "title": "Call mom", "done": 1 } ] } ``` | Field | | | |:--|:--|:--| | `db` | required | Database name | | `query` | required* | One statement, or a `;` script that saves all or nothing | | `params` | optional | A value for each `?`, quoted safely: quotes, emoji, line breaks | | `key` | optional | For encrypted databases | | `table` + `rows` | optional | Bulk insert, *instead of* a query | | `check_updates` | optional | `false` keeps your shortcut 100% offline | | Reply | | |:--|:--| | `ok` | `true`, or `false` with an `error` message | | `rows` · `count` | After a `SELECT` | | `affected` · `insert_id` | After `INSERT`, `UPDATE`, `DELETE` | > **ShortSQL Starter · View in Editor:** install it from *Menu › Developers* for copy-ready blocks (setup, insert, select, update, bulk insert, encryption, private databases). The complete API guide is built into the console too. ### Ideas to build - Notes and to-do apps with search, tags and due dates - Expense and habit trackers with monthly totals - Inventory, collections and reading lists - Settings storage for your own shortcuts: `INSERT … ON DUPLICATE KEY UPDATE` - Logs from automations, queried later with `GROUP BY` --- ## FAQ **Do I need the internet?** No. Everything runs on your iPhone. When you happen to be online, ShortSQL checks once in a while for a new version. **Will updating delete my data?** Never. Your databases live in iCloud Drive, not inside the shortcut. **Can other people's shortcuts read my databases?** Any shortcut you run can call ShortSQL. That's how the API works. Use **ENCRYPTED** for anything sensitive: without the key, the data is unreadable. **What isn't supported?** Users and `GRANT`, views, triggers and stored procedures. It's a personal database with one request at a time, not a server. **Does the name matter?** Yes. Keep it named **ShortSQL** exactly, so other shortcuts can find it. --- ## Support & community | | | |:--|:--| | **[Discord](https://discord.gg/sS5Wqa4TNp)** | Chat, updates, bug fixes and weekend fun | | **[Telegram](https://t.me/ios_shortcuts)** | Updates and bug fixes, straight to you | --- **ShortSQL** · Developed by **Anup**