Expo SQLite Eloquent ORM

Expo SQLite Eloquent ORM is a lightweight Object-Relational Mapping (ORM) wrapper for the expo-sqlite module, designed to provide a fluent and intuitive API for handling database operations in React Native applications. Inspired by Laravel's Eloquent, this library simplifies the process of interacting with SQLite databases by abstracting complex SQL queries into easy-to-understand JavaScript methods.

Features

• Fluent query builder for SQLite databases
• Easy-to-use API for defining models and performing CRUD operations
• Automatic casting of attributes to specified data types
• Support for relationships and eager loading
• Migration system for database versioning and setup

Installation

To install Expo SQLite Eloquent ORM, you need to have an Expo or React Native project set up. Then run:

npm install expo-sqlite-eloquent-orm
# or
yarn add expo-sqlite-eloquent-orm

Run the Example App

• git clone expo-sqlite-eloquent-orm
• cd expo-sqlite-eloquent-orm/example
• npm run start
• Example code is all in the App.js file, https://github.com/theianjohnson/expo-sqlite-eloquent-orm/blob/main/example/App.js

Model Class API

Static Methods

Method	Description	Return Type	Parameters
table	Sets the table name for queries.	Model	name: string
select	Specifies the fields to select in a query.	Model	fields: string | string[]
join	Adds a join clause to the query.	Model	type: 'INNER' | 'LEFT' | 'RIGHT', table: string, firstKey: string, secondKey: string
where	Adds a where clause to the query.	Model	column: string, operatorOrValue: any, value?: any
orderBy	Adds an order by clause to the query.	Model	column: string, direction: 'ASC' | 'DESC'
limit	Sets a limit on the number of records returned.	Model	number: number
with	Specifies relations to include in the query results.	Model	relation: string
find	Finds a record by its ID.	Promise<Model | null>	id: number | string
insert	Inserts a new record into the database.	Promise<SQLResult>	data: Record<string, any>
seed	Seeds data into the database if the table is empty.	Promise<void>	data: Array<Record<string, any>>
executeSql	Executes a custom SQL query.	Promise<SQLResult>	sql: string, params: any[]

Instance Methods

Method	Description	Return Type	Parameters
insert	Instance method to insert a new record.	Promise<SQLResult>	data: Record<string, any>
save	Saves the current instance to the database.	Promise<SQLResult>	-
delete	Deletes the current instance from the database.	Promise<SQLResult>	-
get	Retrieves records based on the current query.	Promise<Model[]>	-
first	Retrieves the first record based on the current query.	Promise<Model | null>	-
update	Updates the current instance in the database.	Promise<SQLResult>	attributes: Partial<ModelAttributes>
hasOne	Defines a has-one relationship.	Promise<Model | null>	relatedModel: Model, foreignKey?: string, localKey: string = 'id'
hasMany	Defines a has-many relationship.	Promise<Model[]>	relatedModel: Model, foreignKey?: string, localKey: string = 'id'
belongsTo	Defines a belongs-to relationship.	Promise<Model | null>	relatedModel: Model, foreignKey: string, otherKey: string = 'id'
belongsToMany	Defines a belongs-to-many relationship.	Promise<Model[]>	relatedModel: typeof Model, joinTableName?: string, foreignKey?: string, otherKey?: string

Types and Interfaces

• Casts: Record of attribute types ('number' \| 'boolean' \| 'string' \| 'json').
• Clauses: Object representing different clauses in a query.
• ModelAttributes: Record of any type representing model attributes.
• SQLResult: Interface representing the result of an SQL query.

Note: Some methods are simplified for brevity. Consult the source code for detailed implementation.

Quick Start

To get started with expo-sqlite-eloquent-orm, you'll need to setup your initial migrations and define your models.

Running Migrations

expo-sqlite-eloquent-orm provides a migration system to manage your database schema and versioning. You can define migrations to create and modify tables in a structured manner.

To run migrations, you need to create migration files and then execute them. You can use the Migration class to handle migrations. Here's an example of how to create and run migrations:

import { Migration } from 'expo-sqlite-eloquent-orm';

// Define your migration scripts
const migrations = {
  '1699486848_init': `
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT,
      email TEXT
    );
  `,
  '1699486885_updating_users_table': `
    ALTER TABLE users ADD COLUMN active BOOLEAN;
  `,
};

// Run migrations
try { 
  await Migration.runMigrations(migrations);
} catch(error) {
  console.error('Error running migrations:', error);
}

Defining Models

Create models by extending the Model class. Specify your table name and any casts for attributes:

import { Model } from 'expo-sqlite-eloquent-orm';

class User extends Model {
  static tableName = 'users';
  static casts = {
    id: 'number',
    active: 'boolean',
    // other attributes...
  };
  // Define relationships, custom methods, etc.
}

Querying

Utilize model methods to perform queries:

// Retrieve a user by ID
const user = await User.find(1);

// Get all users with a specific attribute
const activeUsers = await User.where('active', '=', true).get();

// Chain query methods for more complex queries
const specificUsers = await User.select(['id', 'name'])
                                .where('active', '=', true)
                                .orderBy('name', 'DESC')
                                .limit(10)
                                .get();

Inserting Records

To insert new records into the database, create a new instance of your model with the desired attributes, and then call the save method:

// Create a new user instance
const newUser = new User({
  name: 'John Doe',
  email: '[email protected]',
  active: true
});

// Insert the new user into the database
await newUser.save();

Updating Records

To update an existing record, retrieve the model instance, set the new attribute values, and then call the save method:

// Retrieve a user by ID
const user = await User.find(1);

// Update attributes
user.name = 'Jane Doe';
user.email = '[email protected]';

// Save the changes to the database
await user.save();

Deleting Records

To delete a record from the database, retrieve the model instance and then call the delete method:

// Retrieve a user by ID
const user = await User.find(1);

// Delete the user from the database
await user.delete();

// Delete all inactive users
await User.where('active', '=', false).delete();

Relationships

expo-sqlite-eloquent-orm supports defining and using relationships between models, making it easy to work with related data.

One-to-One Relationship

To define a one-to-one relationship between two models, you can use the hasOne method on the model that declares the relationship. For example, if you have a User model and a Profile model where each user has one profile:

class User extends Model {
  // ...
  
  async profile() {
    return this.hasOne(Profile, 'userId');
  }
}

class Profile extends Model {
  // ...
}

const user = await User.find(1);

// Automatically loaded
const userProfile = user.profile;

One-to-Many Relationship

To define a one-to-many relationship, use the hasMany method. For instance, if each User can have multiple Post records:

class User extends Model {
  // ...
  
  async posts() {
    return this.hasMany(Post, 'userId');
  }
}

class Post extends Model {
  // ...
}

const user = await User.find(1);

// Automatically loaded
const userPosts = user.posts;

Many-to-One Relationship

To define a many-to-one relationship, use the belongsTo method. For example, if each Post belongs to a single User:

class Post extends Model {
  // ...
  
  async user() {
    return this.belongsTo(User, 'userId');
  }
}

class User extends Model {
  // ...
}

const post = await Post.find(1);

// Automatically loaded
const postUser = await post.user;

To Do

[] Fix where('column', null) to accept actual null and just string 'null' [] Fix Typescript errors when defining models, ie "Class static side 'typeof Person' incorrectly extends base class static side 'typeof Model'." [] Many to many attach/detach methods [] Update .with() to accept an array of relationships [] Add .from() that proxies to .table()? [] Update .table() to accept an array of relationships (for joins) [] Add .query() for raw query with params [] Add .create() that returns the created model (https://laravel.com/docs/10.x/eloquent#inserts) [] whereRaw [] Bulk eager loading, currently eager loading is n+1 [] Reactivity? Caching with automatic invalidation?