1 00:00:00,900 --> 00:00:04,400 So sometimes in go, you'll find yourself needing to interface with a 2 00:00:04,400 --> 00:00:08,700 database. May be a small database like SQL light for some sort of local 3 00:00:08,700 --> 00:00:12,500 storage or maybe a large database like say postgres or 4 00:00:12,500 --> 00:00:13,200 MySQL. 5 00:00:14,000 --> 00:00:18,600 Go itself provides a standard interface for dealing with SQL 6 00:00:18,600 --> 00:00:22,900 databases databases such as SQL, which provides the number of functions for opening 7 00:00:22,900 --> 00:00:26,300 and closing databases, doing queries, preparing statements, 8 00:00:26,300 --> 00:00:30,300 executing them Etc, and it's extremely easy to use interface. 9 00:00:30,300 --> 00:00:34,700 All you have to do is include it, import it and also import the 10 00:00:34,700 --> 00:00:38,900 appropriate driver for your database. So to give an example about 11 00:00:38,900 --> 00:00:42,500 how this is pretty easy to do. I've created a little SQL Lite, three 12 00:00:42,500 --> 00:00:44,000 database, and I 13 00:00:44,100 --> 00:00:48,800 So, brittle program, there's a query on it to show you the basics of how this works. So let's just take a 14 00:00:48,800 --> 00:00:52,600 look at it. First of all, you're going to include database 15 00:00:52,600 --> 00:00:56,900 / sequel. So database SQL, that is the standard go interface for doing 16 00:00:56,900 --> 00:01:00,700 it and an appropriate driver. In this case, I'm using 17 00:01:00,700 --> 00:01:04,700 the go SQL Lite, driver by Matt n, which is very 18 00:01:04,700 --> 00:01:08,600 widely used for sqlite3 and I've prepared a little database 19 00:01:08,600 --> 00:01:12,500 file. It's called scientists dot DB, and if I load it up 20 00:01:12,500 --> 00:01:13,200 into 21 00:01:13,900 --> 00:01:17,000 Sqlite then I can see if we just go 22 00:01:17,000 --> 00:01:21,900 there's a table called names and I can say, I just pull everything out 23 00:01:21,900 --> 00:01:25,600 of there. I've got Charles Babbage, Ada 24 00:01:25,600 --> 00:01:29,900 Lovelace, Michael Faraday, and Brunel in here. So just a few little simple names in the 25 00:01:29,900 --> 00:01:33,600 database and I'm going to open that database 26 00:01:33,600 --> 00:01:37,700 in go and do a query on it just to get everybody out of it. Did give you the 27 00:01:37,700 --> 00:01:41,400 basics of how you do things. So although we're using this particular 28 00:01:41,400 --> 00:01:43,800 driver here, you'll notice this underscore in the 29 00:01:44,000 --> 00:01:48,900 And which indicates that we're not actually directly in this module here using any of 30 00:01:48,900 --> 00:01:52,700 the imports from it. So we don't want to go to complain about it. It's actually database 31 00:01:52,700 --> 00:01:56,900 SQL, which will deal with that. And if we go down, you'll see the way this operates 32 00:01:56,900 --> 00:02:00,700 is that I'm doing a sequel open here. 33 00:02:01,000 --> 00:02:05,800 This can be used to open any type of database. So if you for SQL Lite, you're opening a file path 34 00:02:05,800 --> 00:02:09,800 because it's stored in a file. If you might be connected to say postgres 35 00:02:09,800 --> 00:02:13,800 which is running across a TCP socket, then you would have the appropriate connection. 36 00:02:13,900 --> 00:02:17,900 In here, perhaps with user names passwords the address of where that is this 37 00:02:17,900 --> 00:02:21,900 can be used for any sort of database, you're connecting to and I'm using defer so 38 00:02:21,900 --> 00:02:25,800 that when the program terminates, I close out the database itself. 39 00:02:25,800 --> 00:02:29,600 Then I'm going to do a query. I'm going to do a simple query here. Just using 40 00:02:29,600 --> 00:02:33,700 DB dot query is going to give me the a list of rows back 41 00:02:33,700 --> 00:02:37,800 which I can then iterate over. And I'm also going to close that set of rows 42 00:02:37,800 --> 00:02:41,900 when I'm done and notice the course that because of the ordering of defers, the 43 00:02:41,900 --> 00:02:43,800 Rose will close before the database. So there's 44 00:02:43,900 --> 00:02:47,900 Worried about those being in the wrong order and then I'm just going to do a for 45 00:02:47,900 --> 00:02:51,300 Loop and the for Loop is going to iterate over all the returned Rose. 46 00:02:51,600 --> 00:02:55,500 And for each row, I'm going to use this scan function. It will 47 00:02:55,500 --> 00:02:59,800 match each field. That's been returned against one of 48 00:02:59,800 --> 00:03:03,900 these variables here and if you look in the original 49 00:03:03,900 --> 00:03:07,700 database over here, there's an ID which is just an auto-generated integer and 50 00:03:07,700 --> 00:03:11,800 two strings. So doing this will actually match each row one by 51 00:03:11,800 --> 00:03:13,800 one into those variables. 52 00:03:13,900 --> 00:03:17,600 Owls. And I'm going to feed one, just print out the first name, and last name of the 53 00:03:17,600 --> 00:03:21,900 program of the person, and when the program terminates, The Rose will get close. The database will 54 00:03:21,900 --> 00:03:24,800 get it closed. So we can just go ahead and 55 00:03:25,900 --> 00:03:27,200 build this guy. 56 00:03:29,400 --> 00:03:33,700 Like that and then run it. And there it is done. It is coach to the databases open 57 00:03:33,700 --> 00:03:37,800 did then the query and print it out that information right there. So 58 00:03:37,800 --> 00:03:41,400 that's the very basics of accessing a database be sqlite, or something more 59 00:03:41,400 --> 00:03:45,300 complex, like, postgres from within ago program. So the 60 00:03:45,300 --> 00:03:49,800 standard sort of database, access querying. Updating preparing 61 00:03:49,800 --> 00:03:53,500 statements is pretty obvious and it's the same for every 62 00:03:53,900 --> 00:03:57,800 drive you are using, but it also provides the ability to do. 63 00:03:57,900 --> 00:04:01,800 Actions. I've just modified this slightly so that we have a concept of a 64 00:04:01,800 --> 00:04:05,900 transaction used here. And I also modified the database a little bit. So if we just 65 00:04:05,900 --> 00:04:08,800 take a look and I have a look at the 66 00:04:09,600 --> 00:04:13,800 names, I didn't have a field at the end and Boolean, which is thought of the 0 and 1 in 67 00:04:13,800 --> 00:04:17,600 SQL light, as to whether any of these scientists are still alive. And in 68 00:04:17,600 --> 00:04:21,900 accurately, the database says, the Royal eye towards we're gonna have to fix that in the go code by 69 00:04:22,200 --> 00:04:26,400 setting those to false. So I've modified to go slightly 70 00:04:26,900 --> 00:04:27,700 to bring in that. 71 00:04:27,800 --> 00:04:31,900 At Boolean see whether somebody's will live. Although I'm not actually going to use it other than print it 72 00:04:31,900 --> 00:04:35,900 out and I've moved that code that actually query the database 73 00:04:36,100 --> 00:04:40,800 into a function called show, which will just dump out the names table because I'm going to use it a couple of times. 74 00:04:41,600 --> 00:04:45,900 And immediately on starting, I show the database, so show the current state of it and then 75 00:04:45,900 --> 00:04:49,800 I'm going to create a transaction as the way you do that is you call DB dot 76 00:04:49,800 --> 00:04:53,800 begin, which starts a transaction on the database and I'm using prepare 77 00:04:53,800 --> 00:04:57,700 here to prepare this statement to update everybody to have. 78 00:04:58,000 --> 00:05:02,900 Live Boolean set to 0 or false because they no longer alive. And once I've done that, I'm 79 00:05:02,900 --> 00:05:06,500 actually going to execute that. So that it's done. And before 80 00:05:06,500 --> 00:05:10,800 committing the transaction, I'll dump out the database again to verify that nothing has changed. 81 00:05:11,000 --> 00:05:15,900 Then I'll commit it and then I'll dump out the database again. So you should be able to see that there's twice, 82 00:05:15,900 --> 00:05:19,900 it'll say they were alive and the third time, the transaction will actually have been committed so 83 00:05:19,900 --> 00:05:21,800 let's just check that that builds 84 00:05:23,300 --> 00:05:27,500 And then let's run it. So the first time show was 85 00:05:27,500 --> 00:05:31,900 called, let's find where that is, is here, it shows that everybody 86 00:05:31,900 --> 00:05:35,900 in the database had that field Martyrs true. So they're all were thought to be 87 00:05:35,900 --> 00:05:39,400 alive and then started the transaction prepared statement 88 00:05:39,400 --> 00:05:43,500 executed it, but had not committed it yet and show the 89 00:05:43,500 --> 00:05:47,400 database. Again, the database has not been updated and then as soon as the transaction was 90 00:05:47,400 --> 00:05:51,800 committed, everything's been updated to be false. So within the standard 91 00:05:51,800 --> 00:05:52,900 database SQL, 92 00:05:52,900 --> 00:05:56,800 Interface, you've got standard things. You can do with queries, and updating data, 93 00:05:56,800 --> 00:06:00,700 bases, inserts updates. All the things you can do within seek or preparing statements 94 00:06:00,700 --> 00:06:04,700 with fields, that you can actually insert safely with the correct sort of 95 00:06:04,700 --> 00:06:08,900 escaping and as well as transaction support. So, really, within database, SQL 96 00:06:08,900 --> 00:06:12,600 with the appropriate driver, you should be able to access any database you need to 97 00:06:12,700 --> 00:06:14,000 from within go with ease.