It is a well-known fact that Lightroom catalogs are nothing more than SQLite files. In this article, we’ll take a look at how to search multiple LR catalogs at once.
Catalog Management Conundrum
Ever since I first started using Lightroom quite a few years ago, I’ve pondered on how to organize my catalogs. Should I set up multiple catalogs? Multiple folders inside the same catalog? How should I structure the folders? In the beginning, I made the decision to keep everything inside the same catalog and create separate folders for years, and within each year, folders for each month with a short description of the pictures.
Folder structure example:
– 2019
—— 01_1. Montana Winter Vacation
—— 02_1. Snow Bike Ride
—— 02_2. Trip to Chicago
This was all fine and dandy until I started taking photos that weren’t just for my personal use. I figured I should keep my personal stuff separate from my non-personal stuff. Some 7 years ago, I created a separate Lightroom catalog for my non-personal work, along with its own folder structure.
The thing is, the line that separates personal from non-personal work is very blurry these days. I have another blog that focuses on travel by bicycle and I also have another website where I sell bicycle bags. Keep in mind that my two biggest passions are: 1) Photography and 2) Cycling.
Now you can see how taking photos of bikes or taking photos while traveling by bike can both fit into the personal and non-personal work categories. More often than not, I find myself looking for a picture in my “personal” catalog, but for some reason, when I imported the photos, I thought they belonged under the “non-personal” catalog.
That’s when I ran into the need to run seaches across multiple Adobe Lightroom catalogs at the same time.
Technical Bits
Like I mentioned before, Adobe Lightroom .lrcat files are merely SQLite databases disguised as photo catalogs. This means you can run SQL queries on .lrcat files using tools built for SQLite.
While most database systems depend on a server to house the table structure (and the data), SQLite is a self-contained system that lives in a single file.
For this reason, SQLite is a very popular database system for stand-alone programs, especially if the database is not going to be shared among many users at the same time.
Let’s Search Lightroom
Alright, now that we got all that background info out of the way, let’s get into the meat and potatoes – or as they say it in Belgium, the mussels and frites – of how you can use SQLite tools to search across multiple Lightroom catalogs.
First, you’ll need a database tool. I recommend using DB Browser for SQLite. This is a free piece of software that you can run as a portable app (no need to install).
Next, I should warn you that trying to connect your .lrcat files to another program that is not Lightroom, might corrupt your catalog file. To keep things safe, make sure you have a recent backup of your .lrcat files. Also, make sure Lightroom is not running, we don’t want our searches to interfere with Lightroom’s operations. You’ve been warned. In all honesty, though, I’ve haven’t had a file corruption happen to me (yet).
Connecting Two Catalogs
Fire up DB Browser and click the Open Database button. Since Lightroom catalog files have a .lrcat extension, they are not going to show in the file browser by default. Select “All files (*)” at the bottom right of the pop-up screen and select the first .lrcat you want to open.
To open another LR catalog alongside the catalog you already have open, you have to click on the “Attach Database” button. Select the second LR catalog and open it up. Your screen should look like the screenshot below. I attached the second database as “bikeundbier” which is the name of my non-personal catalog.
Now that we have both Lightroom catalogs open, let’s move over to the Execute SQL tab. Here is where most of the magic happens. We will be using five tables in our query:
AgLibraryKeyword – houses all the keywords for a given catalog.
AgLibraryKeywordImage – this is the table that associates images with keywords.
Adobe_images – contains all the images in a catalog.
AgLibraryFile – holds image file basic information.
AgLibraryFolder – the folders where each image resides.
The SQL query below may look lengthy, but it’s pretty straightforward. Make sure to replace bikeundbier with the name you used to attach the second catalog in DB Browser. I’ve only tested this query on LR 6 and LR CC (v 8.2.1) catalogs but I would expect it to work with most Lightroom versions.
SELECT –Select statement for the first catalog
AgLibraryKeyword.name as ‘keywords’,
AgLibraryKeywordImage.image,
AgLibraryKeywordImage.tag,
Adobe_images.rootFile,
AgLibraryFile.baseName as ‘filename’,
AgLibraryFolder.pathFromRoot,
‘personal’ as ‘Catalog’ –This will identify which catalog the image lives under
FROM
AgLibraryKeywordImage,
Adobe_images,
AgLibraryKeyword,
AgLibraryFile,
AgLibraryFolder
WHERE
AgLibraryKeywordImage.image = Adobe_images.id_local
AND AgLibraryKeywordImage.tag = AgLibraryKeyword.id_local
AND Adobe_images.rootFile = AgLibraryFile.id_local
AND AgLibraryFile.folder = AgLibraryFolder.id_local
AND AgLibraryKeyword.lc_name LIKE ‘%
UNION ALL –This will add the second query to the bottom of the firt query
SELECT –Select for the second catalog. Bikeundbier is the name I used to attach the second catalog.
bikeundbier.AgLibraryKeyword.name as ‘keywords’,
bikeundbier.AgLibraryKeywordImage.image,
bikeundbier.AgLibraryKeywordImage.tag,
bikeundbier.Adobe_images.rootFile,
bikeundbier.AgLibraryFile.baseName as ‘filename’,
bikeundbier.AgLibraryFolder.pathFromRoot,
‘non-personal’ as ‘Catalog’ –This will identify which catalog the image lives under
FROM
bikeundbier.AgLibraryKeywordImage,
bikeundbier.Adobe_images,
bikeundbier.AgLibraryKeyword,
bikeundbier.AgLibraryFile,
bikeundbier.AgLibraryFolder
WHERE
bikeundbier.AgLibraryKeywordImage.image = bikeundbier.Adobe_images.id_local
AND bikeundbier.AgLibraryKeywordImage.tag = bikeundbier.AgLibraryKeyword.id_local
AND bikeundbier.Adobe_images.rootFile = bikeundbier.AgLibraryFile.id_local
AND bikeundbier.AgLibraryFile.folder = bikeundbier.AgLibraryFolder.id_local
AND bikeundbier.AgLibraryKeyword.lc_name LIKE ‘%
You can see that I have Bikepacking pictures on both catalogs. This query comes in handy when I want to know in which LR catalog a certain series of photos live in.
Conclusion
Maybe one day I’ll crack a good beer open an take the time to move all the original RAW files into the same folder structure, which would allow me to merge LR catalogs seamlessly. But this a project I’m willing to procrastinate a little longer. For now, I’ll continue to search two Lightroom catalogs at once using the SQLite query above
About the author: André Costa is the photography and bicycling enthusiast behind DPHacks, a website for digital photography hacks. The opinions expressed in this article are solely those of the author. This article was also published here.