I have an Oracle Database, and in that database, I have some data that contains some of my historical music streaming history. Can I build a Summary Dashboard via a React Web app?
Of course you can. But more interestingly, how can our SQLcl MCP Server for the Oracle Database help me do this?
It turns out, quite a lot.
My rig
- Apple Mac Mini (2018), running Sequoia 15
- Oracle Database, 23ai Free
- VS Code & Copilot
- SQL Developer Extension for VS Code, 25.2
- Claude Sonnet 4
The End Result
Let’s just show right away what we ended up with – here’s my app:
Building our app, starting with the prompt:
In VS Code, I opened a fresh directory, so Copilot would have something or somewhere to work. And then I switched to Agent mode, and gave it the following task:
My prompt: connect to my reporting database, inspect my music data – familiarize yourself with the structure of the data and the data itself. help me build a local react application that serves as a reporting dashboard for my streaming history
Phase 1: building the local dev environment, and generating a hard-coded app
I’ve not built a React app on my home machine before, so our coding agent is gonna sort that for me. Lots of brew install and npm commands later, it’s able to start generating the project and all of the necessary front and back end code for me. This is super handy, but there’s nothing novel here.
Coding agents are known to be pretty OK at these sorts of tasks, and I let it do ‘the needful.’
Where it gets interesting for this particular use case, is when it wants to see what kind of data it’s going to be working with. And I’ve told it to work with my reporting database.
Luckily for me, I have an MCP Server to handle my Database tasks, or rather, to allow the Agent (Copilot) to get what it needs.
It does the following:
- Asks for permission to access my SQLcl MCP Server, starting with the ‘list-connections‘ tool
- It finds a connection to satisfy the ‘reporting database’ label, and asks if it can use that to connect, using the ‘connect‘ tool
Note: I gave it a locked down connection, the user can only work with the data in it’s own schema, and can ‘do’ what I want it to do. Security, first! - Once connected it starts running queries via the ‘run-sql‘ tool, to find my ‘music data’
Who or what, generates these queries? It’s the LLM. The LLM understand’s our data dictionary, so it knows it can reasonably find what it wants by grabbing a list of tables by name and their associated comments.
When the data comes back, the LLM will look for something resembling, ‘music.’
From here the LLM generates and runs more queries, something like –
- does a DESC on the table
- samples the data by querying first 10 rows
- collates some stats, think select count(*), count(distinct artist), count(distinct album), … these numbers will be used later to populate our dashboard
From here, the agent is ready to actually build our Reach dashboard components.
It does so, and starts up the app with our favorite
> npm run devAnd even offers to load up the page, directly in VS Code. So handy…
Are we done? Nope!
Since I’ve only worked with React and Vite briefly before, I ask my agent another question –
My prompt: where in my project is stats?.totalTracks defined?
I know it’s hard coded, but, where?
and Copilot answers for me:
async getDashboardStats(): Promise<DashboardStats> {
return {
totalTracks: 4704, // ← Hard-coded mock data here
totalArtists: 281,
totalAlbums: 525,
totalGenres: 63,
totalPlays: 0,
avgPlaysPerTrack: 0
}
}Cool, so it’s a real app, kinda.
What’s interesting is that the agent KNOWS that it has cheated. And it offers to evolve the app, which I gladly take it up on its offer.
Phase 2: adding db connectivity/SQL-backed APIs
My prompt: This looks great! Now let’s evolve the project. We will get the node-oracledb driver, and replace the static values for your dashboard with apis using SQL to query our database
This is where it gets a bit tricksy. The LLM knows it has an MCP Server, so at first it writes some code that allows it to get the data it wants via our MCP Server, instead of actually having backend of the app make a database connection/pool.
I stopped it in it’s tracks, but it went far enough that I could see it was ‘working.’ That’s not what I wanted however, nor would it be an appropriate solution if this app was ever gonna make it anywhere near production.
Here’s where it got tricky again…that .env file, it was gonna use our MCP Connection to try to reverse engineer what it needed to define a database connection. Which it could have helped with by finding the service name and the username for the connection, but again, I stopped it. I told it I would handle the .env file, myself.
Once I had the connectivty sorted, it offered to test the connection from the node.js server, for me, which I gladly said, ‘go for it!’
Database initialization failed: Error: DPI-1047: Cannot locate a 64-bit Oracle Client library: "dlopen(libclntsh.dylib, 0x0001)And it abruptly failed. It had setup the connection using the THICK driver connection type, which requires an $ORACLE_HOME – not necessary! But the agent recognized this and immediately changed the code to go the ‘THIN’ route and run the test again.
Jeffreys-Mac-mini:react thatjeffsmith$ npm run test:db
> [email protected] test:db
> tsx server/test-connection.ts
[[email protected]] injecting env (4) from .env -- tip: ⚙️ write to custom object with { processEnv: myObject }
Dotenv result: {
parsed: {
DB_USER: 'your_username',
DB_PASSWORD: 'your_password',
DB_CONNECTION_STRING: 'localhost:1521/XEPDB1',
PORT: '3001'
}
}
🔄 Testing Oracle Database Connection...
📋 Using connection details:
User: hr
Connection: localhost:1521/freepdb1
Password: [HIDDEN]
Using Oracle thin mode (no client installation needed)
Database pool created successfully
✅ Database connection successful!So from here, we were ready to restart the server, and reload our app! And…it worked! The agent had already sorted the API definitions, the REST endpoints called by each of the elements in the web page:
And yes, the LLM generated this SQL as well. Which means, it’s MY JOB to confirm it’s valid SQL, and that it’s performant, and SAFE!
- does the SQL do what I think it should be doing?
- do we have the stats sorted, indexes created?
- any SQL Injection vulnerabilities?
It took more than a few more prompts to get the dashboard, ‘finished,’ and ‘working.’ But start to end, this was about 40 minutes of work from my side. This is what I would call, ‘vibe coding.’
Did you spot the bug?
Maybe not a bug, but also not super clear when it comes to reporting information in a dashboard. I THINK it’s showing the music I’m listening to based on when the song was released, but let’s find out.
// Get tracks by year
static async getTracksByYear() {
const sql = `
SELECT
EXTRACT(YEAR FROM TO_DATE(year, 'YYYY')) as year,
COUNT(*) as track_count
FROM itunes
WHERE year IS NOT NULL
GROUP BY year
ORDER BY year DESCAyup, it’s confusing what the COLUMN is actually representing, which I can prove by correlating the ’14’ with 1976 input. Also, that SQL sucks.
If the LLM had remembered or noticed that ‘YEAR’ was stored as a number, and only contained the 4 digit year data, it should have known better than to convert it to a date, only to extract the data back out as a string again, just for the same data it started with. In this case the cost difference is literally 1 (70 vs 69), but the lesson here is you want to put your database hat on to make sure things are ‘good,’ before ever committing work.
To ‘fix’ this bug, I would update the SQL to be simpler, but also add a having clause to filter the data out to the top years, OR change up the typescript charting component to allow for more data – there’s a TON of years missing. Oh, and also I would change the label of the chart to accurately reflect what’s being shown, the era of the music being played, not the year it was streamed.
Jeff, you had it use ORDS for the REST APIs, right?
Nope! We just went with a pure node.js implementation here, so these API endpoints, have hard-coded SQL, and they won’t be available to other apps. running outside of this app’s context.
API Endpoints
GET /api/dashboard-stats - Library statistics
GET /api/top-genres - Genre distribution data
GET /api/top-artists - Artist rankings
GET /api/recent-tracks - Track listings with metadata
GET /api/tracks-by-year - Timeline data for visualizationBUT, if I had already implemented ORDS REST endpoints or if I wanted the LLM to convert this app to use APIs served by ORDS instead, I could have done with just a bit more work. The benefit’s there could be pretty powerful, but I leave that for a task to be sorted later, if ever.
Summary
I literally wrote zero of this code. I only provided the prompts and guidance to the Agent as it progressed. It was able to get the information it needed from the database using our SQLcl MCP Server, for both prototyping the app, and also for writing the SQL it used for the backend APIs.
The only typing I did was to update the .env file, so that our Oracle Database node driver could connect to my database.
Read more about using our Oracle Database MCP Server:
You can keep reading if you want more details, but my final message here is, I could have done this on my own, by hand, but it would have taken me days, not minutes. And also, I’m a product manager with zero days experience with TypeScript and React.
Application Stack Breakdown
Frontend Stack
Core Framework & Runtime
- React 19.1.1 – Modern React with hooks and functional components
- TypeScript 5.8.3 – Type-safe JavaScript development
- Vite 7.1.0 – Lightning-fast build tool and dev server
State Management & Data Fetching
- TanStack Query 5.84.1 – Powerful data synchronization for React
- Server state management
- Caching, background updates, and error handling
- Query invalidation and refetching
UI & Styling
- Tailwind CSS 3.4.17 – Utility-first CSS framework
- Custom primary color palette
- Responsive design system
- Component styling
- PostCSS 8.5.6 + Autoprefixer 10.4.21 – CSS processing
- Lucide React 0.539.0 – Beautiful SVG icon library
- Music, Users, Disc, ListMusic icons
- clsx 2.1.1 + tailwind-merge 3.3.1 – Conditional CSS classes
Data Visualization
- Recharts 3.1.2 – Composable charting library
- Bar charts for genre distribution
- Line charts for timeline analysis
- Responsive charts with tooltips
Utilities
- date-fns 4.1.0 – Modern JavaScript date utility library
Backend Stack
Server Framework
- Express.js 5.1.0 – Fast, unopinionated web framework
- CORS 2.8.5 – Cross-Origin Resource Sharing middleware
- Node.js – JavaScript runtime environment
Database Layer
- Oracle Database – Enterprise database system
- iTunes table with 4,704+ tracks, 281 artists, 525 albums, 63 genres
- 27 columns including metadata, play history, ratings
- node-oracledb 6.9.0 – Oracle Database driver for Node.js
- Thin mode – No Oracle client installation required
- Connection pooling for efficient resource management
What the LLM did in our Database via the MCP Server’s tools for this vibe coding session
We log what happens, and here’s a look at what occurred from the database’s perspective from my coding session on this project:
While the stats it chose to highlight weren’t exactly mind-blowing, it was efficient in terms of the work it needed to perform in the database to deliver its plan.
