Documentation

Everything you need to know about using QSQL effectively.

Getting Started

QSQL is a real-time SQL editor that connects to your own Supabase PostgreSQL database. Here's how to get started:

Create a free Supabase account and project
Set up the exec_sql function in your database
Connect to QSQL using your project URL and anon key
Open the playground and start writing SQL

Required Database Function

QSQL executes queries through a PostgreSQL function. You must create this function before using QSQL:

Run this in Supabase SQL Editor

CREATE OR REPLACE FUNCTION exec_sql(query_text TEXT)
RETURNS JSON
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  result JSON;
  is_select BOOLEAN;
BEGIN
  is_select := UPPER(LTRIM(query_text)) ~ '^(SELECT|WITH|TABLE|VALUES|SHOW|EXPLAIN)';

  IF is_select THEN
    EXECUTE 'SELECT COALESCE(json_agg(row_to_json(t)), ''[]''::json) FROM ('
      || query_text || ') t'
    INTO result;
    RETURN result;
  ELSE
    EXECUTE query_text;
    RETURN json_build_object(
      'status', 'success',
      'message', 'Query executed successfully'
    );
  END IF;
EXCEPTION WHEN OTHERS THEN
  RAISE EXCEPTION '%', SQLERRM;
END;
$$;

⚠️ Note: This function uses SECURITY DEFINER. Only use with your anon key and consider adding RLS to your tables.

CRUD Operations

CREATE TABLE

Define new tables.

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  department VARCHAR(50),
  salary DECIMAL(10,2) DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW()
);

INSERT

Add records.

INSERT INTO employees (name, email, department, salary)
VALUES 
  ('Alice Johnson', 'alice@example.com', 'Engineering', 95000),
  ('Bob Smith', 'bob@example.com', 'Marketing', 75000);

SELECT

Query data.

SELECT name, email, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 80000
ORDER BY salary DESC
LIMIT 10;

UPDATE

Modify records.

UPDATE employees
SET salary = salary * 1.10,
    department = 'Senior Engineering'
WHERE department = 'Engineering'
  AND salary > 90000;

DELETE

Remove records.

DELETE FROM employees
WHERE created_at < NOW() - INTERVAL '1 year'
  AND department = 'Inactive';

Keys & Constraints

Primary Key

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  sku VARCHAR(50) NOT NULL
);

Foreign Key

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  product_id INT REFERENCES products(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  quantity INT NOT NULL CHECK (quantity > 0)
);

Composite Key

CREATE TABLE order_items (
  order_id INT REFERENCES orders(id),
  product_id INT REFERENCES products(id),
  quantity INT NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Unique & Check

ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

ALTER TABLE employees
ADD CONSTRAINT check_salary CHECK (salary >= 0);

Joins

INNER JOIN

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

LEFT JOIN

SELECT e.name, o.total
FROM employees e
LEFT JOIN orders o ON e.id = o.employee_id;

RIGHT JOIN

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

FULL OUTER JOIN

SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

Self Join

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

Advanced Features

Indexes

CREATE INDEX idx_emp_email ON employees(email);
CREATE INDEX idx_emp_dept_sal ON employees(department, salary);
CREATE UNIQUE INDEX idx_emp_unique_email ON employees(email);

Views

CREATE VIEW active_employees AS
SELECT id, name, email, department
FROM employees
WHERE status = 'active';

SELECT * FROM active_employees;

CTEs

WITH dept_avg AS (
  SELECT department, AVG(salary) as avg_sal
  FROM employees
  GROUP BY department
)
SELECT e.name, e.salary, d.avg_sal
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_sal;

Window Functions

SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
  AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

Aggregations

SELECT 
  department,
  COUNT(*) as total,
  AVG(salary) as avg_salary,
  MIN(salary) as min_salary,
  MAX(salary) as max_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

Security Best Practices

Row Level Security (RLS)

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Allow public read"
  ON employees FOR SELECT
  USING (true);

Credentials Storage

Your Supabase credentials are stored in your browser's localStorage. They are never sent to any server other than your own Supabase project.