expo-sqlite backup/restore - Error: readonly database (code 1032)

Expo SDK
1

Please provide the following:

  1. SDK Version: 37
  2. Platforms(Android/iOS/web/all): Android

Im using expo-sqlite with typeorm, the Error: attempt to write a readonly database (code 1032) occurs when i try to insert / delete in the restored sqlite database file.

My backup / restore process are the following:

//  BACKUP
// 1 - close connection
await getConnection().close();

// 2 - copy the sqlite database file
const databaseFileUri = `${FileSystem.documentDirectory}SQLite/database.db`;
const backupFileUri = `${FileSystem.documentDirectory}/backup-database.db`;

await FileSystem.copyAsync({
   from: databaseFileUri,
   to: backupFileUri,
 });

// 3 - open connection
createConnection();

// 4 - upload the copied file to google drive as base64 content in the body 
const bdFileBase64 = await FileSystem.readAsStringAsync(backupFileUri , {
     encoding: FileSystem.EncodingType.Base64,
});



// RESTORE
// 1 - download file from google drive:
 await FileSystem.downloadAsync(
      `https://www.googleapis.com/drive/v3/files/${fileId}?alt=media`,
      backupUriBase64,
      {
        headers: {
          Authorization: `Bearer ${accessTokenGoogle}`,
        },
      }
    );

// 2 -  read the downloaded file base64 content as string
      const backupContentBase64 = await FileSystem.readAsStringAsync(
        backupUriBase64,
        {
          encoding: FileSystem.EncodingType.UTF8,
        }
      );

// 3 -  parse base64 content string to binary and write to file
      await FileSystem.writeAsStringAsync(destinationUri, backupContentBase64, {
        encoding: FileSystem.EncodingType.Base64,
      });

// 4 - close connection

// 5 - rename current database file to old.db

// 6 - rename created file with writeAsStringAsync in step 3 to database.db

 // 7 - delete old.db file

// 8 - open connection

// 9 - try to insert : Error: attempt to write a readonly database (code 1032)

the strange part is selects still return the previous (and deleted) database file data, is there any cache that expo create for sqlite files?

i dont think its a encode / decode problem because i downloaded the base64 file from drive, manualy decode and i can open it with https://sqlitebrowser.org/ without errors even insert delete

2

I discovered that the problem comes from opening database when the file don’t exists, if i do something like that:

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

  db.transaction(
	tx => {
	   tx.executeSql(
	     'create table if not exists items (id integer primary key not null, done int, value text);'
	   );
	   tx.executeSql(
	     'insert into items (done, value) values (0, ?)',
	     ['test']
	  );
	  tx.executeSql(
		'select * from items ',
		[],
		(_, { rows }) => console.log(JSON.stringify(rows))
	  );
	},
	null,
	null
  );

SQLite creates 2 files in ${FileSystem.documentDirectory}SQLite/
database.db
database.db-journal

Closing connection and deleting both files and replacing with new ones (from backup) dont work, i just can’t use that name database.db anymore it looks like it stays hold in some cache when it is created automatically (not event restarting the app to use it again need delete expo app data in android).

If i dont let the sqlite create the database file automatically and place my backup in ${FileSystem.documentDirectory}SQLite/ before any openDatabase() and then open it, my backup/restore process just work, i can close connection and replace the file freely without errors and insert delete works.

I noticed that placing file before and then opening connection dont create the -journal file, maybe the issue can be related with that file

Rollback Journals

https://www.sqlite.org/tempfiles.html

3

Solved

4

This topic was automatically closed 20 days after the last reply. New replies are no longer allowed.