Configuring SqlCli
In order for an SqlCli client object to be able to connect and interact with a SQL database, the object needs to be configured first. Essentially, it needs to know two things:
- A “driver” (to know which database to connect to)
- A “connection string” (to connect and access the database)
The driver is specified via the Driver() configuration function, while the connection string is configured via the ConnString() configuration function.
A list of drivers is available in this manual. For each one of such drivers, here below we’re going to document how to build a proper/suitable connection string.
Driver: “n1ql” (Couchbase)
When using the n1ql driver, the connection string can either be the host name (or IP address) and port of a standalone Couchbase instance, or the URL of a Couchbase cluster.
Example of standalone Couchbase connection:
{
var cli = new SqlCli();
cli.Driver("n1ql");
cli.ConnString("localhost:8093");
if (cli.Connect()) {
// Perform SQL tasks....
cli.Close();
}
}
Example of connecting to a Couchbase cluster:
{
var cli = new SqlCli();
cli.Driver("n1ql");
cli.ConnString("http://localhost:9000/");
if (cli.Connect()) {
// Perform SQL tasks....
cli.Close();
}
}
Driver: “firebirdsql” (FirebirdSQL)
When using the firebirdsql driver, the connection string must be in the following format:
user:password@servername/foo/bar.fdb
Example of standalone FirebirdSQL connection:
{
var cli = new SqlCli();
cli.Driver("firebirdsql");
cli.ConnString("user:password@servername/foo/bar.fdb");
if (cli.Connect()) {
// Perform SQL tasks....
cli.Close();
}
}
Driver: “bigquery” (Google BigQuery)
When using the bigquery driver, the connection string must be in the following format:
bigquery://projectid/location/dataset
Example of standalone BigQuery connection:
{
var cli = new SqlCli();
cli.Driver("bigquery");
cli.ConnString("bigquery://projectid/location/dataset");
if (cli.Connect()) {
// Perform SQL tasks....
cli.Close();
}
}
Driver: “mssql” (MS SQL Server)
When using the mssql driver, the general rule to build your connection string is to build it as a URL following this general specification:
sqlserver://username:password@host/instance?param1=valueparam2=value
So, for example, if you’re connecting to a local instance of SQLExpress, you’ll use a connection string that looks like this:
sqlserver://sa@localhost/SQLExpress?database=masterconnection+timeout=30
Whereas if you’re connecting to a SQL server on localhost (but want to specify a non-standard port) you’ll use a connection string like this:
sqlserver://sa:mypass@localhost:1234?database=masterconnection+timeout=30
Remember to URL-encode any and every part of the connection string, because it’s ultimately a URL and must follow the rules of all URLs.
Example of standalone SQL Server connection:
{
var cli = new SqlCli();
cli.Driver("mssql");
cli.ConnString("sqlserver://sa:mypass@localhost?database=master&connection+timeout=30");
if (cli.Connect()) {
// Perform SQL tasks....
cli.Close();
}
}
Driver: “mysql” (MySQL/MariaDB)
When using the mysql driver, the connection string must be in the following format:
user:password@(hostname_or_ipaddr:port)/dbname
Example of standalone MySQL connection:
{
var cli = new SqlCli();
cli.Driver("mysql");
cli.ConnString("user:pass@(localhost:3306)/testdb");
if (cli.Connect()) {
// Perform SQL tasks....
cli.Close();
}
}
Driver: “oracle” (Oracle)
When using the oracle driver, the connection string must be in the following format:
user/pass@hostname_or_ipaddr:port/database
Example of standalone Oracle connection:
{
var cli = new SqlCli();
cli.Driver("oracle");
cli.ConnString("user/pass@localhost:1521/mydb");
if (cli.Connect()) {
// Perform SQL tasks....
cli.Close();
}
}
Driver: “pq” (Postgres)
When using the pg driver, the connection string may contain the following information fields:
- host: the host name or IP address of the machine/VM running Postgres
- port: the port your Postgres listens on (default 5432)
- user: a username to accedd the database
- password: a password to accedd the database
- dbname: the name of the database you want to access
- sslmode: enable || disable
Depending on your database server configuration you may or may not include some of the fields here above in your connection string.
Example of standalone Postgres connection:
{
var cli = new SqlCli();
cli.Driver("pq");
cli.ConnString("host=localhost port=5432 user=your_db_user password=your_db_passwrod dbname=db_name sslmode=disable");
if (cli.Connect()) {
// Perform SQL tasks....
cli.Close();
}
}
Driver: “sqlite” (SQLite)
When using the sqlite driver, the connection string can either be a fully qualified path to the database data file, or the special keyword :memory: if you wish to use a volatile in-memory DB.
Example of in-memory SQLite DB:
{
var cli = new SqlCli();
cli.Driver("sqlite");
cli.ConnString(":memory:");
if (cli.Connect()) {
// Perform SQL tasks....
cli.Close();
}
}
Example of file-based SQLite DB:
{
var cli = new SqlCli();
cli.Driver("sqlite");
cli.ConnString("/home/someuser/mydata.sqlite");
if (cli.Connect()) {
// Perform SQL tasks....
cli.Close();
}
}
Querying a dataset
Querying and extracting data from a database you’re connected to is done via the Query function of the SqlCli client object.
This command accepts a query in whichever SQL dialect your database server speaks, and returns the result set as a JSON array of objects, in which each object is a row in the result set.
The query may contain parameters. Each parameter is passed to the query via an array of untyped values, and each value substitutes a ? (question mark placeholder) in the select statement, in order of appearance.
Example
{
var cli = new SqlCli();
cli.Driver("sqlite");
cli.ConnString("/var/sqlitedbs/mydb.sqlite");
if (cli.Connect()) {
var res = cli.Query('SELECT * FROM places WHERE code=?', [42]);
Log(JSON.stringify(res));
// Output:
// [{"place":"universe","code":42},{"place":"milky way","code":42}]
cli.Close();
}
}
The untyped array of values is optional, and can be skipped when the query doesn’t contain any ? placeholder. For example, the following query is totally valid:
var res = cli.Query('SELECT * FROM places');
And this one with values of different types is valid as well:
var res = cli.Query('SELECT * FROM places WHERE code=? AND place=?', [42, "universe"]);
