Querying Bluesky with DuckDB and SQL

Querying Bluesky directly with DuckDB and SQL is easily possible because the AT Protocol is publicly available and has a Jetstream that you can read.

This is the recent Bluesky Engagement Stats ordered by engagement, including a chart:

See origin on Bluesky post

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

-- Charting bars with engagement metrics
WITH raw_data AS (
  SELECT * FROM read_json_auto('https://public.api.bsky.app/xrpc/app.bsky.feed.getAuthorFeed?actor=did:plc:edglm4muiyzty2snc55ysuqx&limit=100')
),
unnested_feed AS (
  SELECT unnest(feed) as post_data FROM raw_data
),
engagement_data AS (
  SELECT 
    RIGHT(post_data.post.uri, 13) as post_uri,
    --post_data.post.uri as post_uri,
    post_data.post.author.handle,
    LEFT(post_data.post.record.text, 50) as post_text,
    post_data.post.record.createdAt as created_at,
    (post_data.post.replyCount + 
     post_data.post.repostCount + 
     post_data.post.likeCount + 
     post_data.post.quoteCount) as total_engagement,
      post_data.post.replyCount as replies,
      post_data.post.repostCount as reposts,
      post_data.post.likeCount as likes,
      post_data.post.quoteCount as quotes,
  FROM unnested_feed
)
SELECT 
  post_uri,
  created_at,
  total_engagement,
  bar(total_engagement, 0, 
      (SELECT MAX(total_engagement) FROM engagement_data), 
      30) as engagement_chart,
  replies, reposts, likes, quotes,
  post_text,

FROM engagement_data
WHERE handle = 'ssp.sh'
ORDER BY total_engagement DESC
LIMIT 10;  

done with Obsidian DuckDB Extension

See more at DuckDB with Obsidian.

Run the query live on this page:

-- Charting bars with engagement metrics
WITH raw_data AS (
  SELECT * FROM read_json_auto('https://public.api.bsky.app/xrpc/app.bsky.feed.getAuthorFeed?actor=did:plc:edglm4muiyzty2snc55ysuqx&limit=100')
),
unnested_feed AS (
  SELECT unnest(feed) as post_data FROM raw_data
),
engagement_data AS (
  SELECT 
    RIGHT(post_data.post.uri, 13) as post_uri,
    --post_data.post.uri as post_uri,
    post_data.post.author.handle,
    LEFT(post_data.post.record.text, 50) as post_text,
    post_data.post.record.createdAt as created_at,
    (post_data.post.replyCount + 
     post_data.post.repostCount + 
     post_data.post.likeCount + 
     post_data.post.quoteCount) as total_engagement,
      post_data.post.replyCount as replies,
      post_data.post.repostCount as reposts,
      post_data.post.likeCount as likes,
      post_data.post.quoteCount as quotes,
  FROM unnested_feed
)
SELECT 
  post_uri,
  created_at,
  total_engagement,
  bar(total_engagement, 0, 
      (SELECT MAX(total_engagement) FROM engagement_data), 
      30) as engagement_chart,
  replies, reposts, likes, quotes,
  post_text,

FROM engagement_data
WHERE handle = 'ssp.sh'
ORDER BY total_engagement DESC
LIMIT 30;  

Bsky and GitHub Gist:

Queried with Query databases in vim (vim-dadbod) within Neovim.

Or reading the posts - again, run the query live on this page:

-- Query the API directly and flatten the nested JSON structure
WITH raw_data AS (
  SELECT * FROM read_json_auto('https://public.api.bsky.app/xrpc/app.bsky.feed.getAuthorFeed?actor=did:plc:edglm4muiyzty2snc55ysuqx&limit=10')
),
unnested_feed AS (
  SELECT unnest(feed) as post_data FROM raw_data
)
SELECT 
  -- Post basics
  post_data.post.uri as post_uri,
  post_data.post.author.handle as author_handle,
  post_data.post.author.displayName as display_name,
  
  -- Post content
  post_data.post.record.text as post_text,
  post_data.post.record.createdAt as created_at,
  
  -- Engagement metrics
  post_data.post.replyCount as replies,
  post_data.post.repostCount as reposts,
  post_data.post.likeCount as likes,
  post_data.post.quoteCount as quotes,
  
  -- Embedded content (if available)
  CASE 
    WHEN post_data.post.embed IS NOT NULL 
    AND post_data.post.embed['$type'] = 'app.bsky.embed.external#view' 
    THEN post_data.post.embed.external.uri
    ELSE NULL
  END as embedded_link,
  
  CASE 
    WHEN post_data.post.embed IS NOT NULL 
    AND post_data.post.embed['$type'] = 'app.bsky.embed.external#view' 
    THEN post_data.post.embed.external.title
    ELSE NULL
  END as embedded_title,
  
  -- Total engagement score
  (post_data.post.replyCount + 
   post_data.post.repostCount + 
   post_data.post.likeCount + 
   post_data.post.quoteCount) as total_engagement
FROM unnested_feed
ORDER BY created_at DESC;

Uploaded to Reading bsky posts with DuckDB example. · GitHub, too.

Get the Unique ID of my User through public API using HTTPS community extension

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

D WITH __input AS (
    SELECT
      http_get('https://public.api.bsky.app/xrpc/com.atproto.identity.resolveHandle?handle=ssp.sh') AS res
  )
  SELECT
    res::json->>'body' as identity_json
  FROM __input;
┌────────────────────────────────────────────┐
│               identity_json                │
│                  varchar                   │
├────────────────────────────────────────────┤
│ {"did":"did:plc:edglm4muiyzty2snc55ysuqx"} │
└────────────────────────────────────────────┘

Also find a older Git repo with some queries

GitHub - sspaeti/bsky-atproto: Playground for bsky-atproto · GitHub

Rusty Conover created a git repo vgi-bluesky, that adds Bluesky as ordinary DuckDB tables: a read-only VGI worker. Posts, threads, profiles, followers, likes, feeds, trends and the Jetstream firehose, over SQL.

Install (if not from community repo):

1
2
3
4
5

uvx haybarn-cli
⠇ Preparing packages... (0/1)
haybarn-cli          ------------------------------ 5.17 MiB/21.11 MiB                           
memory H INSTALL vgi FROM community;
memory H LOAD vgi;

Then you can attach bluesky database with:

1
2

memory H ATTACH 'bluesky' (TYPE vgi,
	   LOCATION 'uvx --from git+https://github.com/Query-farm/vgi-bluesky@v0.1.0 vgi-bluesky');

And run queries:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

memory H SELECT distinct category FROM bluesky.trends
         ;
┌───────────────┐
│   category    │
│    varchar    │
├───────────────┤
│ politics      │
│ entertainment │
│ science-tech  │
│ sports        │
│ business      │
│ culture       │
└───────────────┘

memory H SELECT rank, display_name, category, post_count FROM bluesky.trends where category='science-tech' ORDER BY rank;
┌───────┬────────────────────────────────────┬──────────────┬────────────┐
│ rank  │            display_name            │   category   │ post_count │
│ int64 │              varchar               │   varchar    │   int64    │
├───────┼────────────────────────────────────┼──────────────┼────────────┤
│     5 │ SpaceX launches Starship Flight 14 │ science-tech │        418 │
│    13 │ Meta launches Muse AI agent        │ science-tech │       3713 │
└───────┴────────────────────────────────────┴──────────────┴────────────┘

• I wrote about Building with Bluesky: Inside the New Open Social Network
• Run DuckDB in your Website
• DuckDB with Obsidian

Origin: ATProto