dflourusso / expo-sqlite-orm

Expo SQLite ORM

expo-sqlite-orm's Issues

I used the Animal Example used in the Document

I am Using the Latest Expo Version 32.0.0.
Added this libray with given yarn code.

Now when i run the Animal Example it shows this warning in console

Possible Unhandled Promise Rejection (id: 0):
Object :{}

Kindly tell what am i missing

I tried several time & rebuilt the project couple of time. Still it shows the error

My Code

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

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

  static get database() {
    return Promise.resolve(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() }
    }
  }
}

My App.JS

import React from 'react';
import { View } from 'react-native';
import Animal from './Animal';

export default class App extends React.Component {
    async componentDidMount() {
        await Animal.createTable();
        await Animal.create({
            id: 1,
            name: '', // <- empty string
            color: 'Brown',
            age: 2
        });
        const createdAnimal = await Animal.find(1);
        console.log(createdAnimal);
    }

    render() {
        return ( <View /> );
    }
}

Is this repository/package still active?

Hi I just wanted to check in.

What are the plans for this package? (will there be a replacement?)
Will there be an update soon? (at least a confirmation that it still works with latest expo-sqlite)

The package seems to work fine with me, but if it's going to be deprecated, I would like to be prepared for that :-)

Have a great day and thank you very much for your work !

@dflourusso @saundefined

Bulk insert/update

Hi. Is there a way to do a bulk insert or update? Thanks

Getting warning on EventDatabase.create(props)

    const props = {
        id: 1,
        eventcity: "TestCity",
        eventlocation: "TestLocation",
        eventname: "TestEvent",
    };
    await EventDatabase.create(props)

After that I get "Possible Unhandled Promise Rejection (id: 0): Object {}"

But the data is in the database and can be get with EventDatabase.query(options).

What can I do to get rid off this warning?

Problems running sql query json_each

no such table: json_each (code 1)
Schema : https://www.db-fiddle.com/f/2bGqr1S8ho7z78BPxcQQ7y/3

Proposal for Missing Operators `OR`, `BETWEEN`, `IS NULL`, `IS NOT NULL`, extending `LIKE`

OR operator

OR operator is discussed here -> #66

LIKE operator

{"startsWith": "John"}

creates LIKE 'John%'

{"endsWith": "John"}

creates LIKE '%John'

{"contains": "John"}

creates LIKE '%John%'

Note

The current implementation of contains allows %John% to be passed it can be prone to SQL injection

LIKE with CASE INSENSITIVE operator

{"istartsWith": "John"}

{"iendsWith": "John"}

{"icontains": "John"}

Similar as above, but appends collate NOCASE.

BETWEEN operator

{"between": [1, 25]}

IS NULL operator

{"null": true}

IS NOT NULL operator

{"notnull": true}

Bug : this.query( where ) not working

Other options field work properly, but the where option is not working at all, thus it's returning the whole table

		const a = await this.query({
			columns: ["recipeCategoryId"],
			where: { recipeCategoryId: 1 },
		});

this code while return this
[{recipeCategoryId: 3},[recipeCategory: 1}]
which is wrong

my migration look like this

	"022420241853_init_recipes": sql`
          CREATE TABLE recipes (
            recipeCategoryId INTEGER NOT NULL
          );`,

Proposal: use hook to listen for table changes.

Example

const users = useSQLite(['users']) // is connected to insert, update, delete option. but is only connected with the mentioned table. 
// if 'animal' is not specified for example, it will not trigger changes

users.map((user) => {

 user.delete() // trigger update
})

db.delete(user) // trigger update

Int with value=0 is mapping to null

The problem is in BaseModel:

setProperties(props) {
    const cm = this.constructor.columnMapping
    Object.keys(cm).forEach(k => {
      if (props[k]) { //<- this evaluates to false for any {key:0}
        this[k] = props[k]
      } else if (isFunction(cm[k].default)) {
        this[k] = cm[k].default()
      } else {
        this[k] = null
      }
    })
    return this
  }

`

Typescript support

Hi! Can we add a typescript support for this? It would be really nice! I would be more than willing to help

column validations are too strict

The column validation is too strict in what can go in and out.

Out:

await SomeModel.query({
    columns: 'COUNT(*)',
});

This returns EMPTY results, because obviously there is no known column name, but if there's an "id" column on the model, this works

await SomeModel.query({
    columns: 'COUNT(*) as id',
});

In
If there are data missing on save, they are added anyways, but usually during update, you just want to update some columns, I would expect from .save() when primary key is present, just update columns available.

Question: Does the lib handle json stringify internally ?

I have the following:

import { ImageResponse } from '@App/types/Movie';
import { ColumnMapping, columnTypes } from 'expo-sqlite-orm';

export type MovieEntity = {
    id: number;
    title: string;
    posters: ImageResponse[];
};

export const movieColumMapping: ColumnMapping<MovieEntity> = {
    id: { type: columnTypes.INTEGER },
    title: { type: columnTypes.TEXT },
    posters: { type: columnTypes.JSON, default: () => '[]' },
};

I am curious to know if when I call save I need to do JSON.stringify() on data.posters or does the lib do this internally?

Foreign keys

@dflourusso Hi, love the ORM! How do we implement foreign key relationships? I couldn't see anything in the docs or the code.

Proposal automated migrations mechanism

This introduce BREAKING CHANGE to migration system.

Migration Mechanism

Say, you want to build a model,

const columMapping: ColumnMapping<Animal> = {
  id: { type: columnTypes.INTEGER },
  name: { type: columnTypes.TEXT }
}

when you run migrations.migrate() it will automatically try to see if there is a migration inside of migrations, if it doesn't it will infer the a JSON and a SQL (here a create table from the mapping, and migrate it to the database, then it will write the JSON to a migrations table with a timestamp as key.

Next time you update the Animal Table,

const columMapping: ColumnMapping<Animal> = {
  id: { type: columnTypes.INTEGER },
  name: { type: columnTypes.TEXT },
+  color: { type: columnTypes.TEXT },
}

When running, migrations.migrate() and it will perform a look up in the table, taking the latest migration, take the JSON, and compare the keys difference, if a new key was added inside the JSON, it will create an alter table and migrate it right away.

File based migration

Similarly instead of writing to a database, it will write to a folder, and read from it. It is developer friendly compared to the database only.

Hybrid

We can do both, where ``migrations.migrate(diff=true)` is introduced to print the difference to console + write to file if there is a diff + on migration, it will run the latest migration JSON to the database.

Update:

instead of JSON we can also look at the table info to see the difference
https://www.sqlite.org/pragma.html#pragma_table_info

Example:

PRAGMA table_info([cities]);

EventDatabase.create(props)

when create a record it created but it give this error:

undefined is not an object (evaluating 'obj.hasOwnProperty')**

• node_modules/expo-sqlite-orm/src/DataTypes.js:31:9 in toModelValue

• [native code]:null in reduce

• node_modules/expo-sqlite-orm/src/DataTypes.js:30:42 in toModelValue
• node_modules/expo-sqlite-orm/src/Repository.js:20:42 in insert
• node_modules/promise/setimmediate/core.js:37:14 in tryCallOne
• node_modules/promise/setimmediate/core.js:123:25 in setImmediate$argument_0
• node_modules/react-native/Libraries/Core/Timers/JSTimers.js:146:14 in _callTimer
• node_modules/react-native/Libraries/Core/Timers/JSTimers.js:194:17 in _callImmediatesPass
• node_modules/react-native/Libraries/Core/Timers/JSTimers.js:458:30 in callImmediates

• [native code]:null in callImmediates

• node_modules/react-native/Libraries/BatchedBridge/MessageQueue.js:407:6 in __callImmediates
• node_modules/react-native/Libraries/BatchedBridge/MessageQueue.js:143:6 in __guard$argument_0
• node_modules/react-native/Libraries/BatchedBridge/MessageQueue.js:384:10 in __guard
• node_modules/react-native/Libraries/BatchedBridge/MessageQueue.js:142:17 in __guard$argument_0

• [native code]:null in flushedQueue
• [native code]:null in invokeCallbackAndReturnFlushedQueue

My code:

class User extends BaseModel {
  constructor(obj) {
    super(obj)
  }
 
  static get database() {
    return async () => SQLite.openDatabase('cosmetique.db')
  }
 
  static get tableName() {
    return 'users'
  }
 
  static get columnMapping() {
    return {
      id: { type: types.TEXT, primary_key: true }, 
      username: { type: types.TEXT, not_null: true, unique: true },
      password: { type: types.TEXT, not_null: true},
      isAdmin: { type: types.NUMERIC, default: 0 },
      timestamp: { type: types.INTEGER, default: () => Date.now() }
    }
  }
}

export const addUser = (data) => {

  return new Promise(async (resolve, reject) => {
    try {
      const user = await User.create({
        id: data.id,
        username: data.username,
        password: data.password,
        isAdmin: data.isAdmin,
      })
      
      resolve({ message: 'Successfully Added' })
    } catch (error) {
      console.log(error)
      reject({ error: 'failled...' })
    }
  })
}

BaseModel.createTable()

Hello,

how to check if table is exist?

Cannot find native module 'ExpoSQLite'

After upgrade to expo sdk 49, when importing any attribute from expo-sqlite-orm the testing library throws "Cannot find native module 'ExpoSQLite'"

Bug: AUTOINCREMENT is being forced on incompatible column type of primary key

SQLite does not support AUTOINCREMENT on text columns, but _createTableColumns() code is always pushing NOT NULL PRIMARY KEY AUTOINCREMENT on primary key, of ANY type.

Settings autoincrement should have option to be disabled (I would keep turned on by default).
Or automatically removed, when the column type is TEXT.

"not_null" in columnMapping, create record with empty string, query and return null instead of empty string

find() and query() returns differents results

Hello,

Im facing a strange behavior, find() and query() functions returns differents results:

• Note: null fields returned by query() are not passed to create() function and should be set by default(). They are set correctly because the return of crete() and find() shows the correct data set by default()

QUERY

const cellsCountersList = await CellCounter.query({
        where: { id_eq: 1 },
        page: 1,
        limit: 10,
      });
      console.log(cellsCountersList);
/*
Array [
  Object {
    "baso_count": 1,
    "baso_percent": 10,
    "created_at": null,
    "description": "",
    "eos_count": 1,
    "eos_percent": 10,
    "id": 1,
    "is_sent": false,
    "lym_count": 1,
    "lym_percent": 10,
    "mono_count": 2,
    "mono_percent": 20,
    "nrbc_count": 1,
    "nrbc_percent": 10,
    "other1_count": 1,
    "other1_percent": 10,
    "other2_count": null,
    "other2_percent": null,
    "other3_count": null,
    "other3_percent": null,
    "patient_name": "test",
    "reference_id": 2587,
    "ret_count": 0,
    "ret_percent": 0,
    "seg_count": 2,
    "seg_percent": 20,
    "sent_at": null,
    "st_count": 1,
    "st_percent": 10,
    "total_count": 10,
    "updated_at": null,
  },
]
*/

FIND / CREATE

    const response = await CellCounter.create(data);
    console.log(response);

      const cellsCountersList2 = await CellCounter.find(1);
      console.log(cellsCountersList2);
      /*
returns the correct data filled with default() from model
        CellCounter {
  "baso_count": 1,
  "baso_percent": 10,
  "created_at": 1581426939311,
  "description": null,
  "eos_count": 1,
  "eos_percent": 10,
  "id": 1,
  "is_sent": false,
  "lym_count": 1,
  "lym_percent": 10,
  "mono_count": 2,
  "mono_percent": 20,
  "nrbc_count": 1,
  "nrbc_percent": 10,
  "other1_count": 1,
  "other1_percent": 10,
  "other2_count": 0,
  "other2_percent": 0,
  "other3_count": 0,
  "other3_percent": 0,
  "patient_name": "test",
  "reference_id": 2587,
  "ret_count": 0,
  "ret_percent": 0,
  "seg_count": 2,
  "seg_percent": 20,
  "sent_at": null,
  "st_count": 1,
  "st_percent": 10,
  "total_count": 10,
  "updated_at": 1581426939311,
}
       */

MODEL

export default class CellCounter extends BaseModel {
  static get database() {
    return async () => SQLite.openDatabase('database.db');
  }

  static get tableName() {
    return 'cells_counters';
  }

  static get columnMapping() {
    return {
      id: { type: types.INTEGER, primary_key: true },
      reference_id: { type: types.INTEGER, unique: true },
      patient_name: { type: types.TEXT, not_null: true },
      description: { type: types.TEXT },
      is_sent: { type: types.BOOLEAN, default: () => false },
      sent_at: { type: types.INTEGER },

      total_count: { type: types.NUMERIC, default: () => 0 },

      seg_count: { type: types.NUMERIC, default: () => 0 },
      seg_percent: { type: types.NUMERIC, default: () => 0 },

      st_count: { type: types.NUMERIC, default: () => 0 },
      st_percent: { type: types.NUMERIC, default: () => 0 },

      lym_count: { type: types.NUMERIC, default: () => 0 },
      lym_percent: { type: types.NUMERIC, default: () => 0 },

      mono_count: { type: types.NUMERIC, default: () => 0 },
      mono_percent: { type: types.NUMERIC, default: () => 0 },

      eos_count: { type: types.NUMERIC, default: () => 0 },
      eos_percent: { type: types.NUMERIC, default: () => 0 },

      baso_count: { type: types.NUMERIC, default: () => 0 },
      baso_percent: { type: types.NUMERIC, default: () => 0 },

      nrbc_count: { type: types.NUMERIC, default: () => 0 },
      nrbc_percent: { type: types.NUMERIC, default: () => 0 },

      ret_count: { type: types.NUMERIC, default: () => 0 },
      ret_percent: { type: types.NUMERIC, default: () => 0 },

      other1_count: { type: types.NUMERIC, default: () => 0 },
      other1_percent: { type: types.NUMERIC, default: () => 0 },

      other2_count: { type: types.NUMERIC, default: () => 0 },
      other2_percent: { type: types.NUMERIC, default: () => 0 },

      other3_count: { type: types.NUMERIC, default: () => 0 },
      other3_percent: { type: types.NUMERIC, default: () => 0 },

      created_at: { type: types.INTEGER, default: () => Date.now() },
      updated_at: { type: types.INTEGER, default: () => Date.now() },
    };
  }
}

2.0.1 has no lib or src with npm install

Did I miss anything?

Update expo-sqlite dependency

The current Version of expo-sqlite is 11.3.1, while this package depends on version 10.3.0

It would be nice to update those to avoid dependency conflicts

.insert is not a function (it is undefined)

Using expo-sqlite-orm 2.1.0 and trying to integrate the example into a new app on the last versions of Expo etc and getting my repository object .insert is not a function (it is undefined)

Do you have a working example somewhere? The linked example repo is on an old version of expo-sqlite-orm

Defaults only work with `new` then `save`, not `create`

This wasn't clear from the README, I'm wondering if it's intentional? Would be happy to PR a change in README if so, or attempt to add this feature otherwise.

Error in toDatabaseValue introduced by #1

Insert,update and delete

Am trying to insert in production mode using
insertExpenses = () =>{
const databaseLayer = new DatabaseLayer(async () => SQLite.openDatabase('uuzaposfive.db'))
databaseLayer.executeSql('INSERT INTO expenses (title,amount,date) values (?,?,?);',['foodoo',10000,'2020-12-15T09:42:00.000Z']).then(response => {
console.log(response)
})
}
but i get this error "insertId": undefined"

Example request

Hi dear,
Where I can find codes of a running example app?
Thank you.

any operation return same weird object

Hello,

I tried to use the expo-sqlite-orm but any operation (createTable, find, findBy..) returned

{"_h": 0, "_i": 0, "_j": null, "_k": null}

can you please help?

The code

  const param = new Parameter(props);
  param.save();

  let paramInDb = Parameter.findBy({ id_eq: 1 });
  console.log("paramInDb 1");
  console.log(paramInDb);
  console.log("paramInDb 2");
  console.log(Parameter.find(1));
  console.log(Parameter.query());

The Parameter class

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


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

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

static get tableName() {
  return 'parameters'
}

static get columnMapping() {
  return {
    id: { type: types.INTEGER, primary_key: true },
    nameParam:{ type: types.TEXT },
    valueParam: { type: types.TEXT }
  }
}

}

Store BLOB Data

Is there a example on how can we store image in sqlite. I am using the same Animal.js example

DataTypes fatal error

columnMapping[p[0]].type causes a fatal error if the column does not exists in columnMapping

Could not find a declaration file for module 'expo-sqlite-orm'. '/node_modules/expo-sqlite-orm/src/index.js' implicitly has an 'any' type. Try `npm install @types/expo-sqlite-orm` if it exists or add a new declaration (.d.ts) file containing `declare module 'expo-sqlite-orm';

Could not find a declaration file for module 'expo-sqlite-orm'. '/node_modules/expo-sqlite-orm/src/index.js' implicitly has an 'any' type.
Try npm install @types/expo-sqlite-orm if it exists or add a new declaration (.d.ts) file containing `declare module 'expo-sqlite-orm';

id Auto increment

Dear,

is there any way when creating a table, to define an Auto Incremented Collumn (usually the id)

best

Update Query not working

Same code works well in IOS but android shows column not exists

await databaseLayer.executeSql(
"UPDATE terminalssearch SET isFavourite = " +
dataStatus +
" WHERE teCpnNo=?",
[favouriteData.teCpnNo]
);

How to run WHERE with OR

Need to run OR WHERE condition but looks like it is not supported yet.

I need to find the records as per below:

SELECT * FROM table_name WHERE title = 'test' or field like 'test%'

Any suggestion on this?

Animal.js in subfolder

I put the Animal.js in a subfolder "entity" and get the warning: "Possible Unhandled Promise Rejection (id: 0): Object {}".

./entity/Animal.js

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

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

    static database() { 
        return Promise.resolve(SQLite.openDatabase('database.db'))
    }

    static get tableName() {
        return 'animals'
    }

    static get columnMapping() {
        return {
            id: { type: types.INTEGER, primary_key: true },
            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() }
        }
    }
}

App.js

import React from 'react';
import { View } from 'react-native';
import Animal from './entity/Animal';

export default class App extends React.Component {
  async componentDidMount() {
    await Animal.createTable();
    await Animal.create({
      id: 1,
      name: '', 
      color: 'Brown',
      age: 2
    });
    const createdAnimal = await Animal.find(1);
    console.log(createdAnimal);
  }

  render() {
    return ( <View /> );
  }
}

Proposal to implement OR operation

Currently, keys are extracted from the where statement which intrinsically creates an AND statement.

To prevent breaking changes, the current behaviour will stay the same, but we can consider to have custom operators to extend the where functionally.

Introducing $ operators.

Theses operators can extend the functionally within where clause.

Example:

qb.query({
  columns: 'id, name, status',
  where: {
    status: { equals: 'finished' },
    $or: [
      { name: { equals: 'Alice' } },
      { city: { equals: 'New York' } }
    ]
  }
})

This will be parsed to become:

Note

Between status and $or, the operation AND stays applicable.
Example below:

select id,name,status from users where status AND ( name = "alice" OR city = "New York" )

This can resolve #43

This can open opportunity to $and

bulkInsertOrReplace failing silently?

Hello,

When trying to insert an array of 257 objects via the bulkInsertOrReplace method, it fails without providing any error messages.

Is there a limit on how many items can be inserted? Is there a way to get more error messages?

The promise and/or error blocks do not run:
await databaseLayer.bulkInsertOrReplace(contactDataToSave).then(response => {
console.log('Back from Contact save, now on to Meta Data Save.');
console.log(response);
}).catch(err => console.error('Error saving metadata ' + err))

Uncaught DOMException: Failed to read the 'insertId' property from 'SQLResultSet': The query didn't result in any rows being added.

I get the above error when running TableName.find(1);

What's going wrong?