How I Track My Music Collection With an Easy SQLite Database

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 even set it up as a flat-file database using Linux text tools, but this wouldn’t scale to a larger collection.

SQLite seemed 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):

        sudo apt install sqlite
    

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 projects large 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:

        
sqlite3 music.db
    

I entered the SQL code to create the table:

        
CREATE TABLE albums (artist,title,year,format)
    

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.

        
INSERT INTO albums VALUES ("Pink Floyd", "The Dark Side of the Moon", 1973,"LP",)
    

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 is DB Browser. It’s similar to PHPMyAdmin for MySQL databases. It’s a GUI front-end that lets 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:

        
sudo apt install sqlitebrowser
    

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:

        
.backup music.db.bak
    

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 can 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:

        BEGIN TRANSACTION;
    

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:

        COMMIT;
    

Here’s an example of adding a few albums to the database

        
BEGIN TRANSACTION;
INSERT INTO albums VALUES ("Pearl Jam","Ten",1991,"CD");
INSERT INTO albums VALUES ("Nirvana", "Nevermind",1991,"CD");
INSERT INTO albums VALUES ("Pink Floyd","Wish You Were Here", 1975,"Pink Floyd Records");
COMMIT;
    

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:

        .mode column
    

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:

        SELECT * FROM albums;
    

The * is a wildcard, similar to wildcards 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:

        SELECT * FROM albums LIMIT 5;

    

To find fields that matched criteria, such as artists, I could use a WHERE clause:

        
SELECT * FROM albums WHERE artist LIKE '%pink floyd%';
    

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;

        ALTER TABLE albums ADD label;
    

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:

        
UPDATE albums SET label = 'Factory' WHERE artist = 'Joy Division';
    

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 a saved search. This lets you save complicated queries you do frequently.

I created one for my vinyl albums:

        CREATE VIEW vinyl AS SELECT * FROM albums WHERE format = "LP";

    

To get back to my view, I simply run this query as if the view was another table:

        
SELECT * FROM vinyl;
    

Now I can keep adding to and examining my music collection, and SQLite made it possible.

Related

How I Used QR Codes and Google Sheets to Organize My Home

Stop aimlessly looking for things you know you have.