.
Accessing Lightroom’s SQLite DB Directly

(This post is mostly for the search engines, to help others looking for the solution that this post provides)

Note that some of the details presented in this post, such as the database filename and the actual schema itself, are for the “beta 3” version of Lightroom, and don't apply to Lightroom Version 1.0 or later.

Lightroom, Adobe's high-volume photo workflow application, stores all kinds of user-preference and per-image data in an SQLite database. As of the Beta 3 release, the database is stored in one file, the Mac filename being /Users/username/Pictures/Lightroom/Lightroom B3 Library.aglib

If you're technically-minded and want to peek at the database directly, you might try to access it with the sqlite3 command, which comes standard with OSX, and be disappointed to find that it doesn't work:

$ sqlite3 'Lightroom B3 Library.aglib'
SQLite version 3.1.3
Enter ".help" for instructions
sqlite> .schema
Error: unsupported file format

Eric Scouten provided the answer in this thread on the Adobe's Lightroom Beta discussion forum. It turns out that Lightroom uses features of SQLite introduced in its version 3.2. OSX (as of 10.4.7) ships with SQLite version 3.1.3, and so it can't read Lightroom's database.

If you have Apple's XCode tools installed, you can download the source from sqlite.org and build a more recent version (3.3.6 is the latest as of this writing), and then it all works:

sqlite3 'Lightroom B3 Library.aglib'
SQLite version 3.3.6
Enter “.help” for instructions
sqlite> .schema
CREATE TABLE Adobe_AdditionalMetadata (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    image INTEGER,
    metadataPresetID,
    xmp
);
CREATE TABLE Adobe_FTPPresets (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    passive,
    password,
    path,
    port,
    protocol,
    server,
    title,
    username
);
CREATE TABLE Adobe_cameraRawSettings (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    name,
    rawSettings UNIQUE
);
CREATE TABLE Adobe_imageDevelopSettings (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    croppedHeight,
    croppedWidth,
    digest,
    fileHeight,
    fileWidth,
    grayscale INTEGER,
    historySettingsID,
    image INTEGER,
    settingsID,
    text,
    whiteBalance
);
CREATE TABLE Adobe_imageFiles (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    absolutePath NOT NULL DEFAULT '',
    idx_filename NOT NULL DEFAULT '',
    image INTEGER NOT NULL DEFAULT 0,
    importHash,
    lc_idx_filename NOT NULL DEFAULT '',
    markedMissing,
    md5,
    modTime,
    relativePath NOT NULL DEFAULT '',
    robustRepresentation
);
CREATE TABLE Adobe_imageProperties (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    image INTEGER,
    propertiesString
);
CREATE TABLE Adobe_images (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    aspectRatioCache,
    captureTime,
    developSettingsIDCache,
    fileHeight,
    fileWidth,
    hidden INTEGER NOT NULL DEFAULT 0,
    importedTime,
    orientation,
    originalCaptureTime,
    originalRootEntity INTEGER,
    panningDistanceH,
    panningDistanceV,
    photoBinNominators INTEGER,
    photoBinPosition,
    printSharpening,
    propertiesCache,
    pyramidIDCache,
    rating,
    rawSettings INTEGER,
    rootFile INTEGER,
    sidecarStatus
);
CREATE TABLE Adobe_libraryImageSavedSettings3 (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    dateCreated INTEGER,
    image INTEGER,
    isUserCheckpoint INTEGER NOT NULL DEFAULT 0,
    name,
    text,
    uuid
);
CREATE TABLE Adobe_namedIdentityPlate (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    description,
    identityPlate,
    identityPlateHash,
    moduleFont,
    moduleSelectedTextColor,
    moduleTextColor
);
CREATE TABLE Adobe_oldMetadataCache (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    image INTEGER,
    xmp
);
CREATE TABLE Adobe_variables (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    name,
    value
);
CREATE TABLE Adobe_variablesTable (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    name UNIQUE,
    value NOT NULL DEFAULT ''
);
CREATE TABLE AgLibraryContent (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    containingTag INTEGER,
    content,
    owningModule
);
CREATE TABLE AgLibraryImageSearchIndex (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    dirty INTEGER NOT NULL DEFAULT 0,
    image INTEGER NOT NULL DEFAULT 0,
    searchIndex
);
CREATE TABLE AgLibrarySpecialCollectionInfo (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    kind UNIQUE NOT NULL DEFAULT '',
    sortDirection,
    sortOrder
);
CREATE TABLE AgLibraryTag (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    dateCreated NOT NULL DEFAULT '',
    flattenedParams,
    genealogy NOT NULL DEFAULT '',
    imageCountFlag INTEGER NOT NULL DEFAULT 1,
    kindName NOT NULL DEFAULT '',
    lastApplied,
    name,
    parent INTEGER,
    sortDirection,
    sortOrder
);
CREATE TABLE AgLibraryTagImage (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    flagged,
    flattenedParams,
    image INTEGER NOT NULL DEFAULT 0,
    positionInCollection NOT NULL DEFAULT 'z',
    tag INTEGER NOT NULL DEFAULT 0,
    tagKind NOT NULL DEFAULT ''
);
CREATE TABLE AgLibraryTagSearchWord (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    implied INTEGER NOT NULL DEFAULT 0,
    tag INTEGER NOT NULL DEFAULT 0,
    word NOT NULL DEFAULT ''
);

sqlite> .quit
$

It would be prudent at this point to quote directly from Eric's post:

Of course, any mention of accessing the database file directly should come with the standard disclaimer that you're doing so at your own risk and you should make backup copies.

Comments so far....

I’m really happy to know SQLite is actually used for something significant. I’ve been plenty happy with DBD::SQLite2 for the past few months, as it gives me that sort of instant database I often find I want in my apps without most of the hassles of having to deal with an “actual” database.

— comment by Sam on August 10th, 2006 at 7:49am JST (8 years ago) comment permalink

Thanks Jeffrey for publishing this info. I’m tempted to write a database app that does some useful things Adobe has not yet implemented in Lightroom. There are things one could do using a database app that would be impossible with a plugin.

Rob Cole
San Francisco, California, USA

Two words of caution, though… one is that the schema can change with each version, so take care on that, and the other is that even read-only access, if done at the wrong time, can corrupt the database, so you’ve really got to take care (and responsibility) if doing this. —Jeffrey

— comment by Rob Cole on February 17th, 2010 at 7:45pm JST (4 years, 5 months ago) comment permalink

How would one go about displaying/printing a report of an image’s current develop settings? At my photography school, the teacher is always asking what we have done to our images since importing, and it would be nice to not have to create a develop status list by hand for each one. I’d love to hear your ideas and suggestions. Thanks in advance from Joanna in Chicago.

Post processing? Moi? I’m offended! :-D

All the data about modifications are stored in the XMP embedded inside exported JPGs (or, for non-DNG raw files, in the XMP sidecar created when you Metadata > Save Metadata to File). It’s saved as cleartext embedded within the image binary, so you can actually open it up in a text viewer and find it… just don’t resave the file! You can also view the JPG in something like my online Exif viewer. Your teacher should know all this. —Jeffrey

— comment by Joanna on March 17th, 2010 at 2:04pm JST (4 years, 4 months ago) comment permalink

This is brilliant. Thanks for the information!

I am writing a CakePHP application that would DEFINITELY benifit from having direct access to the Lightroom database. The question now is how these tables are all related to one another. I did not see any FK declarations in there.

I suppose I could just go and open my own database and poke around… :-/

Writing to the database would be a real kicker too.

I take it the database is effectively LOCKED if Lightroom is running? So running a php app concurrently with Lightroom would fail? Again, I suppose I could experiment myself. Don’t tell. :-D

The data in this post is very, very old. You can use the SQLite Manager plugin for Firefox to look around in a catalog, but it’s very complex, and if you try to update anything you run a very real risk of destroying the catalog. Not recommended. I found it interesting to look at years ago, but I never touch it now. —Jeffrey

— comment by Mike on June 1st, 2010 at 8:10am JST (4 years, 2 months ago) comment permalink

Ah. Yes, well I was dreaming about it last night, and I realized that the only real way to do what I wanted would be to use a supplementary database for the modifications I was considering.

Thanks for the tip on the manager!

— comment by Mike on June 2nd, 2010 at 2:43am JST (4 years, 2 months ago) comment permalink

Hi
Great plugin by the way

One question – i was looking at the tables defined in lrcat files and notice almost all of them are prefixed by Ag – any idea what that stands for ? just curious

Regards
Markus

“Ag” is the chemical symbol for silver, an important component in the history of photographic film development. —Jeffrey

— comment by Markus on September 29th, 2010 at 9:46pm JST (3 years, 10 months ago) comment permalink

Wow that was a fast response – that is quite neat about Ag, i would of never guessed it

I have one more question if that’s ok – do you have documentation/notes for any of the other tables that exist in the lrcat file ? or specifically those related to publishing to flickr

The reason i’m wondering is i’m hoping to manually fix a mismatch between my lightroom flickr files and the ones that are already at my flickr account before i got lightroom

I’ve tried your flickr plugin but for whatever reason(i think it’s caused by lots of braketed HDR shots) it won’t match up the local and flickr files

Regards
Mark

No, sorry, I haven’t looked at the DB schema in years. —Jeffrey

— comment by Markus on September 30th, 2010 at 12:57am JST (3 years, 10 months ago) comment permalink

I use this add-in in Firefox SQLite Manager Add-on SQLite Manager Add-on that I use to “fix” (e.g. purge bad auto-complete entries) records in Firefox’s internal sqlite db’s.

I just did a test and it will open LR5.lrcat (Windows) db’s. So if someone is looking for a gui app to peek (warning: or change! Use at your own peril!) in their catalogs, here is a tool.

Thanks for all the great work (plug-ins and photos)!

Todd in Toledo, OH US who wishes he was back in Salida, CO US

— comment by Todd on November 12th, 2013 at 11:41pm JST (8 months, 17 days ago) comment permalink
Leave a comment...


All comments are invisible to others until Jeffrey approves them.

Please mention what part of the world you're writing from, if you don't mind. It's always interesting to see where people are visiting from.

More or less plain text — see below for allowed markup

You can use the following tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Subscribe without commenting