A Sequelize-like ORM for ClickHouse with parameterized queries and SQL injection protection. Backward compatible with webfunny-node-clickhouse.
npm install webfunny-clickhouse> A Sequelize-like ORM for ClickHouse with parameterized queries and SQL injection protection
- 🛡️ 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
``bash`
npm install webfunny-clickhouse @clickhouse/client
`javascript
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)
`
#### Query Records
`javascript
// 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
`javascript
// Create single record
await User.create({
name: 'John',
age: 25,
email: 'john@example.com'
})
// Bulk create
await User.bulkCreate([
{ name: 'John', age: 25 },
{ name: 'Jane', age: 23 }
])
`
#### Update Records
`javascript`
await User.update(
{ status: 'inactive' },
{ where: { id: 123 } }
)
#### Delete Records
`javascript`
await User.destroy({
where: { id: 123 }
})
`javascript
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 }
]
}
`
`javascript
// 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()
`
`javascript
// 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]
}
}
)
`
#### Complex Conditions
`javascript`
const users = await User.findAll({
where: {
[Op.and]: [
{ status: 'active' },
{ age: { [Op.gte]: 18 } },
{
[Op.or]: [
{ role: 'admin' },
{ role: 'moderator' }
]
}
]
}
})
#### Pagination
`javascript
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
`javascript
// 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
`javascript
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 })
`
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
- Built on official @clickhouse/client
- Parameterization adds ~5-10% parsing overhead
- Network I/O is still the bottleneck
- Security benefits far outweigh performance cost
`bash``
npm test
See examples/ directory for complete examples.
Contributions are welcome! Please read our Contributing Guide first.
MIT © Webfunny Team
- GitHub Repository
- Issue Tracker
- Changelog
| Feature | webfunny-clickhouse | sequelize | raw SQL |
|---------|---------------------|-----------|---------|
| SQL Injection Protection | ✅ | ✅ | ❌ |
| ORM-like API | ✅ | ✅ | ❌ |
| ClickHouse Optimized | ✅ | ❌ | ✅ |
| Type Safety | ✅ | ✅ | ❌ |
| Chain Query Builder | ✅ | ✅ | ❌ |
| Learning Curve | Low | Medium | Low |
- Webfunny Monitor System
- Your project here? Let us know!
---
Made with ❤️ by Webfunny Team