Web-based SQLite Database Browser, powered by Flask and Peewee

November 13, 2014 20:28 / flask python sqlite / 1 comments

For the past week or two I've been spending some of my spare time working on a web-based SQLite database browser. I thought this would be a useful project, because I've switched all my personal projects over to SQLite and foresee using it for pretty much everything. It also dovetailed with some work I'd been doing lately on peewee regarding reflection and code generation. So it seemed like some pretty good bang/buck, especially given my perception that there weren't many SQLite browsers out there (it turns out there are quite a few, however). I'm sharing it in the hopes that other devs (and non-devs?) find it useful.

The sqlite-web project consists of a pip-installable Flask application and a console_script for running it easily from wherever it is installed. You point it at a database file, which will be created if it does not exist, and are presented with a web interface for navigating and performing common management tasks.

Here are the current features:

Works with existing SQLite databases, or can be used to create new ones.
Create or drop tables and indexes.
Add, drop or rename columns (yes, drop and rename with SQLite).
Export data as JSON or CSV.
Import JSON or CSV files, with the option to auto-create columns as needed.
Browse table data.
Run arbitrary SQL queries.

I think the Import feature is kind of exciting because it gives you the option to create new columns automatically. This gives you a workflow of:

Create a new table. It will have a single ID column.
Import your CSV or JSON file with auto-create selected.
Explore your data.

I've played around with this a bit and have enjoyed how easy is to go from a data dump to a populated database table I can run queries on. This code is still fairly new and is built on top of the DataSet extension, so if you find bugs please don't hesitate to let me know.

The one class of features I haven't really explored is manipulating existing data, i.e. updating row values, adding or removing rows. Mostly because, as a developer, the code for data manipulation lives in my app, and this tool is for browsing and manipulating the database schema.

Screenshots

Apologies for copying this mostly verbatim from the README, but I thought it'd be useful to share some screenshots since these really give an idea of how the project works.

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, foreign keys, and triggers (if any exist). From this page you can also create, rename or drop columns and indexes.

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:

Installation

If you're interested in giving it a try, you can install sqlite-web using pip:

$ pip install sqlite-web flask peewee

Then:

$ sqlite_web /path/to/database.db

Thanks for reading

I hope you find this project useful! I'd welcome any suggestions or diffs for improving the UI or features. If you find a bug or want to request a feature, feel free to open a ticket on GitHub.

Comments (1)

Tossu | nov 17 2014, at 12:22pm

Bro, I love your posts <3

Commenting has been closed.