Expo SQLite + TypeORM

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. 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 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:

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 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 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 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.

{
  /* ...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.

// 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, 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.

// 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 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.

@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:

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 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:

$ yarn typeorm migration:create -n CreateTodosTable -d ./src/data/migrations/

then, insert the table creation and drop queries on up and down methods:

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 we'll start by creating the context and declaring the interface for the data that it'll provide:

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:

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:

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:

 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:

// 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:

// 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:

  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:

  <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.

$ expo start --minify

To avoid that, we need to change the minification configurations in Metro Bundler. This post 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:

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":

"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.

Thanks!

Comments

[Mayowa Daniel]

Ridiculously helpful article. I've referenced it in multiple issues!

github.com/typeorm/expo-example/is...

github.com/andpor/react-native-sql...

[José Gabriel M. Bezerra]

Thanks for sharing it! I'm glad you liked it.

[Razi]

Hi, @jgabriel1 ! Is it possible you'll be updating the article with the new TypeORM API? I mean we have breaking changes here and there like no more Connection and createConnection. Can you look into it? Would be nice! Cheers!

[Dan Lawler]

Thanks for this!
Following these instructions, I ran into a small oddity:
If I create a new record, I do not receive the new record's ID with a save operation.

let result = await this.ormRepository.save(todo);

Object {
  "firstName": "first",
  "lastName": "last",
}

Has anyone else had this issue and any recommendations?

Using:
"typeorm": "^0.2.43"
"expo-sqlite": "~10.1.0",

[alper]

Hi Jose,

Really enjoyed reading the article. Very clear and helpful.

Thanks

[José Gabriel M. Bezerra]

Thank you so much! I'm glad it helped you.

[jonasfrid]

Great walkthrough, thanks! FYI, typeorm v 0.2.31 has now been released with a fix to the regex related problem.

[José Gabriel M. Bezerra]

Thanks! Yeah, I just didn't have time to test it with the new patch. I'll make sure to edit that part though.

[Christopher Krogh]

With the new typeorm (DataSource) API, you can store that in the context instead. But don't forget to initialize it before usage!

[Lucky]

Thank you for this awesome article! Would you consider to update it to the latest expo and typeorm version? I am really struggling with getting it to work, especially the migration part.