Expo SQLite + TypeORM
Tags: reactnative, typescript, database, mobile
Published: 2020-12-17 19:03:59

Although **Expo** offers a **SQLite** local database connection API, it is not very practical to use, especially for larger projects with more complex database designs since all it has is an *execute* function where you can write raw queries to be ran.

With that in mind and having had some experience with **TypeORM** on the backend before, I wanted to see what it would be like to combine the two.

Luckily, TypeORM already offers an `expo` driver and it's setup looks very easy. Nevertheless, I still had some problems while trying it out.

This article's main goal is to be a walkthrough of the setup and things to keep in mind so that you don't go through the problems I did. Some of the tips are workarounds, some are optional and others are just necessary.

---

Before procceeding there are some caveats to be made:

* this tutorial assumes that you already know the basics of React Native; 
* it also assumes that you know how to create, run and publish an Expo project;
* if the structure chosen for the project ever feels excessive, it's because I'm aiming to keep it organized and give it scalability;
* if at any point you feel lost please refer to the [repository](https://github.com/jgabriel1/expo-sqlite-typeorm-article). All files and topics named in this article, will be there in the form of commits;
* lastly, this is all a result of my experience while I was first learning it, so if you find something that is just clearly wrong please message me or send it in the comments.

---

# The Application

The application we'll be working on in this article is as simple as it can be. The [**main component**](https://github.com/jgabriel1/expo-sqlite-typeorm-article/blob/pre-persistence-implementation/src/components/TodoList.tsx) will hold a state for a list of To-Dos. The items can be **toggled** on/off, **deleted** and there is an input to **create** a new item:

```tsx
const TodoList: React.FC = () => {
  const [newTodo, setNewTodo] = useState('');
  const [todos, setTodos] = useState<TodoItem[]>([]);

  const handleCreateTodo = useCallback(async () => {
    setTodos(current => [...current, { text: newTodo, isToggled: false }]);

    setNewTodo('');
  }, [newTodo]);

  const handleToggleTodo = useCallback(async (index: number) => {
    setTodos(current =>
      current.map((todo, i) => {
        return index === i ? { ...todo, isToggled: !todo.isToggled } : todo;
      }),
    );
  }, []);

  const handleDeleteTodo = useCallback(async (index: number) => {
    setTodos(current => current.filter((_, i) => i !== index));
  }, []);

  return (
    <View style={styles.container}>
      <View style={styles.newTodoContainer}>
        <TextInput
          style={styles.newTodoInput}
          value={newTodo}
          onChangeText={setNewTodo}
        />
        <Button title="Create" onPress={handleCreateTodo} />
      </View>

      <View style={styles.todosContainer}>
        {todos.map((todo, index) => (
          <TouchableOpacity
            onPress={() => handleToggleTodo(index)}
            onLongPress={() => handleDeleteTodo(index)}
          >
            <Todo
              key={String(index)}
              text={todo.text}
              isToggled={todo.isToggled}
            />
          </TouchableOpacity>
        ))}
      </View>
    </View>
  );
};
```
The version prior to the persistence layer implementation can be found in [this branch](https://github.com/jgabriel1/expo-sqlite-typeorm-article/tree/pre-persistence-implementation) of the article's repository.

With that being presented, the focus is to give this application a persistence layer that will save all To-Dos and their current state to a local in-device database.

**P.S.:** The Todo component is very trivial. All it does is present the text and add a *line-through* decoration in case it gets toggled.

---

## Setting up TypeORM

The one important thing here is that, as of the making of this article, **only versions prior to `0.2.28` of TypeORM can be used with Expo.** 

Basically there's an issue with a new feature introduced in version `0.2.29` which uses *RegEx lookbehinds*. This prevents you from being able to use the **QueryBuilder** object.

Since the **Repository** depends directly on it, that's basically the entire library functionality broken.

The engine that Expo uses to run JavaScript doesn't support these *lookbehinds* and that is what is causing the problem.

For more information, please refer to the [**main issue**](https://github.com/typeorm/typeorm/issues/7026) in the TypeORM repository.

**EDIT:** So as of February 8th, 2021 the **0.2.31** version of TypeORM is now available. This should fix the RegEx related problems when using the query builder / repository APIs. 

If you want to use the latest version instead of 0.2.28, you can just *remove* the version specification "@0.2.28" from the following command.

### Add `typeorm` and `reflect-metadata`
```yarn
$ yarn add typeorm@0.2.28 reflect-metadata
```

### Change `tsconfig.json`
Add these extra configuration lines to `tsconfig`.

The first two are on TypeORM documentation and allow you to use decorators to declare the entities.

The last one is to declare the properties without needing to create a constructor method.
```json
{
  /* ...rest of tsconfig */

  "emitDecoratorMetadata": true,
  "experimentalDecorators": true,
  "strictPropertyInitialization": false
}
```

### Add *Babel* plugin for entities type inference

As is, the inference that TypeORM uses to map the declared properties types to an SQLite data type does not work. Without it you'll need to declare the type explicitly as an option in the `@Column()` decorator.

To make that work, you'll need to install, as a development dependency, a Babel plugin...
```
$ yarn add babel-plugin-transform-typescript-metadata -D
```
and add it to [`babel.config.js`](https://github.com/jgabriel1/expo-sqlite-typeorm-article/blob/main/babel.config.js).
```js
// babel.config.js

 return {	  
   presets: ['babel-preset-expo'],	  
+  plugins: [
+    '@babel/transform-react-jsx-source',
+    'babel-plugin-transform-typescript-metadata',
+  ]
 };
```
### Import `reflect-metadata`

Before the declaration of the main App component in `App.js`, import `reflect-metadata`. This is the entrypoint of the application.

If you're following with the [**repository**](https://github.com/jgabriel1/expo-sqlite-typeorm-article/), you'll have `App.js`, if not, you might be used to `App.tsx` created by Expo. I just changed the main App code to inside the `src/` folder and made `App.js` just forward it to Expo.

```js
// App.js

import 'reflect-metadata';
import App from './src';

export default App;
```

### Installing Expo SQLite

`expo-sqlite` package needs to be installed using the Expo CLI:

```
$ expo install expo-sqlite
```

---

## Setup the database access code
To hold all the database access logic, we'll create a [`src/data`](https://github.com/jgabriel1/expo-sqlite-typeorm-article/tree/main/src/data) folder.

### Declare the To-Do model
For the sake of simplicity, we'll be using the primary id as a numeric value that will be generated incremently.

```ts
@Entity('todos')
export class TodoModel {
  @PrimaryGeneratedColumn('increment')
  id: number;

  @Column()
  text: string;

  @Column()
  is_toggled: boolean;
}
```
### Create a repository to encapsulate access logic

The repository class will recieve a connection type from TypeORM as a parameter:

```ts
export class TodosRepository {
  private ormRepository: Repository<TodoModel>;

  constructor(connection: Connection) {
    this.ormRepository = connection.getRepository(TodoModel);
  }

  public async getAll(): Promise<TodoModel[]> {
    const todos = await this.ormRepository.find();

    return todos;
  }

  public async create({ text }: ICreateTodoData): Promise<TodoModel> {
    const todo = this.ormRepository.create({
      text,
      is_toggled: false,
    });

    await this.ormRepository.save(todo);

    return todo;
  }

  public async toggle(id: number): Promise<void> {
    await this.ormRepository.query(
      `
      UPDATE
        todos
      SET
        is_toggled = ((is_toggled | 1) - (is_toggled & 1))
      WHERE
        id = ?;
      `,
      [id],
    );
  }

  public async delete(id: number): Promise<void> {
    await this.ormRepository.delete(id);
  }
}
```

### Optional: Create migrations
If you don't intend to use migrations, TypeORM has a [*synchronization*](https://github.com/typeorm/typeorm/blob/master/docs/faq.md#how-do-i-update-a-database-schema) feature that will update all tables according to the models.

If you do want to use migrations, you can start by generating the migration class through the CLI:
```bash
$ yarn typeorm migration:create -n CreateTodosTable -d ./src/data/migrations/
```

then, insert the table creation and drop queries on up and down methods:
```ts
export class CreateTodosTable1608217149351 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: 'todos',
        columns: [
          {
            name: 'id',
            type: 'integer',
            isPrimary: true,
            isGenerated: true,
            generationStrategy: 'increment',
          },
          {
            name: 'text',
            type: 'text',
          },
          {
            name: 'is_toggled',
            type: 'boolean',
          },
        ],
      }),
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropTable('todos');
  }
}
```

### Create a React context for the connection
Another detail of TypeORM that has to work differently while in a React project is that it **is not possible to access the global connection object**. This has something to do with the way the bundler works.

The workaround - and at the same time the beauty of it - is using a React context to pass down the connection to each component that is going to use it.

For this project, since we'll be going to use the Repository pattern to abstract any database logic, the context will provide all available repositories.

In [`src/data/connection.tsx`](https://github.com/jgabriel1/expo-sqlite-typeorm-article/blob/main/src/data/connection.tsx) we'll start by creating the context and declaring the interface for the data that it'll provide:

```tsx
interface DatabaseConnectionContextData {
  todosRepository: TodosRepository;
}

const DatabaseConnectionContext = createContext<DatabaseConnectionContextData>(
  {} as DatabaseConnectionContextData,
);
```

Then, we'll create the connection provider component, which will hold the connection logic for the entire App, and the `useDatabaseConnection` hook, which will be called inside components that want to subscribe to the context:
```tsx
export const DatabaseConnectionProvider: React.FC = ({ children }) => {
  const [connection, setConnection] = useState<Connection | null>(null);

  const connect = useCallback(async () => {
    // TODO
  }, []);

  useEffect(() => {
    if (!connection) {
      connect();
    }
  }, [connect, connection]);

  if (!connection) {
    return <ActivityIndicator />;
  }

  return (
    <DatabaseConnectionContext.Provider
      value={{
        todosRepository: new TodosRepository(connection),
      }}
    >
      {children}
    </DatabaseConnectionContext.Provider>
  );
};

export function useDatabaseConnection() {
  const context = useContext(DatabaseConnectionContext);

  return context;
}
```
What this component does is basically try to connect to the database as soon as the App starts. When that happens, it passes down the `todosRepository` as the context value to the children components (which is the entire application).

Let's take a closer look at the `connect` function, since it will hold the configurations object:

```tsx
const connect = useCallback(async () => {
    const createdConnection = await createConnection({
      type: 'expo',
      database: 'todos.db',
      driver: require('expo-sqlite'),
      entities: [TodoModel],

      //If you're not using migrations, you can delete these lines,
      //since the default is no migrations:
      migrations: [CreateTodosTable1608217149351],
      migrationsRun: true,
      
      // If you're not using migrations also set this to true
      synchronize: false,
    });

    setConnection(createdConnection);
  }, []);
```

### Provide the connection context to the App
Since it's a context, it needs to be provided in order to be used inside the app.

In `src/index.tsx`:
```tsx
 const App: React.FC = () => {
   return (
~    <DatabaseConnectionProvider>
       <StatusBar style="auto" />
       <SafeAreaView style={{ flex: 1 }}>
         <TodoList />
       </SafeAreaView>
~    </DatabaseConnectionProvider>
   );
 };
```

---

## Implement the Persistence layer

Now that we've setup the database connection, all that is left to do is to implement it to our application.

### Call `useDatabaseConnection` in the component
To access `todosRepository`, we'll call the custom hook in the main application component and destructure it:

```ts
// src/components/TodoList.tsx

const { todosRepository } = useDatabaseConnection();
```

Now the component is wired to the database and can make changes on it through the repository.

### Load stored To-Dos
Through a `useEffect` hook, the To-Dos can be loaded and stored in the already existing state:
```ts
// src/components/TodoList.tsx

const [todos, setTodos] = useState<TodoItem[]>([]);

// ...

useEffect(() => {
  todosRepository.getAll().then(setTodos);
}, []);
```

### Change all state altering callbacks

Change all the existing callbacks to now persist the changes to the database:

```ts
  const handleCreateTodo = useCallback(async () => {
+   const todo = await todosRepository.create({ text: newTodo });

~   setTodos(current => [...current, todo]);

    setNewTodo('');
~ }, [newTodo, todosRepository]);

  const handleToggleTodo = useCallback(
~   async (id: number) => {
+     await todosRepository.toggle(id);

      setTodos(current =>
~       current.map(todo => {
~         return todo.id === id
~           ? { ...todo, is_toggled: !todo.is_toggled }
~           : todo;
        }),
      );
    },
~   [todosRepository],
  );

  const handleDeleteTodo = useCallback(
~   async (id: number) => {
+     await todosRepository.delete(id);

~     setTodos(current => current.filter(todo => todo.id !== id));
    },
~   [todosRepository],
  );
``` 

### Change the declared interface
Now that we have the To-Dos ids available, they can be used to identify each item in the state list instead of the index:
```tsx
  <View style={styles.todosContainer}>
~  {todos.map(todo => (
      <TouchableOpacity
~       key={String(todo.id)}
~       onPress={() => handleToggleTodo(todo.id)}
~       onLongPress={() => handleDeleteTodo(todo.id)}
      >
        <Todo text={todo.text} isToggled={todo.is_toggled} />
      </TouchableOpacity>
    ))}
  </View>
```

Now the changes should persist in the database when you turn the application off and back on!

> Great! We're done now, right?
> Not yet!

Even though the app has been working just fine until now on development mode, it will not once it's published with `expo publish` or built into a standalone App.

---

## Minification configuration

TypeORM relies a lot on the class and prop names to work and that's not really a concern on the backend, since the code is not really *bundled* or *minified* in that context.

However, in React Native, the code is usually minified in order to reduce the file size of the final application. That minification proccess will cause TypeORM problems.

For example, in the migration class we created earlier, the name `CreateTodosTable1608217149351` will usually be converted to a `t` or any other single letter variable name. That's usually how the minification happens.

That will cause TypeORM to indicate an invalid migration name, since it's not able to grab the timestamp from it anymore.

If you wanna emulate that still in development mode, you can run it with the `--minify` tag. Your application will not be able to  start correctly since it will throw the above error.
```bash
$ expo start --minify
```

To avoid that, we need to change the minification configurations in *Metro Bundler*. [This post](https://forums.expo.io/t/change-minifierconfig-for-minify-uglify/36460) will give us a configuration file and how to enable it.

I'll admit I didn't look more in depth into it on how to do a finer tunning to change only what's neccessary. I think it's enough that we know what the problem is and that this is solving it.

Create a `metro.config.js` in the project root folder:
```js
module.exports = {
  transformer: {
   minifierConfig: {
     keep_classnames: true, // FIX typeorm
     keep_fnames: true, // FIX typeorm
     mangle: {
       keep_classnames: true, // FIX typeorm
       keep_fnames: true, // FIX typeorm
     },
     output: {
       ascii_only: true,
       quote_style: 3,
       wrap_iife: true,
     },
     sourceMap: {
       includeSources: false,
     },
     toplevel: false,
     compress: {
       reduce_funcs: false,
     },
   },
 },
};
```

In `app.json` add the following property inside `"expo"`:
```json
"packagerOpts": {
  "config": "metro.config.js"
}
```

Now the application is ready to be published!

## Final comments

Most of the problems addressed in this article stem form the fact that **TypeORM**'s main focus is to be a library that will run on Node and we're trying to run it in a different platform (React Native).

Nevertheless, in my opinion it is still the best option would you ever want to use a local relational database in the device for a complex business implementation and happened to be using Expo.

If this article helped you in any way, please tell me in the comments. If you have any concerns you can comment as well, in the meantime please refer to the [**repository**](https://github.com/jgabriel1/expo-sqlite-typeorm-article).

Thanks!