SQLtool is a MySQL object modeling tool designed to work in an asynchronous environment. SQLtool supports both promises and callbacks.
$ npm install sqltool2
// Using Node.js `require()`
const sqltool = require('sqltool2');
// Using ES6 imports
import sqltool from 'sqltool2';
await sqltool.connect({
host: 'localhost',
user: 'root',
password: '',
database: 'db_name'
});
Once connected, the open
event is fired on the Connection
instance. If you're using sqltool.connect
, the Connection
is sqltool.connection
. Otherwise, sqltool.createConnection
return value is a Connection
.
Important! SQLtool buffers all the commands until it's connected to the database. This means that you don't have to wait until it connects to MySQL in order to define models.
Models are defined through the Schema interface.
const Schema = sqltool.Schema;
const BlogPost = new Schema({
author: 'VARCHAR',
title: 'VARCHAR',
body: 'TEXT',
date: 'DATE'
});
The following example with some features.
const Comment = new Schema({
name: { type: 'VARCHAR', size: 32, default: 'hahaha' },
age: { type: 'INT', unsigned: true },
bio: { type: 'VARCHAR' },
date: { type: 'DATE', default: new Date() }
});
// create index
Comment.index({name: 'text', bio: 'text'});
// middleware
Comment.pre('save', function (next) {
console.log(this.name);
next();
});
Important! We define table structure through model and table is created automatically.
Once we define a model through sqltool.model('ModelName', mySchema)
, we can access it through the same function
const MyModel = sqltool.model('ModelName');
Or just do it all at once
const MyModel = sqltool.model('ModelName', mySchema);
The first argument is the singular name of the table your model is for.
Once we have our model, we can then instantiate it, and save it:
const instance = MyModel.new();
instance.name = 'karen';
instance.save(function (err) {
//
});
or
const instance = MyModel.new({ name: 'karen' });
instance.save(function (err) {
//
});
We can find documents from the same table
// find all documents
MyModel.find({ }).exec(function (err, docs) {
// docs.forEach
});
// find all documents named karen and at least 19
await MyModel.find({ name: 'karen', age: { $gte: 19 } }).exec();
// executes, passing results to callback
MyModel.find({ name: 'karen', age: { $gte: 19 } }).exec(function (err, docs) {
// docs.forEach
});
// executes, name LIKE karen and only selecting the "name" and "age" fields
await MyModel.find({ name: { $in: 'karen' } }, ['name', 'age']).exec();
// find 3 documents and sort name in DESC order
await MyModel.find({ }).limit(3).sort({ name: -1 }).exec();
// find 3 documents skipping 4
await MyModel.find({ }).limit(3).skip(4).exec();
You can also findOne
, findById
, updateOne
, etc.
const instance = await MyModel.findOne({ ... }).exec();
console.log(instance.name); // 'karen'
Important! If you opened a separate connection using sqltool.createConnection()
but attempt to access the model through sqltool.model('ModelName')
it will not work as expected since it is not hooked up to an active db connection. In this case access your model through the connection you created:
const conn = await sqltool.createConnection({ conn_params });
const MyModel = conn.model('ModelName', schema);
const m = MyModel.new();
m.save(); // works
vs
const conn = await sqltool.createConnection({ conn_params });
const MyModel = sqltool.model('ModelName', schema);
const m = MyModel.new();
m.save(); // does not work b/c the default connection object was never connected