In this blog, we’ll take you through the development of a feature-rich, web-based SQLite database manager. We’ll use Flask to build a backend API and React to create an interactive frontend. Along the way, you’ll see how to handle table views, CRUD operations, and even manage foreign key relationships dynamically. This project is perfect for those who want to learn more about integrating a lightweight database with a modern web application.
The Journey to a Fully Functional Database Manager
Managing databases via the command line or desktop clients can feel clunky, especially when you’re juggling multiple datasets. What if you could have a sleek web interface for your SQLite database? With React, Flask, and a bit of CSS magic, we’ll make this dream a reality.
Project Setup
Backend: Flask API
Setting up Flask
First, create the Flask backend to handle API requests for fetching tables, managing records, and running queries. Here’s how we defined our routes for core functionalities:
Copied!@app.route('/api/list_tables', methods=['GET']) def list_tables(): """List all tables in the database.""" conn = connect_db() cur = conn.cursor() cur.execute("SELECT name FROM sqlite_master WHERE type='table'") tables = [row['name'] for row in cur.fetchall()] conn.close() return jsonify({"tables": tables})
CRUD Operations
Handling basic Create, Read, Update, Delete (CRUD) functionalities required dynamic routing for individual tables. For example, adding a record to a table dynamically looks like this:
Copied!@app.route('/api/table/<table_name>/add', methods=['POST']) def add_row(table_name): data = request.json keys = ', '.join(data.keys()) values = ', '.join(['?' for _ in data.values()]) query = f"INSERT INTO {table_name} ({keys}) VALUES ({values})" conn = connect_db() cur = conn.cursor() cur.execute(query, list(data.values())) conn.commit() conn.close() return jsonify({"status": "success"}), 201
Handling Foreign Keys
SQLite doesn’t enforce foreign key constraints unless explicitly enabled. After setting up proper relationships, the API dynamically fetches foreign key options to populate dropdowns in the frontend.
Copied!@app.route('/api/table/<table_name>/schema', methods=['GET']) def get_table_schema(table_name): """Retrieve the schema for a table, including foreign key references.""" conn = connect_db() cur = conn.cursor() cur.execute(f"PRAGMA foreign_key_list({table_name})") foreign_keys = cur.fetchall() schema = [{"name": column["name"], "foreign_key": foreign_keys.get(column["name"])} for column in cur.description] conn.close() return jsonify(schema)
Frontend: React UI
Table Management
We used React’s useEffect hook to fetch tables dynamically and display them in an interactive menu:
Copied!useEffect(() => { async function fetchTables() { const response = await axios.get("/api/list_tables"); setTables(response.data.tables); } fetchTables(); }, []);
Dynamic Form Handling
Adding and editing records dynamically adjusts form fields based on the database schema. Here’s how we handle a form for adding records:
Copied!function AddForm({ schema, onSave, onCancel }) { const [formData, setFormData] = useState(Array); useEffect(() => { const initialData = schema.reduce((acc, col) => { if (col.name !== "id") acc[col.name] = col.default || ""; return acc; }, Array); setFormData(initialData); }, [schema]); const handleSubmit = (e) => { e.preventDefault(); onSave(formData); }; return ( <form onSubmit={handleSubmit}> {schema.map((col) => col.name !== "id" ? ( <div key={col.name}> <label>{col.name}</label> <input name={col.name} value={formData[col.name]} onChange={(e) => setFormData({ ...formData, [e.target.name]: e.target.value }) } /> </div> ) : null )} <button type="submit">Add</button> <button type="button" onClick={onCancel}> Cancel </button> </form> ); }
Polishing the UI
To enhance the usability of our app, we added CSS styles for tables and forms. Here’s a snippet of our styles:
Copied!body { font-family: Arial, sans-serif; background-color: #f9f9f9; margin: 0; padding: 0; } nav { background-color: #4caf50; color: white; padding: 15px; text-align: center; } table { width: 100%; border-collapse: collapse; margin: 20px 0; } th, td { border: 1px solid #ddd; padding: 8px; text-align: left; } th { background-color: #f2f2f2; }
Advanced Features: Running Queries
For power users, we added a query executor allowing raw SQL queries to be run directly from the interface.
function QueryExecutor() {
const [query, setQuery] = useState("");
const [result, setResult] = useState([]);
const handleSubmit = async (e) => {
e.preventDefault();
const response = await axios.post("/api/query", { query });
setResult(response.data);
};
return (
<div>
<h2>Run a Query</h2>
<form onSubmit={handleSubmit}>
<textarea
value={query}
onChange={(e) => setQuery(e.target.value)}
rows="4"
cols="50"
></textarea>
<button type="submit">Run</button>
</form>
<table>
<thead>
<tr>
{result.length > 0 &&
Object.keys(result[0]).map((key) => <th key={key}>{key}</th>)}
</tr>
</thead>
<tbody>
{result.map((row, index) => (
<tr key={index}>
{Object.values(row).map((value, idx) => (
<td key={idx}>{value}</td>
))}
</tr>
))}
</tbody>
</table>
</div>
);
}Wrapping Up
With this project, we’ve demonstrated how to build a functional SQLite database manager using React and Flask. From dynamic table views to CRUD operations and schema-based forms, the app covers a wide range of real-world needs for database management.
What’s next?
We can extend this project to support more complex queries, user authentication, or even database backups. For now, enjoy managing your SQLite databases with style and ease!