← Back
Case Study · Contribute · 2024

OptiSQL

A VS Code extension that rewrites Oracle SQL queries with Mistral AI — sargable filters, bind variables, better join order — then drops the diff right in your editor.

0%

query time saved

0.7×

join optimisation

0

components shipped

Optisql - demo
What it actually does

Watch the rewrite
happen live

Hit replay to see OptiSql stream the diff exactly as it appears in VS Code — removed lines in red, additions in green. One click to accept and move on.

query.sql — diff
−SELECT e.name, d.dept_name, COUNT(o.id)
+SELECT /*+ LEADING(d e o) USE_NL(o) */
+ e.name, d.dept_name, COUNT(o.id)
FROM employees e
−JOIN departments d ON e.dept_id = d.id
−JOIN orders o ON o.emp_id = e.id
−WHERE TO_CHAR(o.created_at, 'YYYY') = '2023'
− AND UPPER(e.status) = 'ACTIVE'
+FROM departments d
+JOIN employees e ON e.dept_id = d.id
+ AND e.status = 'ACTIVE' -- :b_status
+JOIN orders o ON o.emp_id = e.id
+ AND o.created_at >= DATE '2023-01-01'
+ AND o.created_at < DATE '2024-01-01'
GROUP BY e.name, d.dept_name
−4 lines removed+7 lines added~2.7× faster
The story
01

The problem at Contribute

Analysts were writing ad-hoc Oracle queries in SQL Developer. Functions wrapping indexed columns (TO_CHAR, UPPER), joins in the wrong order, no bind variables. Plans were inconsistent. Runtimes were long. No one knew why.

02

What I built

I designed and built three components end-to-end: the VS Code extension in JavaScript, the Flask REST API in Python, and the Mistral prompt engineering that enforces Oracle-specific rewrite rules. My teammates handled the Oracle Cloud VM and ADB wallet config.

03

The result

Median query time dropped 40–70% on target workloads. Plan regressions nearly gone. Analysts see changes as a diff, accept with one click, and move on. The workflow went from "why is this slow" to "done" in under ten seconds.

System design

Three moving parts

01
extension.js

VS Code Extension

  • Reads selected text from editor
  • POSTs to Flask, awaits response
  • Opens built-in diff editor
  • Replace or Cancel — one click
02
app.py

Flask API

  • POST /analyze_sql { sql_code }
  • Validates + sanitises input
  • Calls Mistral client
  • Returns { optimized_sql }
03
mistral_api.py

Mistral Client

  • Deterministic system prompt
  • Enforce sargable rewrites
  • Bind variables required
  • Returns SQL only — no prose
Stack
Editor
JavaScriptVS Code Extension APIDiff Renderer
AI
Mistral AISystem prompt engineeringLow-temperature completion
Backend
PythonFlaskREST API
Infra
Oracle Cloud VMOracle Autonomous DBWallet auth (ADB)
View source on GitHub