Summary
If you’re a music fan, you probably have shelves of records or CDs. How do you keep track of them? You could use a database, but aren’t they complicated to set up? SQLite is a powerful tool that lets you set up SQL databases without a server. It’s surprisingly easy to set up. Here’s how I did it, despite having a vague knowledge of SQL.
Why SQLite?
There’s still something about a physical music collection, even in the age of streaming. It’s why sales of vinyl records have gone up over the past several years,according to the RIAA. The hottest new music format isn’t Spotify, MP3, or FLAC, but the good old LP.
Being a Millennial, my music listening habits were formed in the tail end of the “Album Era.” That meant collecting albums. Since this was the ’90s when I started getting seriously interested in music, the dominant format was the CD, barring a few old cranks who preferred vinyl. (Who would want those old records?)
When you have a substantial physical collection of any type, not just music, it becomes hard for one person to keep track of it. This is where databases come in. The relational database, with its rows and columns, is easy to understand, but SQL has a reputation for being tricky to learn. I’d been exposed to it in a college computer information systems class, but I wanted to get more comfortable with databases and SQL.
I thought about installing a database server on my machine like MySQL or PostgreSQL, but those are big, complicated systems that need a lot of work to maintain. I’m just a guy with a keyboard and a crate of albums.
I could also have set up a database in Discogs. I do have a user account there, but I wanted a database that would run locally under my control. I could have evenset it up as a flat-file database using Linux text tools, but this wouldn’t scale to a larger collection.
SQLiteseemed ideal. It was a relational database that used SQL as the query language. It’s lightweight and it will run without a server.A lot of real-world applications have used SQLite, including iTunes. If SQLite can manage a digital music library, I figured it could easily handle a physical one. This might be why record collections are popular examples for setting up relational databases. They’re like the “Hello, world!” of databases.
Installing SQLite
Since SQLite is free and available in so many Linux distros, all I had to do was use apt in Ubuntu (it was in WSL, but it would be the same in any version of Debian or Ubuntu):
That was it. I had SQLite installed in one. This shows how easy it is to install SQLite.
I could have downloaded the native Windows version, but I just prefer Linux for programming-related stuff.Unix-like systems are the best for programming projectslarge and small, and that’s why WSL exists.
Creating the Database
With SQLite installed, it was time to start creating my database. I thought about what I would need. A database for my collection of CDs and LPs would need to have fields for the artist, title, the year of original release, and the format.
I started up the command-line client with the name of the database file I wanted to create:
I entered the SQL code to create the table:
Terminate every SQL statement with a semicolon (;) character.
The nice thing about SQLite compared to other database systems is that I didn’t need to hard-code the data types beforehand. SQLite uses dynamic typing, where it guesses what kind of data the field contains by what you put into it. If you put in a character string, it decides that field is a string. This saved me time and effort in defining the table.
While SQL statements are traditionally written in uppercase, it’s not necessary. It’s a convention, but you can enter lowercase commands since SQLite accepts either. The SQLite developers use lowercase in their documentation.
With the table created, I now had to put records into it, and I mean the database records, not vinyl records. That’s where the INSERT INTO command comes in.
I could do this for my entire collection, but there’s an easier way. Typing all of the SQL statements can be tedious, so there are several helper front-ends available. A good one isDB Browser. It’s similar toPHPMyAdmin for MySQL databases. It’s a GUI front-end thatlets you create and manipulate SQLite databases easily.
All I had to do was install that in Ubuntu as well, though it’s also available for Windows.
To install it on Ubuntu, use this command:
To work on an SQLite database, I just open the music.db file I created by going to File > Open Database > and navigating to the database file. With the database created, I can add fields with the “Browse Data” tab and then press the “Insert Record” button. It shows a table similar to a spreadsheet. I can fill in the fields for the albums I have. Behind the scenes, it uses transactions mentioned later to guard against undesired changes.
Even though this is a personal database, I want to establish some good habits. When working with data, it’s best to have a backup. The SQLite command-line client lets me save a backup copy of my database with the .backup command:
I could have called the file anything I wanted, but I just tend to use the .bak extension for backup copies of files.
When you’re working on a database, you want to have a way to roll back changes if something you do causes something bad to happen. SQLite, like many other database systems, has a transaction system. With database transactions, an operation either completes or doesn’t.
More importantly, if you make a mistake, you may roll back to previous states with the ROLLBACK command. It’s like the undo operation in a word processor.
Tom Scott learned the hard way the dangers of neglecting to use transactions and backups while working on a database:
To start a transaction, use this command:
Anything you do to the database will be shown, such as adding, deleting, or modifying records, but they won’t be saved until the end of a transaction, with the COMMIT command:
Here’s an example of adding a few albums to the database
Make a backup copy and turn on transactions before making changes to a database.
Exploring My Database
With my database slowly becoming populated, I could try querying it. By default, SQLite outputs records separated by a “|” character. To see them in columns, I set the mode:
Commands that are part of SQLite 3 like that one don’t use a semicolon at the end.
To see my entire collection, I could use the SELECT * command:
The * is a wildcard, similar towildcards on the Linux shell, that lets me select every record. Of course, I would only want to see a few records of a time. I can do that with the LIMIT command:
To find fields that matched criteria, such as artists, I could use a WHERE clause:
SQLite LIKE statements match a field partially, and they’re case-insensitive on SQLite.
Oops, I Need More Fields!
Even as carefully as you design fields in a database, it seems you need to adjust tables when you realize you need another field. I realized that it would be good to have a field for the record label. Fortunately, it was easy to change my table in SQLite.
To get a look at how the data is structured, I use the .schema command.
I opened the command-line client and typed;
I also could have done this easily through DB Browser. I just click on the albums table in the “Database Structure” tab and click the “Modify Table” function. I can add or delete rows, as well as select the types as a constraint. When I was entering the release year in DB Browser, I noticed that it wouldn’t sort the release year correctly for entries I added. I told SQLite explicitly that the release year was a number through the menu by selecting the “INTEGER” option, and that seemed to fix the problem.
It’s possible to add the label to already entered albums with an UPDATE command like this:
For simple updates, using DB Browser might be better because you can edit entries directly and it automatically uses transactions for safety. This was also the opportunity to create a view. A view is similar to asaved search. This lets you save complicated queries you do frequently.
I created one for my vinyl albums:
To get back to my view, I simply run this query as if the view was another table:
Now I can keep adding to and examining my music collection, and SQLite made it possible.
