Introduction to SQLite Database
SQLite Database is a lightweight, self-contained, and serverless database engine that is widely used in applications where a full-scale relational database management system (RDBMS) like MySQL or PostgreSQL would be unnecessary. It is known for its simplicity, portability, and reliability, making it an excellent choice for embedded databases in mobile applications, desktop software, and even some web-based applications.
Unlike other database management systems that require a dedicated server to function, SQLite database operates as a simple file on disk. This characteristic makes it extremely easy to deploy and use, as there is no need for complex installation, configuration, or administration. The entire database, including its schema, tables, indexes, and data, is stored in a single file, making it highly portable.
Developing Your Own Chat with Stranger System Using PHP
Follow this video for complete guidance :
SQLite Database Viewer using PHP – Full Source Code
<?php
// index.php
session_start();
class SQLiteViewer {
private $db = null;
public function connect($dbPath) {
try {
$this->db = new SQLite3($dbPath);
return true;
} catch (Exception $e) {
return false;
}
}
public function getTables() {
$tables = [];
$result = $this->db->query("SELECT name FROM sqlite_master WHERE type='table'");
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$tables[] = $row['name'];
}
return $tables;
}
public function getTableInfo($table) {
$structure = [];
$result = $this->db->query("PRAGMA table_info('$table')");
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$structure[] = $row;
}
$data = [];
$result = $this->db->query("SELECT * FROM '$table' LIMIT 100");
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$data[] = $row;
}
return ['structure' => $structure, 'data' => $data];
}
}
// Handle AJAX requests
if (isset($_POST['action'])) {
$viewer = new SQLiteViewer();
$response = ['success' => false];
switch ($_POST['action']) {
case 'connect':
$response['success'] = $viewer->connect($_POST['dbPath']);
if ($response['success']) {
$_SESSION['dbPath'] = $_POST['dbPath'];
$response['tables'] = $viewer->getTables();
}
break;
case 'getTableInfo':
if (isset($_SESSION['dbPath'])) {
$viewer->connect($_SESSION['dbPath']);
$response = $viewer->getTableInfo($_POST['table']);
$response['success'] = true;
}
break;
}
header('Content-Type: application/json');
echo json_encode($response);
exit;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>SQLite Viewer</title>
<link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.3.0/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css" rel="stylesheet">
<style>
:root {
--primary-color: #2563eb;
--secondary-color: #1d4ed8;
--light-bg: #f8fafc;
}
body {
background-color: var(--light-bg);
font-family: 'Segoe UI', system-ui, -apple-system, sans-serif;
}
.navbar {
background: linear-gradient(135deg, var(--primary-color), var(--secondary-color));
padding: 1rem;
box-shadow: 0 2px 4px rgba(0,0,0,0.1);
}
.navbar-brand {
font-size: 1.5rem;
font-weight: 600;
display: flex;
align-items: center;
gap: 0.5rem;
}
.form-control {
border-radius: 8px;
border: 1px solid #e2e8f0;
padding: 0.75rem 1rem;
box-shadow: 0 1px 2px rgba(0,0,0,0.05);
}
.form-control:focus {
border-color: var(--primary-color);
box-shadow: 0 0 0 3px rgba(37,99,235,0.1);
}
.btn-connect {
background-color: white;
color: var(--primary-color);
border: none;
padding: 0.75rem 1.5rem;
border-radius: 8px;
font-weight: 600;
transition: all 0.2s;
}
.btn-connect:hover {
background-color: #f8fafc;
transform: translateY(-1px);
}
.card {
border: none;
border-radius: 12px;
box-shadow: 0 4px 6px rgba(0,0,0,0.05);
margin-bottom: 1.5rem;
}
.card-header {
background: white;
border-bottom: 1px solid #e2e8f0;
padding: 1rem 1.5rem;
border-radius: 12px 12px 0 0 !important;
}
.card-header h5 {
color: var(--primary-color);
font-weight: 600;
margin: 0;
display: flex;
align-items: center;
gap: 0.5rem;
}
.table-list {
max-height: calc(100vh - 200px);
overflow-y: auto;
}
.list-group-item {
border: none;
padding: 0.75rem 1.5rem;
cursor: pointer;
transition: all 0.2s;
color: #475569;
display: flex;
align-items: center;
gap: 0.5rem;
}
.list-group-item:hover {
background-color: #f1f5f9;
color: var(--primary-color);
}
.list-group-item.active {
background-color: #eff6ff;
color: var(--primary-color);
border: none;
}
.table-info {
max-height: calc(100vh - 100px);
overflow-y: auto;
}
.table {
margin: 0;
}
.table thead th {
background-color: #f8fafc;
color: #475569;
font-weight: 600;
border-bottom: 2px solid #e2e8f0;
}
.table td {
padding: 0.75rem;
color: #475569;
}
</style>
</head>
<body>
<nav class="navbar">
<div class="container-fluid">
<span class="navbar-brand text-white">
<i class="fas fa-database"></i>
SQLite Viewer
</span>
<form class="d-flex" onsubmit="connectDb(event)">
<input type="text" id="dbPath" class="form-control me-2" placeholder="Enter database path...">
<button class="btn btn-connect">
<i class="fas fa-plug me-2"></i>Connect
</button>
</form>
</div>
</nav>
<div class="container-fluid mt-4">
<div class="row g-4">
<!-- Tables List -->
<div class="col-md-3">
<div class="card h-100">
<div class="card-header">
<h5>
<i class="fas fa-table"></i>
Tables
</h5>
</div>
<div class="card-body p-0 table-list">
<div class="list-group list-group-flush" id="tableList"></div>
</div>
</div>
</div>
<!-- Table Information -->
<div class="col-md-9">
<div class="card h-100">
<div class="card-header">
<h5>
<i class="fas fa-info-circle"></i>
<span id="tableTitle">Select a table</span>
</h5>
</div>
<div class="card-body table-info" id="tableInfo"></div>
</div>
</div>
</div>
</div>
<script>
async function connectDb(event) {
event.preventDefault();
const dbPath = document.getElementById('dbPath').value;
if (!dbPath) return;
const response = await fetch('index.php', {
method: 'POST',
headers: {'Content-Type': 'application/x-www-form-urlencoded'},
body: `action=connect&dbPath=${encodeURIComponent(dbPath)}`
});
const data = await response.json();
if (data.success) {
displayTables(data.tables);
} else {
alert('Failed to connect to database');
}
}
function displayTables(tables) {
const tableList = document.getElementById('tableList');
tableList.innerHTML = tables.map(table => `
<button class="list-group-item list-group-item-action" onclick="showTableInfo('${table}')">
<i class="fas fa-table"></i>
${table}
</button>
`).join('');
}
async function showTableInfo(table) {
// Update active state
document.querySelectorAll('.list-group-item').forEach(item => {
item.classList.remove('active');
if(item.textContent.trim() === table) {
item.classList.add('active');
}
});
const response = await fetch('index.php', {
method: 'POST',
headers: {'Content-Type': 'application/x-www-form-urlencoded'},
body: `action=getTableInfo&table=${encodeURIComponent(table)}`
});
const data = await response.json();
if (data.success) {
document.getElementById('tableTitle').textContent = table;
let html = `
<h6 class="text-primary mb-4">
<i class="fas fa-table me-2"></i>
Data Preview (100 rows)
</h6>
<div class="table-responsive">
<table class="table table-sm table-striped table-bordered">
<thead>
<tr>
${Object.keys(data.data[0] || {}).map(key =>
`<th>${key}</th>`
).join('')}
</tr>
</thead>
<tbody>
${data.data.map(row => `
<tr>
${Object.values(row).map(val =>
`<td>${val}</td>`
).join('')}
</tr>
`).join('')}
</tbody>
</table>
</div>`;
document.getElementById('tableInfo').innerHTML = html;
}
}
</script>
</body>
</html>
Why Use SQLite?
SQLite has several advantages that make it an attractive choice for many applications:
- Serverless Architecture: Unlike MySQL or PostgreSQL, SQLite does not require a separate server process. It is embedded within the application, reducing complexity and overhead.
- Zero Configuration: There is no need to set up a database server, making it easy to use and deploy.
- Cross-Platform Compatibility: Since SQLite is implemented as a C library, it can run on almost any operating system, including Windows, macOS, Linux, and Android.
- Compact and Lightweight: The entire SQLite library is only a few megabytes in size, making it perfect for applications that require a small footprint.
- Atomic Transactions: SQLite supports ACID (Atomicity, Consistency, Isolation, Durability) transactions, ensuring data integrity.
- Readable File Format: SQLite database files are cross-platform and can be easily copied and shared.
- Great for Prototyping: Because of its simplicity, developers often use SQLite for rapid prototyping before migrating to a more robust database.
Limitations of SQLite
Despite its many advantages, SQLite is not suitable for every use case. Some of its limitations include:
- Not Suitable for High-Concurrency Applications: Since SQLite locks the entire database during a write operation, it is not ideal for high-traffic web applications with many concurrent users.
- Limited Scalability: While it works well for small to medium-sized projects, SQLite struggles with massive datasets and high query loads.
- Fewer Features Compared to Other RDBMS: Features like user management, advanced security controls, and stored procedures are not available in SQLite.
Web-Based SQLite Viewer
To interact with an SQLite database in a user-friendly manner, a web-based SQLite viewer can be useful. This viewer allows users to connect to an SQLite database, browse its tables, and view data without using command-line tools or third-party applications.
Key Features of the SQLite Viewer
The SQLite viewer built with PHP provides the following functionalities:
- Connect to an SQLite Database: Users can enter the file path of an SQLite database to establish a connection.
- Retrieve a List of Tables: Once connected, the viewer retrieves and displays all tables within the database.
- View Table Structure: Users can examine the schema of a selected table, including column names, data types, and constraints.
- View Table Data: The first 100 rows of a table can be displayed for quick browsing.
- User-Friendly Interface: The viewer features a Bootstrap-based UI for an intuitive and responsive experience.
Advantages of Using This SQLite Viewer
- Ease of Use: No command-line interaction is needed; users can connect and browse SQLite databases through a web interface.
- Portability: The tool runs on any web server with PHP support, making it a lightweight alternative to standalone database management tools.
- Faster Data Access: Instead of manually querying the database, users can browse tables and data quickly.
- Ideal for Debugging: Developers working with SQLite databases can use this viewer to inspect and debug database contents efficiently.
SQLite is a powerful yet lightweight database engine that is well-suited for many applications, from mobile apps to lightweight web projects. A web-based SQLite viewer, as demonstrated in this article, enhances the usability of SQLite databases by providing an interactive way to explore and manage data.
By leveraging PHP and JavaScript, developers can create a robust, user-friendly SQLite viewer that simplifies database interaction. While SQLite has limitations, its benefits make it a preferred choice for small-scale applications, testing environments, and situations where a simple database solution is required. Whether you’re a developer, a data analyst, or a researcher, having an intuitive SQLite viewer at your disposal can greatly improve productivity and database management efficiency.
