Expo SQLite ORM

It is a simple ORM utility to use with expo sqlite

Install

yarn add expo-sqlite-orm

Creating a model

You need to provide 3 things:

• database: Instance of expo SQLite or promise with that instance
• tableName: The name of the table
• columnMapping: The columns for the model and their types
  • Supported options: type, primary_key, not_null, unique, default

import { SQLite } from 'expo-sqlite'
import { BaseModel, types } from 'expo-sqlite-orm'

export default class Animal extends BaseModel {
  constructor(obj) {
    super(obj)
  }

  static get database() {
    return async () => SQLite.openDatabase('database.db')
  }

  static get tableName() {
    return 'animals'
  }

  static get columnMapping() {
    return {
      id: { type: types.INTEGER, primary_key: true }, // For while only supports id as primary key
      name: { type: types.TEXT, not_null: true },
      color: { type: types.TEXT },
      age: { type: types.NUMERIC },
      another_uid: { type: types.INTEGER, unique: true },
      timestamp: { type: types.INTEGER, default: () => Date.now() }
    }
  }
}

Database operations

Drop table

Animal.dropTable()

Create table

Animal.createTable()

Create a record

const props = {
  name: 'Bob',
  color: 'Brown',
  age: 2
}

const animal = new Animal(props)
animal.save()

or

const props = {
  name: 'Bob',
  color: 'Brown',
  age: 2
}

Animal.create(props)

Find a record

const id = 1
Animal.find(id)

or

Animal.findBy({ age_eq: 12345, color_cont: '%Brown%' })

Update a record

const id = 1
const animal = await Animal.find(id)
animal.age = 3
animal.save()

or

const props = {
  id: 1 // required
  age: 3
}

Animal.update(props)

Destroy a record

const id = 1
Animal.destroy(id)

or

const id = 1
const animal = await Animal.find(id)
animal.destroy()

Destroy all records

Animal.destroyAll()

Query

const options = {
  columns: 'id, name',
  where: {
    age_gt: 2
  },
  page: 2,
  limit: 30,
  order: 'name ASC'
}

Animal.query(options)

Where operations

• eq: =,
• neq: <>,
• lt: <,
• lteq: <=,
• gt: >,
• gteq: >=,
• cont: LIKE

Data types

• INTEGER
• FLOAT
• TEXT
• NUMERIC
• DATE
• DATETIME
• BOOLEAN
• JSON

How to exec a sql manually?

import { BaseModel } from 'expo-sqlite-orm'

export default class Example extends BaseModel {
  //...another model methods...
  static myCustomMethod() {
    const sql = 'SELECT * FROM table_name WHERE status = ?'
    const params = ['active']
    return this.repository.databaseLayer.executeSql(sql, params).then(({ rows }) => rows)
  }
}

or

import { SQLite } from 'expo-sqlite'
import DatabaseLayer from 'expo-sqlite-orm/src/DatabaseLayer'

const databaseLayer = new DatabaseLayer(async () => SQLite.openDatabase('database_name'))
databaseLayer.executeSql('SELECT * from table_name;').then(response => {
  console.log(response)
})

Bulk insert or replace?

import { SQLite } from 'expo-sqlite'
import DatabaseLayer from 'expo-sqlite-orm/src/DatabaseLayer'

const databaseLayer = new DatabaseLayer(async () => SQLite.openDatabase('database_name'), 'table_name')
const itens = [{id: 1, color: 'green'}, {id: 2, color: 'red'}]
databaseLayer.bulkInsertOrReplace(itens).then(response => {
  console.log(response)
})

Changelog

• 1.5.0 - Return unlimited rows if page is not specified in the query params

Development

docker-compose run --rm bump         # patch
docker-compose run --rm bump --minor # minor

git push
git push --tags

Test

docker-compose run --rm app install
docker-compose run --rm app test

Author

• Daniel Fernando Lourusso

License

This project is licensed under MIT License