Xardao is a universal database driver. It wraps around database-specific drivers to provide a simple, elegant way to execute SQL statements. Currently supported databases are:
// Let's import the module
const xardao = require ('xardao')
// Then create a connection
cn = new xardao.Connection('mariadb://root:rootpw@localhost/apptest')
// and open it
await cn.open()
// Get data
let oc = await cn.getObjects("select * from contact")
// Finally look at the result
console.log( JSON.stringify(oc,undefined,4))
The output will look like this:
[
{
"id": 1,
"firstname": "James",
"lastname": "O'Connor",
"birthdate": "1957-08-09 11:00:00",
"age": 62
},
{
"id": 3,
"firstname": "John1",
"lastname": "Doe-1",
"birthdate": "2001-06-08 16:00:00",
"age": 18
},
{
"id": 4,
"firstname": "John2",
"lastname": "Doe-2",
"birthdate": "2001-06-08 16:00:00",
"age": 18
}
]
Well, that is only teaser. Xardao provides much more:
Establishing a database connection required 2 steps:
To create the Connection object simply use the Connection generator:
let cn = new xardao.Connection('mariadb://root:rootpw@localhost/apptest')
Then open it:
// Using promises
await cn.open()
// Using a callback function
cn.open( function() {
// Place your code here
})
The connection string in the following format: protocol >://[<username>[:<password>]@]<hostname>/<database>[?<parameters>]
Below are basic examples to connect using default options:
mariadb://user:userpw@localhost/apptestpostgres://user:userpw@localhost/apptestmssql://user:userpw@localhost/apptestsqlite://databaseFor complete details of connection string parameters, please check the section "Connection string reference"
Xardao provides 3 simple methods for reading and updating data:
Other SQL-related funtions are described in "Function reference".
Calls to exec and getObjects are very similar. The description below applies to both functions.
Static SQL statement:
let oc = await cn.getObjects("select * from contact")
To call using parameters and behavior options, you need to pass an object that contains at least 2 properties:
let oc = await cn.getObjects({
sql: "select * from contact where id = @id",
params: { id: 4 },
options: { useSnakeCase: true } // This is optional.
})
Xardao Connection lastInsertId contains the ID of the last inserted record, when running inserts statements.
Note that Postgresql does not provide the last insert id mechanism when using exec(). Instead, you need to use
getScalar() and specify which field needs to be returned:
let newId = await cn.getScalar("insert into contact(firstname, lastname) values ('John','Doe') returning id ")
Asynchronous functions support both the promise and callback style. When no callback function is provided, the
promise style is assumed and the call to the function must include the await predicate.
xardao.Connection(URI)
Create a new connection as specified by URI. (See Connection string reference).
Connection#open([callback])
Opens the connection.
Connection#close([callback])
Closes the connection.
Connection#exec(query <string || object>, [callback])
Executes the query. If query is an object, you can specify parameters and options (see "Using exec and getObjects").
Connection#getObjects(query <string || object>, [callback])
Executes the query. If query is an object, you can specify parameters and options (see "Using exec and getObjects").
Returns an array of objects.
Connection#getSingleObject(query <string || object>, [callback])
Executes the query. If query is an object, you can specify parameters and options (see "Using exec and getObjects").
Returns the first row as an object.
Connection#getScalar(query <string || object>, [callback])
Executes the query. If query is an object, you can specify parameters and options (see "Using exec and getObjects").
Returns the first field of the row as single value.
Connection#getList(query <string || object>, [callback])
Executes the query. If query is an object, you can specify parameters and options (see "Using exec and getObjects").
Returns an array containing only the first field of each row.
For example:
let newId = await cn.getList("select id from myLOV")
may return something like:
[ "New", "Active", "Inactive" ]
Connection#getKVList(query <string || object>, [callback])
Executes the query. If query is an object, you can specify parameters and options (see "Using exec and getObjects").
Returns a dictionary (object) where the first field of each row is the key name and the second field of each row is the value.
For example:
let newId = await cn.getKVList("select id, description from myLOV")
may return something like:
{
"New": "The item was just added.",
"Active": "The item is fully functional.",
"Inactive": "The item may not be used."
}
When using a database with Express, it is important to ensure that all DB connections are properly closed after
executing a handler.
The Xardao method express.connect opens a connection while making sure it will close properly when the response closes.
To open a connection that will automatically close in your handler, follow the example below:
function myHandler(req, res, next ) {
let conn = await xardao.express.connect("postgres://user:userpw@localhost/apptest")
// Your code here...
// No need to close the connection. It will close at the same time as the response.
}
Note:Do not forget the the await predicate, as your ardao.express.connect returns a promise !
The connection string uses standard URI format:
<protocol>://[<username>[:<password>]@]<hostname>/<database>[?<paramname>=<value>[&<paramname>=<value>]...]
Protocol for mariaDB or Mysql can be "mariadb" or "mysql". Both will work the same way.
Protocol for mariaDB or PostgresQL can be "pg", "pgsql" or "postgres". All will work the same way.
Protocol for mariaDB or Mysql can be "ms" or "mssql". Both will work the same way.
Protocol for mariaDB or Mysql can be "sqlite" or "sqlite3". Both will work the same way.
Xardao is made available under MIT License