How to Build a Data Visualizations Tools with Replit

1// Prompt to type into Replit Agent:
2// Build a data visualization tool with Express and PostgreSQL using Drizzle ORM.
3// Create these tables in shared/schema.ts:
4// - datasets: id serial pk, user_id text, name text, source_type text default 'csv',
5//   row_count integer, column_definitions jsonb, data_preview jsonb, created_at timestamp
6// - data_rows: id serial pk, dataset_id integer references datasets, row_data jsonb not null
7// - charts: id serial pk, user_id text, dataset_id integer references datasets,
8//   name text, chart_type text (line/bar/area/pie/scatter/radar),
9//   config jsonb (x_axis, y_axis, group_by, aggregate, filters, color_scheme), created_at timestamp
10// - dashboards: id serial pk, user_id text, name text, charts jsonb,
11//   is_public boolean default false, share_code text unique, created_at timestamp
12// Set up Replit Auth middleware. Install multer and csv-parse for file handling.
13// Create server/index.js with all routes and start the server on 0.0.0.0.

1const multer = require('multer');
2const { parse } = require('csv-parse/sync');
3const { db } = require('../db');
4const { datasets, dataRows } = require('../../shared/schema');
5
6const upload = multer({ storage: multer.memoryStorage(), limits: { fileSize: 10 * 1024 * 1024 } });
7
8function detectType(values) {
9  const sample = values.filter(Boolean).slice(0, 20);
10  if (sample.every(v => !isNaN(Number(v)))) return 'number';
11  if (sample.every(v => !isNaN(Date.parse(v)))) return 'date';
12  return 'string';
13}
14
15router.post('/api/datasets/upload', upload.single('file'), async (req, res) => {
16  try {
17    const rows = parse(req.file.buffer.toString(), { columns: true, skip_empty_lines: true });
18    if (rows.length === 0) return res.status(400).json({ error: 'CSV is empty' });
19
20    const columnNames = Object.keys(rows[0]);
21    const columnDefinitions = columnNames.map(name => ({
22      name,
23      type: detectType(rows.map(r => r[name]))
24    }));
25
26    const [dataset] = await db.insert(datasets).values({
27      userId: req.user.id,
28      name: req.file.originalname.replace('.csv', ''),
29      sourceType: 'csv',
30      rowCount: rows.length,
31      columnDefinitions,
32      dataPreview: rows.slice(0, 10)
33    }).returning();
34
35    // Batch insert in groups of 500
36    for (let i = 0; i < rows.length; i += 500) {
37      const batch = rows.slice(i, i + 500).map(row => ({ datasetId: dataset.id, rowData: row }));
38      await db.insert(dataRows).values(batch);
39    }
40
41    res.json({ id: dataset.id, name: dataset.name, rowCount: dataset.rowCount, columnDefinitions });
42  } catch (err) {
43    res.status(500).json({ error: err.message });
44  }
45});

1const { db } = require('../db');
2const { sql, eq, and, gte, lte, like } = require('drizzle-orm');
3const { dataRows } = require('../../shared/schema');
4
5const AGGREGATES = { sum: 'SUM', count: 'COUNT', avg: 'AVG', min: 'MIN', max: 'MAX' };
6
7async function executeChartQuery(datasetId, config) {
8  const { xAxis, yAxis, groupBy, aggregate, filters = [] } = config;
9  const agg = AGGREGATES[aggregate] || 'SUM';
10
11  // Build safe parameterized query via Drizzle sql template
12  const conditions = [eq(dataRows.datasetId, datasetId)];
13  for (const f of filters) {
14    if (f.operator === 'eq') conditions.push(sql`row_data->>${f.column} = ${f.value}`);
15    if (f.operator === 'gte') conditions.push(sql`(row_data->>${f.column})::numeric >= ${Number(f.value)}`);
16    if (f.operator === 'lte') conditions.push(sql`(row_data->>${f.column})::numeric <= ${Number(f.value)}`);
17    if (f.operator === 'like') conditions.push(sql`row_data->>${f.column} ILIKE ${'%' + f.value + '%'}`);
18  }
19
20  const groupCol = groupBy || xAxis;
21  const result = await db.execute(
22    sql`SELECT
23          row_data->>${sql.raw(`'${groupCol}'`)} AS label,
24          ${sql.raw(agg)}((row_data->>${sql.raw(`'${yAxis}'`)})::numeric) AS value
25        FROM data_rows
26        WHERE ${and(...conditions)}
27        GROUP BY row_data->>${sql.raw(`'${groupCol}'`)}
28        ORDER BY value DESC
29        LIMIT 500`
30  );
31
32  return result.rows;
33}
34
35module.exports = { executeChartQuery };

1// Prompt to type into Replit Agent:
2// Add these routes to server/routes/charts.js:
3// POST /api/charts — save chart config: {name, datasetId, chartType, config}
4//   Config object: {xAxis, yAxis, groupBy, aggregate, filters, colorScheme}
5//   Return the saved chart with id
6// GET /api/charts — list user's charts with dataset name joined
7// GET /api/charts/:id/data — execute the chart query using queryEngine.js,
8//   return {chartType, config, data: [{label, value}]}
9// DELETE /api/charts/:id — delete chart and remove from any dashboards
10//
11// Add these routes to server/routes/dashboards.js:
12// POST /api/dashboards — create dashboard: {name, charts: [{chartId, x, y, w, h}]}
13//   Generate share_code as 8-char alphanumeric using crypto.randomBytes(4).toString('hex')
14// GET /api/dashboards — list user's dashboards
15// PUT /api/dashboards/:id — update layout (charts array with positions)
16// PATCH /api/dashboards/:id/publish — toggle is_public boolean
17// GET /api/public/d/:shareCode — public route, no auth required,
18//   return dashboard with all chart data pre-fetched via executeChartQuery

1// Prompt to type into Replit Agent:
2// Build a React chart builder at client/src/pages/ChartBuilder.jsx:
3// 1. Dataset selector dropdown — fetches GET /api/datasets on mount
4// 2. When a dataset is selected, fetch its column_definitions and populate axis dropdowns
5// 3. Chart type picker — icon buttons for: line, bar, area, pie, scatter, radar
6//    Use simple SVG icons or emoji labels (e.g. 📈 Line, 📊 Bar)
7// 4. Axis configuration panel:
8//    - X Axis: dropdown of all column names
9//    - Y Axis: dropdown of numeric columns only (filter by type === 'number')
10//    - Aggregate: select (sum, count, avg, min, max)
11//    - Group By: optional dropdown
12//    - Color Scheme: input for a color hex string
13// 5. Preview button — calls GET /api/charts/:id/data or POST /api/charts/preview
14//    Render the result with recharts:
15//    - line/area → LineChart/AreaChart with XAxis, YAxis, Tooltip, Line/Area
16//    - bar → BarChart with Bar
17//    - pie → PieChart with Pie and Cell per segment
18//    - scatter → ScatterChart with Scatter
19//    - radar → RadarChart with Radar and PolarGrid
20// 6. Save button — POST /api/charts with name input, redirect to dashboard builder

1// Prompt to type into Replit Agent:
2// Add a PostgreSQL retry wrapper to server/db.js:
3// const { drizzle } = require('drizzle-orm/node-postgres');
4// const { Pool } = require('pg');
5// const pool = new Pool({
6//   connectionString: process.env.DATABASE_URL,
7//   max: 5,
8//   idleTimeoutMillis: 30000,
9//   connectionTimeoutMillis: 5000
10// });
11// pool.on('error', (err) => console.error('DB pool error', err));
12// module.exports.db = drizzle(pool);
13//
14// Also ensure server/index.js binds to 0.0.0.0 not localhost:
15// app.listen(process.env.PORT || 3000, '0.0.0.0', ...)
16//
17// Open the Secrets panel (lock icon in sidebar) and add:
18// DATABASE_URL — already set by Replit's built-in PostgreSQL
19// SESSION_SECRET — any random 32-character string
20// Then click Deploy → Autoscale in the top-right menu.