Expo SQLite无法向表中插入数据问题求助

问题说明 #

使用expo-sqlite创建数据库和表成功，但执行插入数据操作时触发错误日志，无法正常完成数据插入。

代码示例 #

import * as SQLite from 'expo-sqlite';
import { useEffect } from 'react';

const Onboarding = ({ navigation }) => {
  const db = SQLite.openDatabase('custom.db');

  const createTable = () => {
    db.transaction((tx) => {
      tx.executeSql(
        'CREATE TABLE IF NOT EXISTS currencies (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, sign TEXT);',
        [],
        () => console.log('Table created successfully'),
        (error) => console.log('Error creating table: ', error)
      );
    });
  };

  const insertCurrency = (name, sign) => {
    db.transaction((tx) => {
      tx.executeSql(
        'INSERT INTO currencies (name, sign) VALUES (?, ?);',
        [name, sign],
        () => {
          console.log('CURRENCY INSERTED');
        },
        (error) => {
          console.log('Error occurred while inserting the currency:', error);
        }
      );
    });
  };

  const getAllEntries = () => {
    db.transaction((tx) => {
      tx.executeSql(
        'SELECT * FROM currencies;',
        [],
        (_, result) => {
          const entries = result.rows._array;
          console.log("These are the entries:" + entries);
        },
        (_, error) => {
          console.error(error);
        }
      );
    });
  };

  useEffect(() => {
    createTable();
    insertCurrency('US Dollar', '$');
    getAllEntries();
  })

  return(
    // rest of my code here
  );
};

错误日志（翻译后） #

插入货币时发生错误: {"_complete": false, "_error": null, "_running": true, "_runningTimeout": false, "_sqlQueue": {"first": undefined, "last": undefined, "length": 0}, "_websqlDatabase": {"_currentTask": {"errorCallback": [匿名函数], "readOnly": false, "successCallback": [匿名函数], "txnCallback": [匿名函数]}, "_db": {"_closed": false, "_name": "custom.db"}, "_running": true, "_txnQueue": {"first": [对象], "last": [对象], "length": 1}, "closeAsync": [绑定的close函数], "deleteAsync": [绑定的deleteAsync函数], "exec": [绑定的exec函数], "version": "1.0"}}

问题分析 #

1. 异步时序问题：createTable、insertCurrency、getAllEntries都是异步操作，但在useEffect中同步调用会导致插入和查询可能在表创建完成前就执行，触发事务异常。错误日志中_error为null，说明不是SQL语法错误，而是时序混乱导致的事务状态异常。
2. useEffect无依赖数组：组件每次渲染都会重复执行这三个操作，加剧了事务队列的混乱。

解决方法 #

将操作改为按顺序执行，通过Promise化事务并使用async/await控制流程，同时给useEffect添加空依赖数组避免重复执行：

修改后的代码 #

import * as SQLite from 'expo-sqlite';
import { useEffect } from 'react';

const Onboarding = ({ navigation }) => {
  const db = SQLite.openDatabase('custom.db');

  // 表创建改为返回Promise
  const createTable = () => {
    return new Promise((resolve, reject) => {
      db.transaction((tx) => {
        tx.executeSql(
          'CREATE TABLE IF NOT EXISTS currencies (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, sign TEXT);',
          [],
          () => {
            console.log('Table created successfully');
            resolve();
          },
          (_, error) => {
            console.log('Error creating table: ', error);
            reject(error);
          }
        );
      });
    });
  };

  // 插入操作改为返回Promise
  const insertCurrency = (name, sign) => {
    return new Promise((resolve, reject) => {
      db.transaction((tx) => {
        tx.executeSql(
          'INSERT INTO currencies (name, sign) VALUES (?, ?);',
          [name, sign],
          () => {
            console.log('CURRENCY INSERTED');
            resolve();
          },
          (_, error) => {
            console.log('Error occurred while inserting the currency:', error);
            reject(error);
          }
        );
      });
    });
  };

  // 查询操作改为返回Promise
  const getAllEntries = () => {
    return new Promise((resolve, reject) => {
      db.transaction((tx) => {
        tx.executeSql(
          'SELECT * FROM currencies;',
          [],
          (_, result) => {
            const entries = result.rows._array;
            console.log("查询到的条目:", entries);
            resolve(entries);
          },
          (_, error) => {
            console.error(error);
            reject(error);
          }
        );
      });
    });
  };

  useEffect(() => {
    const initDatabase = async () => {
      try {
        // 按顺序执行：先建表，再插入，最后查询
        await createTable();
        await insertCurrency('US Dollar', '$');
        await getAllEntries();
      } catch (err) {
        console.error('数据库初始化失败:', err);
      }
    };

    initDatabase();
  }, []); // 空依赖数组确保只执行一次

  return(
    // rest of my code here
  );
};

额外说明 #

• 也可以通过回调嵌套的方式控制顺序（在createTable的成功回调中调用insertCurrency，再在insertCurrency的成功回调中调用getAllEntries），但async/await的写法更简洁易读。
• 你的CREATE和INSERT语句语法正确，问题核心是异步时序混乱。

内容的提问来源于stack exchange，提问作者Artyom