So let's dive into SQLite. Again
we got a module for that supported by expo which we install just as we installed the other native modules
as well
and now just some quick words about SQLite. SQLite is a database system you could say which
is available on both iOS and Android, so you can quickly set up such a database there,
it'll be super easy with this module and you can then use some SQL syntax, some basic SQL syntax
to run queries against that database.
Now, I'll not dive deeply into the SQL syntax here because this is obviously no SQL course but
to learn all about how to use that, obviously
check out the official docs and attached you also find a link with a more detailed snippet that dives
into how to use the SQLite package in an expo app where you can see more examples for how to delete
stuff and so on and you'll also find a SQL reference for SQLite where you can learn more about
the SQL syntax.
I'll show some basic syntax now in this module
but for all the things you can do in your queries, for all the commands you can execute,
definitely check out these resources. Now with that, let's add SQLite by running expo install
expo-sqlite in our project which will install this into our project here and it will work out of
the box just as before, no extra configuration is needed
and once this installation finished, we can start working with SQLite.
Now working with the database involves a couple of things,
for example you need to open a connection to the database and if no database exists yet which is the
case when you first access it in the lifetime of your app, it will also create that database
and after you have that connection to that created database, you can of course run queries against
it.
So for that, I'll actually add a helpers folder with a db.js file in there which you don't need to
do but I want to have my database logic in there to keep my other files lean and so that I have one file
where we can see all the database logic. Now in there, we can import SQLite from the package and as you see,
the import syntax is now a little bit different than for the other packages.
Instead of importing star as SQLite from this package, we install it here with this named import
syntax,
other than that, it's still very similar
and now we can start using that package here.
Now first of all, I'll create a db constant which holds a reference to my database which I get by running
SQLite open database
and there we pass in a database name which could be places.db.
Now what this will do is it will connect to this database or create the database if it can't find it,
so when we first launched the app and you don't need to do anything else to get access to the database,
so that's very trivial.
Now this code line will be executed whenever we execute this file which effectively happens when we first
import this file anywhere.
Now I also want to add an init function here stored in a constant which I do to initialize this
database and I'll export this function as a named export.
Now in this function, I want to make sure that we create a basic table because SQL databases work
with tables which hold your records and records are basically the rows of data you add to your table.
Now initially when we create the database, it's empty but in order to store places, we need a table that
can hold these places.
So I want to have an initialization function here which will actually create that table if it doesn't
exist yet.
So there, I can use my db constant which points at that database and run transaction. Now transaction is
a method offered by the SQLite package on the database and you'll find all of that in the official
docs of course
and this transaction method takes a function as an argument which gives you access to the transaction
object it creates for you.
The concept of transactions simply is a concept where this package in the end then guarantees that your
query is always executed as a whole and that if some part of the query should fail, the entire query
is rolled back so that you can't end up with corrupted data in your database,
that's why you actually wrap every query into such a transaction.
So this creates a transaction or initializes a transaction and then gives you access to this transaction
object here in this function which will be executed for you.
So in here, we can now use this transaction object to execute a SQL query with the help of the execute
SQL method,
again that's also documented here. So execute SQL now takes a string which describes your SQL query
and that's the part where you can dive into that SQL doc
I mentioned earlier where you can learn all about the SQL language.
Now I want to create a table here which we do with the create table command and you don't need to write
this in uppercase,
I just do this to make it clear that these are standard commands built into the SQL language and
I want to create the table
if it doesn't exist which we do with the if not exists restriction here and now the name of the table
will be places, lowercase,
now that's my own dynamic thing
here, right
I want to name it places and now between parentheses, you configure what's in the table and thereafter,
you can add a semicolon.
So now we define the different fields we want to have in that table, the different columns
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
supported data types in SQL
and it will be a primary key which means it will also be forced to be unique,
you can't have the same ID twice in there and it will autogenerate that ID for you which is very
convenient and it should not be null,
so adding an empty value there will not be allowed. Now with a comma,
we can add the next column we want to have and that will be my title.
Now the title should be of type text and you can also add not null there if you want to,
all my data should not be null actually.
Now besides the title, I also want to have the imageUri stored in there and that's also
text which is not null.
It's text of course and not a file or anything like that because we're not storing the file in the database,
we're storing the path to the file in the database which is a string and now something else we'll
need later which I also already want to set up is my address which is a text, that should be a string
which describes the address of the place and a latitude field or just lat which is a real, which is a
floating point number in the end and the longitude here abbreviated with lng which is also a real
and these are all not null.
Now we're not fetching the location yet but we'll do so later and we'll express a location as a combination
of latitude and longitude which is the standard way of expressing points on the world.
So this creates such a table now if it doesn't exist already.
Now the execute SQL method also takes a second argument which is an array of arguments which will
come into play later which we don't need yet,
these would be dynamic arguments you can inject into this query but we don't need that here
and then we have two functions here as argument number three and four. The first function which we pass
in is a success function,
this executes if this command succeeded.
The second argument is an error function which executes if this failed.
Now the first argument to each function here and these functions are executed on your behalf by the
SQL package of course,
so these functions always as a first argument get basically the query you executed
and by adding an underscore as a name here, I signal that I don't care about this but I'm interested
in the second argument and here, this would be my error
object and now to make this usable in an easy way, I will actually wrap this into a custom promise.
So here, I'll create a promise by using new promise, promises built into Javascript and supported by the
Javascript version React Native uses and promise takes a function which takes a resolve and a reject
function as an argument
and now we can move this database transaction code in there and in the success case, I call resolve
which means that the outer promise will be resolved, in the error case, I call reject and forward my
error here and then I can return the promise here
overall in my init method and now I can call the init method from other places in the app and I get
back a promise that will resolve if we were successful in initializing the database,
so in creating the table or if we faced an error. By the way if no table is created because it already
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
anything in the database but we're setting up the database.
So now let's make sure we call init and of course the place to call it is the app.js file because
we want to initialize the database as soon as possible
when our app starts up. So I'll import something from my helpers folder there, from the database file
in there and that's something unsurprisingly is the function and therefore here when this app.js
file executes, when we also initialize the Redux store and so on, I want to call init here and now
init is either successful or not, so for now I'll just add a then block where I say console log initialized
database,
so that's the success case or a catch block where we get a potential error where I console log initializing
db failed and where I then also console log the error we got. And if we now save this, you should see
here in the log initialized database which is the output from the iPhone and now that it started on
Android, you see it there for Android too.
So that works and now we got a database initialized, time to now also add more methods to make sure we
can also insert data and later also of course, fetch data.