Read & Write from Database

[00:00:00]
>> Kyle Simpson: The first thing that we wanna do is decide whether or not the other that we have been provided is already in the database or not. If it's not, we need to insert it. If it is we need to get its ID. So I'm gonna make a function that's called insertOrLookup, we'll call it insertOrLookupOther.

[00:00:29]
>> Kyle Simpson: Okay, and we wanna pass the other value in. And this is gonna be an asynchronous operation, so we wanna await on it. And the thing that we're gonna be getting back is the otherID. So the ID of that record in the other table, whether we had to make a new record, or whether we just looked it up, that's what insertOrLookupOther is gonna do.

[00:00:54]
>> Kyle Simpson: So let's define,
>> Kyle Simpson: An async function called insertOrLookupOther(other).
>> Kyle Simpson: What we're going to do is use the SQL3. And because we're trying to do a lookup first, we wanna use the get command which would return us a single record. If we need to get multiple records, we use all.

[00:01:22]
If we wanna do an insert updated delete, we use run. So basically get is the thing we wanna use. We're gonna call,
>> Kyle Simpson: SQL3.get, and we're gonna be awaiting this because again it's a promise. We're gonna call .get, and we wanna give it a string of SQL. And I'm gonna use the back tick string so that I can spread my SQL across multiple lines.

[00:01:51]
You can use whatever kind of string works for you, but I'm gonna spread them across multiple lines.
>> Kyle Simpson: So, the first thing that we wanna do is Lookup to see whether Other is already in our other table. If it is, we just need the ID. So we're gonna do SELECT id.

[00:02:08]
If we look at our schema, that's the name of our primary key. I'm gonna SELECT id FROM Other, WHERE, and then the value here is called data. So WHERE data =? And the way you give a placeholder for values that'll be interpolated in SQL lights API is use a question mark.

[00:02:36]
You do not need to do any of the escaping like escaping or putting quotes around stuff. It handles all that for you. So you just put a placeholder any place that you're gonna interpolate a value in. And then we pass as the second argument the value, in this case, Other.

[00:02:53]
So we're gonna get back a database result. It's either going to be a single object since we called get. It's either gonna be a single object with whatever fields on it that we've asked for, or it's gonna be null or undefined because it failed. So what we wanna do is check to see whether or not we got back an ID.

[00:03:13]
If result && result.id, then we're done. We have an ID and we can simply return it.
>> Kyle Simpson: Otherwise, we need to do a little bit more work, which is we need to insert our Other value into that table. So with this one we're gonna say result = await SQL3.run, because we're gonna do an insert statement.

[00:03:54]
>> Kyle Simpson: INSERT INTO, the table we're inserting into is called Other. The columns that we are using in the other table are just the data column. And then the VALUES clause is going to have just our single value with our placeholder.
>> Kyle Simpson: And that will insert Other into the other table.

[00:04:25]
Now we need to know whether this succeeded, and if so, what was the primary key that it inserted, because we've got an auto increment in primary key what primary key was used. And the way we do that is, if result && result.lastID, then we're gonna return result.lastID.
>> Kyle Simpson: Okay, so we need to check to see whether or not that was successful.

[00:04:54]
So we're gonna say, if otherID.
>> Kyle Simpson: And then with this otherID, we're gonna do some other work. And that's when we're gonna uncomment this,
>> Kyle Simpson: Error because we want to return if we've been successful.
>> Kyle Simpson: So now that we have an otherID that we've just been inserted or that we looked up, now the next task is we need to insert into the something table.

[00:05:30]
>> Kyle Simpson: So we need to make another function called insertSomething. We're gonna be,
>> Kyle Simpson: Getting back a result after awaiting that call. We need to pass in the otherID, and we need to pass in the something value that we've already generated.
>> Kyle Simpson: And if that result is successful, then we will return.

[00:06:02]
Otherwise, something went wrong.