# PawSQLite

PawSQLite is a library for interacting with SQLite databases. Through the use of multiple adapters for various environments, it provides a simple API that abstracts out implementation specific quirks.

## Installation

You can install using npm:

```bash
npm install -s git+https://git.n0m.org/n0m/PawSQLite.git
```
In order to actually use PawSQLite, you must also install an adapter for your specific environment. Currently, adapters are available for the following environments:

### WebSQL:
PawSQLite-WebSQL-Adapter: [https://git.n0m.org/n0m/PawSQLite-WebSQL-Adapter](https://git.n0m.org/n0m/PawSQLite-WebSQL-Adapter)


### Apache Cordova:
PawSQLite-Cordova-Adapter: [https://git.n0m.org/n0m/PawSQLite-Cordova-Adapter](https://git.n0m.org/n0m/PawSQLite-Cordova-Adapter)


### Node.js via Sqlite3 package:
PawSQLite-Node-Adapter: [https://git.n0m.org/n0m/PawSQLite-Node-Adapter](https://git.n0m.org/n0m/PawSQLite-Node-Adapter)

## Usage

### Importing:

ES6 Module:

```javascript
import { PawSQLite, PawSQLiteError } from "pawsqlite";
```

CommonJS:

```javascript
const PawSQLite = require("pawsqlite");
```

### Registering an adapter:

You must register at least one adapter before you can use PawSQLite

```javascript
const PawSQLite = require("pawsqlite");
const PawSQLiteNodeAdapter = require("pawsqlite-node-adapter");

PawSQLite.registerAdapter(PawSQLiteNodeAdapter)
```

### Opening a database:

`PawSQLite.open` accepts two arguments. The first argument is the name of the database. This name is handled differently depending on the adapter in use, but in general it corresponds to the full filename of the database. The second argument is an object for various configuration options.

```javascript
const db = await PawSQLite.open("test", {
  adapter: 'PawSQLiteNodeAdapter'
});
```

### Querying the database:

You can query the database by using the `sql` method on a database or transaction object. The `sql` method also allows you to bind parameters to the satatement. Bound parameters are escaped by whichever native SQLite implementation your chosen adapter uses.

```javascript
await db.sql("SELECT * FROM contacts WHERE name=?", "Paul");
```

The above code will return an array of objects representing the matched rows in the contacts table. It might look like the following:

```javascript
[
  { name: 'Paul', number: '+1 333 333 3333' },
  { name: 'Paul', number: '+1 444 444 4444' },
  { name: 'Paul', number: '+1 555 555 5555' }
]
```

PawSQLite also allows you to bind multiple parameters according to the number of parameters in an array using `???`. Although this might reduce readability.

```javascript
await db.sql("INSERT INTO contacts VALUES (???)", ["Pat", 23])
```

The result of successful `INSERT` statements will also include an `insertId` property containing the id of the last inserted row. The result of `UPDATE` and `DELETE` statements will include a `rowsAffected` property containing a count of the number of rows that were affected.

### Transactions:

Transactions can be handled manually, or automatically. To use transactions manually, first create a transaction by calling the transaction method on your database. The actual transaction won't be started until the first SQL statement is executed on it.

```javascript
const tx = db.transaction();
const result = await tx.sql("SELECT 1");
await tx.sql("INSERT INTO contacts (name) VALUES (?);", "Bob");
await tx.commit();
```

Only one transaction can be active at any one time. PawSQLite will automatically queue additional transactions until the active transaction is complete.

```javascript
const tx1 = db.transaction();
const tx2 = db.transaction();

tx1.sql(`SELECT "tx1 - first select"`);
tx2.sql(`SELECT "tx2 - first select"`);
tx1.sql(`SELECT "tx1 - second select"`);
tx2.sql(`SELECT "tx2 - second select"`);

await tx1.commit();
await tx2.commit();
```

With debugging enabled, console output will show that the above statements were correctly sequenced and `tx2` was forced to wait until `tx1` had completed:

```SQL
BEGIN
SELECT "tx1 - first select"
SELECT "tx1 - second select"
COMMIT
BEGIN
SELECT "tx2 - first select"
SELECT "tx2 - second select"
COMMIT
```

Transactions can also be handled automatically. `autoTransaction` accepts two arguments, the first is a callback which will receive the transaction object. The callback is expected to return a promise whose completion signifies the end any work that needed to be wrapped in the transaction. The second argument is optional and can contain another transaction object to use instead of starting a new transaction. This functionality is very useful for chaining database operations, and will be demonstrated [below](#recommended-approach-to-model-classes).

```javascript
await db.autoTransaction(async (tx) => {
  await tx.sql(`SELECT "Some Value"`);
  await someAsyncOperation();
  await tx.sql(`INSERT INTO contacts (name) VALUES (?)`, "Ringo Starr");
});
```

### Recommended Approach to Model Classes

Supposing we have two tables in our database. The first table `contacts` contains rows of contact names. The second table `numbers` contains rows of numbers and foreign keys for contact ids. We could model that database like this:

```javascript
class NumberModel {
  constructor(db) {
    this.db = db;
    this.id = null;
    this.contactId = null;
    this.number = null;
  }

  load(numberId, inheritTx) {
    return this.db.autoTransaction(async (tx) => {
      const result = await tx.sql(
        "SELECT (number) FROM numbers WHERE id=?",
        numberId
      );
      if (!result.length) {
        throw new Error(`Unknown number: ${ numberId }`);
      }

      this.id = numberId;
      this.contactId = result[0].contactId;
      this.number = result[0].number;
    }, inheritTx);
  }

  save(inheritTx) {
    return this.db.autoTransaction(async (tx) => {
      const isInsert = !this.id;

      if (isInsert) {
        const result = await tx.sql(
          "INSERT INTO numbers (contact_id, number) VALUES (?, ?)",
          this.contactId,
          this.number
        );
        this.id = result.insertId;
      } else {
        await tx.sql(
          "UPDATE numbers SET contact_id=? number=? WHERE id=?",
          this.contactId,
          this.number,
          this.id
        );
      }

    }, inheritTx);

  }
}


class ContactModel {
  constructor(db) {
    this.db = db;
    this.id = null;
    this.name = null;
    this.numbers = [];
  }

  load(contactId, inheritTx) {
    return this.db.autoTransaction(async (tx) => {
      const result = await tx.sql(
        "SELECT (name) FROM contacts WHERE id=?",
        contactId
      );
      if (!result.length) {
        throw new Error(`Unknown contact: ${ contactId }`);
      }
      this.id = contactId;
      this.name = result[0].name;

      const numbers = await tx.sql(
        "SELECT (id) FROM numbers WHERE contact_id=?",
        this.id
      );

      this.numbers = await Promise.all(
        numbers.map(async (number) => {
          const numberModel = new NumberModel(this.db);
          await numberModel.load(number.id, tx);
          return numberModel;
        })
      );
    }, inheritTx);
  }

  save(inheritTx) {
    return this.db.autoTransaction(async (tx) => {
      const isInsert = !this.id;

      if (isInsert) {
        const result = await tx.sql(
          "INSERT INTO contacts (name) VALUES (?)",
          this.name
        );
        this.id = result.insertId;
      } else {
        await tx.sql(
          "UPDATE contacts SET name=? WHERE id=?",
          this.name,
          this.id
        );
      }

      // Set contactId and save all numbers
      await Promise.all(
        this.numbers.map((numberModel) => {
          numberModel.contactId = this.id;
          return numberModel.save(tx);
        })
      );

    }, inheritTx);

  }
}
```

You can then create a contact like this:

```javascript
const c1 = new ContactModel(db);
c1.name = "Bob Dylan";

const n1 = new NumberModel(db);
n1.number = "+1 555 555 5555";
c1.numbers.push(n1);

const n2 = new NumberModel(db);
n2.number = "+1 999 999 9999";
c1.numbers.push(n2);

await c1.save();
```

You can retrieve a contact like this:

```javascript
const c2 = new ContactModel(db);
await c2.load(contactId);
```

## Contributing
Pull requests are welcome. For major changes, please open an issue first to discuss what you would like to change.

## License
[MIT](https://choosealicense.com/licenses/mit/)