Pinterest Stumbleupon Whatsapp
Ads by Google

sqlite database browserHave you ever found yourself in need of a database, but you can’t really afford Microsoft Access, and you certainly can’t afford to install and operate an Oracle server at home. Whether your goal is to practice your hand with learning SQL commands on a real, live database, or you just would like to have a simple and highly portable database, the SQLite Browser is exactly what you need.

I really, really like the power of SQL, as you might have noticed from my article on how to edit thousands of WordPress articles How To Edit Thousands of Wordpress Posts With Just One SQL Command How To Edit Thousands of Wordpress Posts With Just One SQL Command Growing a blog takes years, and a whole lot of pain and struggle. Then there are the major transitions, when you realize that you have to make some major changes, and it's going to take... Read More with one SQL command. These are the sorts of cool things you can accomplish if you have some familiarity with SQL calls and the structure of databases.

That knowledge and experience doesn’t come overnight, it comes from playing around with SQL calls on a live database. It isn’t always easy to do that when the only database you might have access to is one at work. Well, using SQL Lite, you can create your own personal SQL database, fill it with as much data of all types, and then practice new SQL commands on that data to see how the results look.

Of course, another more simple use for SQLite Browser is to create a simple database to store any information you’d like to have in a searchable, database format. This is probably the quickest, easiest way to do so without the need to install or configure an actual database.

Creating a Database with SQLite

Whether you want to practice SQL programming or just want a simple database to store info, the starting point is the same. You’re going to need to create a database.

When you first run the SQLite Database Browser, you’ll see a straightforward main window with a menu bar, a toolbar and three tabs. When you first get started, obviously there won’t be any database structure available, so the main display area will be blank.

Ads by Google

sqlite database browser

Click on “File” and you can either click on “New Database” or click on “Import” to import data that you might already have in some other format, like an existing database from an SQL file, or an Excel table that you’ve exported to a CSV file. Either formats can be imported into your new SQLite database.

sqlite browser

If you want to start from scratch, then click “New Database”, and you’ll need to create the structure of your database. Create your first table, add database fields to that table, and define the format for each field (text, number, etc…).

sqlite browser

Each database field can be a string (text), a number (numeric), a blob (binary data) or an integer key.

sqlite browser

When you’re done creating your first table in the database, you’ll see the structure under the Database Structure tab on the main window. As you create each table in the database, you’ll see the tree start forming that will contain all tables and the fields within them. This is a fast, quick overview of what your entire database looks like, and an easy way to navigate it once it’s starts growing.

sqlite

Viewing and manipulating your database data is as simple as clicking the “Browse Data” tab and editing the records directly. This is also where you can create new data records, delete records, or search for data within very large tables.

Of course, the most important feature – at least the main reason that I installed the software – is the “Execute SQL” tab, where you can enter your SQL command strings that you want to run on your database. When you click on “Execute query”, you’ll see the results of the query in the “Data returned” field. Or…you’ll see the error message. Hopefully you won’t see many of those!

However, as a tool to learn SQL, the error message field is kind of nice because it tells you what you’re doing wrong. You can use that as a clue to rework your SQL statement and try again.

sqlite

If you want to get into more advanced database management as well, SQLite Database Browser does offer the ability to create an index for your database as well. This is the starred table icon in the icon toolbar.

sqlite

Another nice feature, especially if you’re using this as an SQL learning tool like I have, is the SQL Log window that you can open up by clicking on the “Log” icon in the toolbar. This shows you a complete log of all SQL statements that have been executed. This is nice when you’ve just gotten lost and your query is completely messed up from all of the tweaks you’ve tried. You can go back into the log and find the original version of your query before it got all twisted up.

sqlite database browser

SQLite Browser is a sweet application to get started with database programming and if you want to quickly create a personal database to store some data that you’ve got kicking around. Having it in such a database gives you the added benefit of conducting SQL queries on it, which you couldn’t really do with the data if it’s just in some spreadsheet.

So, give SQLite Browser a try and see if it gives your SQL programming skills a bit of a boost. Share your experiences and thoughts with us in the comments section below.

Image Credits: Database Structure via Shutterstock

  1. Ventura
    October 15, 2012 at 5:36 pm

    This application is very good. I try other but is very easy. Tranks.

  2. RoelGellings
    October 1, 2012 at 5:41 pm

    It's a nice tutorial, but the progam fails to bad...
    You can't even insert a new field below another field, it always creates the field at the top, so you have to create a total new table if you want to add one field? is this serious, or is there somewhere hidden a way to do this?

    • dumdum
      May 20, 2016 at 3:13 pm

      try clicking the move field up button

  3. Gaz
    September 3, 2012 at 2:27 am

    Ryan
    My nurse wife uses her iPad to manage her baby clinic files on the go. I have used Numbers for iPad to set up a Patient Data table containing patient information - Patient ID (primary key), name, DOB, address, etc). Also an Activity Table - Activity ID (primary key), date, weight, length, head circumference, immunisationID, etc. Simple - only two tables. But I want relational database type functionality to produce easy to read reports.
    In an attempt to produce an Individual Child Growth Report I constructed a sheet using the LOOKUP function to call up the required fields from the Client table and Activity table. This function was nested inside an IF function to test each Activity record for PatientID= nominated PatientID (returning a dash for false and LOOKUP function for true). This resulted in a long array of mostly dashes, with the required data halfway down the list of records. Very clunky. Because it has to LOOKUP every single Activity record.
    Is there some iPad app that would allow me to specify a one to many relationship between my two tables, and to use SQL commands to SELECT where (criteria), etc.
    Very longwinded comment - sorry, but I hope you know of an app that offers relational database functionality without me needing to get into a lot of iPad application coding - way beyond me.
    Thanks
    Gaz

  4. Gian Singh
    July 17, 2012 at 10:20 pm

    want to learn sql

  5. sachin
    June 20, 2012 at 9:32 am

    Hi, How to this browser connect my Activity class .

  6. Scutterman
    April 26, 2012 at 7:45 pm

    I already have a wamp server installed, so I can run that and use the MySql server if I ever need one. I'm more used to accessing databases through php anyway - I keep forgetting the semicolon when I'm working in a sql prompt.

    MySql also has more data types. It can seem daunting at first, but it's good when you get used to them.

    I use phpMyAdmin for some tasks - like creating tables and adding fields - but that's more convenience than anything else.

    • Ryan Dube
      May 18, 2012 at 2:43 pm

      Yes, I like phpMyAdmin as well and use it for just about all of my websites & blogs. What a like about SQLite is the ability to develop actual applications using very small/simple SQL databases that you could run on any standard PC.

Leave a Reply

Your email address will not be published. Required fields are marked *