[macOS] Automate macOS launchpad sorting, folders, etc

[0xdevalias]

It would be cool if we could create folders and sort apps into them in an automated way.

• https://apple.stackexchange.com/questions/237136/where-does-launchpad-store-folder-information-database-etc/237139#237139
• https://apple.stackexchange.com/questions/18535/where-is-the-launchpad-database-stored-and-is-there-a-way-to-edit-it-directly
  • https://apple.stackexchange.com/questions/342042/how-can-i-query-the-hardware-uuid-of-a-mac-programmatically-from-a-command-line
  • https://apple.stackexchange.com/questions/247923/where-is-defaults-currenthost-write-domain-key-value-written-to
    • defaults -currentHost read com.apple.dock LaunchPadDBName
• https://stackoverflow.com/questions/8317177/see-what-process-is-using-a-file-in-mac-os-x
  • lsof | grep myfilename
• https://www.chriswrites.com/19385-2/

  • How to use Launchpad on your Mac like a pro

  • http://launchpadmanager.com/

⇒  lsof | grep com.apple.dock.launchpad
Dock       1322 devalias  txt       REG                1,4       32768              444803 /private/var/folders/rd/nx4rzsrn1ms23qcgtpbkh_jm0000gn/0/com.apple.dock.launchpad/db/db-shm
Dock       1322 devalias    5u      REG                1,4     7376896              444800 /private/var/folders/rd/nx4rzsrn1ms23qcgtpbkh_jm0000gn/0/com.apple.dock.launchpad/db/db
Dock       1322 devalias    6u      REG                1,4     3889312              444802 /private/var/folders/rd/nx4rzsrn1ms23qcgtpbkh_jm0000gn/0/com.apple.dock.launchpad/db/db-wal
Dock       1322 devalias    7u      REG                1,4       32768              444803 /private/var/folders/rd/nx4rzsrn1ms23qcgtpbkh_jm0000gn/0/com.apple.dock.launchpad/db/db-shm

[0xdevalias]

The following is an extract from my (private) obsidian note: DeepDive - Dotfiles (and macOS defaults, settings) management tools, processes, etc

obsidian://open?vault=Notes%20(Dropbox)&file=deepdive%2FDeepDive%20-%20Dotfiles%20(and%20macOS%20defaults%2C%20settings)%20management%20tools%2C%20processes%2C%20etc)

---

Edit [[macOS]] launchpad database

• [macOS] Automate macOS launchpad sorting, folders, etc #7

  • Automate macOS launchpad sorting, folders, etc

  • It would be cool if we could create folders and sort apps into them in an automated way

• https://fgimian.github.io/blog/2016/12/23/how-macos-stores-launchpad-configuration/

  • How macOS Stores Launchpad Configuration

  • Connecting to the Launchpad SQLite Database

    • cd $(getconf DARWIN_USER_DIR)/com.apple.dock.launchpad/db
    • sqlite3 --column --header db
      • .schema

    • For anyone who stumbles across this article and is interested in the YAML-based script for configuring things, the (archived) repo appears to be available here: https://github.com/fgimian/launchpad.cr

    • There's also a Python version here if you prefer: https://github.com/fgimian/macbuild-ansible/blob/master/extras/launchpad.py

• https://apple.stackexchange.com/questions/18535/where-is-the-launchpad-database-stored-and-is-there-a-way-to-edit-it-directly

  • The Launchpad database is located at ~/Library/Application Support/Dock/*some-hex-name*.db. To edit it, you'll have to use SQLite.

  • On [[macOS]] Sierra and newer the old launchpad database does not exists anymore. Instead every user has his personal launchpad database somewhere inside /private/var/folders

  • The /private/var/folders directory contains lots of cryptic folder names, but you can identify the path to your user's personal folder using getconf DARWIN_USER_DIR. You may note this command returns a path inside /var/folders instead of /private/var/folders, but /var is a symlink to /private/var.

    • echo /private$(getconf DARWIN_USER_DIR)com.apple.dock.launchpad/db/db

  •  the value for some-hex-name is stored in ~/Library/Preferences/ByHost/com.apple.dock.*another-hex-name*.plist as LaunchPadDBName.

    • defaults -currentHost read com.apple.dock LaunchPadDBName
• https://apple.stackexchange.com/questions/150965/how-to-reset-launchpad-ordering-in-os-x-yosemite
  • defaults -currentHost write com.apple.dock ResetLaunchPad -bool true; killall Dock
• https://osxdaily.com/2015/05/05/reset-launchpad-layout-mac-os-x/
  • defaults write com.apple.dock ResetLaunchPad -bool true; killall Dock
• https://www.rawinfopages.com/mac/content/streamline-launchpad-and-make-it-faster-more-useful

  • How to organize and clean up Launchpad on the Apple Mac

  • Change the Launchpad layout

    • defaults write com.apple.dock springboard-rows -int N
    • defaults write com.apple.dock springboard-columns -int N

    • Replace the N at the end to set the number of rows and columns of icons to display, such as 3, 5, 6, whatever. You need to restart the Mac after entering those Terminal commands.

  • How do you reset Launchpad?

    • defaults write com.apple.dock ResetLaunchPad -bool TRUE && killall Dock
• https://discussions.apple.com/thread/8285610

  • Launchpad database location

• https://www.technorms.com/18829/control-launchpad

  • If you don’t want to use terminal commands you can use an app called Launchpad Manager to do all of the work for you. Launchpad Manager will present you with a view of your Launchpad screen and allow you to add, delete, arrange, or group any of your icons.

    • http://launchpadmanager.com/
• www.chriswrites.com/19385-2/
  • https://webcache.googleusercontent.com/search?q=cache:YiFVaSR8l1UJ:https://www.chriswrites.com/19385-2/+&cd=1&hl=en&ct=clnk&gl=au

  • How to use Launchpad on your Mac like a pro

  • defaults write com.apple.dock springboard-columns -int 4
  • defaults write com.apple.dock springboard-rows -int 6
  • defaults write com.apple.dock ResetLaunchPad -bool TRUE
  • killall Dock

[0xdevalias]

ChatGPT Ref: https://chat.openai.com/chat/68754471-c6a5-446e-a37b-af15d36c72c6

USER

What is the macOS screen called that shows all installed apps?

ASSISTANT

The macOS screen that shows all installed apps is called the Launchpad. It provides a convenient way to access and launch applications on a Mac, similar to the home screen on an iOS device. The Launchpad can be opened by clicking on its icon in the Dock, pressing the F4 key on newer Macs, or using a multi-touch gesture. Once open, you can scroll through your installed applications, organize them into folders, or search for a specific app using the search bar at the top.

USER

Where is the data from launchpad stored, including the folder names, and which apps are stored within them?

ASSISTANT

The data for Launchpad is stored in a database file called "db" located in the folder /private/var/folders/xx/xxxxxxxxxxxxxxxxxxxxxxxxxxxxx/0/com.apple.dock.launchpad/. The xx/xxxxxxxxxxxxxxxxxxxxxxxxxxxxx part of the path is a randomly generated folder name that will be different on every Mac. You can navigate to this folder by opening Finder and pressing Shift+Command+G, then pasting the above path into the "Go to folder" dialog.

Within this folder, you will find the Launchpad database file named "db". This file contains information about all of the apps installed on your Mac, including their names, icons, and the folders they are organized into. It is a SQLite database file that can be opened and queried using various third-party tools.

Note that modifying this database file directly can be risky and may cause issues with Launchpad. It is recommended that you use the built-in functionality of Launchpad to organize your apps, rather than attempting to modify the database file manually.

USER

How can I directly access the database?

ASSISTANT

Directly accessing and modifying the Launchpad database file can be risky and may cause issues with Launchpad. However, if you still want to access the Launchpad database file, you can use a SQLite database viewer, such as the free "DB Browser for SQLite" application.

Here are the steps to access the Launchpad database file:

1. Download and install DB Browser for SQLite from its official website.

2. Open the "Finder" application on your Mac, then press "Shift+Command+G" to open the "Go to Folder" dialog.

3. In the "Go to Folder" dialog, type /private/var/folders, then press the "Go" button.

4. In the "folders" folder, you will find a randomly named folder. Open it and navigate to /0/com.apple.dock.launchpad/.

5. In this folder, you will find the Launchpad database file named "db". Make a backup of this file before you proceed.

6. Open the DB Browser for SQLite application, and select "Open Database" from the "File" menu.

7. In the file selection dialog, navigate to the Launchpad database file named "db", select it, and click the "Open" button.

8. You can now view and query the Launchpad database using DB Browser for SQLite.

Note that modifying the Launchpad database file directly can be risky and may cause issues with Launchpad. It is recommended that you use the built-in functionality of Launchpad to organize your apps, rather than attempting to modify the database file manually.

USER

What tables exist in this db, and what are they used for?

ASSISTANT

The Launchpad database file ("db") contains several tables that store information about the installed applications and how they are organized within Launchpad. Here is a brief overview of the tables and their purpose:

1. apps: This table contains information about each installed application, such as its name, bundle identifier, and path to its executable file.

2. groups: This table contains information about each Launchpad folder, such as its name and position within Launchpad.

3. items: This table contains information about each item in Launchpad, which can be an application or a folder. This includes the item's position within Launchpad, its type (app or folder), and its association with a specific app or folder.

4. aliases: This table contains information about any aliases used in Launchpad, which can point to an application or a folder.

5. bundle_data: This table contains additional information about each installed application, such as its icon and version number.

6. dbinfo: This table contains metadata about the Launchpad database, such as its schema version and last modified date.

7. pictures: This table contains information about the pictures used in Launchpad, such as their name and file path.

These tables are used by Launchpad to organize and display the installed applications on your Mac. By querying and modifying the Launchpad database file, you can change how the applications are organized and displayed within Launchpad. However, modifying the Launchpad database file directly can be risky and may cause issues with Launchpad. It is recommended that you use the built-in functionality of Launchpad to organize your apps, rather than attempting to modify the database file manually.

[samyak1409]

Any update on solution?

[0xdevalias]

Any update on solution?

@samyak1409 There are snippets of bits that you could pull together into a solution above; but it's not something I have had the capacity to look into / do completely myself yet.

The main approach I would probably be looking at following would be to read from / write to the SQLite database; and/or to look if there are any private apple frameworks that would be helpful.

---

Some other tangentially related gists of mine that might be useful in understanding more about how Apple's SQLite DB's are laid out, private frameworks (/System/Library/PrivateFrameworks/), etc (particularly the more detailed notes / helper scripts for Core Data / etc in the reminders gist):

• macOS Launchpad DB Tricks (0xdevalias' gist)
• Accessing / Exporting Apple's Reminders Data on macOS (0xdevalias' gist)
  • Core Data
    • extract-coredata-model-hierarchy.py
  • macOS Private Frameworks
• Accessing / Exporting Apple's Screen Time Data (0xdevalias' gist)
• Decompile Apple Shortcuts into raw XML 'source code' (0xdevalias' gist)

---

Using some of the notes from above, I believe this is likely the main SQLite db files we want these days:

⇒ ls $(getconf DARWIN_USER_DIR)/com.apple.dock.launchpad/db
db  db-shm  db-wal

We can see the tables in that with the following:

⇒ sqlite3 -readonly --header --column \
  "$(getconf DARWIN_USER_DIR)/com.apple.dock.launchpad/db/db" \
  ".tables" \
  | tr ' ' '\n' \
  | grep -v '^$' \
  | sort

app_sources
apps
categories
dbinfo
downloading_apps
groups
image_cache
items

We can see the full schema and triggers with:

sqlite3 -readonly "$(getconf DARWIN_USER_DIR)/com.apple.dock.launchpad/db/db" ".schema"

DB Schema + Triggers

CREATE TABLE dbinfo (key VARCHAR, value VARCHAR);
CREATE TABLE items (rowid INTEGER PRIMARY KEY ASC, uuid VARCHAR, flags INTEGER, type INTEGER, parent_id INTEGER NOT NULL, ordering INTEGER);
CREATE TABLE apps (item_id INTEGER PRIMARY KEY, title VARCHAR, bundleid VARCHAR, storeid VARCHAR,category_id INTEGER, moddate REAL, bookmark BLOB);
CREATE TABLE groups (item_id INTEGER PRIMARY KEY, category_id INTEGER, title VARCHAR);
CREATE TABLE downloading_apps (item_id INTEGER PRIMARY KEY, title VARCHAR, bundleid VARCHAR, storeid VARCHAR, category_id INTEGER, install_path VARCHAR);
CREATE TABLE categories (rowid INTEGER PRIMARY KEY ASC, uti VARCHAR);
CREATE TABLE app_sources (rowid INTEGER PRIMARY KEY ASC, uuid VARCHAR, flags INTEGER, bookmark BLOB, last_fsevent_id INTEGER, fsevent_uuid VARCHAR);
CREATE TABLE image_cache (item_id INTEGER, size_big INTEGER, size_mini INTEGER, image_data BLOB, image_data_mini BLOB);
CREATE TRIGGER update_items_order BEFORE UPDATE OF ordering ON items WHEN new.ordering > old.ordering AND 0 == (SELECT value FROM dbinfo WHERE key='ignore_items_update_triggers')
BEGIN
	UPDATE dbinfo SET value=1 WHERE key='ignore_items_update_triggers';
	UPDATE items SET ordering = ordering - 1 WHERE parent_id = old.parent_id AND ordering BETWEEN old.ordering and new.ordering;
	UPDATE dbinfo SET value=0 WHERE key='ignore_items_update_triggers';
END;
CREATE TRIGGER update_items_order_backwards BEFORE UPDATE OF ordering ON items WHEN new.ordering < old.ordering AND 0 == (SELECT value FROM dbinfo WHERE key='ignore_items_update_triggers')
BEGIN
	UPDATE dbinfo SET value=1 WHERE key='ignore_items_update_triggers';
	UPDATE items SET ordering = ordering + 1 WHERE parent_id = old.parent_id AND ordering BETWEEN new.ordering and old.ordering;
	UPDATE dbinfo SET value=0 WHERE key='ignore_items_update_triggers';
END;
CREATE TRIGGER update_item_parent AFTER UPDATE OF parent_id ON items WHEN 0 == (SELECT value FROM dbinfo WHERE key='ignore_items_update_triggers')
BEGIN
	UPDATE dbinfo SET value=1 WHERE key='ignore_items_update_triggers';
	UPDATE items SET ordering = (SELECT ifnull(MAX(ordering),0)+1 FROM items WHERE parent_id=new.parent_id AND ROWID!=old.rowid) WHERE ROWID=old.rowid;
	UPDATE items SET ordering = ordering - 1 WHERE parent_id = old.parent_id and ordering > old.ordering;
	UPDATE dbinfo SET value=0 WHERE key='ignore_items_update_triggers';
END;
CREATE TRIGGER insert_item AFTER INSERT on items WHEN 0 == (SELECT value FROM dbinfo WHERE key='ignore_items_update_triggers')
BEGIN
	UPDATE dbinfo SET value=1 WHERE key='ignore_items_update_triggers';
	UPDATE items SET ordering = (SELECT ifnull(MAX(ordering),0)+1 FROM items WHERE parent_id=new.parent_id) WHERE ROWID=new.rowid;
	UPDATE dbinfo SET value=0 WHERE key='ignore_items_update_triggers';
END;
CREATE TRIGGER app_inserted AFTER INSERT ON items WHEN new.type = 4 OR new.type = 5
BEGIN
	INSERT INTO image_cache VALUES (new.rowid,0,0,NULL,NULL);
END;
CREATE TRIGGER app_deleted AFTER DELETE ON items WHEN old.type = 4 OR old.type = 5
BEGIN
	DELETE FROM image_cache WHERE item_id=old.rowid;
END;
CREATE TRIGGER item_deleted AFTER DELETE ON items
BEGIN
	DELETE FROM apps WHERE rowid=old.rowid;
	DELETE FROM groups WHERE item_id=old.rowid;
	DELETE FROM downloading_apps WHERE item_id=old.rowid;
	UPDATE dbinfo SET value=1 WHERE key='ignore_items_update_triggers';
	UPDATE items SET ordering = ordering - 1 WHERE old.parent_id = parent_id AND ordering > old.ordering;
	UPDATE dbinfo SET value=0 WHERE key='ignore_items_update_triggers';
END;
CREATE INDEX items_uuid_index ON items (uuid);
CREATE INDEX items_ordering_index ON items (parent_id,ordering);
CREATE INDEX items_type ON items (type);
CREATE INDEX image_cache_index ON image_cache (item_id);

Passing that schema through ChatGPT (gpt-5) with the following prompt:

Given the following schema for a sqlite database for the macOS launchpad; can we figure out how to query what apps are shown, their order, what folders they are in, etc?

Gave (ref):

🔧 Schema structure

Table	Purpose
items	The core tree structure. Each entry is a node in the Launchpad hierarchy. type tells you what kind of item it is (app, folder, etc). parent_id links to the parent item. ordering defines the position among siblings.
apps	Metadata for items whose type corresponds to an app (typically type = 4 or 5). Joined via apps.item_id = items.rowid.
groups	Metadata for folder items (folder title). Joined via groups.item_id = items.rowid.
image_cache	App icon blobs
categories	App category names (store information only; not needed for layout)

So:

• The layout tree is entirely driven by the items table.
• Top-level pages have parent_id = 1 (row 1 in items is usually the “ROOT” item created by the system).
• Folders are rows where type indicates a group (Apple uses type = 2 for a group header and type = 3 for the folder itself).
• The order within each page or folder is the ordering column.
• Apps are rows where type = 4 (normal app) or type = 5 (app alias).

---

Iterating a little bit, we can get the top-level Launchpad pages using the following query.

These rows all have type = 3 and parent_id = 1, which means they represent 'page containers' in the Launchpad grid.

⚠️ Note: you may see one row where uuid = 'HOLDINGPAGE'. That isn’t a real page — it’s a temporary placeholder page that macOS creates internally when pages get re-organised (for example when dragging apps between pages). It remains in the database even if it’s currently empty, so you should treat it as a placeholder, not an actual page with icons.

sqlite3 -readonly -header -column -nullvalue "" \
  "$(getconf DARWIN_USER_DIR)/com.apple.dock.launchpad/db/db" \
  "SELECT
      rowid,
      uuid,
      flags,
      type,
      'grid_container' AS type_name,
      parent_id,
      ordering
   FROM items
   WHERE parent_id = 1
     AND type = 3
   ORDER BY ordering;"

eg.

rowid  uuid                                  flags  type  type_name       parent_id  ordering
-----  ------------------------------------  -----  ----  --------------  ---------  --------
2      HOLDINGPAGE                                  3     grid_container  1          0
61     F1838D66-EFD1-4FA6-84B7-6F82C4A8A5AD  2      3     grid_container  1          1
112    0AF58ECF-2C4E-419A-BD72-AA14B7A85721  2      3     grid_container  1          2
321    32CA1995-B9B2-4164-9CB3-F595E07BC96B  2      3     grid_container  1          3
411    8820CC84-7874-4B50-AAF5-E8E5CFA66D8F  0      3     grid_container  1          4

---

We can list the items on a page by using it's rowid with something like this:

PAGE_ID=61

sqlite3 -readonly -header -column -nullvalue "" \
  "$(getconf DARWIN_USER_DIR)/com.apple.dock.launchpad/db/db" \
  "
  -- Error if the row doesn't exist or isn't a grid_container (type=3)
  SELECT 'ERROR: rowid ' || $PAGE_ID || ' does not exist or is not a grid_container (type=3)' AS error_message
  WHERE NOT EXISTS (
    SELECT 1 FROM items WHERE rowid = $PAGE_ID AND type = 3
  );

  -- Only return page items if the ID is a valid container
  SELECT
      i.rowid,
      i.type,
      CASE i.type
        WHEN 4 THEN 'app'
        WHEN 3 THEN 'grid_container'
        WHEN 2 THEN 'folder'
        WHEN 6 THEN 'recently_added_placeholder'
        ELSE 'unknown'
      END AS type_name,
      COALESCE(a.title, g.title) AS title,
      i.ordering
   FROM items i
   LEFT JOIN apps   a ON a.item_id = i.rowid
   LEFT JOIN groups g ON g.item_id = i.rowid
   WHERE i.parent_id = $PAGE_ID
     AND (SELECT type FROM items WHERE rowid = $PAGE_ID) = 3
   ORDER BY i.ordering;
  "

eg.

rowid  type  type_name  title                  ordering
-----  ----  ---------  ---------------------  --------
57     4     app        App Store              0
48     4     app        Maps                   1
10     4     app        Find My                2
13     4     app        Photo Booth            3
51     4     app        Photos                 4
..snip..

Or a page with folders in it too looks like:

rowid  type  type_name  title                          ordering
-----  ----  ---------  -----------------------------  --------
129    2     folder     Social/Communication           0
137    2     folder     Internet/Download              1
133    2     folder     Music/Media/Entertainment      2
158    2     folder     Music Production / DJ          3
123    2     folder     Games                          4
131    2     folder     Developer Tools                5
334    2     folder     Security Tools                 6
..snip..
277    4     app        Toggl Track                    19
..snip..

---

We can look at the contents of a folder with a query like this:

FOLDER_ID=123

sqlite3 -readonly -header -column -nullvalue "" \
  "$(getconf DARWIN_USER_DIR)/com.apple.dock.launchpad/db/db" \
  "
  -- Error if the ID doesn't exist *or* isn't a folder (type=2)
  SELECT 'ERROR: rowid ' || $FOLDER_ID || ' does not exist or is not a folder (type=2)' AS error_message
  WHERE NOT EXISTS (
    SELECT 1 FROM items WHERE rowid = $FOLDER_ID AND type = 2
  );

  -- Only runs if the ID is valid
  SELECT
      i.rowid,
      i.type,
      CASE i.type
        WHEN 4 THEN 'app'
        WHEN 3 THEN 'grid_container'
        WHEN 2 THEN 'folder'
        WHEN 6 THEN 'recently_added_placeholder'
        ELSE 'unknown'
      END AS type_name,
      COALESCE(a.title, g.title) AS title,
      i.ordering
  FROM items i
  LEFT JOIN apps   a ON a.item_id = i.rowid
  LEFT JOIN groups g ON g.item_id = i.rowid
  WHERE i.parent_id = (
        SELECT rowid
        FROM items
        WHERE parent_id = $FOLDER_ID AND type = 3
      );
  "

eg.

rowid  type  type_name  title                 ordering
-----  ----  ---------  --------------------  --------
249    4     app        Beeper                0
86     4     app        Messenger             1
87     4     app        Telegram              2
143    4     app        Discord               3
..snip..

---

Then, if we wanted to put all of that together in one big mega-query to give us a markdown'ish nested list/tree structure of the pages, folders, and apps; something like this would do it:

sqlite3 -readonly -noheader -nullvalue "" \
  "$(getconf DARWIN_USER_DIR)/com.apple.dock.launchpad/db/db" \
  "
  WITH
  pages AS (
    SELECT rowid, ordering,
           length('- Page ' || ordering) AS prefix_len
    FROM items
    WHERE parent_id = 1 AND type = 3 AND uuid != 'HOLDINGPAGE'
  ),
  page_items AS (
    SELECT
      p.ordering AS page_number,
      p.rowid    AS page_rowid,
      i.rowid    AS item_id,
      i.type     AS item_type,
      COALESCE(a.title,g.title) AS title,
      i.ordering AS item_order,
      length('  - ' || COALESCE(a.title,g.title)) AS prefix_len
    FROM pages p
    JOIN items i ON i.parent_id = p.rowid
    LEFT JOIN apps   a ON a.item_id = i.rowid
    LEFT JOIN groups g ON g.item_id = i.rowid
  ),
  folders_with_grid AS (
    SELECT
      f.page_number,
      f.item_order,
      f.item_id               AS folder_rowid,
      COALESCE(f.title,'(untitled)') AS title,
      grid.rowid              AS grid_rowid,
      length('  - ' || COALESCE(f.title,'(untitled)')) AS prefix_len
    FROM page_items f
    JOIN items grid ON grid.parent_id = f.item_id AND grid.type = 3
    WHERE f.item_type = 2
  ),
  folder_items AS (
    SELECT
      fw.page_number,
      fw.item_order     AS folder_order,
      fw.folder_rowid,
      fw.title          AS folder_title,
      fw.grid_rowid,
      i.rowid           AS child_id,
      COALESCE(a2.title,g2.title) AS child_title,
      i.ordering        AS child_order,
      length('    - ' || COALESCE(a2.title,g2.title)) AS prefix_len
    FROM folders_with_grid fw
    JOIN items i        ON i.parent_id = fw.grid_rowid
    LEFT JOIN apps   a2 ON a2.item_id = i.rowid
    LEFT JOIN groups g2 ON g2.item_id = i.rowid
  ),
  maxlen AS (
    SELECT max(prefix_len) AS w FROM (
      SELECT prefix_len FROM pages
      UNION ALL SELECT prefix_len FROM page_items
      UNION ALL SELECT prefix_len FROM folder_items
    )
  ),
  -- Build full tree with sort keys
  tree AS (
    SELECT
      printf('%-*s (type=page, rowid=%d)',
             (SELECT w FROM maxlen),
             '- Page ' || ordering,
             rowid) AS line,
      ordering * 1000000 AS sk
    FROM pages

    UNION ALL

    SELECT
      printf('%-*s (type=%s, %s)',
             (SELECT w FROM maxlen),
             printf('  - %s', title),
             CASE item_type
               WHEN 2 THEN 'folder'
               WHEN 4 THEN 'app'
               WHEN 6 THEN 'recently_added'
               ELSE 'unknown'
             END,
             CASE item_type
               WHEN 2 THEN 'folder_rowid='||item_id||', grid_rowid='||
                            (SELECT grid_rowid FROM folders_with_grid WHERE folder_rowid=item_id)
               ELSE
                 'rowid='||item_id
             END) AS line,
      page_number * 1000000 + item_order * 1000 AS sk
    FROM page_items

    UNION ALL

    SELECT
      printf('%-*s (type=app, rowid=%d)',
             (SELECT w FROM maxlen),
             printf('    - %s', child_title),
             child_id) AS line,
      page_number * 1000000 + folder_order * 1000 + child_order AS sk
    FROM folder_items
  )

  -- Output only the line, ordered by sk
  SELECT line
  FROM tree
  ORDER BY sk;
  "

eg.

- Page 1                                   (type=page, rowid=61)
  - App Store                              (type=app, rowid=57)
  - Maps                                   (type=app, rowid=48)
  - Find My                                (type=app, rowid=10)
  - Photo Booth                            (type=app, rowid=13)
  - Photos                                 (type=app, rowid=51)
  ..snip..
- Page 2                                   (type=page, rowid=112)
  - Social/Communication                   (type=folder, folder_rowid=129, grid_rowid=130)
    - Beeper                               (type=app, rowid=249)
    - Messenger                            (type=app, rowid=86)
    - Telegram                             (type=app, rowid=87)
    ..snip..
  - Internet/Download                      (type=folder, folder_rowid=137, grid_rowid=138)
    - Google Chrome                        (type=app, rowid=65)
    ..snip..
  ..snip..
  - Toggl Track                            (type=app, rowid=277)
- Page 3                                   (type=page, rowid=321)
  ..snip..
  - Bartender 5                            (type=app, rowid=352)
  ..snip..
- Page 4                                   (type=page, rowid=411)
  ..snip..

---

Though more useful for backup purposes would be to put together a JSON backup of the contents of each page/folder/etc in a way that's not explicitly tied to the IDs; so we can restore it later, even on a different machine/etc; which can be done with something like this:

sqlite3 -readonly "$(getconf DARWIN_USER_DIR)/com.apple.dock.launchpad/db/db" "
  WITH
  pages AS (
    SELECT rowid, ordering
    FROM items
    WHERE parent_id = 1 AND type = 3 AND uuid != 'HOLDINGPAGE'
    ORDER BY ordering
  ),
  page_items AS (
    SELECT
      p.ordering AS page_order,
      p.rowid    AS page_rowid,
      i.rowid    AS item_id,
      i.type     AS item_type,
      COALESCE(a.title,g.title) AS title,
      a.bundleid,
      i.ordering AS item_order
    FROM pages p
    JOIN items i ON i.parent_id = p.rowid
    LEFT JOIN apps   a ON a.item_id = i.rowid
    LEFT JOIN groups g ON g.item_id = i.rowid
  ),
  folder_children AS (
    SELECT
      f.page_order,
      f.item_id    AS folder_id,
      i.rowid      AS child_rowid,
      apps.bundleid,
      apps.title,
      i.ordering   AS child_order
    FROM page_items f
    JOIN items grid      ON grid.parent_id = f.item_id AND grid.type = 3
    JOIN items i         ON i.parent_id   = grid.rowid
    LEFT JOIN apps apps  ON apps.item_id  = i.rowid
    WHERE f.item_type = 2
  )

  SELECT
    json_group_array(
      json_object(
        'type','page',
        'items', (
          SELECT json_group_array(
                   CASE
                     WHEN pi.item_type = 4 THEN
                       json_object('type','app','title',pi.title,'bundleid',pi.bundleid)
                     WHEN pi.item_type = 2 THEN
                       json_object(
                         'type','folder',
                         'title',pi.title,
                         'items', (
                           SELECT json_group_array(
                                    json_object('type','app','title',fc.title,'bundleid',fc.bundleid)
                                  )
                           FROM folder_children fc
                           WHERE fc.folder_id = pi.item_id
                           ORDER BY fc.child_order
                         )
                       )
                   END
                 )
          FROM page_items pi
          WHERE pi.page_order = p.ordering
          ORDER BY pi.item_order
        )
      )
    )
  FROM pages p
  ORDER BY p.ordering;
  " | jq .

eg.

[
  {
    "type": "page",
    "items": [
      { "type": "app", "title": "App Store", "bundleid": "com.apple.AppStore" },
      { "type": "app", "title": "Maps", "bundleid": "com.apple.Maps" },
      // ..snip..
    ]
  },
  {
    "type": "page",
    "items": [
      {
        "type": "folder",
        "title": "Social/Communication",
        "items": [
          { "type": "app", "title": "Beeper", "bundleid": "im.beeper" },
          // ..snip..
        ]
      },
      {
        "type": "folder",
        "title": "Internet/Download",
        "items": [
          { "type": "app", "title": "Google Chrome", "bundleid": "com.google.Chrome" },
          // ..snip..
        ]
      },
      // ..snip..
      { "type": "app", "title": "Toggl Track", "bundleid": "com.toggl.daneel" },
      // ..snip..
    ]
  },
  {
    "type": "page",
    "items": [
      // ..snip..
      { "type": "app", "title": "Bartender 5", "bundleid": "com.surteesstudios.Bartender" },
      // ..snip..
    ]
  },
  {
    "type": "page",
    "items": [
      // ..snip..
    ]
  }
]

---

Given all of that, all that would be left would be to put together a script that could consume that backup json format, diff it against the current DB entries, display a plan of what changes it will make, and then safely execute them.

I'll see if I can iterate something useful for that too; but at least for now, the above should give you some good starting points.

---

Edit: I will likely iterate on a safe restore script at some point, but I have no idea when that will be; as I have already spent more time on this now than I really intended to/have capacity for. But hopefully the above gets you a good bit of the way to it in any case.