Redis

Connect

Redis credentials live in a DuckDB secret. Every redis_* function takes a final secret argument naming this secret:

CREATE SECRET IF NOT EXISTS redis (
  TYPE redis,
  PROVIDER config,
  host 'localhost',
  port '6379',
  password 'optional_password'
);

For production, parameterize the password from the environment so it doesn’t appear in cached query plans:

SET VARIABLE redis_password = getenv('REDIS_PASSWORD');

CREATE SECRET prod_redis (
  TYPE redis,
  PROVIDER config,
  host 'cache.prod.internal',
  port '6379',
  password :redis_password
);

See CREATE SECRET for the full secrets DSL and the Secrets section on this page for the redis parameter table.

String operations

-- Set a value (Redis SET)
SELECT redis_set('user:1', 'John Doe', 'redis') AS ok;

-- Get a value (Redis GET)
SELECT redis_get('user:1', 'redis') AS user_name;

-- Bulk write from a column
INSERT INTO users (id, name)
SELECT id,
       redis_set('user:' || id::VARCHAR, name, 'redis')
FROM new_users;

-- Multi-get in one round-trip (Redis MGET)
SELECT redis_mget('user:1,user:2,user:3', 'redis');

Maps to GET, SET, MGET. See redis_get / redis_set / redis_mget for the function signatures.

Hash operations

-- Set fields (Redis HSET)
SELECT redis_hset('user:1', 'email', '[email protected]', 'redis');
SELECT redis_hset('user:1', 'age',   '30',               'redis');

-- Get one field (Redis HGET)
SELECT redis_hget('user:1', 'email', 'redis') AS email;

-- All fields and values as a table (Redis HGETALL)
SELECT * FROM redis_hgetall('user:1', 'redis');

Maps to HGET, HSET, HGETALL. See redis_hget / redis_hset / redis_hgetall.

List operations

-- Push values (Redis LPUSH)
SELECT redis_lpush('queue:jobs', 'job-1', 'redis');
SELECT redis_lpush('queue:jobs', 'job-2', 'redis');

-- Read as a comma-joined string
SELECT redis_lrange('queue:jobs', 0, -1, 'redis');

-- Or as a table — one row per element
SELECT * FROM redis_lrange_table('queue:jobs', 0, -1, 'redis');

Maps to LPUSH and LRANGE. redis_lrange_table is the table-shaped variant — pick it when you want one DuckDB row per list element instead of a single comma-joined string from redis_lrange.

Discovery (SCAN, KEYS)

KEYS is convenient but blocks the Redis server on large keyspaces. In production, prefer cursor-paginated SCAN:

-- KEYS — fine for development / small keyspaces
SELECT * FROM redis_keys('user:*', 'redis');

-- SCAN — paginated, non-blocking
SELECT redis_scan('0', 'user:*', 100, 'redis');
-- → "1024:user:1,user:2,...,user:50"
-- Pass the returned cursor (1024) back in to fetch the next page; cursor "0" means done.

See redis_keys and redis_scan.

Bulk hash discovery (redis_hscan_over_scan)

The killer feature for migration and bulk analysis — combines SCAN with HSCAN to emit (key, field, value) rows for every hash matching a key pattern, in one query:

-- Every field of every user:* hash, as rows
SELECT key, field, value
FROM redis_hscan_over_scan(
  /* scan_pattern  */ 'user:*',
  /* hscan_pattern */ '*',
  /* count         */ 100,
  /* secret        */ 'redis'
)
ORDER BY key, field;

Pipe directly into a CREATE TABLE AS for offline analysis or migration:

CREATE TABLE redis_users_dump AS
SELECT key, field, value
FROM redis_hscan_over_scan('user:*', '*', 100, 'redis');

See redis_hscan_over_scan for the full signature.

Key management

-- Type, existence, deletion
SELECT redis_type('user:1', 'redis');     -- 'hash'
SELECT redis_exists('user:1', 'redis');   -- TRUE
SELECT redis_del('user:1', 'redis');      -- TRUE if existed and removed

Maps to TYPE, EXISTS, DEL. See redis_type / redis_exists / redis_del.