Local SQLite Database for Expo React Native App with Import and Export Database from Device Files

hi everyone today I'm going to show you how to add a sqlite database to your Expo react native app so you're first going to want to install the Expo sqlite package and then you can import Star as to collect from Expo sqlite I'm also going to import use effect and I'm inside that I'm going to sort of set up my database and load some data from my database and I'm also going to use state to determine whether anything's still loading from my database later on in the video I'll show you how to Import and Export databases so you might want to use this if you've got a sqlite database in your application just because you might want to store a bit more data than you would store in the async storage and you may not have your own servers set up at this stage so I'm going to use a state variable for whether it's loading or not so when it is loading I'm going to show on screen a little message that says that it's loading and when it's not loading I'll show The Loaded data so I'm just returning a view with the text that it's loading I'm just going to have a really simple database today it's just going to basically have a table of names um if you wanted more complex stuff or if you want to learn a bit more of sqlite because you don't know it I'll link in the description a useful website to learn sqlite and from there you can sort of build your own uh seek collect queries and you can access your database and do whatever you want with it I've got another state variable for names because that's what I'm going to be loading and I'm also going to have a little text editing box um where people can enter a name and edit add the name and so that current name there that I'm adding right now is just going to be what is in the text input I'll also use the text input to be able to edit the name so you're going to see that later on in the application so the value of my text input is going to be the current name and when the text value changes I'm going to set the current name I'm also just going to have a placeholder of name so that people know that that's where you enter a name that's all I need for my text input I'm just going to set this use state to false so that you um so you can see that text input I've made because I've basically not loading the data yet but I'll be loading a bit later I can set it back to true and it will show us loading again now I'm going to go ahead and set up my use effect hook so basically what's going to happen is this is going to be called every time the application is loaded because I'm not depending on anything it's only going to be called once so what I'm going to do is in here is I'm going to set up a database table if it doesn't exist and also load all my names because that's the database table I'm going to be setting at so just run a SQL like query on your database you're going to first need to create transaction which you can then execute SQL on and you can pass in a command inside the string here it's um so here I'm just doing create a table if not exists names and then I specify the um different columns in the table so I have an ID column which is going to be the primary key which means that other tables can reference it as a foreign key and um you can create a foreign constraint and they'll you'll be able to refer to different tables from within your table within another table I'm also going to have a name which is just of type text it's a very simple table and I'm not going to create other any other tables if you want to learn more about sqlite once again I'll link that in the description below so I'm going to create another database transaction and inside this one I'm going to actually select the data from the table that I want so I'm going to go transaction.execute SQL and I'm going to pass in my SQL query which is just Select Staff from names basically that's going to select all the data from names and I'm passing a null for the parameters because I don't have any parameters inside my query um this allows you to sort of avoid SQL injection because if you um I'll show you it a bit later but basically when you're building a SQL query if you just concatenate user input you can actually get an issue with a person that um sort of like malicious text and it sort of can execute SQL on your database that you don't intend to so you should parameterize your SQL queries and yeah if you don't know about SQL injection you can just have a little bit of a Google and read about that but it's a very common attack if there's an error I'm just going to log it and for the actual results I'm going to set that against the names State variable and when that's loaded it will be able to show on screen once I um add the UI for that I'll also set as loading to false because at this stage I've loaded the names so you can see that now it shows that name text input but it doesn't show any um any names at this stage and that's because I haven't implemented the UI for that it also doesn't show any errors though so that's a good sign I'll show you what would happen if there was an error a bit later by forcing an error so now I'm going to go ahead and just create a new function that's going to show the names so I'm just creating the function now it's called show names and basically what I'm going to do is I'm just going to map the names to a new sort of row so some map function is actually a really useful function that you can use on arrays and basically what it does is you um it goes through each individual item in the array and you can sort of get an array of um reaction native components back because you're mapping from that item in the array to a component so I'm returning this component here and it means basically you could just sort of build up a very simple list of um components that relate to whatever array you have so I'm going to show the name and I'm going to show a um a button a bit later that will do the update and delete but for now I'm just going to show you the names I don't have any names at the moment so I'm going to need to go and add a button that will allow me to add a name also just going to style my um name rows so that they're actually appearing in the row because later on I'm going to add buttons so it's going to show the name text and an update and delete button all on the same line and it's going to be kind of like a list of the names so now I've got that all starred nicely I'm gonna go add my button which is going to allow me to add the name so I'm just going to give that a title it's going to say add name and on press I'm just going to call the function add name I'm just going to go ahead and Define that function now so once again I need that DB and I'm going to do a transaction there and on that transaction I can start executing my SQL my sequel here is going to be an insert because I'm wanting to insert a row into the database as I'm adding a name so I'm just going execute SQL and insert into names and then I specify the um the column I want to add and then the values you can add multiple you can see I'm using that question mark which means it's parameterized query because I'm taking data from the user and then I'm supplying the values that match with the um with the parameter so the question mark and that's the current name which comes from the text input then I've got my two callbacks so I've Got My Success callback which is going to give me the result set and I'm just going to basically add that to the existing names um um to the to the names and update the state variable so what I'm doing here is I've got that spread operator and basically making a clone of the names array and then I'm adding to it and then I'm just going to go ahead and set the array so I'm just adding my names row and then I'm just going to go ahead and set that names State variable with that new updated names which will include my latest inserted name then I can go ahead and do my error I'm just going to console log that this will be called if there's any errors executing the SQL statement so you might want to do more than console logging but for my bit this is this is totally fine I'm also going to set that current name to undefined so they clear it out that the next name can be added so I've added a name and I can add as many names as I wish if I were to reload my application then it would um keep those names it would load them out of that SQL database sqlite database and so the data would be persisted for my application I wouldn't need to enter it again every single time I loaded up the application it's a good way of sort of keeping data between application sessions next up I'm going to do the delete name and basically that's going to be a button added to my individual name rows and I'm going to need another database transaction because I'm executing SQL so with that transaction I can once again do TX dot execute SQL and then I can write my command which is the delete from names and then I just am passing the ID that I want to delete so I need to check that the ID is equal to the correct value once again I'm parameterizing that and passing the parameter I want into the execute SQL function and then I've got my success and error callbacks once again I'm checking if there are row any rows affected um because if there aren't any rows affected then um there's no point in updating my names um but I'm just going to go ahead and update my names in the same way I'm going to create a clone of the names array and I'm going to filter out the results where um basically I don't want any of them the IDS to equal the ID that I want to delete because I'm filtering that ID out and then I'm going to set the names to this new array and then I'm going to create my error call back now I need to go ahead and add my delete button so I'm just going to add that to my um my name row I'm going to give that a title of delete and on press I'm just going to call that delete um name function and I'm going to pass in the ID so you can see I've got these delete buttons now and if I delete it will delete it from the page and you can actually check it's actually deleted by reloading the application and you can see that it didn't really um load that name when I reloaded the application which showed it actually is deleted from the database I'm also going to want to be able to do updates so what I'm going to do here is I'm going to um create a new update function and I'm going to do a very similarly to the delete I'm just going to take in an ID and update a particular um row depending on what id is passed in and I'm going to use the value from that text input on screen so here's my SQL command it's just going to be a simple update names set the name equal to the parameter up that I'm going to pass in from the text input where the ID is equal to the parameter passed in from the ID so then I specify my parameters which is the current name and ID you specify them in the order that the question marks appear basically and then I'm going to specify my success and value callbacks so for My Success callback once again I'm going to want to check if any rows are affected if they aren't then I sort of know that nothing's changed I shouldn't be bothering up to update my names um State variable so I'll do F resultcet dot rows affected is greater than zero I'm going to want to get those existing names using and create a clone of the names um array using that spread operator once again then I'm finding the index to update on the names array so I'm finding the index that matches the certain criteria so I'm going to use the ID to find that index because the ideas and unique unique value whereas the name may not be and then I'm going to go ahead and update that index name with that current name and then I'm going to set the names to be these existing names I'm going to set the current name to undefined so here I'm gonna add another name first and then I need to go ahead and add a update button to my rows so there's my row and I'm just going to go and add the button give that a title of update and um press I'm going to want to go ahead and call that update function with the ID cool so now if I enter a name and go update okay so I was crashing for some reason okay and I can see that I actually set name to the current name rather than existing name so it wasn't an array so you can see now if I go ahead and add a name to that text input and click update on a row it'll update that row and if I were to reload it it would persist that update because that's what I've done I'm going to show you an error now so I'm just going to select star from name which doesn't exist as a table and you can see that it's console logging the error out if I go back to names it's just going to work successfully and there's going to be no error I'm also going to show you how to provide the option to export and import your database using those packages there so the xpofile system document picker sharing and also div client so we're going to need the div client because the document picker requires some extra information in the info.plist and that will allow us to run as a standalone app and access the document picker so there's a few instructions here just on installing and configuring your um Expo div client builds and I'll go through that a bit later anyway so first off I'm just going to want to add a plugin so the plugin is basically and I'm going to want to add um a few different um options to my app.json that are basically related to the document picker it's in the document picker documentation so I'm just going to link that in the description below you can check that out if you want to but basically what you need is that it uses our iCloud storage true or false and the bundle identifier and you're also going to need to specify that you're wanting the config plugin which will um that which is how it works with the expert Dev client to um add the additional information to the info p list of your Standalone application without you having to eject from Expo all this documentation is available on Expo document picker and I'll just um Link in the description the iCloud container environment can be development or production because I'm in development at this stage I'm just going to specify that it's development once you're done you can go ahead and save that you can go back to your app.js and we're going to start writing our code for importing and exporting I'm going to make my databases State variable because I might change it so I might import a new database when I've got already an existing database and in which case I want to update the UI accordingly and because I'm going to be loading the values in this use effect I'm actually going to make that use effect depend on the database I'm going to import Star as sharing from Expo sharing and this is how I'm going to export my database it just creates a very simple way of sharing my database file and saving it to the file system so I'm going to write my export function now so it's just I'm just going to call it export database it's going to be asynchronous because the sharing function is asynchronous so I'm just going to await that sharing.share async and then I'm just going to specify the path to my database we're going to also want to import from Express File system as that's how we're getting the document directory where we're going to share our database from once I've done that I can actually go ahead and add a button that's going to allow me to call my export database function and export my database so I'm just going to create it with the title of export and on press I'm just going to call my export DB function now if I go ahead and click that it's going to give me a few different sharing options and one of those is save to file I can save it anywhere I want but I'm just going to choose to save it on my iPhone I'm going to now go ahead and show you how I would go about creating a button that would allow me to import that if I go to the files you can actually see that it has been saved it's now I'm going to go ahead and show you how to import the database and once again this is going to be asynchronous as there's a view asynchronous functions that I'm going to rely on to import my database you know I'm going to be using the document picker which will allow me to open up the files and choose my file that I want to import so because of that I'm going to need to import from the document picker so I'm going to import Sarah's document picker from Expo document picker and then I'm going to go ahead and save that I'm going to await documentpicker.get document async that's going to allow the user to go and select a document I'm going to copy the cache directory so that it's available to the um file system straight away and I can use that to import it into my application if the result is equal to success then I know that a document has been successfully chosen in which case I can go ahead and start my import I'm going to see it's loading to true because I don't know if this could take a while it's best to sort of show my loading um screen instead of just having the user not knowing what's going on what I'm going to want to do is I want to check if the sqlite directory exists already and if it doesn't then I'm going to want to go ahead and create it because that's where I'm going to save my file to basically the sqlite directory is where your um app is going to look for a database file when you do sqlite.open database so here I'm just going to go ahead and create that directory next I'm just going to load the base64 data from my file and then I can go ahead and save that into the um location where I'm going to read from for my database so I'm specifying the file URI based on what the user's selected and then I'm going to specify the encoding I want to be base64. next I'm just going to write that as a string so that my database file exists on where I want it to be I'm giving the file name that I want to open and read from once again I'm just giving that base64 data as that's the type that I'm um the data that I want to save and that's got all my data from that I've loaded from the imported file then I'm going to close my existing database as otherwise you will get an era of sorts due to potential inconsistency and now I can go ahead and open my new one this is going to trigger my use effects which will then mean that the app is reloaded with the new data I've got my import function sorted I'm going to go and add a button that will allow me to do that so I'm going to call that import DB function from my import button so now if I go and click on import I can go and select the file you can see that there's my example database but it's grayed up I'm pretty sure this is actually because I I'm I'm using Expo go currently if I go ahead and use the um use an actual Standalone application this should work so I'm going to go ahead and do that now so basically what you're going to want to do is first you're going to want to configure for EAS to do an EAS build configure and then you're going to want to select all because you're going to want to be able to do IOS and Android I'm going to go into my EAS dot Json file which is generated from this EAS configure command and I'm going to in the development section add an iOS section and set simulator to true that's basically going to allow me to build a file that I can just drag and drop onto my Simulator for testing purposes it's going to make my life really easy then I can go ahead and run this command here if you do dash dash local then you can just build locally I'm just going to build on the Expo website if you wanted to build locally it's going to sort of save you queue times if you're wanting to build both IOS and Android um so that's one reason why you might build locally it might be a bit faster too just depending on wait times so it's gone ahead and uploaded that to EAS and then it just goes ahead and builds it for you and once it's done you can download it as gonna download a tar file which is going to include the application file that you can drag onto your simulator so I'm going to go ahead and open my files expand that tar file and there you go there's the app that I can drag across and it will install automatically for me then I can just go ahead and run the command Expo start dash dash Dev client and specify that I want to open on iOS and it's going to open up inside their application rather than Expo go yeah this is good for also testing your Standalone apps because it's going to be more representative of what it will be like in production so I can go ahead and choose import DB I can go to on my iPhone and I can actually now select that file and it loaded them up as you could see just then now I'm going to show you this on the Android and so one key difference here is is I can add by names but when I go export it doesn't give me the option to save directly to the files so in Android you need to do it kind of slightly differently um because when you go import it's going to um use the document picker it's going to try and import from your files and so you want to be able to save to the same place as where you're going to be loading from and so for that reason I'm going to go ahead and show you how to save directly to the files files on an Android so what I'm going to do is I just want to check whether the operating system is Android and if it is I'm just going to do some additional code so first off I'm just asking for permission to act this um the file system on the Android device so you can do that using the storage access framework and this isn't available for iOS which is why the code has to differ it's going to basically pop up and ask for permission and you can grant permission to a folder and then the permissions object return will tell you what folder you have access to and where the permission was granted then what I'm going to do is I'm basically going to um I'm going to read by file that is um my my my database file into base64 and then I'm just going to save it to that location that I wanted to save it to that I've selected using the storage access framework so very similar codes to when I'm importing but kind of in the reverse where I'm reading the existing file and saving to um like reading the database file that is already in use and then saving to the device's valve system so yeah I'm gonna go ahead and create the file um inside the file system for Android using that storage access framework and then once I've created that file I can go ahead and write the data to it which is the basics before encoded data so now I've created that file wherever the users accept um selected I can go ahead and save the database file there so I'm just going to write that data from the database that I want to export to where I want it to export to and I'm just specifying that the encoding type is base64. I'll catch any errors and just console log them if this was your production app and there was an area you'd probably want to do something else but for me this is suitable for showing you how what to do if the user doesn't Grant permission I'm just going to console log permission not granted you'd probably want to show them something on screen to sort of prompt them to Grant permission so I'm going to go back ahead back again and now I'm going to try and Export database again so I'm just going to make some changes I'm going to add another name I'm going to export the database so you can now see that I can save to anywhere on my phone I'm going to create a new folder for it I'm just going to call it my app my app's only going to have access to this folder because I'm giving it permission using the storage access framework so now that should be exported there if I were to delete something and then import it should reload based on the database that I saved so you can see that's reloaded that data I hope you've enjoyed this tutorial today if you have please like And subscribe for more content all my code will be available on GitHub

Channel: MissCoding

Views: 44,116

Keywords: Expo SQLite db tutorial, Expo react native, Expo dev client, Expo file system, Expo document picker, Expo sharing, Expo-SQLite, React native SQLite db, Local db react native, Expo local database, React native tutorial, Expo-sqlite

Id: 1kSLd9oQX7c

Length: 38min 45sec (2325 seconds)

Published: Thu Dec 01 2022