# will-sql

> SQL by place holder parameter naming and value setting for execute statement on databases

Latest version **1.0.27** (published 2026-02-02) · MIT license · 0 weekly downloads

## Install

```sh
npm install will-sql
pnpm add will-sql
yarn add will-sql
bun add will-sql
```

## Health

**Score 55/100 (C)** — status: stable.

Positive: has types; no vulnerabilities; high quality score.

Warnings: low downloads; no esm support.

## Facts

| | |
|---|---|
| Version | 1.0.27 |
| Published | 2026-02-02 |
| First published | 2022-10-15 |
| Weekly downloads | 0 |
| License | MIT |
| TypeScript types | bundled |
| Module format | CommonJS |
| Node | >=20 |
| Dependencies | 2 |
| Unpacked size | 107.8 KB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| GitHub stars | 0 |
| Author | tassun_oro@hotmail.com |
| Maintainers | tassun_oro |
| Keywords | SQL, Database, DBConnect, DBConnections, DBAlias, DBConfig, KnSQL |

## Links

- npm: https://www.npmjs.com/package/will-sql
- Repository: https://github.com/tassun/will-sql
- Homepage: https://github.com/tassun/will-sql#readme
- Issues: https://github.com/tassun/will-sql/issues
- npm.io page: https://npm.io/package/will-sql

## Dependencies (2)

- [config](https://npm.io/package/config.md) ^3.3.8
- [will-util](https://npm.io/package/will-util.md) ^1.0.11

## Alternatives

- [@libsql/sqlite3](https://npm.io/package/@libsql/sqlite3.md) — 39.8K weekly downloads
- [@fortemi/core](https://npm.io/package/@fortemi/core.md) — 461 weekly downloads
- [cdb-converter](https://npm.io/package/cdb-converter.md) — 341 weekly downloads
- [@uplo/adapter-prisma](https://npm.io/package/@uplo/adapter-prisma.md) — 75 weekly downloads
- [typeorm-aios](https://npm.io/package/typeorm-aios.md) — 30 weekly downloads

## Recent versions

- 1.0.27 (latest) — 2026-02-02
- 1.0.26 — 2024-04-16
- 1.0.25 — 2023-10-01
- 1.0.24 — 2023-10-01
- 1.0.23 — 2023-09-13
- 1.0.22 — 2023-08-31
- 1.0.21 — 2023-08-19
- 1.0.20 — 2023-08-19
- 1.0.19 — 2023-08-04
- 1.0.18 — 2023-02-22
- 1.0.17 — 2023-01-18
- 1.0.16 — 2022-11-11
- 1.0.15 — 2022-11-11
- 1.0.14 — 2022-11-09
- 1.0.13 — 2022-10-30
- … 13 more at https://npm.io/package/will-sql/versions

## README

# will-sql

SQL by place holder parameter naming and value setting for execute statement on databases

## Installation

    npm install will-sql

## Examples

### Configuration

This module require configuration([config](https://www.npmjs.com/package/config)) setting by config/default.json under project and [will-util](https://www.npmjs.com/package/will-util)

    npm install config

```json
{
    "MYSQL" : { "alias": "mysql", "dialect": "mysql", "url": "mysql://user:password@localhost:3306/testdb?charset=utf8&connectionLimit=10", "user": "user", "password": "password" },
    "ODBC" : { "alias": "odbc", "dialect": "mysql", "url": "DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=localhost;DATABASE=testdb;HOST=localhost;PORT=3306;UID=user;PWD=password;", "user": "user", "password": "password" },
    "MSSQL": { "alias": "mssql", "dialect": "mssql", "url": "Server=localhost,1433;Database=testdb;User Id=user;Password=password;Encrypt=false;Trusted_Connection=Yes;", "user": "user", "password": "password" },
    "ORACLE": { "alias": "oracle", "dialect": "oracle", "url": "localhost:1521/ORCLCDB.localdomain", "user": "user", "password": "password" },
    "POSTGRES": { "alias": "postgres", "dialect": "postgres", "url": "postgresql://user:password@localhost:5432/testdb", "user": "user", "password": "password" },
    "INFORMIX": { "alias": "odbc", "dialect": "informix", "url": "DRIVER={IBM INFORMIX ODBC DRIVER (64-bit)};SERVER=online_localhost;DATABASE=refdb;HOST=localhost;SERVICE=9088;UID=user;PWD=password;CLIENT_LOCALE=th_th.thai620;DB_LOCALE=th_th.thai620;", "user": "user", "password":"password" },
    "SQLITE" : { "alias": "sqlite", "dialect": "sqlite", "url": ":memory:", "user": "", "password": "" },
    "MYSQL2" : { "alias": "mysql2", "dialect": "mysql", "url": "", "user": "user", "password": "password", "host": "localhost", "port": 3306, "database": "testdb", "options": { "charset": "utf8", "connectionLimit": 10 } },
}
```
    npm install will-util

### Queries
Since [mysql](https://www.npmjs.com/package/mysql), [mssql](https://www.npmjs.com/package/mssql), [odbc](https://www.npmjs.com/package/odbc), [oracle](https://www.npmjs.com/package/oracledb), [postgres](https://www.npmjs.com/package/pg) node module using difference place holder for parameter
naming and value setting, like mysql and odbc using ? sign, mssql using @ sign and oracledb using : sign, and postgres using $ sign for naming parameters

#### KnSQL
KnSQL wrap up query statement using only place holder ? sign as parameter naming and value setting

##### java script
```javascript
const connector = require("will-sql");

async function testdb() {
    let knsql = new connector.KnSQL();
    knsql.append("select * from testdbx where share = ?share ");
    knsql.set("share","BBL");
    const db = connector.getDBConnector("MYSQL");
    let rs = await knsql.executeQuery(db);
    console.log("rs",rs);
    db.close();
}
```

##### type script
```typescript
import { KnSQL, KnDBConnections } from "will-sql";

async function testQuery() {
    let knsql = new KnSQL();
    knsql.append("select * from testdbx where share = ?share ");
    knsql.set("share","BBL");
    const db = KnDBConnections.getDBConnector("MYSQL");
    let rs = await knsql.executeQuery(db);
    console.log("rs",rs);
    db.close();
}
```

### Transaction

```typescript
import { KnSQL, KnDBConnections } from "will-sql";

async function testTransaction() {
    let knsql = new KnSQL();
    knsql.append("update testdbx set percent = ?percent where mktid = ?mktid ");
    knsql.set("percent",60);
    knsql.set("mktid","TST");
    const db = KnDBConnections.getDBConnector("MYSQL");
    try {
        await db.beginWork();
        let rs = await knsql.executeUpdate(db);
        console.log("update",rs);
        await db.commitWork();
    } catch(ex) {
        await db.rollbackWork();
    }
    db.close();
}
```

### Database Connector
In order to get database connection `KnDBConnections.getDBConnector` or `getDBConnector` method can specified by configuration section or configuration setting to establish

#### configuration section

```typescript
    const db = KnDBConnections.getDBConnector("MYSQL");
```
For example `"MYSQL"` point to section in config/default.json

#### configuration setting

```typescript
    const db = KnDBConnections.getDBConnector({
        schema: "MYSQL", 
        alias: "mysql", 
        dialect: "mysql", 
        url: "mysql://user:password@localhost:3306/testdb?charset=utf8&connectionLimit=10", 
        user: "user", 
        password: "password"
    });
```
Multiple pool supported by section or `schema` setting so it can defined in difference way 

### Database Adapter
Database adapter now support for `mysql`, `mssql`, `odbc`, `oracle` and `postgres` alias setting. When using database connector instance it can send raw query statement depending on database module

#### mysql

    npm install mysql

```typescript
import { KnDBConnections } from "will-sql";

async function testdb() {
    const db = KnDBConnections.getDBConnector("MYSQL");
    let rs = await db.executeQuery("select * from testdbx where percent > ? ",{ 
        percent: {value: 50, type: "DECIMAL"} 
    });
    console.log("rs",rs);
    db.close();
}
```
#### mysql2

    npm install mysql2

```typescript
import { KnDBConnections } from "will-sql";

async function testdb() {
    const db = KnDBConnections.getDBConnector("MYSQL2");
    let rs = await db.executeQuery("select * from testdbx where percent > ? ",{ 
        percent: {value: 50, type: "DECIMAL"} 
    });
    console.log("rs",rs);
    db.close();
}
```
#### odbc

    npm install odbc

```typescript
import { KnDBConnections } from "will-sql";

async function testdb() {
    const db = KnDBConnections.getDBConnector("ODBC");
    let rs = await db.executeQuery("select * from testdbx where percent > ? ",{ 
        percent: {value: 50, type: "DECIMAL"} 
    });
    console.log("rs2",rs);
    db.close();
}
```
#### mssql

    npm install mssql

```typescript
import { KnDBConnections } from "will-sql";

async function testdb() {
    const db = KnDBConnections.getDBConnector("MSSQL");
    console.log("db",db);
    let rs = await db.executeQuery("select * from testdbx where percentage > @percentage ",{ 
        percentage: {value: 50, type: "DECIMAL"} 
    });
    console.log("rs",rs);
    db.close();
}
```
#### oracledb

    npm install oracledb

```typescript
import { KnDBConnections } from "will-sql";

async function testdb() {
    const db = KnDBConnections.getDBConnector("ORACLE");
    let rs = await db.executeQuery("select * from testdbx where percentage > :percentage ",{ 
        percentage: {value: 50, type: "DECIMAL"} 
    });
    console.log("rs",rs);
    db.close();
}
```
#### postgres

    npm install pg

```typescript
import { KnDBConnections } from "will-sql";

async function testdb() {
    const db = KnDBConnections.getDBConnector("POSTGRES");
    let rs = await db.executeQuery("select * from testdbx where percentage > $1 ",{ 
        percentage: {value: 50, type: "DECIMAL"} 
    });
    console.log("rs",rs);
    db.close();
}
```
#### sqlite

    npm install sqlite3

```typescript
import { KnDBConnections } from "will-sql";

async function testdb() {
    const db = KnDBConnections.getDBConnector("SQLITE");

    await db.executeUpdate("create table testdbx(share text, mktid text, yield numeric, percent numeric)");
    await db.executeUpdate("insert into testdbx(share,mktid,yield,percent) values('BBL','TEST',100.50,25.50)");
    await db.executeUpdate("insert into testdbx(share,mktid,yield,percent) values('SCB','TEST',200.50,55.50)");

    let rs = await db.executeQuery("select * from testdbx");
    console.log("rs",rs);

    rs = await db.executeQuery("select * from testdbx where percent > ? ",{ 
        percent: {value: 50, type: "DECIMAL"} 
    });
    console.log("rs2",rs);
    db.close();
}
```

### Connection Pool
Database adapter has connector via connection pool then after used, all connection pools must be closed (or else it do not exit to commamd prompt when running as stand alone application)

```typescript
import { KnSQL, KnDBConnections } from "will-sql";

async function testQuery() {
    let knsql = new KnSQL();
    knsql.append("select * from testdbx where share = ?share ");
    knsql.set("share","BBL");
    const db = KnDBConnections.getDBConnector("MYSQL");
    let rs = await knsql.executeQuery(db);
    console.log("rs",rs);
    db.close(); //release connection to pool
    db.end(); //close connection pool
}
```

---
_Source: https://npm.io/package/will-sql · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._