SQLite Forum

Best way to keep song lyrics in SQLite
Login

Best way to keep song lyrics in SQLite

(1) By jose isaias cabrera (jicman) on 2021-11-06 20:29:38 [link]

Greetings!

I need of creating a SQLite database of songs to be able to search on words having songs with those words popup. What is the best way to do this on SQLite? I am thinking of having a songID, songTitle, songKeywords, and songLyrics. Any thought would be greatly appreciated. Thanks.

josé

(2) By Simon Slavin (slavin) on 2021-11-07 00:00:16 in reply to 1

If you're just making a database for a few thousand songs, that will do fine.  You would want to find songs which have words which include a substring, and the simplest way to do that in SQLite is to use LIKE as shown here

<https://www.sqlitetutorial.net/sqlite-like/>

If you're going to be searching hundreds of thousands of songs, it would be faster using SQLite's FTS5.  But it's extra work and then you wouldn't be asking us how to do it.

A couple of notes:

A) Song titles aren't unique.  Four different songs called "The Power of Love" have been hits.  Often two versions were written in the same year, so you can't use year as a disambiguator.

B) Many songs have different versions with different lyrics.  Often because the album version has an extra verse, or includes a swearword which would prevent airplay.  You're going to have to figure out what to do about this.

(3) By jose isaias cabrera (jicman) on 2021-11-07 02:16:32 in reply to 2 [link]

Thanks, Simon. This is great.  I think the songs are small enough that I will leave FTS5 for later.  I appreciate your input.

josé