sqlite-web

Web-based SQLite database browser written in Python

3,326

329

3,326

View on GitHub

Top Related Projects

sqlitebrowser

20,897

Official home of the DB Browser for SQLite (DB4S) project. Previously known as "SQLite Database Browser" and "Database Browser for SQLite". Website at:

sql.js-httpvfs

3,449

Hosting read-only SQLite databases on static file hosters like Github Pages

sqlitestudio

4,529

A free, open source, multi-platform SQLite database manager.

sql.js

12,535

A javascript library to run SQLite on the web.

Quick Overview

The sqlite-web project is a lightweight web-based SQLite database management tool. It provides a user-friendly interface for interacting with SQLite databases, allowing users to view, edit, and manage their data directly from a web browser.

Pros

Simplicity: sqlite-web is a lightweight and easy-to-use tool, making it accessible for both developers and non-technical users.
Cross-platform: The application can be run on various operating systems, including Windows, macOS, and Linux.
Embedded Database: SQLite is an embedded database, which means it can be easily integrated into applications without the need for a separate database server.
Open-source: The project is open-source, allowing for community contributions and customizations.

Cons

Limited Features: While sqlite-web provides basic database management functionality, it may lack some advanced features found in more comprehensive database management tools.
Security Concerns: As with any web-based application, there are potential security risks that users should be aware of and address accordingly.
Dependency on SQLite: The tool is tightly coupled with SQLite, which may be a limitation for users who require support for other database engines.
Lack of Active Development: The project appears to have a relatively low level of active development, which could impact its long-term maintenance and support.

Code Examples

Here are a few examples of how to use the sqlite-web library:

Launching the SQLite Web Application:

from sqlite_web import app

if __name__ == "__main__":
    app.run(debug=True)

This code launches the sqlite-web application in debug mode.

Connecting to a SQLite Database:

from sqlite_web.app import db

db.connect("path/to/your/database.db")

This code connects to a SQLite database file located at "path/to/your/database.db".

Executing SQL Queries:

from sqlite_web.app import db

result = db.execute("SELECT * FROM users WHERE id = ?", [1])
for row in result:
    print(row)

This code executes a SQL query to retrieve all rows from the users table where the id column is equal to 1.

Modifying Database Data:

from sqlite_web.app import db

db.execute("INSERT INTO users (name, email) VALUES (?, ?)", ["John Doe", "john.doe@example.com"])
db.commit()

This code inserts a new row into the users table with the provided name and email values, and then commits the changes to the database.

Getting Started

To get started with sqlite-web, follow these steps:

Install the required dependencies:

pip install sqlite-web

Launch the application:

from sqlite_web import app

if __name__ == "__main__":
    app.run(debug=True)

Open your web browser and navigate to http://localhost:5000 to access the SQLite web interface.
Connect to your SQLite database by clicking the "Connect" button and providing the path to your database file.

Once connected, you can explore your database, view and edit data, and execute SQL queries directly within the web interface.

Competitor Comparisons

sqlitebrowser

20,897

Official home of the DB Browser for SQLite (DB4S) project. Previously known as "SQLite Database Browser" and "Database Browser for SQLite". Website at:

Pros of SQLite Browser

Full-featured desktop application with a graphical user interface
Supports multiple database files and SQL execution
Offers advanced features like database structure modification and data import/export

Cons of SQLite Browser

Requires installation and is not web-based
May have a steeper learning curve for beginners
Less suitable for quick, lightweight database viewing

Code Comparison

SQLite Browser (C++):

void DBBrowserDB::updateSchema()
{
    sqlite3_stmt* stmt;
    QString sql = "SELECT sql FROM sqlite_master WHERE sql IS NOT NULL";
    int result = sqlite3_prepare_v2(_db, sql.toUtf8(), -1, &stmt, NULL);
    if(result == SQLITE_OK)
    {
        while(sqlite3_step(stmt) == SQLITE_ROW)
        {
            QString schema = QString::fromUtf8((const char*)sqlite3_column_text(stmt, 0));
            // Process schema...
        }
        sqlite3_finalize(stmt);
    }
}

sqlite-web (Python):

@app.route('/<table>/content')
@require_table
def table_content(table):
    page_number = request.args.get('page') or '1'
    order_by = request.args.get('order_by')
    order = request.args.get('order')

    dataset = ds_table.find(
        order_by=order_by,
        order=order,
        page=page_number,
        page_size=app.config['rows_per_page'])

    return render_template(
        'table_content.html',
        table=table,
        dataset=dataset,
        total_pages=dataset.pages,
        total_rows=dataset.total)

Both projects serve different purposes and target different user groups. SQLite Browser is a comprehensive desktop application, while sqlite-web is a lightweight, web-based solution for quick database viewing and manipulation.

sql.js-httpvfs

3,449

Hosting read-only SQLite databases on static file hosters like Github Pages

Pros of sql.js-httpvfs

Runs entirely in the browser, allowing for client-side SQLite database querying
Supports virtual file system for efficient loading of large databases
Enables working with read-only SQLite databases without server-side processing

Cons of sql.js-httpvfs

Limited to read-only operations on the database
Requires more client-side resources for processing
May have slower performance for very large databases compared to server-side solutions

Code Comparison

sqlite-web (Python):

@app.route('/<table>/content')
def table_content(table):
    page_number = request.args.get('page') or '1'
    page_number = int(page_number)
    data = dataset.query(table, page=page_number)
    return render_template('table_content.html', data=data, table=table)

sql.js-httpvfs (JavaScript):

const db = await createDbWorker(
  [{ from: "inline", config: { serverMode: "full" } }],
  "/path/to/database.sqlite3"
);
const result = await db.db.exec("SELECT * FROM mytable LIMIT 10");
console.log(result[0].values);

The code snippets demonstrate the different approaches: sqlite-web uses server-side Python to handle database queries and render templates, while sql.js-httpvfs operates client-side in JavaScript, allowing direct database interaction in the browser.

sqlitestudio

4,529

A free, open source, multi-platform SQLite database manager.

Pros of SQLiteStudio

More feature-rich desktop application with a graphical user interface
Supports multiple database connections and advanced SQL editing capabilities
Offers data import/export functionality and database structure modifications

Cons of SQLiteStudio

Requires installation and is not web-based like sqlite-web
May have a steeper learning curve due to its more complex interface
Larger file size and resource consumption compared to the lightweight sqlite-web

Code Comparison

SQLiteStudio (C++):

void SQLiteStudio::init()
{
    initializeServices();
    initializePlugins();
    initializeWidgets();
}

sqlite-web (Python):

def initialize_app():
    app.config.from_object(__name__)
    app.config.from_envvar('SQLITE_WEB_SETTINGS', silent=True)
    db.init_app(app)

Both projects provide initialization functions, but SQLiteStudio's approach is more modular and object-oriented, reflecting its desktop application nature. sqlite-web's initialization is simpler and focused on web application configuration.

sql.js

12,535

A javascript library to run SQLite on the web.

Pros of sql.js

Runs entirely in the browser, allowing for client-side SQLite operations without a server
Supports both Node.js and browser environments
Provides a JavaScript API for direct interaction with SQLite databases

Cons of sql.js

Limited built-in user interface, requiring more development effort for visual database management
May have performance limitations for large databases due to in-browser execution
Lacks some advanced features found in server-side SQLite management tools

Code Comparison

sqlite-web (Python):

@app.route('/<database>/table/<table>/content')
def table_content(database, table):
    db = sqlite3.connect(database)
    cursor = db.cursor()
    cursor.execute(f'SELECT * FROM {table}')
    return render_template('table_content.html', rows=cursor.fetchall())

sql.js (JavaScript):

const sqlJs = await initSqlJs();
const db = new sqlJs.Database();
db.run("CREATE TABLE test (col1, col2);");
db.run("INSERT INTO test VALUES (?,?), (?,?)", [1, 111, 2, 222]);
const result = db.exec("SELECT * FROM test");
console.log(result);

Both projects provide SQLite database management capabilities, but with different approaches. sqlite-web offers a web-based interface for managing SQLite databases, while sql.js focuses on providing a JavaScript API for SQLite operations directly in the browser or Node.js environment.

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot

README

sqlite-web is a web-based SQLite database browser written in Python.

Project dependencies:

Installation

$ pip install sqlite-web

Usage

$ sqlite_web /path/to/database.db

Features

Works with your existing SQLite databases, or can be used to create new databases.
Add or drop:
- Tables
- Columns (with support for older versions of Sqlite)
- Indexes
Export data as JSON or CSV.
Import JSON or CSV files.
Browse table data.
Insert, Update or Delete rows.

Screenshots

The index page shows some basic information about the database, including the number of tables and indexes, as well as its size on disk:

The structure tab displays information about the structure of the table, including columns, indexes, triggers, and foreign keys (if any exist). From this page you can also create, rename or drop columns and indexes.

Columns are easy to add, drop or rename:

The content tab displays all the table data. Links in the table header can be used to sort the data:

The query tab allows you to execute arbitrary SQL queries on a table. The query results are displayed in a table and can be exported to either JSON or CSV:

The import tab supports importing CSV and JSON files into a table. There is an option to automatically create columns for any unrecognized keys in the import file:

The export tab supports exporting all, or a subset, of columns:

Basic INSERT, UPDATE and DELETE queries are supported:

Command-line options

The syntax for invoking sqlite-web is:


$ sqlite_web [options] /path/to/database-file.db

The following options are available:

-p, --port: default is 8080
-H, --host: default is 127.0.0.1
-d, --debug: default is false
-l, --log-file: filename for application logs.
-x, --no-browser: do not open a web-browser when sqlite-web starts.
-P, --password: prompt for password to access sqlite-web. Alternatively, the password can be stored in the "SQLITE_WEB_PASSWORD" environment variable, in which case the application will not prompt for a password, but will use the value from the environment.
-r, --read-only: open database in read-only mode.
-R, --rows-per-page: set pagination on content page, default 50 rows.
-Q, --query-rows-per-page: set pagination on query page, default 1000 rows.
-T, --no-truncate: disable ellipsis for long text values. If this option is used, the full text value is always shown.
-e, --extension: path or name of loadable extension(s). To load multiple extensions, specify -e [path] for each extension.
-f, --foreign-keys: enable foreign-key constraint pragma.
-u, --url-prefix: URL prefix for application, e.g. "/sqlite-web".
-c, --cert and -k, --key - specify SSL cert and private key.
-a, --ad-hoc - run using an ad-hoc SSL context.

Using docker

A Dockerfile is provided with sqlite-web. To use:


$ cd docker/  # Change dirs to the dir containing Dockerfile
$ docker build -t coleifer/sqlite-web .
$ docker run -it --rm \
    -p 8080:8080 \
    -v /path/to/your-data:/data \
    -e SQLITE_DATABASE=db_filename.db \
    coleifer/sqlite-web

Top Related Projects

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot