webfunny-clickhouse

A Sequelize-like ORM for ClickHouse with parameterized queries and SQL injection protection

✨ Features

• 🛡️ SQL Injection Protection - Parameterized queries prevent SQL injection attacks
• 📝 Sequelize-like API - Familiar ORM-style interface for ClickHouse
• 🔗 Chain Query Builder - Build complex queries with method chaining
• 🎯 Rich Operators - Support for 18+ query operators (eq, gt, like, in, between, etc.)
• 🚀 Auto Table Creation - Intelligent table creation with existence checking
• 📊 Type Inference - Automatic type detection for query parameters
• ⚡ Performance - Built on official @clickhouse/client
• 🔄 Backward Compatible - Works alongside existing SQL queries

📦 Installation

npm install webfunny-clickhouse @clickhouse/client

🚀 Quick Start

const WebfunnyClickHouse = require('webfunny-clickhouse')
const { Op } = WebfunnyClickHouse
const { createClient } = require('@clickhouse/client')

// Create ClickHouse client
const client = createClient({
  host: 'http://localhost:8123',
  username: 'default',
  password: '',
  database: 'default'
})

// Create model instance
const User = new WebfunnyClickHouse({
  client,
  tableName: 'users'
})

// Query data
const users = await User.findAll({
  where: {
    age: { [Op.gt]: 18 },
    status: 'active'
  },
  limit: 10
})

console.log(users)

📚 Documentation

Basic Usage

Query Records

// Find all
const users = await User.findAll({
  where: { status: 'active' }
})

// Find one
const user = await User.findOne({
  where: { id: 123 }
})

// Find by primary key
const user = await User.findByPk(123)

// Count
const count = await User.count({
  where: { status: 'active' }
})

Create Records

// Create single record
await User.create({
  name: 'John',
  age: 25,
  email: '[email protected]'
})

// Bulk create
await User.bulkCreate([
  { name: 'John', age: 25 },
  { name: 'Jane', age: 23 }
])

Update Records

await User.update(
  { status: 'inactive' },
  { where: { id: 123 } }
)

Delete Records

await User.destroy({
  where: { id: 123 }
})

Operators

const { Op } = WebfunnyClickHouse

// Comparison
{ field: { [Op.eq]: value } }     // =
{ field: { [Op.ne]: value } }     // !=
{ field: { [Op.gt]: value } }     // >
{ field: { [Op.gte]: value } }    // >=
{ field: { [Op.lt]: value } }     // <
{ field: { [Op.lte]: value } }    // <=

// Range
{ field: { [Op.in]: [1, 2, 3] } }           // IN
{ field: { [Op.notIn]: [1, 2, 3] } }        // NOT IN
{ field: { [Op.between]: [10, 100] } }      // BETWEEN

// String
{ field: { [Op.like]: '%test%' } }          // LIKE
{ field: { [Op.notLike]: '%test%' } }       // NOT LIKE
{ field: { [Op.startsWith]: 'test' } }      // LIKE 'test%'
{ field: { [Op.endsWith]: 'test' } }        // LIKE '%test'

// NULL
{ field: { [Op.isNull]: true } }            // IS NULL
{ field: { [Op.isNotNull]: true } }         // IS NOT NULL

// Logic
{
  [Op.and]: [
    { field1: value1 },
    { field2: value2 }
  ]
}
{
  [Op.or]: [
    { field1: value1 },
    { field2: value2 }
  ]
}

Chain Query Builder

// Basic chain
const users = await User
  .select(['id', 'name', 'age'])
  .where({ status: 'active' })
  .orderBy('age', 'DESC')
  .limit(10)
  .execute()

// Complex chain
const users = await User
  .select('*')
  .where({ status: 'active' })
  .whereRaw('age > :minAge', { minAge: 18 })
  .orderBy('createdAt', 'DESC')
  .limit(10)
  .offset(0)
  .execute()

// Get first record
const user = await User
  .where({ status: 'active' })
  .orderBy('createdAt', 'DESC')
  .first()

// Get count
const count = await User
  .where({ status: 'active' })
  .count()

Parameterized Queries

// Named parameters (recommended)
const result = await User.query(
  'SELECT * FROM users WHERE id = :id AND name = :name',
  {
    replacements: {
      id: 123,
      name: 'John'
    }
  }
)

// Positional parameters
const result = await User.query(
  'SELECT * FROM users WHERE id = ? AND name = ?',
  {
    replacements: [123, 'John']
  }
)

// IN clause (auto-expand)
const result = await User.query(
  'SELECT * FROM users WHERE id IN (:ids)',
  {
    replacements: {
      ids: [1, 2, 3, 4, 5]
    }
  }
)

Advanced Usage

Complex Conditions

const users = await User.findAll({
  where: {
    [Op.and]: [
      { status: 'active' },
      { age: { [Op.gte]: 18 } },
      {
        [Op.or]: [
          { role: 'admin' },
          { role: 'moderator' }
        ]
      }
    ]
  }
})

Pagination

const page = 1
const pageSize = 20
const offset = (page - 1) * pageSize

const users = await User.findAll({
  where: { status: 'active' },
  limit: pageSize,
  offset: offset,
  order: [['createdAt', 'DESC']]
})

const total = await User.count({
  where: { status: 'active' }
})

Dynamic Table Names

// For log tables with dynamic names
const ErrorLog = new WebfunnyClickHouse({
  client,
  tableName: 'error_log' // default table name
})

// Use different table
const errors = await ErrorLog.findAll({
  where: { level: 'error' },
  tableName: `error_log_20241004` // override
})

// Or use withTable
const errors = await ErrorLog
  .withTable('error_log_20241004')
  .where({ level: 'error' })
  .execute()

Auto Table Creation

const User = new WebfunnyClickHouse({
  client,
  schemaPath: './schema/user.js' // schema file
})

// Auto create table if not exists
// force: true means fail-safe mode
await User.createTable({ force: true })

🔐 Security Features

1. SQL Injection Prevention - All user inputs are parameterized
2. Type Safety - Automatic type inference and conversion
3. Input Validation - Table names and field names are validated
4. Whitelist Mechanism - Support for dynamic table name whitelisting

📊 Performance

• Built on official @clickhouse/client
• Parameterization adds ~5-10% parsing overhead
• Network I/O is still the bottleneck
• Security benefits far outweigh performance cost

🧪 Testing

npm test

📝 Examples

See examples/ directory for complete examples.

🤝 Contributing

Contributions are welcome! Please read our Contributing Guide first.

📄 License

MIT © Webfunny Team

🔗 Links

• GitHub Repository
• Issue Tracker
• Changelog

💡 Comparison with Other Libraries

| Feature | webfunny-clickhouse | sequelize | raw SQL | |---------|---------------------|-----------|---------| | SQL Injection Protection | ✅ | ✅ | ❌ | | ORM-like API | ✅ | ✅ | ❌ | | ClickHouse Optimized | ✅ | ❌ | ✅ | | Type Safety | ✅ | ✅ | ❌ | | Chain Query Builder | ✅ | ✅ | ❌ | | Learning Curve | Low | Medium | Low |

🌟 Who's Using

• Webfunny Monitor System
• Your project here? Let us know!

Made with ❤️ by Webfunny Team