LADC
LADC is a common API on top of relational database (SQL) connectors. It can connect to Postgresql, MariaDB, MySQL, SQLite. The API is inspired from PDO and JDBC. It’s named LADC for “a Layer Above Database Connectors”.
A sample of code
Here is an example of code that uses a LADC connection:
Tutorial: Getting Started with LADC
LADC works with Node 8 or above. Add it to a project:
npm install ladc
Then you'll need an adapter for the DBMS of your choice. Here are the available adapters:
- @ladc/pg-adapter for Postgresql, using the pg connector;
- @ladc/mysql2-adapter for MariaDB and MySQL, using the mysql2 connector;
- @ladc/sqlite3-adapter for SQLite, using the sqlite3 connector.
Let's pick the SQLite's one:
npm install @ladc/sqlite3-adapter
The code below show how to connect to a SQLite database:
;;
The SQLite driver will create an empty database in a new testdb.sqlite
file if it does not exist. We can then create a test
table:
Finally, here is how to execute all this stuff.
main.catchconsole.log;
The Particular Case of Transactions in Asynchronous Programming
In asynchronous programming, it is common to open once a connection to a database. But we shouldn’t use a common connection for transactions, because other queries from other callbacks could be unintentionally executed in the transaction.
LADC provides a pool of connections. Each transaction takes an exclusive underlying connection. When the transaction is committed or rolled back, the underlying connection is released into the pool. In addition, the mechanism is optimized so that, if no operation has taken place simultaneously outside the transaction, then the transaction will have simply used the main underlying connection without opening a second one.
Here is an example of code with a transaction:
Prepared Statements
Drivers for Node.js allow to start several prepared statements on the same connection. But the way to proceed is very different from a DBMS to another. The LADC API provides a common way to use prepared statements:
Cursors
A LADC cursor implements the interfaces AsyncIterable
and AsyncIterator
. Here is how to use a cursor with Node.js 10 and above:
Notice:
- There is a limitation of one cursor by underlying connection;
- The MySQL connector doesn't provide cursors.
How to Integrate a Query Builder
LADC will integrate well with the query builder SQL Bricks, using the package @ladc/sql-bricks-modifier.
Add the dependencies:
npm install sql-bricks @ladc/sql-bricks-modifier
And create the modified connection:
Then, use it:
;
Log Errors
Because LADC uses a pool of underlying connections, errors can occur independently of any query. By default, independant errors are logged with console.error(message)
. But it is possible to log them where you want:
; ;
The Complete API
MainConnection
Members of a Common methods between MainConnection
and TransactionConnection
:
cn.prepare(sql)
returns a promise of aPreparedStatement
;cn.exec(sql, params?)
executes the query and returns a promise of anExecResult
;cn.all(sql, params?)
executes the select query and returns a promise of an array of rows;cn.singleRow(sql, params?)
fetches withcn.all
and returns the single row;cn.singleValue(sql, params?)
fetches withcn.all
and returns the single value of the single row;cn.cursor(sql, params?)
opens a cursor and returns a promise of aAsyncIterableIterator
.
Members that are specific to a MainConnection
:
cn.beginTransaction()
starts a transaction and returns a promise of aTransactionConnection
;cn.script(sql)
executes a multi-line script;cn.close()
closes the LADC connection, this includes closing the pool of underlying connections.
ExecResult
Members of an result.affectedRows
is a readonly number;result.getInsertedId()
returns the inserted identifier;result.getInsertedIdAsNumber()
returns the inserted identifier as anumber
;result.getInsertedIdAsString()
returns the inserted identifier as astring
.
PreparedStatement
Members of a ps.bind(params)
binds values to the specified parameters;ps.bind(indexOrKey, value)
binds a value to the specified parameter;ps.unbind()
unbinds all the bound values;ps.unbind(indexOrKey)
unbinds the value from the specified parameter;ps.exec(params?)
executes the query and returns a promise of anExecResult
;ps.all(params?)
executes the select query and returns a promise of an array of rows;ps.singleRow(params?)
fetches withps.all
and returns the single row;ps.singleValue(params?)
fetches withps.all
and returns the single value of the single row;ps.cursor(params?)
opens a cursor and returns a promise of aAsyncIterableIterator
;ps.close()
closes the prepared statement.
TransactionConnection
Members of a Common methods between MainConnection
and TransactionConnection
:
tx.prepare(sql)
returns a promise of aPreparedStatement
;tx.exec(sql, params?)
executes the query and returns a promise of anExecResult
;tx.all(sql, params?)
executes the select query and returns a promise of an array of rows;tx.singleRow(sql, params?)
fetches withtx.all
and returns the single row;tx.singleValue(sql, params?)
fetches withtx.all
and returns the single value of the single row;tx.cursor(sql, params?)
opens a cursor and returns a promise of aAsyncIterableIterator
.
Members that are specific to a TransactionConnection
:
tx.inTransaction
is a readonly boolean;tx.rollback()
rollbacks the transaction, then releases the underlying connection to the pool;tx.commit()
commits the transaction, then releases the underlying connection to the pool.
Contribute
With VS Code, our recommanded plugin is:
- TSLint from Microsoft (
ms-vscode.vscode-typescript-tslint-plugin
)