dev.routes.js
import express from "express";
import { DB } from "../../database/models/index.js";
const contractRouter = express.Router();
contractRouter.get("/db-view",async (req, res, next) => {
try {
const tables = await DB.sequelize.query("SHOW TABLES", { type: DB.Sequelize.QueryTypes.SHOWTABLES });
res.render("view", { tables, records: null, selectedTable: null, page: 1, totalPages: 0,queryResult: null,schema: null, selectedSchemaTable: null, query: null }
//,(err,html)=>{console.log(["error_view",err,html])}
);
} catch (error) {
console.error(error);
res.status(500).send("Error fetching tables");
}
});
// Route to fetch table schema
contractRouter.get("/schema/:name", async (req, res) => {
try {
const { name } = req.params;
const tables = await DB.sequelize.query("SHOW TABLES", { type: DB.Sequelize.QueryTypes.SHOWTABLES });
const schema = await DB.sequelize.query(`DESCRIBE ${name}`, { type: DB.Sequelize.QueryTypes.DESCRIBE });
res.render("view", { tables, records: null, selectedTable: null, page: 1, totalPages: 0, queryResult: null, schema, selectedSchemaTable: name, query: null });
} catch (error) {
res.status(500).send("Error fetching schema");
}
});
// Route to execute user query
contractRouter.post("/execute-query", async (req, res) => {
try {
const { query } = req.body;
const queryResult = await DB.sequelize.query(query, { type: DB.Sequelize.QueryTypes.SELECT });
const tables = await DB.sequelize.query("SHOW TABLES", { type: DB.Sequelize.QueryTypes.SHOWTABLES });
if(typeof queryResult == 'object'){
res.render("view", { tables, records: null, selectedTable: null, page: 1, totalPages: 0, queryResult: JSON.stringify(queryResult, null, 2), schema: null, selectedSchemaTable: null,query });
}else{
res.render("view", { tables, records: null, selectedTable: null, page: 1, totalPages: 0, queryResult: queryResult , schema: null, selectedSchemaTable: null,query });
}
} catch (error) {
console.log(error);
res.status(500).send("Error executing query");
}
});
contractRouter.get("/table/:name", async (req, res) => {
try {
const { name } = req.params;
const { page = 1 } = req.query;
const limit = 10;
const offset = (page - 1) * limit;
const tables = await DB.sequelize.query("SHOW TABLES", { type: DB.Sequelize.QueryTypes.SHOWTABLES });
const records = await DB.sequelize.query(`SELECT * FROM ${name} ORDER BY id DESC LIMIT ${limit} OFFSET ${offset}`, { type: DB.Sequelize.QueryTypes.SELECT });
const countData = await DB.sequelize.query(`SELECT COUNT(*) as count FROM ${name}`, { type: DB.Sequelize.QueryTypes.SELECT });
const totalRecords = countData[0].count;
const totalPages = Math.ceil(totalRecords / limit);
res.render("view", { tables, records, selectedTable: name, page: parseInt(page), totalPages,queryResult: null,schema: null, selectedSchemaTable: null, query: null }
//,(err,html)=>{console.log(["error_view",err,html])}
);
} catch (error) {
console.log(["error_view",error])
res.status(500).send("Error fetching records");
}
});
export default contractRouter;
view.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Database Tables</title>
</head>
<body>
<form method="GET" action="/">
<label for="table">Select Table:</label>
<select name="table" id="table" onchange="location = '/api/dev/schema/' + this.value;">
<option value="">--Select--</option>
<% tables.forEach(table => { %>
<option value="<%= table %>" <%= table === selectedTable ? 'selected' : '' %>><%= table %></option>
<% }) %>
</select>
</form>
<form method="GET" action="/">
<label for="table">Select Table:</label>
<select name="table" id="table" onchange="location = '/api/dev/table/' + this.value;">
<option value="">--Select--</option>
<% tables.forEach(table => { %>
<option value="<%= table %>" <%= table === selectedTable ? 'selected' : '' %>><%= table %></option>
<% }) %>
</select>
</form>
<form method="POST" action="/api/dev/execute-query">
<label for="query">Enter SQL Query:</label><br>
<textarea name="query" id="query" rows="4" cols="50"><%= query ? query : '' %></textarea><br>
<button type="submit">Execute</button>
</form>
<% if (queryResult) { %>
<pre id="query-result"><%= queryResult %></pre>
<% } %>
<!-- Display Table Schema -->
<% if (schema) { %>
<h3>Schema for Table: <%= selectedSchemaTable %></h3>
<table border="1">
<tr>
<th>Field</th>
<th>Type</th>
<th>Null</th>
<th>Default</th>
<th>Key</th>
<th>AutoIncrement</th>
<th>Comment</th>
</tr>
<% Object.keys(schema).forEach(field => { %>
<tr>
<td><%= field %></td>
<td><%= schema[field].type %></td>
<td><%= schema[field].allowNull %></td>
<td><%= schema[field].defaultValue %></td>
<td><%= schema[field].primaryKey %></td>
<td><%= schema[field].autoIncrement %></td>
<td><%= schema[field].comment %></td>
</tr>
<% }) %>
</table>
<% } %>
<% if (records && records.length > 0) { %>
<table border="1">
<tr>
<% Object.keys(records[0]).forEach(column => { %>
<th><%= column %></th>
<% }) %>
</tr>
<% records.forEach(record => { %>
<tr>
<% Object.values(record).forEach(value => { %>
<td><%= value %></td>
<% }) %>
</tr>
<% }) %>
</table>
<% if (totalPages > 1) { %>
<div>
<% for (let i = 1; i <= totalPages; i++) { %>
<a href="/table/<%= selectedTable %>?page=<%= i %>"><%= i %></a>
<% } %>
</div>
<% } %>
<% } %>
</body>
<script src="https://cdn.jsdelivr.net/gh/caldwell/renderjson@master/renderjson.js"></script>
<script type="text/javascript">
const queryResult = document.getElementById('query-result');
queryResult.appendChild(renderjson(JSON.parse(queryResult.innerText)));
</script>
</html>
router.js
import dev from "../modules/dev/dev.routes.js";
const router = express.Router();
router.use("/dev",dev);index.js
import ejs from "ejs";
const appServer = express();
appServer.set("view engine", "ejs");