linsql
使用TS封装node-mysql的常用操作,可链式调用,与ThinkPHP的查询器类似
部分功能还在优化
Install
npm install --save linsql
Introduction
配置(CONFIG)
参数 | 说明 | 类型 | 默认值 |
---|
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 => {
})
Promise化
-
select
db.name('teacher').where('username', '').select().then(res => {})
async function find() {
let res = await db.name('teacher').where('username','').find()
}
-
update
db.name('teacher').where('username', '').update({
username:'张三'
}).then(res => {
console.log(res)
})
-
delete
db.name('teacher').where('username', '').delete().then(res => {
console.log(res)
})
-
insert
db
.name('teacher')
.insert([{
username: '李四',
create_time: new Date(),
}])
.then((res) => {
console.log(res)
})
-
insertGetId
db
.name('teacher')
.insertGetId([{
username: '李四',
create_time: new Date(),
}])
.then((res) => {
console.log(res)
})
-
query
db.query("SELECT * FROM `sc_teacher` WHERE `username` = ''").then(res => {
})
db.exec("SELECT * FROM `sc_teacher` WHERE `username` = ?", ['']).then(res => {
})
Methods
-
name (names: string | string[] ) 主要用于指定操作的数据表(不包含表前缀)
db.name('teacher').where('username', '').select()
选择多张表
db.name(['teacher', 'student'])
设置表别名
db.name(['teacher t', 'student s'])
-
table (names:string | string[] ) 主要用于指定操作的数据表(包含表前缀)
db.table('sc_teacher').where('username', '').select()
-
where (field:string|object, operator?:string|number, condition?:string|string[]|number|number[]) 用于数据查询
db.name('teacher').where('username = \'\'')
db.name('teacher').where('username', '')
db.name('teacher').where('username', '=', '')
db.name('teacher').where({
username:['']
})
db.name('teacher').where({
username:['=', '']
})
db.name('teacher').where('username', 'like', '%张')
db.name('teacher').where({
username:['like','%张']
})
db.name('teacher').where({
username:[
['like', '%张'],
'or',
['like', '张%']
]
})
db.name('teacher').where({
id:[
['>', 1],
['<', 10]
]
})
db.name('teacher').where({
id:[
['>',1],
'and',
['<', 10]
]
})
db.name('teacher').where('id', 'between', [1,4])
db.name('teacher').where('id', 'not between', [1,4])
db.name('teacher').where('id', 'in', [1,2,3])
db.name('teacher').where('id', 'not in', [1,2,3])
db.name('teacher').where('id', 'null')
db.name('teacher').where('id', 'not null')
-
alias (names:string | object ) 用于设置当前的数据表的别名
db.table('sc_teacher').alias('a').where('username', '').select()
db.table(['sc_teacher', 'sc_student']).alias({
'sc_teacher':'a',
'sc_student':'b'
}).where('username', '').select()
-
field (fields:string ) 主要用于返回指定的字段
db.table('sc_teacher').field('id,username,create_time').where('username', '').select()
-
limit (star:string|number, end?:number) 主要用于分页操作
db.table('sc_teacher').where('username', '').limit('1,5').select()
db.table('sc_teacher').where('username', '').limit(1,5).select()
-
group (fields:string)
db.table('sc_teacher').where('username', '').group('id').select()
-
distinct (isDistinct: boolean)
db.table('sc_teacher').field('username').distinct(true).select()
-
join (table: string, condition: string, joinType:sqlJoinType = 'INNER')
type JoinType = 'INNER' | 'LEFT' | 'RIGHT' | 'FULL'
db.table('sc_teacher').alias('a').join("sc_student b", "a.username = b.teacher_name").select()
db.table('sc_teacher').alias('a').where('username', '').join("sc_student b", "a.username = b.teacher_name").select()
-
order (field: string)
db.table('sc_teacher').where('username', '').order('id DESC').select()
db.table('sc_teacher').where('username', '').order('id DESC, username ASC').select()
-
db.table('sc_teacher').comment("Query sc_teacher").where('username', '').select()
-
transform (sql:string, values:any[]) 主要用于转换sql查询语句
db.transform('SELECT * FROM sc_teacher a WHERE a.username = ?', [''])
-
parse () 解析查询器
db.name('teacher').where('username', '').parse('prefix')
Jest
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)
})
})