# pip-services4-sqlite-node

> SQLite components for Pip.Services in Node.js / ES2017

Latest version **0.0.4** (published 2023-08-18) · MIT license · 0 weekly downloads

## Install

```sh
npm install pip-services4-sqlite-node
pnpm add pip-services4-sqlite-node
yarn add pip-services4-sqlite-node
bun add pip-services4-sqlite-node
```

## Health

**Score 25/100 (F)** — status: abandoned.

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

Warnings: low downloads; no esm support; pre 1.0.

Negative: abandoned; low maintenance score.

## Facts

| | |
|---|---|
| Version | 0.0.4 |
| Published | 2023-08-18 |
| First published | 2023-06-06 |
| Weekly downloads | 0 |
| License | MIT |
| TypeScript types | bundled |
| Module format | CommonJS |
| Node | >=14.0.0 |
| Dependencies | 6 |
| Unpacked size | 209 KB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| GitHub stars | 0 |
| Author | Enterprise Innovation Consulting |
| Maintainers | pipdeveloper |
| Keywords | pip.services, microservice, sqlite, library |

## Links

- npm: https://www.npmjs.com/package/pip-services4-sqlite-node
- Repository: https://github.com/pip-services4/pip-services4-node
- Homepage: https://github.com/pip-services4/pip-services4-node#readme
- Issues: https://github.com/pip-services4/pip-services4-node/issues
- npm.io page: https://npm.io/package/pip-services4-sqlite-node

## Dependencies (6)

- [sqlite3](https://npm.io/package/sqlite3.md) ^5.0.0
- [pip-services4-config-node](https://npm.io/package/pip-services4-config-node.md) ^0.0.5
- [pip-services4-commons-node](https://npm.io/package/pip-services4-commons-node.md) ^0.0.2
- [pip-services4-components-node](https://npm.io/package/pip-services4-components-node.md) ^0.0.5
- [pip-services4-persistence-node](https://npm.io/package/pip-services4-persistence-node.md) ^0.0.4
- [pip-services4-observability-node](https://npm.io/package/pip-services4-observability-node.md) ^0.0.3

## Recent versions

- 0.0.4 (latest) — 2023-08-18
- 0.0.3 — 2023-08-14
- 0.0.2 — 2023-06-29
- 0.0.1 — 2023-06-06

## README

# <img src="https://uploads-ssl.webflow.com/5ea5d3315186cf5ec60c3ee4/5edf1c94ce4c859f2b188094_logo.svg" alt="Pip.Services Logo" width="200"> <br/> SQLite components for Node.js

This module is a part of the [Pip.Services](http://pipservices.org) polyglot microservices toolkit. It provides a set of components to implement SQLite persistence.

The module contains the following packages:
- **Build** - Factory to create SQLite persistence components.
- **Connect** - Connection component to configure SQLite connection to database.
- **Persistence** - abstract persistence components to perform basic CRUD operations.

<a name="links"></a> Quick links:

* [Configuration](https://www.pipservices.org/recipies/configuration) 
* [API Reference](https://pip-services4-node.github.io/pip-services4-sqlite-node/globals.html)
* [Change Log](CHANGELOG.md)
* [Get Help](https://www.pipservices.org/community/help)
* [Contribute](https://www.pipservices.org/community/contribute)

## Use

Install the NPM package as
```bash
npm install pip-services4-sqlite-node --save
```

As an example, lets create persistence for the following data object.

```typescript
import { IIdentifiable } from 'pip-services4-commons-node';

export class MyObject implements IIdentifiable {
  public id: string;
  public key: string;
  public value: number;
}
```

The persistence component shall implement the following interface with a basic set of CRUD operations.

```typescript
export interface IMyPersistence {
  getPageByFilter(context: IContext, filter: FilterParams, paging: PagingParams,
    callback: (err: any, page: DataPage<MyObject>) => void): void;
    
  getOneById(context: IContext, id: string, callback: (err: any, item: MyObject) => void): void;
    
  getOneByKey(context: IContext, key: string, callback: (err: any, item: MyObject) => void): void;
    
  create(context: IContext, item: MyObject, callback?: (err: any, item: MyObject) => void): void;
    
  update(context: IContext, item: MyObject, callback?: (err: any, item: MyObject) => void): void;
    
  deleteById(context: IContext, id: string, callback?: (err: any, item: MyObject) => void): void;
}
```

To implement postgresql persistence component you shall inherit `IdentifiableSqlitePersistence`. 
Most CRUD operations will come from the base class. You only need to override `getPageByFilter` method with a custom filter function.
And implement a `getOneByKey` custom persistence method that doesn't exist in the base class.

```typescript
import { IdentifiableSqlitePersistence } from 'pip-services4-sqlite-node';

export class MySqlitePersistence extends IdentifableSqlitePersistence {
  public constructor() {
    super("myobjects");
    this.autoCreateObject("CREATE TABLE myobjects (id VARCHAR(32) PRIMARY KEY, key VARCHAR(50), value TEXT");
    this.ensureIndex("myobjects_key", { key: 1 }, { unique: true });
  }

  private composeFilter(filter: FilterParams): any {
    filter = filter || new FilterParams();
    
    let criteria = [];

    let id = filter.getAsNullableString('id');
    if (id != null)
        criteria.push("id='" + id + "'");

    let tempIds = filter.getAsNullableString("ids");
    if (tempIds != null) {
        let ids = tempIds.split(",");
        filters.push("id IN ('" + ids.join("','") + "')");
    }

    let key = filter.getAsNullableString("key");
    if (key != null)
        criteria.push("key='" + key + "'");

    return criteria.length > 0 ? criteria.join(" AND ") : null;
  }
  
  public getPageByFilter(context: IContext, filter: FilterParams, paging: PagingParams,
    callback: (err: any, page: DataPage<MyObject>) => void): void {
    super.getPageByFilter(context, this.composeFilter(filter), paging, "id", null, callback);
  }  
  
  public getOneByKey(context: IContext, key: string,
    callback: (err: any, item: MyObject) => void): void {
    
    let query = "SELECT * FROM " + this.quotedTableName() + " WHERE \"key\"=?";
    let params = [ key ];

    this._client.get(query, params, (err, result) => {
      err = err || null;

      if (item == null)
        this._logger.trace(context, "Nothing found from %s with key = %s", this._tableName, key);
      else
        this._logger.trace(context, "Retrieved from %s with key = %s", this._tableName, key);

      item = this.convertToPublic(item);
      callback(err, item);
    });
  }

}
```

Alternatively you can store data in non-relational format using `IdentificableJsonSqlitePersistence`.
It stores data in tables with two columns - `id` with unique object id and `data` with object data serialized as JSON.
To access data fields you shall use `JSON_EXTRACT(data, '$.field')` expression.

```typescript
import { IdentifiableJsonSqlitePersistence } from 'pip-services4-sqlite-node';

export class MySqlitePersistence extends IdentifableJsonSqlitePersistence {
  public constructor() {
    super("myobjects");
    this.ensureTable("VARCHAR(32)", "JSON");
    this.ensureIndex("myobjects_key", { "JSON_EXTRACT(data, '$.key')": 1 }, { unique: true });
  }

  private composeFilter(filter: FilterParams): any {
    filter = filter || new FilterParams();
    
    let criteria = [];

    let id = filter.getAsNullableString('id');
    if (id != null)
        criteria.push("JSON_EXTRACT(data, '$.id')='" + id + "'");

    let tempIds = filter.getAsNullableString("ids");
    if (tempIds != null) {
        let ids = tempIds.split(",");
        filters.push("JSON_EXTRACT(data, '$.id') IN ('" + ids.join("','") + "')");
    }

    let key = filter.getAsNullableString("key");
    if (key != null)
        criteria.push("JSON_EXTRACT(data, '$.key')='" + key + "'");

    return criteria.length > 0 ? criteria.join(" AND ") : null;
  }
  
  public getPageByFilter(context: IContext, filter: FilterParams, paging: PagingParams,
    callback: (err: any, page: DataPage<MyObject>) => void): void {
    super.getPageByFilter(context, this.composeFilter(filter), paging, "id", null, callback);
  }  
  
  public getOneByKey(context: IContext, key: string,
    callback: (err: any, item: MyObject) => void): void {
    
    let query = "SELECT * FROM " + this.quotedTableName() + " WHERE JSON_EXTRACT(data, '$.key')=?";
    let params = [ key ];

    this._client.get(query, params, (err, result) => {
      err = err || null;

      if (item == null)
        this._logger.trace(context, "Nothing found from %s with key = %s", this._tableName, key);
      else
        this._logger.trace(context, "Retrieved from %s with key = %s", this._tableName, key);

      item = this.convertToPublic(item);
      callback(err, item);
    });
  }

}
```

Configuration for your microservice that includes SQLite persistence may look the following way.

```yaml
...
{{#if SQLITE_ENABLED}}
- descriptor: pip-services:connection:postgres:con1:1.0
  connection:
    database: {{SQLITE_DB}}{{#unless SQLITE_DB}}./data/app.db{{/unless}}
    
- descriptor: myservice:persistence:postgres:default:1.0
  dependencies:
    connection: pip-services:connection:postgres:con1:1.0
  table: {{SQLITE_TABLE}}{{#unless SQLITE_TABLE}}myobjects{{/unless}}
{{/if}}
...
```

## Develop

For development you shall install the following prerequisites:
* Node.js 8+
* Visual Studio Code or another IDE of your choice
* Docker
* Typescript

Install dependencies:
```bash
npm install
```

Compile the code:
```bash
tsc
```

Run automated tests:
```bash
npm test
```

Generate API documentation:
```bash
./docgen.ps1
```

Before committing changes run dockerized build and test as:
```bash
./build.ps1
./test.ps1
./clear.ps1
```

## Contacts

The library is created and maintained by **Sergey Seroukhov** and **Danil Prisyazhniy**.

The documentation is written by **Mark Makarychev** and **Eugenio Andrieu**.

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