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

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

// 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:

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

or

await db.createAllTables()

Insert

// 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:

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

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

// 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:

{
  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:

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:

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:

// 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

// 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

// 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

// 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

// 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

// 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

// 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

// 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

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:

// 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

// 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

// 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