mtpdb

A Node.js client for MTPDB — supporting both MySQL-compatible SQL and Redis-compatible KeyValue operations.

Installation

npm install mtpdb

Features

• SQL Interface: MySQL-compatible wire protocol for relational queries
• KeyValue Interface: Redis-compatible commands over HTTP API
• Async/Await: Promise-based API for KeyValue operations
• Type Support: Strings, Hashes, Lists, Sets, Sorted Sets
• Pub/Sub: Publish messages to channels
• TTL Support: Key expiration with EX/PEX commands

Quick Start

SQL Database

const mtpdb = require('mtpdb');

// Create SQL connection
const connection = mtpdb.createConnection({
  host     : 'localhost',
  port     : 3306,        // Wire protocol port
  user     : 'root',
  password : 'mtpdb_root',
  database : 'my_db'
});

connection.connect();

connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.end();

KeyValue Database

const mtpdb = require('mtpdb');

async function main() {
  // Create KeyValue client
  const kv = mtpdb.createKeyValueClient({
    host     : 'localhost',
    port     : 8765,      // HTTP API port
    database : 'my_kv_db' // KeyValue database name
  });

  await kv.connect();

  // Basic string operations
  await kv.set('name', 'Alice');
  const name = await kv.get('name');
  console.log(name); // 'Alice'

  // With expiration (10 seconds)
  await kv.set('temp', 'value', { ex: 10 });

  // Increment
  await kv.set('counter', '0');
  await kv.incr('counter'); // 1
  await kv.incrby('counter', 5); // 6

  // Hash operations
  await kv.hset('user:1', 'name', 'Bob');
  await kv.hset('user:1', 'age', '25');
  const user = await kv.hgetall('user:1');
  console.log(user); // { name: 'Bob', age: '25' }

  // List operations
  await kv.rpush('queue', 'task1', 'task2', 'task3');
  const task = await kv.lpop('queue'); // 'task1'

  // Set operations
  await kv.sadd('tags', 'nodejs', 'database', 'sql');
  const isMember = await kv.sismember('tags', 'nodejs'); // 1

  // Sorted set operations
  await kv.zadd('leaderboard', { 'player1': 100, 'player2': 150 });
  const topPlayers = await kv.zrange('leaderboard', 0, 1); // ['player1', 'player2']

  // Cleanup
  await kv.del('name', 'counter', 'user:1');
}

main().catch(console.error);

SQL API

mtpdb.createConnection(config)

Create a new SQL connection. Config options:

| Option | Default | Description | |------------|---------------|------------------------------| | host | 'localhost' | Server hostname | | port | 3306 | Wire protocol port | | user | 'root' | Username | | password | '' | Password | | database | null | Default database to USE |

connection.connect([callback])

Establish connection and validate credentials.

connection.query(sql, [values], callback)

Execute a SQL query. Supports ? placeholders:

connection.query(
  'SELECT * FROM users WHERE id = ?',
  [42],
  function(error, results, fields) {
    console.log(results);
  }
);

connection.end([callback])

Close the connection.

KeyValue API

mtpdb.createKeyValueClient(config)

Create a new KeyValue client. Config options:

| Option | Default | Description | |------------|---------------|--------------------------------| | host | 'localhost' | Server hostname | | port | 8765 | HTTP API port | | database | required | KeyValue database name |

await kv.connect()

Validate connection to the server. Returns true on success.

String Commands

await kv.set(key, value, [options])

Set a key-value pair.

Options:

• ex - Expire time in seconds
• px - Expire time in milliseconds
• nx - Only set if key does not exist
• xx - Only set if key exists

await kv.set('key', 'value');
await kv.set('key', 'value', { ex: 60 });     // Expires in 60 seconds
await kv.set('key', 'value', { px: 1000 });   // Expires in 1000ms
await kv.set('key', 'value', { nx: true });   // Only if not exists
await kv.set('key', 'value', { xx: true });   // Only if exists

await kv.get(key)

Get the value of a key. Returns null if key doesn't exist.

const value = await kv.get('key');

await kv.del(...keys)

Delete one or more keys. Returns number of keys deleted.

await kv.del('key1');
await kv.del('key1', 'key2', 'key3');

await kv.exists(...keys)

Check if keys exist. Returns number of keys that exist.

const count = await kv.exists('key1', 'key2');

await kv.expire(key, seconds)

Set a key's time to live in seconds.

await kv.expire('key', 60);  // Expires in 60 seconds

await kv.ttl(key)

Get the remaining TTL of a key in seconds.

• Returns positive number: seconds until expiration
• Returns -1: Key exists but has no TTL
• Returns -2: Key doesn't exist

const seconds = await kv.ttl('key');

await kv.persist(key)

Remove the expiration from a key.

await kv.persist('key');

await kv.incr(key), await kv.decr(key)

Increment or decrement the integer value of a key by one.

await kv.set('counter', '10');
await kv.incr('counter');  // 11
await kv.decr('counter');  // 10

await kv.incrby(key, increment)

Increment the integer value of a key by the given amount.

await kv.incrby('counter', 5);

await kv.append(key, value)

Append a value to a key. Returns length of the string after append.

await kv.set('key', 'Hello');
await kv.append('key', ' World');  // Returns 11
await kv.get('key');  // 'Hello World'

await kv.strlen(key)

Get the length of the value stored in a key.

const len = await kv.strlen('key');

await kv.getset(key, value)

Set a key's value and return its old value.

const oldValue = await kv.getset('key', 'new_value');

await kv.mset(keyValues)

Set multiple keys to multiple values.

await kv.mset({ key1: 'value1', key2: 'value2' });

await kv.mget(...keys)

Get the values of multiple keys.

const values = await kv.mget('key1', 'key2', 'key3');
// Returns: ['value1', 'value2', null]

Hash Commands

await kv.hset(key, field, value)

Set a field in a hash. Returns 1 if new field, 0 if updated.

await kv.hset('user:1', 'name', 'Alice');
await kv.hset('user:1', 'age', '30');

await kv.hget(key, field)

Get a field from a hash.

const name = await kv.hget('user:1', 'name');

await kv.hdel(key, ...fields)

Delete one or more fields from a hash.

await kv.hdel('user:1', 'age');

await kv.hexists(key, field)

Check if a field exists in a hash.

const exists = await kv.hexists('user:1', 'name');  // 1 or 0

await kv.hgetall(key)

Get all fields and values in a hash. Returns an object.

const user = await kv.hgetall('user:1');
// Returns: { name: 'Alice', age: '30' }

await kv.hlen(key)

Get the number of fields in a hash.

const fieldCount = await kv.hlen('user:1');

await kv.hkeys(key)

Get all field names in a hash.

const fields = await kv.hkeys('user:1');
// Returns: ['name', 'age']

await kv.hvals(key)

Get all values in a hash.

const values = await kv.hvals('user:1');
// Returns: ['Alice', '30']

await kv.hincrby(key, field, increment)

Increment a hash field by the given amount.

await kv.hincrby('user:1', 'visits', 1);

await kv.hsetnx(key, field, value)

Set a field in a hash only if it doesn't exist.

await kv.hsetnx('user:1', 'created', '2024-01-01');

List Commands

await kv.lpush(key, ...values), await kv.rpush(key, ...values)

Push values to the left or right of a list. Returns length after push.

await kv.rpush('queue', 'task1', 'task2');
await kv.lpush('queue', 'urgent_task');

await kv.lpop(key), await kv.rpop(key)

Pop a value from the left or right of a list.

const task = await kv.lpop('queue');

await kv.llen(key)

Get the length of a list.

const length = await kv.llen('queue');

await kv.lrange(key, start, stop)

Get a range of elements from a list.

const items = await kv.lrange('queue', 0, -1);  // All elements
const firstThree = await kv.lrange('queue', 0, 2);

await kv.lindex(key, index)

Get an element from a list by index.

const item = await kv.lindex('queue', 0);

await kv.lset(key, index, value)

Set the value of an element in a list by index.

await kv.lset('queue', 0, 'updated_task');

await kv.lrem(key, count, value)

Remove elements from a list.

await kv.lrem('queue', 1, 'task1');  // Remove 1 occurrence from head
await kv.lrem('queue', 0, 'task1');  // Remove all occurrences

Set Commands

await kv.sadd(key, ...members)

Add members to a set. Returns number of members added.

await kv.sadd('tags', 'nodejs', 'javascript', 'database');

await kv.srem(key, ...members)

Remove members from a set.

await kv.srem('tags', 'database');

await kv.smembers(key)

Get all members in a set.

const tags = await kv.smembers('tags');
// Returns: ['nodejs', 'javascript']

await kv.sismember(key, member)

Check if a member exists in a set.

const isMember = await kv.sismember('tags', 'nodejs');  // 1 or 0

await kv.scard(key)

Get the number of members in a set.

const count = await kv.scard('tags');

await kv.sunion(...keys), await kv.sinter(...keys), await kv.sdiff(key, ...keys)

Set operations: union, intersection, difference.

await kv.sadd('set1', 'a', 'b', 'c');
await kv.sadd('set2', 'b', 'c', 'd');

const union = await kv.sunion('set1', 'set2');      // ['a', 'b', 'c', 'd']
const inter = await kv.sinter('set1', 'set2');      // ['b', 'c']
const diff = await kv.sdiff('set1', 'set2');        // ['a']

Sorted Set Commands

await kv.zadd(key, scoreMembers)

Add members with scores to a sorted set.

await kv.zadd('leaderboard', { 'player1': 100, 'player2': 200 });

await kv.zrem(key, ...members)

Remove members from a sorted set.

await kv.zrem('leaderboard', 'player1');

await kv.zscore(key, member)

Get the score of a member.

const score = await kv.zscore('leaderboard', 'player1');

await kv.zrank(key, member)

Get the rank (0-based index) of a member.

const rank = await kv.zrank('leaderboard', 'player1');  // 0 = first

await kv.zrange(key, start, stop)

Get members by rank range (lowest to highest score).

const topThree = await kv.zrange('leaderboard', 0, 2);

await kv.zrangebyscore(key, min, max)

Get members by score range.

const midRange = await kv.zrangebyscore('leaderboard', 100, 500);

await kv.zcard(key)

Get the number of members in a sorted set.

const count = await kv.zcard('leaderboard');

Key Commands

await kv.keys(pattern)

Find keys matching a pattern (supports *, ?, [...]).

const allKeys = await kv.keys('*');
const userKeys = await kv.keys('user:*');

await kv.scan(cursor, [options])

Incrementally iterate over keys.

// First iteration
let result = await kv.scan(0, { match: 'user:*', count: 10 });
console.log(result.keys);

// Continue until cursor is 0
while (result.cursor !== 0) {
  result = await kv.scan(result.cursor, { match: 'user:*', count: 10 });
  console.log(result.keys);
}

await kv.rename(key, newKey)

Rename a key.

await kv.rename('old_name', 'new_name');

await kv.renamenx(key, newKey)

Rename a key only if the new key doesn't exist.

const renamed = await kv.renamenx('key1', 'key2');  // 1 or 0

await kv.type(key)

Get the type of a key's value.

const type = await kv.type('mykey');
// Returns: 'string', 'hash', 'list', 'set', 'zset', or 'none'

await kv.dbsize()

Get the number of keys in the database.

const count = await kv.dbsize();

await kv.flushdb()

Delete all keys in the database. Use with caution!

await kv.flushdb();

Pub/Sub Commands

await kv.publish(channel, message)

Publish a message to a channel. Returns number of subscribers.

const subscribers = await kv.publish('notifications', 'New order received!');

Raw Command Execution

await kv.execute(command)

Execute a raw Redis-style command string.

const result = await kv.execute('SET mykey myvalue EX 60');

User Management (SQL)

MTPDB supports MySQL-style user management:

// Create a user
connection.query("CREATE USER 'app_user' IDENTIFIED BY 'secret123'", callback);

// Grant privileges
connection.query("GRANT ALL ON my_db.* TO 'app_user'", callback);

// Show users
connection.query("SHOW USERS", callback);

// Show grants
connection.query("SHOW GRANTS FOR 'app_user'", callback);

// Revoke privileges
connection.query("REVOKE ALL ON my_db.* FROM 'app_user'", callback);

// Drop user
connection.query("DROP USER 'app_user'", callback);

Error Handling

SQL Connections

connection.query('SELECT * FROM nonexistent', (error, results) => {
  if (error) {
    console.error('Query failed:', error.message);
    console.error('Error code:', error.code);
    return;
  }
  console.log(results);
});

KeyValue Client

try {
  await kv.set('key', 'value');
} catch (error) {
  console.error('KV operation failed:', error.message);
}

Examples

Session Store

const mtpdb = require('mtpdb');

class SessionStore {
  constructor(kv) {
    this.kv = kv;
  }

  async create(sessionId, data, ttlSeconds = 3600) {
    await this.kv.hset(`session:${sessionId}`, 'data', JSON.stringify(data));
    await this.kv.expire(`session:${sessionId}`, ttlSeconds);
  }

  async get(sessionId) {
    const data = await this.kv.hget(`session:${sessionId}`, 'data');
    return data ? JSON.parse(data) : null;
  }

  async destroy(sessionId) {
    await this.kv.del(`session:${sessionId}`);
  }
}

Rate Limiter

async function checkRateLimit(userId, maxRequests = 100, windowSeconds = 60) {
  const key = `ratelimit:${userId}`;
  const current = await kv.incr(key);
  
  if (current === 1) {
    await kv.expire(key, windowSeconds);
  }
  
  return current <= maxRequests;
}

Leaderboard

async function updateScore(playerId, score) {
  await kv.zadd('leaderboard', { [playerId]: score });
}

async function getTopPlayers(count = 10) {
  return await kv.zrange('leaderboard', 0, count - 1);
}

async function getPlayerRank(playerId) {
  const rank = await kv.zrank('leaderboard', playerId);
  return rank !== null ? rank + 1 : null; // 1-based rank
}

License

MIT