SphinxQL query builder for Node.JS wrote in Typescript. Make easy queries avoiding to write raw SphinxQL strings always that you can. By default, it uses escaped query parameters, always thinking in security.
It is heavily inspired in the PHP SphinxQL-Query-Builder and also the Eloquent query builder (Laravel framework ORM)
The client used for create connection is mysql2 that is focused in performance.
You must use Node.JS >= 6.x
Just run the npm command:
npm install --save sphinxql
To create a simple connection (not the most recommended, use a pool connection) and write your first query, just do this:
const { Sphinxql, Expression } = require('sphinxql');
const sphql = Sphinxql.createConnection({
host: 'localhost',
port: 9306
});
sphql.getQueryBuilder()
.select('*')
.from('books')
.match('title', 'harry potter')
.where('created_at', '<', Expression.raw('YEAR()'))
.between(Expression.raw(`YEAR(created_at)`), 2014, 2019)
.orderBy({'date_published': 'ASC', 'price': 'DESC'})
.limit(10)
.execute()
.then((result, fields) => {
console.log(result);
})
.catch(err => {
console.log(err);
});
There are two possible ways of creating a connection between your application and the Manticore/Sphinx server.
First and simplest is using the createConnection
method.
const { Sphinxql } = require('sphinxql');
const sphql = Sphinxql.createConnection({
host: 'localhost',
port: 9306
});
The second option is using createPoolConnection
method. This methodology allows you to have multiple open connections
with Manticore/Sphinx reusing previous connections. To learn more about mysql2 connection pools (allowed parameters for
the creation and configuration of the pool) read mysql2 documentation about using connection pools.
This technique uses more memory so be aware.
const { Sphinxql } = require('sphinxql');
// Create the connection pool. The pool-specific settings are the defaults
const sphql = Sphinxql.createPoolConnection({
host: 'localhost',
port: 9306,
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
This section is separated in many parts but if you have used SphinxQL before or SQL you can see this section also very basic for you. Anyway i recommend strongly to read the Manticore Search or Sphinx documentation for making a good idea of how to use this API.
- where(columnExpr: string, operator: string, value?: any)
- whereIn(column: string, values: any[])
- whereNotIn(column: string, values: any[])
- between(column: string, value1: any, value2: any)
- match(fields: string[] | string, value: string, escapeValue: boolean = true)
- orMatch(fields: string[] | string, value: string, escapeValue: boolean = true)
Example here:
sphql.getQueryBuilder()
.select('id', 'author_id', 'publication_date')
.from('books')
.match('*', '"harry potter"', false)
.whereIn('lang', ['en', 'sp', 'fr'])
.between(Expression.raw(`YEAR(publication_date)`), 2008, 2015)
.execute()
.then((result, fields) => {
console.log(result);
})
.catch(err => {
console.log(err);
});
You can chain multiple options using the method "option". The method head is:
- option(option: string, value: any) where value argument can be an instance of:
- Expression instance for unescaped parameters
- key-value object (example below)
- string for simple and escaped option value.
Example with OPTION:
sphql.getQueryBuilder()
.select('id', 'author_id', 'publication_date')
.from('books')
.match('*', '"harry potter"', false)
.between(Expression.raw(`YEAR(publication_date)`), 2008, 2015)
.orderBy({'publication_date': 'ASC', 'price': 'DESC'})
.limit(10)
.option('rank_fields', 'title content')
.option('field_weights', {title: 100, content: 1})
.execute()
.then((result, fields) => {
console.log(result);
})
.catch(err => {
console.log(err);
});
// TODO
An INSERT statement is created like this:
const document = {
id: 1,
content: 'this is the first post for the blog...',
title: 'First post'
};
connection.getQueryBuilder()
.insert('my_rtindex', document)
.execute()
.then((result, fields) => {
console.log(result);
})
.catch(err => {
console.log(err);
});
Or using an array of key-value pairs to insert multiple values in the same query
const document = [{
id: 1,
content: 'this is the first post for the blog...',
title: 'First post'
}, {
id: 2,
content: 'this is the second post for the blog...',
title: 'Second post'
}];
connection.getQueryBuilder()
.insert('my_rtindex', document)
.execute()
.then((result) => {
console.log(result);
})
.catch(err => {
console.log(err);
});
Replaces a document using the doc id or insert. Similar to insert statement only changing INSERT for REPLACE.
const document = {
id: 1,
content: 'this is the first post for the blog...',
title: 'UPDATE! First post'
};
connection.getQueryBuilder()
.replace('my_rtindex', document)
.execute()
.then((result) => {
console.log(result);
})
.catch(err => {
console.log(err);
});
const document = {
content: 'UPDATE! it\'s an old post. this is the first post for the blog...',
title: 'First post (edit)'
};
connection.getQueryBuilder()
.update('my_rtindex')
.set(document)
.match('fullname', 'John')
.where('salary', '<', 3000)
.execute()
.then((result, fields) => {
console.log(result);
})
.catch(err => {
console.log(err);
});
This package also comes with support for transactions. Remember that transactions are only available for RT indexes. For more information visit transactions documentation for Manticore search.
The transactions API is simple and the list of methods is below here:
- connection.getQueryBuilder().transaction.begin()
- connection.getQueryBuilder().transaction.start() // same that begin()
- connection.getQueryBuilder().transaction.commit()
- connection.getQueryBuilder().transaction.rollback()
all this methods returns a promise object.
A simple example working with transactions:
const document = {
id: 1,
content: 'this is the first post for the blog...',
title: 'First post'
};
const insertDocumentAndCommit = async (doc) => {
await connection.getQueryBuilder().transaction.begin();
connection.getQueryBuilder()
.insert('my_rtindex', doc)
.execute()
.then((result, fields) => {
console.log(result);
})
.catch(err => {
console.log(err);
});
await connection.getQueryBuilder().transaction.commit();
return true;
}
insertDocumentAndCommit(document);
First of all you need to know the limitations of multi queries in Manticore/Sphinx. As Manticore Search and Sphinx documentation said there is only support for the following statements used in a batch:
- SELECT
- SHOW WARNINGS
- SHOW STATUS
- SHOW META
Said this, now is the moment to write code. There is a class, Queue, that implements just the necessary methods, it is usefull to run multi queries. To enable multi statements you must specify in your configuration object for the connection creation the multipleStatements: true as follow:
const { Sphinxql } = require('sphinxql');
const sphql = Sphinxql.createConnection({
host: 'localhost',
port: 9306,
multipleStatements: true
});
Now let's create a queue and process it:
const { Queue, Sphinxql } = require('sphinxql');
const sphql = Sphinxql.createConnection({
host: 'localhost',
port: 9306,
multipleStatements: true
});
const queue = new Queue(sphql.getConnection());
queue
.push(sphql.getQueryBuilder().select('*').from('rt').where('id', '=', 1))
.push(
sphql.getQueryBuilder()
.select('id', 'author_id', 'publication_date')
.from('books')
.match('*', '"harry potter"', false)
);
queue.process()
.then(results => {
console.log(results.results.length) // 2
})
.catch(err => console.log(err));
- optimizeIndex(index: string): Promise
- attachIndex(diskIndex: string): AttachIndexStatement
- truncate(rtIndex: string): TruncateStatement
- reloadIndex(index: string): ReloadIndexStatement
Read about ATTACH INDEX in Manticore documantation To use this statement see example below:
connection.getQueryBuilder()
.attachIndex('my_disk_index')
.to('my_rt_index')
.withTruncate() // this method is optional
.execute()
.then((result, fields) => {
console.log(result);
})
.catch(err => {
console.log(err);
});
Read about FLUSH RTINDEX To use this statement see example below):
connection.getQueryBuilder()
.flushRTIndex('my_rt_index')
.execute()
.then((result, fields) => {
console.log(result);
})
.catch(err => {
console.log(err);
});
Read about TRUNCATE RTINDEX in Manticore documantation To use this statement see example below:
connection.getQueryBuilder()
.truncate('my_rt_index')
.withReconfigure() // this method is optional
.execute()
.then((result, fields) => {
console.log(result);
})
.catch(err => {
console.log(err);
});
Read about RELOAD INDEX in Manticore documantation To use this statement see example below:
connection.getQueryBuilder()
.reloadIndex('my_index')
.from('/home/mighty/new_index_files') // this method is optional
.then((result, fields) => {
console.log(result);
})
.catch(err => {
console.log(err);
});
Run raw queries using the query method that is available after call getQueryBuilder method. This method allows prepared statement using a ? (question mark) where you want to escape the value.
connection.getQueryBuilder()
.query(`SELECT * FROM sales WHERE MATCH(@title "italian lamp") AND tags IN (?, ?)`, ['home', 'italian style'])
.then((result, fields) => {
console.log(result);
})
.catch(err => {
console.log(err);
});
All statements has a final method which is used internally to execute queries. This method is available outside using generate() and returns a string with the final query.
const sphinxqlQuery = connection.getQueryBuilder()
.select('user_id', 'product_id', Expression.raw('SUM(product_price) as total').getExpression())
.from('rt_sales')
.facet((f) => {
return f
.fields(['category_id'])
.by(['category_id'])
})
.facet((f) => {
return f
.field('brand_id')
.orderBy(Expression.raw('facet()'))
.limit(5)
})
.generate();
console.log(sphinxqlQuery); // SELECT user_id, product_id, SUM(product_price) as total FROM rt_sales FACET category_id BY category_id FACET brand_id ORDER BY facet() DESC LIMIT 5