1 00:00:02,100 --> 00:00:05,240 So let's dive into SQLite. Again 2 00:00:05,300 --> 00:00:11,100 we got a module for that supported by expo which we install just as we installed the other native modules 3 00:00:11,130 --> 00:00:12,120 as well 4 00:00:12,120 --> 00:00:18,570 and now just some quick words about SQLite. SQLite is a database system you could say which 5 00:00:18,570 --> 00:00:22,840 is available on both iOS and Android, so you can quickly set up such a database there, 6 00:00:22,860 --> 00:00:30,240 it'll be super easy with this module and you can then use some SQL syntax, some basic SQL syntax 7 00:00:30,570 --> 00:00:32,860 to run queries against that database. 8 00:00:32,940 --> 00:00:39,030 Now, I'll not dive deeply into the SQL syntax here because this is obviously no SQL course but 9 00:00:39,030 --> 00:00:41,730 to learn all about how to use that, obviously 10 00:00:41,730 --> 00:00:50,880 check out the official docs and attached you also find a link with a more detailed snippet that dives 11 00:00:50,880 --> 00:00:56,550 into how to use the SQLite package in an expo app where you can see more examples for how to delete 12 00:00:56,550 --> 00:01:02,460 stuff and so on and you'll also find a SQL reference for SQLite where you can learn more about 13 00:01:02,460 --> 00:01:03,990 the SQL syntax. 14 00:01:03,990 --> 00:01:06,630 I'll show some basic syntax now in this module 15 00:01:06,720 --> 00:01:10,360 but for all the things you can do in your queries, for all the commands you can execute, 16 00:01:10,470 --> 00:01:17,170 definitely check out these resources. Now with that, let's add SQLite by running expo install 17 00:01:17,170 --> 00:01:24,010 expo-sqlite in our project which will install this into our project here and it will work out of 18 00:01:24,010 --> 00:01:27,250 the box just as before, no extra configuration is needed 19 00:01:28,510 --> 00:01:32,720 and once this installation finished, we can start working with SQLite. 20 00:01:32,830 --> 00:01:35,750 Now working with the database involves a couple of things, 21 00:01:35,770 --> 00:01:41,020 for example you need to open a connection to the database and if no database exists yet which is the 22 00:01:41,020 --> 00:01:46,260 case when you first access it in the lifetime of your app, it will also create that database 23 00:01:46,450 --> 00:01:51,070 and after you have that connection to that created database, you can of course run queries against 24 00:01:51,070 --> 00:01:52,090 it. 25 00:01:52,090 --> 00:01:57,310 So for that, I'll actually add a helpers folder with a db.js file in there which you don't need to 26 00:01:57,310 --> 00:02:03,220 do but I want to have my database logic in there to keep my other files lean and so that I have one file 27 00:02:03,220 --> 00:02:10,210 where we can see all the database logic. Now in there, we can import SQLite from the package and as you see, 28 00:02:10,290 --> 00:02:13,810 the import syntax is now a little bit different than for the other packages. 29 00:02:13,980 --> 00:02:19,320 Instead of importing star as SQLite from this package, we install it here with this named import 30 00:02:19,320 --> 00:02:20,300 syntax, 31 00:02:20,310 --> 00:02:22,180 other than that, it's still very similar 32 00:02:22,500 --> 00:02:25,110 and now we can start using that package here. 33 00:02:25,260 --> 00:02:31,080 Now first of all, I'll create a db constant which holds a reference to my database which I get by running 34 00:02:31,080 --> 00:02:34,290 SQLite open database 35 00:02:34,290 --> 00:02:39,020 and there we pass in a database name which could be places.db. 36 00:02:39,060 --> 00:02:43,980 Now what this will do is it will connect to this database or create the database if it can't find it, 37 00:02:44,010 --> 00:02:48,990 so when we first launched the app and you don't need to do anything else to get access to the database, 38 00:02:49,080 --> 00:02:50,370 so that's very trivial. 39 00:02:50,380 --> 00:02:55,110 Now this code line will be executed whenever we execute this file which effectively happens when we first 40 00:02:55,170 --> 00:02:57,870 import this file anywhere. 41 00:02:57,990 --> 00:03:04,320 Now I also want to add an init function here stored in a constant which I do to initialize this 42 00:03:04,320 --> 00:03:08,890 database and I'll export this function as a named export. 43 00:03:08,910 --> 00:03:15,300 Now in this function, I want to make sure that we create a basic table because SQL databases work 44 00:03:15,300 --> 00:03:21,450 with tables which hold your records and records are basically the rows of data you add to your table. 45 00:03:21,450 --> 00:03:27,420 Now initially when we create the database, it's empty but in order to store places, we need a table that 46 00:03:27,420 --> 00:03:29,200 can hold these places. 47 00:03:29,220 --> 00:03:35,190 So I want to have an initialization function here which will actually create that table if it doesn't 48 00:03:35,190 --> 00:03:35,900 exist yet. 49 00:03:36,690 --> 00:03:44,780 So there, I can use my db constant which points at that database and run transaction. Now transaction is 50 00:03:44,790 --> 00:03:50,220 a method offered by the SQLite package on the database and you'll find all of that in the official 51 00:03:50,220 --> 00:03:51,560 docs of course 52 00:03:51,780 --> 00:03:57,570 and this transaction method takes a function as an argument which gives you access to the transaction 53 00:03:57,660 --> 00:03:59,620 object it creates for you. 54 00:03:59,750 --> 00:04:06,870 The concept of transactions simply is a concept where this package in the end then guarantees that your 55 00:04:06,870 --> 00:04:13,200 query is always executed as a whole and that if some part of the query should fail, the entire query 56 00:04:13,200 --> 00:04:17,340 is rolled back so that you can't end up with corrupted data in your database, 57 00:04:17,340 --> 00:04:21,670 that's why you actually wrap every query into such a transaction. 58 00:04:21,750 --> 00:04:26,760 So this creates a transaction or initializes a transaction and then gives you access to this transaction 59 00:04:26,790 --> 00:04:30,830 object here in this function which will be executed for you. 60 00:04:30,840 --> 00:04:37,890 So in here, we can now use this transaction object to execute a SQL query with the help of the execute 61 00:04:37,890 --> 00:04:39,450 SQL method, 62 00:04:39,450 --> 00:04:48,260 again that's also documented here. So execute SQL now takes a string which describes your SQL query 63 00:04:48,530 --> 00:04:52,500 and that's the part where you can dive into that SQL doc 64 00:04:52,520 --> 00:04:57,450 I mentioned earlier where you can learn all about the SQL language. 65 00:04:57,560 --> 00:05:02,840 Now I want to create a table here which we do with the create table command and you don't need to write 66 00:05:02,870 --> 00:05:03,980 this in uppercase, 67 00:05:03,980 --> 00:05:11,600 I just do this to make it clear that these are standard commands built into the SQL language and 68 00:05:11,600 --> 00:05:12,590 I want to create the table 69 00:05:12,590 --> 00:05:19,130 if it doesn't exist which we do with the if not exists restriction here and now the name of the table 70 00:05:19,130 --> 00:05:20,750 will be places, lowercase, 71 00:05:20,750 --> 00:05:22,830 now that's my own dynamic thing 72 00:05:22,820 --> 00:05:23,300 here, right 73 00:05:23,360 --> 00:05:28,760 I want to name it places and now between parentheses, you configure what's in the table and thereafter, 74 00:05:28,760 --> 00:05:31,110 you can add a semicolon. 75 00:05:31,130 --> 00:05:35,420 So now we define the different fields we want to have in that table, the different columns 76 00:05:35,450 --> 00:05:42,380 so to say and I want to have an ID column and I will set this up to be of type integer which is one of the 77 00:05:42,380 --> 00:05:44,660 supported data types in SQL 78 00:05:44,660 --> 00:05:50,360 and it will be a primary key which means it will also be forced to be unique, 79 00:05:50,360 --> 00:05:55,400 you can't have the same ID twice in there and it will autogenerate that ID for you which is very 80 00:05:55,400 --> 00:05:57,880 convenient and it should not be null, 81 00:05:57,890 --> 00:06:03,680 so adding an empty value there will not be allowed. Now with a comma, 82 00:06:03,680 --> 00:06:08,030 we can add the next column we want to have and that will be my title. 83 00:06:08,030 --> 00:06:14,460 Now the title should be of type text and you can also add not null there if you want to, 84 00:06:14,670 --> 00:06:17,200 all my data should not be null actually. 85 00:06:17,670 --> 00:06:22,860 Now besides the title, I also want to have the imageUri stored in there and that's also 86 00:06:22,860 --> 00:06:24,110 text which is not null. 87 00:06:24,120 --> 00:06:28,950 It's text of course and not a file or anything like that because we're not storing the file in the database, 88 00:06:29,220 --> 00:06:34,020 we're storing the path to the file in the database which is a string and now something else we'll 89 00:06:34,020 --> 00:06:40,530 need later which I also already want to set up is my address which is a text, that should be a string 90 00:06:40,530 --> 00:06:47,760 which describes the address of the place and a latitude field or just lat which is a real, which is a 91 00:06:47,760 --> 00:06:54,990 floating point number in the end and the longitude here abbreviated with lng which is also a real 92 00:06:55,050 --> 00:06:56,330 and these are all not null. 93 00:06:56,340 --> 00:07:03,090 Now we're not fetching the location yet but we'll do so later and we'll express a location as a combination 94 00:07:03,090 --> 00:07:10,080 of latitude and longitude which is the standard way of expressing points on the world. 95 00:07:10,110 --> 00:07:14,150 So this creates such a table now if it doesn't exist already. 96 00:07:14,150 --> 00:07:20,130 Now the execute SQL method also takes a second argument which is an array of arguments which will 97 00:07:20,130 --> 00:07:22,500 come into play later which we don't need yet, 98 00:07:22,590 --> 00:07:26,850 these would be dynamic arguments you can inject into this query but we don't need that here 99 00:07:27,120 --> 00:07:33,810 and then we have two functions here as argument number three and four. The first function which we pass 100 00:07:33,810 --> 00:07:35,790 in is a success function, 101 00:07:36,000 --> 00:07:38,940 this executes if this command succeeded. 102 00:07:38,940 --> 00:07:43,140 The second argument is an error function which executes if this failed. 103 00:07:43,200 --> 00:07:48,930 Now the first argument to each function here and these functions are executed on your behalf by the 104 00:07:48,930 --> 00:07:50,510 SQL package of course, 105 00:07:50,520 --> 00:07:54,990 so these functions always as a first argument get basically the query you executed 106 00:07:55,140 --> 00:07:59,490 and by adding an underscore as a name here, I signal that I don't care about this but I'm interested 107 00:07:59,490 --> 00:08:01,740 in the second argument and here, this would be my error 108 00:08:01,740 --> 00:08:10,130 object and now to make this usable in an easy way, I will actually wrap this into a custom promise. 109 00:08:10,170 --> 00:08:17,100 So here, I'll create a promise by using new promise, promises built into Javascript and supported by the 110 00:08:17,100 --> 00:08:23,820 Javascript version React Native uses and promise takes a function which takes a resolve and a reject 111 00:08:23,910 --> 00:08:25,290 function as an argument 112 00:08:25,800 --> 00:08:32,190 and now we can move this database transaction code in there and in the success case, I call resolve 113 00:08:32,220 --> 00:08:37,200 which means that the outer promise will be resolved, in the error case, I call reject and forward my 114 00:08:37,200 --> 00:08:41,220 error here and then I can return the promise here 115 00:08:41,220 --> 00:08:47,400 overall in my init method and now I can call the init method from other places in the app and I get 116 00:08:47,400 --> 00:08:52,220 back a promise that will resolve if we were successful in initializing the database, 117 00:08:52,230 --> 00:08:58,440 so in creating the table or if we faced an error. By the way if no table is created because it already 118 00:08:58,440 --> 00:09:06,680 exists, we'll not get an error but we'll still end up in a success case. So with this, we're still not storing 119 00:09:06,680 --> 00:09:10,100 anything in the database but we're setting up the database. 120 00:09:10,100 --> 00:09:15,260 So now let's make sure we call init and of course the place to call it is the app.js file because 121 00:09:15,260 --> 00:09:18,060 we want to initialize the database as soon as possible 122 00:09:18,140 --> 00:09:25,700 when our app starts up. So I'll import something from my helpers folder there, from the database file 123 00:09:25,700 --> 00:09:30,620 in there and that's something unsurprisingly is the function and therefore here when this app.js 124 00:09:30,750 --> 00:09:37,550 file executes, when we also initialize the Redux store and so on, I want to call init here and now 125 00:09:37,550 --> 00:09:45,380 init is either successful or not, so for now I'll just add a then block where I say console log initialized 126 00:09:45,770 --> 00:09:46,520 database, 127 00:09:46,550 --> 00:09:54,200 so that's the success case or a catch block where we get a potential error where I console log initializing 128 00:09:54,230 --> 00:10:03,610 db failed and where I then also console log the error we got. And if we now save this, you should see 129 00:10:04,000 --> 00:10:09,880 here in the log initialized database which is the output from the iPhone and now that it started on 130 00:10:09,880 --> 00:10:11,920 Android, you see it there for Android too. 131 00:10:12,070 --> 00:10:18,610 So that works and now we got a database initialized, time to now also add more methods to make sure we 132 00:10:18,610 --> 00:10:21,820 can also insert data and later also of course, fetch data.