Laravel SQL Dashboard

What it solves

A Laravel app has no place where a team can save a SQL query, rerun it with new dates and see the numbers as a chart.

People end up passing queries around in chat and running them with a desktop client on a login that can write, or running a separate BI server next to the app.

A self-hosted SQL dashboard inside your Laravel app. Queries run only on a separate connection whose database user has been checked to be read-only, one SELECT or WITH statement at a time, inside a read-only transaction (a read-only file on SQLite), with a statement timeout and a row limit. Plain Blade pages with server-drawn SVG charts: no JavaScript library and no CDN.

• Saved queries with a name, description and owner, shared with the team or private to the owner
• Typed {variables} (text, whole number, number, date, date and time, yes/no, one of a list), validated and bound as parameters; dates accept today, yesterday or -7 days
• Result tables, CSV export with formula cells neutralised, and line, bar and pie charts picked from the result's shape or chosen per query
• Dashboards of several queries with one form for the variables they share
• Scheduled checks every 5 minutes, hour or day that fail on any row or on a threshold, with mail alerts when the state changes (Slack is implemented but untested against real Slack)
• Audit log of every run, export, check and refused attempt; three gates, local environment only by default

Install

Requires PHP 8.3+ and Laravel 12 and 13.

composer require amjadiqbal/laravel-sql-dashboard

Usage

-- {status}: one of a list (paid, refunded, failed); {since}: a date defaulting to -30 days
SELECT day, sum(total) AS revenue
FROM orders
WHERE status = {status} AND day >= {since}
GROUP BY day
ORDER BY day

Setup

The migration adds five sql_dashboard_* tables (queries, dashboards, the link between them, checks and the audit log) on your default connection, or on SQL_DASHBOARD_STORAGE_CONNECTION. Queries never run there: add a second connection in config/database.php that logs in as a database user with SELECT only (on SQLite, open the file with the read-only flag) and point SQL_DASHBOARD_CONNECTION at it. Until you do, nothing runs and the dashboard says how to set it up. The dashboard refuses a connection with the same name as its storage connection, and sql-dashboard:verify checks the connection and runs SELECT 1 through every layer.

1. 1 composer require amjadiqbal/laravel-sql-dashboard
2. 2 php artisan migrate
3. 3 Add a read-only connection and set SQL_DASHBOARD_CONNECTION to its name.
4. 4 php artisan sql-dashboard:verify
5. 5 Define the gates, then open /sql-dashboard.

-- MySQL / MariaDB
CREATE USER 'dashboard'@'%' IDENTIFIED BY 'a-long-password';
GRANT SELECT, SHOW VIEW ON your_app.* TO 'dashboard'@'%';

-- PostgreSQL
CREATE ROLE dashboard LOGIN PASSWORD 'a-long-password';
GRANT USAGE ON SCHEMA public TO dashboard;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dashboard;

Who can use it

Three gates decide access. Without your own definitions each one allows the local environment only (APP_ENV=local, for anyone who can reach the app there) and returns 403 everywhere else. viewSqlDashboard opens the dashboard and lists saved queries and dashboards; runSqlDashboardQueries runs them and downloads CSV; editSqlDashboardQueries writes SQL, manages dashboards and checks and reads the audit log. Writing SQL also needs a signed-in user whatever the gate returns. The gates run after the configured middleware (default web), so emptying that list does not open the pages. Shared queries and dashboards are visible to everyone allowed in, and anyone with the edit gate can change them; private ones return 404 to everyone but their owner.

use Illuminate\Support\Facades\Gate;

Gate::define('viewSqlDashboard', fn ($user = null) => $user?->is_admin || $user?->is_analyst);
Gate::define('runSqlDashboardQueries', fn ($user = null) => $user?->is_admin || $user?->is_analyst);
Gate::define('editSqlDashboardQueries', fn ($user = null) => (bool) $user?->is_admin);

How read-only is enforced

Every query passes three layers. First, a dialect-aware tokenizer (not a full SQL parser) reads strings, quoted names, nested PostgreSQL comments, dollar quotes and MySQL's -- and # rules, and the statement must start with SELECT or WITH and be one statement. Write, DDL and session words anywhere outside quotes are refused, which also catches a write hidden in a CTE, as are INTO (including INTO OUTFILE), FOR UPDATE and FOR SHARE, raw ?, $1 and :name parameters, MySQL /*! */ comments, PostgreSQL U&"..." escaped names and unterminated strings or comments. Calls to a deny-list of side-effect functions (set_config, advisory locks, nextval, pg_read_file and the other server-file functions, large objects, dblink, GET_LOCK, LOAD_FILE, load_extension and more) are refused however the name is written: bare, double-quoted, backtick-quoted, [bracketed] or schema-qualified. Where text could be read two ways, it refuses rather than guesses. It is a deny-list, not an allow-list, so it is a first filter; the next two layers make a missed name harmless. Second, the database refuses writes itself: START TRANSACTION READ ONLY on MySQL and MariaDB, BEGIN READ ONLY with the query inside DECLARE ... CURSOR on PostgreSQL, and on SQLite a separate PHP process that opens the file read-only with PRAGMA query_only on (on PHP 8.5, SQLite's authorizer also allows only reads). After each query any advisory lock (PostgreSQL) or GET_LOCK lock (MySQL) the session took is released. Third, before running, the connection's user is checked. On MySQL and MariaDB every privilege in SHOW GRANTS must be a read privilege: FILE, PROCESS, SUPER and roles are refused. On PostgreSQL the role must not be a superuser, must not be a member of pg_read_server_files, pg_write_server_files, pg_execute_server_program or pg_signal_backend, must not be able to execute dblink or the server-file functions, must not have USAGE on a foreign server, and must not own, or hold INSERT, UPDATE, DELETE or TRUNCATE on, any table. On SQLite the connection must have the read-only open flag. A read replica (super_read_only, or a PostgreSQL standby) skips the write checks but not the others. That result is cached for 60 seconds per process. Limits: it is not a sandbox. Nothing hides columns: anyone who can write SQL can read every table the read-only user can, so grant it only what your team should see. MySQL loadable functions (CREATE FUNCTION ... SONAME) can be called by any user and cannot be seen from a read-only one; the well-known sys_exec family is refused, but don't install such functions on a server the dashboard reads.

Timeouts and row limits

• Timeout (limits.timeout, 15 seconds): max_execution_time on MySQL, max_statement_time on MariaDB, SET LOCAL statement_timeout and lock_timeout on PostgreSQL, and on SQLite the child process is killed.
• Rows (limits.max_rows, 1000 in the browser; limits.export_max_rows, 10000 in a CSV): sql_select_limit plus an unbuffered fetch on MySQL and MariaDB, a server-side cursor fetched 500 rows at a time on PostgreSQL, and the SQLite process stops reading.
• A result also stops at 16 MB of values. A cut-off result says so on the page and in the audit log.
• Session settings changed for a run (timeouts, sql_select_limit, sql_mode) are put back afterwards, the transaction is rolled back, and any advisory or GET_LOCK lock the session took is released.

Variables, charts and CSV

Write {name} in the SQL and give it a type, a label, a default and, for a list, its options. Values are checked against the type and bound as PDO parameters, never written into the SQL; text is capped at 1,000 characters. Date and date-time values accept a date or now, today, yesterday, tomorrow or offsets like -7 days, so a saved query or check always looks at a fresh window. A {name} inside quotes stays plain text and the editor says so. Charts are drawn as SVG on the server: a date first gives a line per number column (or per label for date, label, number), a label first gives bars, and two columns with the number column named pie give a pie; you can also pick line, bar, pie or table per query. Up to 500 points and 8 series are drawn. CSV exports are UTF-8 with a BOM, and a text cell starting with =, +, -, @, a tab or a carriage return gets a leading apostrophe so a spreadsheet does not run it.

Scheduled checks and alerts

A check runs a saved query with each variable's default (so every variable needs one) at its interval: every 5 minutes, hourly or daily. It fails when the query returns any row, or when a named column in the first row is above, at or above, below, at or below, equal to or not equal to a number. You get one alert when it starts failing, one when it passes again and one when it errors, not one every run; a new check that comes up passing sends nothing. Mail goes to the addresses on the check with up to 10 result rows (alerts.mail_domains can restrict the domains). Slack alerts post to the incoming webhook in SQL_DASHBOARD_SLACK_WEBHOOK_URL, set only in your environment, with up to 5 rows; they are implemented and tested only against a faked HTTP client, not a real Slack workspace yet. The package schedules sql-dashboard:run-checks every minute and sql-dashboard:prune-audit daily, so your app must run the Laravel scheduler; set SQL_DASHBOARD_SCHEDULE=false to schedule them yourself.

php artisan sql-dashboard:run-checks             # the checks that are due
php artisan sql-dashboard:run-checks --check=3   # one check now
php artisan sql-dashboard:prune-audit --days=30

Audit log and things to know

• The audit log records every run, export, check and refused attempt: the user and IP, where it came from (page, editor, dashboard, export, check or console), the connection, the SQL, the variable values, rows, milliseconds and the outcome (ok, refused, failed or timeout). If writing the entry fails, the run still goes ahead and a warning is logged.
• Entries older than 90 days (audit.retention_days) are deleted by sql-dashboard:prune-audit; null keeps them all.
• Supported drivers: mysql, mariadb, pgsql and sqlite. An in-memory SQLite database cannot be used, because SQLite queries run in a separate process.
• Tested on MySQL 8.4, MariaDB 11, PostgreSQL 16 and SQLite. Slack alerts are implemented and tested only against a faked HTTP client, not a real Slack workspace yet.
• php artisan about shows the version, path and connection. The path, domain and middleware are configurable (default /sql-dashboard with web).

Support

Found a bug or want a feature? Open an issue on GitHub. For help adding it to your own project, use the quote form above.