linsql
Advanced tools
Readme
使用TS封装node-mysql的常用操作，可链式调用，与ThinkPHP的查询器类似 部分功能还在优化
npm install --save linsql
| 参数 | 说明 | 类型 | 默认值 |
|---|---|---|---|
| host | 数据库的主机名 | string | 必填项 |
| port | 要连接的端口号 | string | 3306 |
| user | MySQL用户 | string | 必填项 |
| password | MySQL用户的密码 | string | 必填项 |
| database | 数据库名称 | string | 必填项 |
| prefix | 数据库的前缀 | string | 可选 |
其他配置 mysqljs/mysql
import linsql from 'linsql'
const db = linsql.connect({
host: 'localhost',
password: 'root',
user: 'root',
database: 'demo',
prefix: 't_'
})
import linsql from 'linsql'
const db = linsql.connect({
host: 'localhost',
password: 'root',
user: 'root',
database: 'school',
prefix: 'sc_'
})
db.name('teacher').where('username', '').select().then(res => {
//return [rows]
})
//select
db.name('teacher').where('username', '').select().then(res => {})
async function find() {
let res = await db.name('teacher').where('username','').find()
}
db.name('teacher').where('username', '').update({
username:'张三'
}).then(res => {
console.log(res) //return affected rows
})
db.name('teacher').where('username', '').delete().then(res => {
console.log(res) //return affected rows
})
db
.name('teacher')
.insert([{
username: '李四',
create_time: new Date(),
}])
.then((res) => {
console.log(res) //return { affectedRows, insertId }
})
db
.name('teacher')
.insertGetId([{
username: '李四',
create_time: new Date(),
}])
.then((res) => {
console.log(res) //return insert Id
})
db.query("SELECT * FROM `sc_teacher` WHERE `username` = ''").then(res => {
//return { affectedRows, insertId }
})
//PrepareStatement机制
db.exec("SELECT * FROM `sc_teacher` WHERE `username` = ?", ['']).then(res => {
//return { affectedRows, insertId }
})
db.name('teacher').where('username', '').select()
//SELECT * FROM `sc_teacher` WHERE `username` = ''
db.name(['teacher', 'student'])
//SELECT * FROM t_teacher,t_student
db.name(['teacher t', 'student s'])
//SELECT * FROM t_teacher AS t,t_student AS s
db.table('sc_teacher').where('username', '').select()
//SELECT * FROM `sc_teacher` WHERE `username` = ''
field 查询的字段 也可以是一段查询表达式 如: 'id is not null'
operator 查询表达式(不区分大小写)
| 表达式 | 说明 |
|---|---|
= or eq | 等于 |
> or gt | 大于 |
< or lt | 小于 |
>= or egt | 大于等于 |
<= or elt | 小于等于 |
<> or neq | 不等于 |
like | 模糊查询 |
between | 区间查询 |
not between | 不在区间查询 |
in | in查询 |
not in | 不在in查询 |
null | null查询 |
not null | 不是null查询 |
| 表达式还不支持时间, 后续迭代 |
condition 查询的条件
//以下的查询都是等价的
db.name('teacher').where('username = \'\'')
db.name('teacher').where('username', '')
db.name('teacher').where('username', '=', '')
db.name('teacher').where({
username:[''] // or username:'' 默认是=表达式
})
db.name('teacher').where({
username:['=', '']
})
//一个字段有多个条件情况下
//like
db.name('teacher').where('username', 'like', '%张')
db.name('teacher').where({
username:['like','%张']
})
//多个like查询
db.name('teacher').where({
username:[
['like', '%张'],
'or', // 'and'
['like', '张%']
]
})
db.name('teacher').where({
id:[
['>', 1],
['<', 10]
]
})
//等价于下面这个例子
db.name('teacher').where({
id:[
['>',1],
'and',
['<', 10]
]
})
//between查询
db.name('teacher').where('id', 'between', [1,4]) //条件为数组
db.name('teacher').where('id', 'not between', [1,4])
//in查询
db.name('teacher').where('id', 'in', [1,2,3])
db.name('teacher').where('id', 'not in', [1,2,3])
//null查询
db.name('teacher').where('id', 'null')
db.name('teacher').where('id', 'not null')
db.table('sc_teacher').alias('a').where('username', '').select()
//SELECT * FROM `sc_teacher` WHERE `username` = ''
db.table(['sc_teacher', 'sc_student']).alias({
'sc_teacher':'a',
'sc_student':'b'
}).where('username', '').select()
db.table('sc_teacher').field('id,username,create_time').where('username', '').select()
//SELECT id,username,create_time FROM `sc_teacher` WHERE `username` = ''
db.table('sc_teacher').where('username', '').limit('1,5').select()
db.table('sc_teacher').where('username', '').limit(1,5).select()
//SELECT * FROM `sc_teacher` WHERE `username` = '' LIMIT 1,5
db.table('sc_teacher').where('username', '').group('id').select()
//SELECT * FROM `sc_teacher` WHERE `username` = '' GROUP BY id
db.table('sc_teacher').field('username').distinct(true).select()
//SELECT DISTINCT username FROM `sc_teacher`
type JoinType = 'INNER' | 'LEFT' | 'RIGHT' | 'FULL'
db.table('sc_teacher').alias('a').join("sc_student b", "a.username = b.teacher_name").select()
//SELECT * FROM sc_teacher a INNER JOIN sc_student b ON a.username = b.teacher_name
db.table('sc_teacher').alias('a').where('username', '').join("sc_student b", "a.username = b.teacher_name").select()
//SELECT * FROM sc_teacher a INNER JOIN sc_student b ON a.username = b.teacher_name WHERE a.username = ''
db.table('sc_teacher').where('username', '').order('id DESC').select()
//SELECT * FROM sc_teacher a WHERE a.username = '' ORDER BY `id` DESC
db.table('sc_teacher').where('username', '').order('id DESC, username ASC').select()
//SELECT * FROM sc_teacher a WHERE a.username = '' ORDER BY `id` DESC, `username` ASC
db.table('sc_teacher').comment("Query sc_teacher").where('username', '').select()
//SELECT * FROM sc_teacher a WHERE a.username = '' ##Query sc_teacher
db.transform('SELECT * FROM sc_teacher a WHERE a.username = ?', [''])
//SELECT * FROM sc_teacher a WHERE a.username = ''
db.name('teacher').where('username', '').parse('prefix')
//{
// sql:SELECT * FROM t_teacher WHERE `username` = ?,
// values:['']
//}
npm run test
const config = {
host: '127.0.0.1',
database: 'ormtype',
user: 'root',
password: 'root',
prefix: 'sc_'
}
let db = Db.connect(config)
describe('where查询 => table:teacher,condition:id = 1', () => {
it('用例1:', () => {
let data = {
sql: 'SELECT * FROM `sc_teacher` WHERE `id` = ?',
values:[1]
}
db.name('teacher').where('id',1)
let map = db.getEscapMap()
expect(map).toEqual(data)
})
})
FAQs
使用TS封装node-mysql的常用操作，可链式调用，与ThinkPHP的查询器类似
The npm package linsql receives a total of 6 weekly downloads. As such, linsql popularity was classified as not popular.
We found that linsql demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
The Python Software Foundation has secured a 5-year sponsorship from Fastly that supports PSF's activities and events, most notably the security and reliability of the Python Package Index (PyPI).
Security News
LDAPjs, an LDAP Client and Server API for Node.js, was decommissioned after its maintainer received an abusive email from a user, raising concerns about this form of abuse as a potential attack vector.
Security News
CISA launched a new project called Vulnrichment to enrich CVEs with details that help prioritize patching and mitigation efforts, as the NVD backlog of unenriched CVEs awaiting analysis surpasses 10,000.