Adobe Lightroom and SQL Lite

I've been messing around with Adobe Lightroom 4 and SQL Lite recently trying to find a way to glean some useful information from my library catalog. Why? To learn something about it, to learn some new skills and why not.

So I downloaded SQLLiteSpy and hooked it up to my Lightroom catalog.

WARNING: don't do this while Lightroom is running or you could hose your catalog. It's always best to make a copy of one of your backup catalogs first, then work with that one. Really test the beejezus out of anything that does write operations. 

DISCLAIMER: I will not be held responsible for anything that you do to your catalog, images, computer,  mental health, others, etc. 

OK, if you are still reading and want to play around with your catalog then try hooking up SQLLiteSpy to your Lightroom catalog. You will have to tell it to look for .lrcat file extensions so pick "Any file".

You should see a whole bunch of tables with Adobe or Ag prefixes. This is good.

Adobe SQLLite database
There are many interesting tables but you should first look at Adobe_images, AgLibraryFile, AgLibraryFolder and AgLibraryRootFolder. These tables are a great place to start and figure out the relationships of the data.
I was fiddling about with these tables and came up with the following query:
root.absolutePath || folder.pathFromRoot || file.baseName || '.' || file.extension AS "FILEPATH"
FROM AgLibraryFile file
JOIN AgLibraryFolder folder ON file.folder = folder.id_local
JOIN AgLibraryRootFolder root ON folder.rootFolder = root.id_local
This query will show you the paths for all your images in the catalog.

Date and Time

I found that dates were the trickiest bit to figure out. Lightroom uses the SQL Lite REAL data type but there is absolutely no documentation on how they store it. I searched high and low on the internet and didn't come up with anything useful.

Using SQL Lite to find the current time you can do something like the following.
SELECT datetime('now', 'localtime');
But to figure out how Lightroom actually stores dates and times took some more effort. I read all the documentation on SQL Lite dates and times, searched all over the place for tutorials, etc. and came up with squat. It's like a coveted secret like the holy grail or how they get the caramilk inside the chocolate bar.

Well here it is, here is the secret. They use the year 2001 as their epoch. Weird! Anyway to calculate a date you need to do the following:
SELECT modTime, datetime(modTime,'unixepoch','localtime', '+31 years') FROM AgLibraryFile;
  • localtime adds/subtracts your timezone offset
  • unixepoch uses the unix start date of 1970-JAN-01
  • +31 years adds 31 years to the unixepoch to make it 2001
This will give you a proper date based on your timezone offset. Horray!


Now, what I really wanted to attempt isn't available in Lightroom, so I thought I would write a query to do it. I wanted to get a set of records that were exported to my hard drive within the past 90 days. Why? Why not, and I might be able to use some kind of script (Perl, bash, batch, etc.) to work with that data. I wish Lightroom had this functionality. Maybe the Lightroom gods will listen and add this in a future release...
  root.absolutePath || folder.pathFromRoot || file.baseName || '.' || file.extension AS "FILEPATH"
FROM AgLibraryFile file
JOIN AgLibraryFolder folder ON file.folder = folder.id_local
JOIN AgLibraryRootFolder root ON folder.rootFolder = root.id_local
JOIN Adobe_images image ON image.rootFile = file.id_local
JOIN Adobe_libraryImageDevelopHistoryStep step ON step.image = image.id_local
WHERE julianday('now', 'localtime') - julianday(step.dateCreated,'unixepoch','localtime', '+31 years') <= 90 
AND step.name LIKE ('Export - H%')
Good Luck and happy hacking.



The debate over RAW vs. JPEG has been around since digital photography existed. Unfortunately it is not as black and white as people try to make it out to be. Each format has its uses and like any tool at your disposal, it's all about knowing when to use it.


This file format is the raw data captured by the cameras sensor. Nothing is done to the data, it is lossless. It is written to the file as it is captured. The camera makes no assumptions about how it should interpret the data. The camera records all the relevant settings on the camera like aperture, shutter speed, ISO, lens data, white balance selection, etc. 


Stands for Joint Photographic Experts Group. The format was released in 1992 for use on the web for photographic images. It is a lossy format, in that data is discarded when the image is created. JPEG is a visual format and is designed for perceptual viewing. The algorithms it uses to encode the data rely on the fact that the human eye can't see certain small changes in an images colour, contrast and details.

When recording a JPEG image you can select the quality of the image. This determines how much data is preserved in the final product. Cameras usually describe this as Basic, Normal and Fine. In software such as Adobe Photoshop this is usually a percentage from 1-100. The higher the percentage the less information is discarded. The downside is that the file is larger in size.


File Sizes

The chart below shows a simple example of a single image comparing a RAW image to the same image saved as a JPEG. The JPEG image can save a lot of space on your cameras memory card or computer hard drive. At 90% a JPEG takes about 1/3 the space of a RAW image.

Below 50% JPEG images start to show compression artifacts. The image may start to look blocky. This is due to the compression algorithm used. It really depends on the image, but generally darker areas show this effect sooner than highlights. Since cameras usually only have 2 or 3 JPEG settings selecting Normal is probably the lowest you should go.

Percent %102030405060708090100
RAW Size (Kb)n/a33075
JPEG Size (Kb)653785919115216262643391960671004614868

Bit Depth

Raw images can be anywhere from 10 bits / colour channel all the way up to 14 bits. JPEG images are limited to 8 bits. For the average person this is gobbledygook. Where it matters is when you need to edit the image. The higher the bit depth of your image the more data that is recorded. This is the crux of the argument of RAW vs. JPEG.

A 14 bit RAW image captures 214 bits / colour channel which is 16384 distinct shades. On the other hand a JPEG image has 28 bits / colour channel or 256 distinct shades. Please refer to the image below to see a comparison of the various bit depths.


  • great for viewing
  • widely supported format
  • very small file sizes
  • saves quickly (great for sports or journalism)
  • more data for editing


So like most things in life it really depends on what you want to do with the end result. If you intend to edit the image you should be shooting RAW. RAW images are also better since it a record of the sensor data as captured by the camera. It provides proof that the image has not been tampered with and requires specialized software to view it.

If you are just taking snapshots and are simply going to upload them to Facebook or Instagram then JPEG is probably the better choice. Also if you take lots of photos then JPEG might save you some money since RAW images are so much larger in size.

PS: When I go out shooting I shoot both RAW and JPEG. Why? My camera supports two memory cards. So the first card has all RAW images and the second card has all JPEGs. This is for safety just in case one card dies. The second card also allows me to upload JPEG images right away if I need to.

More Information

To learn more about RAW images check out https://en.wikipedia.org/wiki/Raw_image_format.
To learn more about JPEG images go to https://en.wikipedia.org/wiki/JPEG.