# SQLITE-TS

SQLite ORM for Typescript

## Installation

Using npm:

```
npm i -S sqlite-ts
```

or yarn:

```
yarn add sqlite-ts
```

## Usage

It's easy!

### Define Entities

```ts
import { Column, Primary } from 'sqlite-ts'

class Person {
  @Primary()
  id: number = 0

  @Column('NVARCHAR')
  name: string = ''

  @Column('DATETIME')
  dob: Date = new Date()

  @Column('INTEGER')
  age: number = 0

  @Column('BOOLEAN')
  married: boolean = false

  @Column('MONEY')
  salary: number = 0
}

class Address {
  @Primary()
  id: number = 0

  @Column('INTEGER')
  person: number = 0

  @Column('NVARCHAR')
  address: string = ''
}
```

### Connect to Database

```ts
// let's use sqlite3 from https://github.com/mapbox/node-sqlite3
import Sqlite3 = require('sqlite3')

// define entities object
const entities = {
  Person,
  Address
}

// make a connection using SQLite3.
// you can use other available drivers
// or create your own
const sqlite3Db = new sqlite.Database(':memory:')
const db = await Db.init({
  // set the driver
  driver: new SQLite3Driver(sqlite3Db),

  // set your entities here
  entities,

  // set `true` so all tables in entities will automatically created for you
  // if it does not exists yet in database
  createTables: false
})
```

### Working with Entities

From now to work with entities you can access your entities via `db.tables.[entity name].[action function]`.

#### Create

For example to create table you can simply do this:

```ts
await db.tables.Person.create()
await db.tables.Address.create()
```

or

```ts
await db.createAllTables()
```

#### Insert

```ts
// insert single data
const result = await db.tables.Person.insert({
  name: 'Joey',
  married: true,
  dob: new Date(2000, 1, 1, 0, 0, 0),
  age: 18,
  salary: 100
})
```

The `Person` entity is using default primary key which is `INTEGER` that is autogenerated.
You can get inserted primary key value from the `result` of `insert` action above that returns:

```ts
{
  insertId: 1, // generated primary key
  rowsAffected: 1 // number of created data
}
```

You may want to insert multiple data at once like so:

```ts
// insert multiple data at once
const results = await db.tables.Person.insert([
  {
    name: 'Hanna',
    married: false,
    dob: new Date(2001, 2, 2, 0, 0, 0),
    age: 17,
    salary: 100
  },
  {
    name: 'Mary',
    married: false,
    dob: new Date(2002, 3, 3, 0, 0, 0),
    age: 26,
    salary: 50
  }
])
```

But you can't get advantage of getting the generated primary keys for inserted data.
Because the `results` only returns the last generated primary key:

```ts
{
  insertId: 3, // latest generated primary key
  rowsAffected: 2 // number of created data
}
```

If you have multiple action that you want to execute under `BEGIN` and `COMMIT` statement,
you can use transaction to do this:

```ts
await db.transaction(({ exec, tables }) => {
  exec(
    tables.Address.insert({
      person: 1,
      address: `Joy's Home`
    })
  )
  exec(
    tables.Address.insert({
      person: 2,
      address: `Hanna's Home`
    })
  )
  exec(
    tables.Address.insert({
      person: 3,
      address: `Marry's Home`
    })
  )
})
```

Need to get inserted generated primary key under transaction? Simply do this instead:

```ts
let address1: any
let address2: any
let address3: any
await db.transaction(({ exec, tables }) => {
  exec(
    tables.Address.insert({
      person: 1,
      address: `Joy's Home`
    })
  ).then(r => {
    address1 = r
  })

  exec(
    tables.Address.insert({
      person: 2,
      address: `Hanna's Home`
    })
  ).then(r => {
    address2 = r
  })

  exec(
    tables.Address.insert({
      person: 3,
      address: `Marry's Home`
    })
  ).then(r => {
    address3 = r
  })
})
```

The actions above should returns:

```ts
// address1:
{
  insertId: 1,
  rowsAffected: 1
}

// address2:
{
  insertId: 2,
  rowsAffected: 1
}

// address3:
{
  insertId: 1,
  rowsAffected: 1
}
```

You can also do same things for `upsert`, `update`, `delete`, `create` and `drop` action.

#### Select

##### Select All

```ts
// select all
const people = await db.tables.Person.select()
```

returns:

```
[ 
  { id: 1,
    name: 'Joey',
    dob: 2000-01-31T17:00:00.000Z,
    age: 18,
    married: true,
    salary: 100
  },
  { id: 2,
    name: 'Hanna',
    dob: 2001-03-01T17:00:00.000Z,
    age: 17,
    married: false,
    salary: 100
  },
  { id: 3,
    name: 'Mary',
    dob: 2002-04-02T17:00:00.000Z,
    age: 26,
    married: false,
    salary: 50
  }
]
```

##### Select Columns

```ts
// select columns
const people2 = await db.tables.Person.select(c => [c.id, c.name, c.salary])
```

returns:

```
[
  { id: 1, name: 'Joey', salary: 100 },
  { id: 2, name: 'Hanna', salary: 100 },
  { id: 3, name: 'Mary', salary: 50 }
]
```

##### Select Limit

```ts
// select with limit
const people3 = await db.tables.Person.select(c => [
  c.id,
  c.name,
  c.salary
]).limit(1)
```

returns:

```
[{ id: 1, name: 'Joey', salary: 100 }]
```

##### Select Where

```ts
// select with condition
const people4 = await db.tables.Person.select(c => [c.id, c.name]).where(c =>
  c.greaterThanOrEqual({ salary: 100 })
)
```

returns:

```
[ { id: 1, name: 'Joey' }, { id: 2, name: 'Hanna' } ]
```

##### Select Order

```ts
// select with order
const people5 = await db.tables.Person.select(c => [c.id, c.name])
  .where(c => c.notEquals({ married: true }))
  .orderBy({ name: 'DESC' })
```

returns:

```
[ { id: 3, name: 'Mary' }, { id: 2, name: 'Hanna' } ]
```

##### Select Single Data

```ts
// select single data
const person = await db.tables.Person.single(c => [c.id, c.name])
```

returns:

```
{ id: 1, name: 'Joey' }
```

For the rest, you can play around with editor intellisense to get more options.

#### Update

```ts
// let's prove that she's not married yet
let hanna = await db.tables.Person.single(c => [c.id, c.name, c.married]).where(
  c => c.equals({ id: 2 })
)
// returns:
// hanna is not married yet = { id: 2, name: 'Hanna', married: false }

// let's marry her
await db.tables.Person.update({ married: true }).where(c => c.equals({ id: 2 }))

hanna = await db.tables.Person.single(c => [c.id, c.name, c.married]).where(c =>
  c.equals({ id: 2 })
)
// returns:
// hanna is now married = { id: 2, name: 'Hanna', married: true }
```

#### Join

```ts
const people6 = await db.tables.Person.join(
  t => ({
    // FROM Person AS self JOIN Address AS address
    address: t.Address
  }),
  (p, { address }) => {
    // ON self.id = address.person
    p.equal({ id: address.person })
  }
).map(f => ({
  // SELECT self.id AS id, self.name AS name, address.address AS address
  id: f.self.id,
  name: f.self.name,
  address: f.address.address
}))
```

results:

```
[
  { id: 1, name: 'Joey', address: "Joy's Home" },
  { id: 2, name: 'Hanna', address: "Hanna's Home" },
  { id: 3, name: 'Mary', address: "Marry's Home" }
]
```

You can follow the `join` action with `where`, `limit` and `orderBy` as well:

```ts
// join where order and limit
const people7 = await db.tables.Person.join(
  t => ({
    // FROM Person AS self JOIN Address AS address
    address: t.Address
  }),
  (p, { address }) => {
    // ON self.id = address.person
    p.equal({ id: address.person })
  }
)
  .map(f => ({
    // SELECT self.id AS id, self.name AS name, address.address AS address
    id: f.self.id,
    name: f.self.name,
    address: f.address.address
  }))
  // WHERE self.married = 1
  .where(p => p.self.equals({ married: true }))
  // ORDER BY address.address ASC
  .orderBy({ address: { address: 'ASC' } })
  // LIMIT 1
  .limit(1)
```

result:

```
[{ id: 2, name: 'Hanna', address: "Hanna's Home" }]
```

#### Delete

```ts
// delete
const delResult = await db.tables.Person.delete().where(c =>
  c.equals({ id: 3 })
)
```

result:

```
{
  insertId: 3,
  rowsAffected: 1
}
```

You can put `delete` action under `transaction`.

#### Drop

```ts
// drop
await db.tables.Address.drop()

// or drop inside transaction
await db.transaction(({ exec, tables }) => {
  exec(tables.Address.drop())
  exec(tables.Person.drop())
})

// or drop all tables
await db.dropAllTables()
```

## License

MIT