React Native Expo中SQLite数据库插入数据失败求助

我是React Native Expo新手，开发喝水提醒应用时，采用React Native Expo+SQLite技术，向数据库表插入数据时持续报错，ChatGPT和Google Gemini均未能解决该问题。

报错日志 #

Table created successfully ERROR Error checking date in database:
{"_complete": false, "_error": null, "_running": true,
"_runningTimeout": false, "_sqlQueue": {"first": undefined, "last":
undefined, "length": 0}, "_websqlDatabase": {"_currentTask":
{"errorCallback": [Function anonymous], "readOnly": false,
"successCallback": [Function anonymous], "txnCallback": [Function
anonymous]}, "_db": {"_closed": false, "_name": "waterTracker.db",
"close": [Function closeAsync]}, "_running": true, "_txnQueue":
{"first": undefined, "last": undefined, "length": 0}, "closeAsync":
[Function bound closeAsync], "closeSync": [Function bound closeSync],
"deleteAsync": [Function bound deleteAsync], "exec": [Function bound
exec], "execAsync": [Function bound execAsync], "execRawQuery":
[Function bound execRawQuery], "transactionAsync": [Function bound
transactionAsync], "version": "1.0"}}

相关代码 #

import React, { useState, useEffect } from 'react';
import { View, Text, Button, StyleSheet } from 'react-native';
import * as SQLite from 'expo-sqlite';
import { format } from 'date-fns';

const db = SQLite.openDatabase('waterTracker.db');

const App = () => {
  const [dailyIntake, setDailyIntake] = useState(0);
  const [currentDate, setCurrentDate] = useState('');
  const [isDbInitialized, setIsDbInitialized] = useState(false);

  useEffect(() => {
    const initializeDatabase = () => {
      db.transaction(tx => {
        tx.executeSql(
          'CREATE TABLE IF NOT EXISTS waterIntake (id INTEGER PRIMARY KEY AUTOINCREMENT, date DATE, intakeChange INTEGER, totalIntake INTEGER)',
          [],
          () => {
            console.log('Table created successfully');
            setIsDbInitialized(true);
          },
          error => {
            console.error('Error creating table: ', error);
          }
        );
      });
    };

    initializeDatabase();

    const date = format(new Date(), 'yyyy-MM-dd');
    setCurrentDate(date);

    db.transaction(tx => {
      tx.executeSql(
        'SELECT * FROM waterIntake WHERE date = ?',
        [date],
        (_, { rows }) => {
          if (rows.length === 0) {
            db.transaction(tx => {
              tx.executeSql(
                'INSERT INTO waterIntake (date, totalIntake) VALUES (?, ?)',
                [date, 0], // Set totalIntake to initial value
                () => {
                  console.log('Today\'s date inserted into the database');
                },
                error => {
                  console.error('Error inserting date into the database: ', error);
                }
              );
            });
          } else {
            // Fetch and set daily intake if date exists in the database
            setDailyIntake(rows.item(0).totalIntake);
          }
        },
        error => {
          console.error('Error checking date in database: ', error);
        }
      );
    });
  }, []);

  const addWater = () => {
    const intakeChange = 1;
    if (isDbInitialized) {
      updateIntakeAndSaveHistory(intakeChange);
    } else {
      console.error('Database is not initialized yet.');
    }
  };

  const deleteWater = () => {
    if (dailyIntake > 0) {
      const intakeChange = -1;
      if (isDbInitialized) {
        updateIntakeAndSaveHistory(intakeChange);
      } else {
        console.error('Database is not initialized yet.');
      }
    }
  };

  const updateIntakeAndSaveHistory = (intakeChange) => {
    const newIntake = dailyIntake + intakeChange;
    setDailyIntake(newIntake);

    db.transaction(tx => {
      tx.executeSql(
        'INSERT INTO waterIntake (date, intakeChange, totalIntake) VALUES (?, ?, ?)',
        [currentDate, intakeChange, newIntake],
        (_, results) => {
          if (results.rowsAffected > 0) {
            console.log('Water intake history saved successfully');
          } else {
            console.log('Failed to save water intake history');
          }
        },
        error => {
          console.error('Error saving water intake history: ', error);
        }
      );
    });
  };

  return (
    <View style={styles.container}>
      <Text style={styles.header}>Water Tracker</Text>
      <Button title="Add Glass" onPress={addWater} />
      <Text style={styles.text}>Daily Intake: {dailyIntake} glasses</Text>
      <Button onPress={deleteWater} title="Delete Glass" />
      <Text style={styles.text}>Today's Date: {currentDate}</Text>
    </View>
  );
};

const styles = StyleSheet.create({
  container: {
    flex: 1,
    justifyContent: 'center',
    alignItems: 'center',
    backgroundColor: '#fff',
  },
  header: {
    fontSize: 24,
    fontWeight: 'bold',
    marginBottom: 20,
  },
  text: {
    fontSize: 18,
    marginBottom: 10,
  },
});

export default App;

问题原因及解决方法 #

核心问题 #

1. 数据库操作顺序混乱：初始化表的事务与后续查询/插入事务并行执行，可能表还未创建完成就执行了SQL操作，导致报错。
2. 嵌套事务冲突：在查询回调中开启新事务，Expo SQLite的事务处理对嵌套执行存在兼容性问题。
3. 状态更新异步延迟：isDbInitialized状态更新是异步的，后续操作可能在状态未完成更新时触发。

修复步骤 #

1. 保证操作顺序：将查询/插入逻辑放到表创建成功的回调中，或使用异步事务API确保执行顺序。
2. 避免嵌套事务：在同一事务内完成查询和插入，或使用链式事务调用。
3. 改用异步事务API：使用transactionAsync和executeSqlAsync配合async/await语法，简化代码结构，统一错误处理。

修复后的代码示例 #

import React, { useState, useEffect } from 'react';
import { View, Text, Button, StyleSheet } from 'react-native';
import * as SQLite from 'expo-sqlite';
import { format } from 'date-fns';

const db = SQLite.openDatabase('waterTracker.db');

const App = () => {
  const [dailyIntake, setDailyIntake] = useState(0);
  const [currentDate, setCurrentDate] = useState('');
  const [isDbInitialized, setIsDbInitialized] = useState(false);

  useEffect(() => {
    const initializeAndSetupData = async () => {
      try {
        // 创建表
        await db.transactionAsync(async tx => {
          await tx.executeSqlAsync(
            'CREATE TABLE IF NOT EXISTS waterIntake (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, intakeChange INTEGER, totalIntake INTEGER)'
          );
        });
        console.log('Table created successfully');
        setIsDbInitialized(true);

        const date = format(new Date(), 'yyyy-MM-dd');
        setCurrentDate(date);

        // 查询今日数据
        const [result] = await db.transactionAsync(async tx => {
          return await tx.executeSqlAsync(
            'SELECT * FROM waterIntake WHERE date = ?',
            [date]
          );
        });

        if (result.rows.length === 0) {
          // 插入初始数据
          await db.transactionAsync(async tx => {
            await tx.executeSqlAsync(
              'INSERT INTO waterIntake (date, totalIntake) VALUES (?, ?)',
              [date, 0]
            );
          });
          console.log('Today\'s date inserted into the database');
        } else {
          setDailyIntake(result.rows.item(0).totalIntake);
        }
      } catch (error) {
        console.error('Database operation failed: ', error);
      }
    };

    initializeAndSetupData();
  }, []);

  const updateIntakeAndSaveHistory = async (intakeChange) => {
    const newIntake = dailyIntake + intakeChange;
    setDailyIntake(newIntake);

    try {
      await db.transactionAsync(async tx => {
        await tx.executeSqlAsync(
          'INSERT INTO waterIntake (date, intakeChange, totalIntake) VALUES (?, ?, ?)',
          [currentDate, intakeChange, newIntake]
        );
      });
      console.log('Water intake history saved successfully');
    } catch (error) {
      console.error('Error saving water intake history: ', error);
    }
  };

  const addWater = () => {
    if (isDbInitialized) {
      updateIntakeAndSaveHistory(1);
    } else {
      console.error('Database is not initialized yet.');
    }
  };

  const deleteWater = () => {
    if (dailyIntake > 0 && isDbInitialized) {
      updateIntakeAndSaveHistory(-1);
    } else if (!isDbInitialized) {
      console.error('Database is not initialized yet.');
    }
  };

  return (
    <View style={styles.container}>
      <Text style={styles.header}>Water Tracker</Text>
      <Button title="Add Glass" onPress={addWater} />
      <Text style={styles.text}>Daily Intake: {dailyIntake} glasses</Text>
      <Button onPress={deleteWater} title="Delete Glass" />
      <Text style={styles.text}>Today's Date: {currentDate}</Text>
    </View>
  );
};

const styles = StyleSheet.create({
  container: {
    flex: 1,
    justifyContent: 'center',
    alignItems: 'center',
    backgroundColor: '#fff',
  },
  header: {
    fontSize: 24,
    fontWeight: 'bold',
    marginBottom: 20,
  },
  text: {
    fontSize: 18,
    marginBottom: 10,
  },
});

export default App;

额外优化点 #

• 将date字段类型从DATE改为TEXT：SQLite无原生DATE类型，用TEXT存储格式化日期字符串更可靠，避免类型转换问题。
• 合并重复逻辑：将添加和删除操作统一到updateIntakeAndSaveHistory函数，减少冗余代码。
• 统一错误处理：使用try/catch捕获所有数据库操作异常，便于排查问题。

内容的提问来源于stack exchange，提问作者Harsh Divine Author