June 29th 2011

Flex 4.5 MySQL Query Browser

I was looking for a simple MySQL query browser to provide individuals who wish to learn SQL with a tool to get started. Since I couldn’t find a satisfactory software I decided to create one.

The result consists of a Flex interface using Zend AMF for remoting, therefore PHP as backend technology, and is available for cloning and other experiments on github.

Visit the demo page or watch the screencast to see it in action.

Objectives

I wanted a simple application to query MySQL databases. From the user perspective the interface should be simple, clean and inviting. From a technical view, I wanted to get grip on the AsyncListView collection wrapper class introduced in Flex 4 which offers data paging to handle large result sets.

Features

The query browser consists of tree which displays the information about the database, its tables and fields. Auxiliary information such as table size (rows) and field types are shown alongside.

Clicking table or field nodes creates the appropriate  SQL query. For instance, if you wish to get all data from table directors, click on the the table node and a query in the form of SELECT * FROM directorsis displayed in the query input.

This is a very simple query builder, so do not expect it to create joins or multi table statements.Queries are validated using a simple Regular Expression, which tests for presence of field and table name. Hence, queries like SELECT 1+1will not validate.

The query history drop down list offers quick access to previous queries.

Results are displayed in a simple Spark Data Grid control. However, the dataProvider for this grid is an AsyncListView wrapping a PagedList, which enables data paging for large result sets.

Watch the following screencast that demonstrates the features.

 

Limitations

Obviously there are a lot of limitations compared to an application like MySQL Workbench. However, I never even thought about getting there.

A remaining limitation is the query of large tables (millions of records) with non-consecutive or without a primary key. In this scenario, I never got around working with the LIMIT clause, which is a real performance killer.

2 Responses to “Flex 4.5 MySQL Query Browser”

  1. Thanks Brian – for your blog post which helped a lot to get this thing started and your compliment.
    I would very much appreciate a feedback about the implementation.

  2. Hey, nice work. Will have more of a look through the implementation (thanks for the credits btw, very cool)

Leave a Reply

 (required)
 (required)