Database Management Systems typically require big installations and configuration to get running. I’ve always found this to be a major threshold for working with databases, especially if you only want to test some simple queries or if you are learning SQL and something to fiddle around with.
SQLite is a database engine intended for embedding in application. For example, the Firefox uses it to store internal data. It does not require installation and the database is contained in a single file. Here I will present two free database managements tools that lets you work against a SQLite database file, similar to tools like Microsoft SQL Server Management Studio. I’ve found these quite useful for learning and testing how SQL works in various situations.
SQLiteSpy is a tiny application that offers a tabbed interface with two panes in each tab, one for your query and one for the result. I really like this litte app. It’s fast and easy to work with, but pretty much everything is done by running SQL queries, so you need to know your CREATE commands if you want to make your own database. It does however includes a useful database called World.db3 that is perfect for testing SELECT queries against! Another cool feature is the fact that if you just issue a CREATE TABLE statement without first creating a new database, the new table ends up in the “main” database, which is not save on disk and only exists in memory until you close the program.
SQLite Admin is a more advanced utility. Besides running queries, it offers a graphical interface for creating and editing tables, triggers, views and indexes, as well as editing the table data. Other features include code completion, an import feature and generating create statements for a database object. The GUI is however sometime quirky and slow. Also, the developer does not seem to update the program anymore.
For designing a database, SQLite Admin is a better choice thanks to its graphical interface, but after that I prefer the speed and simplicity of SQLiteSpy when running queries. All the extra features in SQLite Admin sure are nice, but if you are into learning SQL this kind of defeats the purpose. (Tip: When you save your database in SQLite Admin it gets a “s3db” file extension. SQLiteSpy only looks for db3 files when opening files. But it can still read the s3db files: Either rename .s3db to .db3, or have the SQLiteSpy open dialog box simply list all files.)
Both of these apps are delivered as zip-files – just unzip and run! SQLiteSpy is fully portable. Unfortunately SQLite Admin writes its settings in the AppData system folder. Since databases are saved as a single files they can easily be sent to for example a study buddy. Or just save both program and your databases on a USB stick and bring with you!