Have 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.

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.

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.

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…).

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

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.

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.

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.

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 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.

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?

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

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.

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.

Ryan Dube is MUO's Managing Editor. With an Electrical Engineering degree and working as an IT Programmer/Analyst, his writing focuses on life hacking and self improvement through automation. You can visit him on Twitter or his website.