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

1import { Text } from '@components'
2import { ColumnMapping, columnTypes, IStatement, Migrations, Repository, sql } from 'expo-sqlite-orm'
3import React, { useMemo, useState } from 'react'
4import { ScrollView } from 'react-native'
5
6import { RootTabScreenProps } from '../../navigation/types'
7
8/**
9 * Expo Sqlite ORM V2 - Usage example
10 */
11
12interface Animal {
13  id: number
14  name: string
15  color: string
16  age: number
17  another_uid?: number
18  timestamp?: number
19}
20
21const columMapping: ColumnMapping<Animal> = {
22  id: { type: columnTypes.INTEGER },
23  name: { type: columnTypes.TEXT },
24  color: { type: columnTypes.TEXT },
25  age: { type: columnTypes.NUMERIC },
26  another_uid: { type: columnTypes.INTEGER },
27  timestamp: { type: columnTypes.INTEGER, default: () => Date.now() },
28}
29
30const statements: IStatement = {
31  '1662689376195_create_animals': sql`
32        CREATE TABLE animals (
33          id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
34          name TEXT NOT NULL,
35          color TEXT,
36          age NUMERIC,
37          another_uid TEXT UNIQUE,
38          timestamp INTEGER
39        );`,
40}
41
42const databaseName = 'dbName'
43
44export function MeusServicosScreen({ navigation }: RootTabScreenProps<'MeusServicos'>) {
45  const [animals, setAnimals] = useState<Animal[]>([])
46  const migrations = useMemo(() => new Migrations(databaseName, statements), [])
47
48  const animalRepository = useMemo(() => {
49    return new Repository(databaseName, 'animals', columMapping)
50  }, [])
51
52  const onPressRunMigrations = async () => {
53    await migrations.migrate()
54  }
55
56  const onPressReset = async () => {
57    await migrations.reset()
58    setAnimals([])
59  }
60
61  const onPressInsert = () => {
62    animalRepository.insert({ name: 'Bob', color: 'Brown', age: 2 }).then((createdAnimal) => {
63      console.log(createdAnimal)
64    })
65  }
66
67  const onPressQuery = () => {
68    animalRepository.query({ where: { age: { gte: 1 } } }).then((foundAnimals) => {
69      console.log(foundAnimals)
70      setAnimals(foundAnimals)
71    })
72  }
73
74  return (
75    <ScrollView>
76      <Text type="text2" onPress={onPressRunMigrations}>
77        Migrate
78      </Text>
79      <Text type="text2" onPress={onPressReset}>
80        Reset Database
81      </Text>
82      <Text type="text2" onPress={onPressInsert}>
83        Insert Animal
84      </Text>
85      <Text type="text2" onPress={onPressQuery}>
86        List Animals
87      </Text>
88      <Text type="text2">{JSON.stringify(animals, null, 1)}</Text>
89    </ScrollView>
90  )
91}
92

Database operations

Insert a record

1const props: Animal = {
2  name: 'Bob',
3  color: 'Brown',
4  age: 2
5}
6
7animalRepository.insert(props)

Find a record

1const id = 1
2animalRepository.find(id)

or

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

Update a record

1const props = {
2  id: 1 // required
3  age: 3
4}
5
6animalRepository.update(props)

Destroy a record

1const id = 1
2animalRepository.destroy(id)

Destroy all records

1animalRepository.destroyAll()

Query

1const options = {
2  columns: 'id, name',
3  where: {
4    id: { in: [1, 2, 3, 4] },
5    age: { gt: 2, lt: 10 }
6  },
7  page: 2,
8  limit: 30,
9  order: { name: 'ASC' }
10}
11
12animalRepository.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?

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

Bulk insert or replace?

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

Migrations

Execute the migrations

1import * as SQLite from 'expo-sqlite/legacy'
2import { Migrations, sql } from 'expo-sqlite-orm'
3
4const statements: IStatement = {
5  '1662689376195_init': sql`CREATE TABLE animals (id TEXT, name TEXT);`,
6  '1662689376196_add_age_column': sql`ALTER TABLE animals ADD age NUMERIC;`,
7  '1662689376197_add_color_column': sql`ALTER TABLE animals ADD color TEXT;`
8}
9
10const migrations = new Migrations('databaseName', statements)
11await migrations.migrate()

Reset the database

1const migrations = new Migrations('databaseName', statements)
2await migrations.reset()

TODO

• Add basic typescript support
• Make it easier to use with react-hooks
• Complete typescript autocomplete for where queries
• Add migrations feature
• Create a singleton to handle the instances easily
• 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

1docker-compose run --rm bump         # patch
2docker-compose run --rm bump --minor # minor
3
4git push
5git push --tags

Test

1docker-compose run --rm app install
2docker-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