Expo SQLite ORM


It is a simple ORM utility to use with expo sqlite

> Warn: it works only on iOS and Android. Web is not supported (SEE)

Install

yarn add expo-sqlite-orm

Basic usage

You need to provide 3 things:

- databaseName: Name of the database to be created/used by expo SQLite
- tableName: The name of the table
- columnMapping: The columns for the model and their types
- Supported options: type, primary_key, autoincrement, not_null, unique, default

``typescript
import { Text } from '@components'
import { ColumnMapping, columnTypes, IStatement, Migrations, Repository, sql } from 'expo-sqlite-orm'
import React, { useMemo, useState } from 'react'
import { ScrollView } from 'react-native'

import { RootTabScreenProps } from '../../navigation/types'

/**
* Expo Sqlite ORM V2 - Usage example
*/

interface Animal {
id: number
name: string
color: string
age: number
another_uid?: number
timestamp?: number
}

const columMapping: ColumnMapping = {
id: { type: columnTypes.INTEGER },
name: { type: columnTypes.TEXT },
color: { type: columnTypes.TEXT },
age: { type: columnTypes.NUMERIC },
another_uid: { type: columnTypes.INTEGER },
timestamp: { type: columnTypes.INTEGER, default: () => Date.now() },
}

const statements: IStatement = {
'1662689376195_create_animals': sql
CREATE TABLE animals (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
color TEXT,
age NUMERIC,
another_uid TEXT UNIQUE,
timestamp INTEGER
);,
}

const databaseName = 'dbName'

export function MeusServicosScreen({ navigation }: RootTabScreenProps<'MeusServicos'>) {
const [animals, setAnimals] = useState([])
const migrations = useMemo(() => new Migrations(databaseName, statements), [])

const animalRepository = useMemo(() => {
return new Repository(databaseName, 'animals', columMapping)
}, [])

const onPressRunMigrations = async () => {
await migrations.migrate()
}

const onPressReset = async () => {
await migrations.reset()
setAnimals([])
}

const onPressInsert = () => {
animalRepository.insert({ name: 'Bob', color: 'Brown', age: 2 }).then((createdAnimal) => {
console.log(createdAnimal)
})
}

const onPressQuery = () => {
animalRepository.query({ where: { age: { gte: 1 } } }).then((foundAnimals) => {
console.log(foundAnimals)
setAnimals(foundAnimals)
})
}

return (


Migrate


Reset Database


Insert Animal


List Animals

{JSON.stringify(animals, null, 1)}

)
}

`

Database operations

$3

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

animalRepository.insert(props)
`

$3

`javascript
const id = 1
animalRepository.find(id)
`

or

`javascript
animalRepository.findBy({ age: { equals: 12345 }, color: { contains: '%Brown%' } })
`

$3

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

animalRepository.update(props)
`

$3

`javascript
const id = 1
animalRepository.destroy(id)
`

$3

`javascript
animalRepository.destroyAll()
`

$3

`javascript
const options = {
columns: 'id, name',
where: {
id: { in: [1, 2, 3, 4] },
age: { gt: 2, lt: 10 }
},
page: 2,
limit: 30,
order: { name: 'ASC' }
}

animalRepository.query(options)
`

> The property page is applied only if you pass the limit as well

Where operations

- equals: =,
- notEquals: <>,
- lt: <,
- lte: <=,
- gt: >,
- gte: >=,
- contains: LIKE
- in: IN (?)
- notIn: NOT IN (?)

Data types

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

How to exec a sql manually?

`typescript
myCustomMethod() {
const sql = 'SELECT * FROM table_name WHERE status = ?'
const params = ['active']
return animalRepository.databaseLayer.executeSql(sql, params).then(({ rows }) => rows)
}
`

Bulk insert or replace?

`javascript
const itens = [{id: 1, color: 'green'}, {id: 2, color: 'red'}]
animalRepository.databaseLayer.bulkInsertOrReplace(itens).then(response => {
console.log(response)
})
`

Migrations

$3

`typescript
import * as SQLite from 'expo-sqlite/legacy'
import { Migrations, sql } from 'expo-sqlite-orm'

const statements: IStatement = {
'1662689376195_init': sqlCREATE TABLE animals (id TEXT, name TEXT);,
'1662689376196_add_age_column': sqlALTER TABLE animals ADD age NUMERIC;,
'1662689376197_add_color_column': sqlALTER TABLE animals ADD color TEXT;
}

const migrations = new Migrations('databaseName', statements)
await migrations.migrate()
`

$3

`typescript
const migrations = new Migrations('databaseName', statements)
await migrations.reset()
`

TODO

- [x] Add basic typescript support
- [x] Make it easier to use with react-hooks
- [x] Complete typescript autocomplete for where queries
- [x] Add migrations feature
- [x] Create a singleton to handle the instances easily
- [x] Allow IN statement
- [ ] Allow OR statement

Changelog

- 1.5.0 - Return unlimited rows if page is not specified in the query params
- 1.6.0 - Make autoincrement property to be optional
- 2.0.0 - BREAKING CHANGE
- Add typescript support
- Remove BaseModel in favor of Repository (Easier to use with react-hooks)
- Add migrations support

Development

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

git push
git push --tags
`

$3

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

Working examples

- https://github.com/dflourusso/expo-sqlite-orm-example
- https://snack.expo.io/@dflourusso/expo-sqlite-orm-example

Author

- Daniel Fernando Lourusso

License

This project is licensed under
MIT License