PostgreSQL extension for VS Code

Key Takeaways

[Music] All right, welcome everyone. So I'm going to be demoing the new Postgres extension from Microsoft for VS Code. Uh now this extension just dropped like three days ago just in time for the conference and we haven't even formally announced it yet. So I am actually not even allowed to post on social media but all of you could. Uh so uh it's really super new and I'm pretty excited about it because the team worked really hard uh and did a lot of usability studies in order to figure out what developers wanted uh and then like improve improve the extension based off that. But they're still taking lots of feedback. So we encourage you to try this out if you have any feedback. There's a public issue tracker here. And so this is where you can file all your issues and the team will be very responsive. You see currently zero issues. So you could be the first to file an issue in this public issue tracker. So I'm going to be demoing many different ways that you can use it. So here I've got it installed already in this VS Code. And I've got a few different data uh you know server connections set up here. Some of these are local ones that I actually created using the extension. So it makes it super easy to make a dockerbased Postgress container locally. Uh and then one of them is an Azure one which you know I'm connecting to an Azure server. Uh so let's go ahead and take a look at uh this one here right so here I can explore the servers expand them I can take a look at the schemas inside and then expand you know those and look at the tables right so this is the kind of thing you'd expect just from the you know basic navigation uh I can you know click on this and say oh I want to connect with PGSQL and then it'll just you know go ahead and make the the query here uh you know and then I'm inside here and now I can you Look at look at some stuff here. Uh let's see. DT sales is that I'm actually not that good at PGSQL. [Music] Yeah, I know. Yeah. Right then. Yeah, we're going to show the co-pilot mode. Yeah. So, normally I don't use the PGSQL because we have this nice like co-pilot mode. All right. So, that was PGSQL. Now, what's really cool is that we can do a visualize schema. And this does this visualization here where we can go and like look at all the different things and like see how they're connected. We're going to see like, oh yeah, so this is like a, you know, a foreign key pointing to this over here and this points over there. And you can browse all around and see how your data is laid out and how it all connects to each other here. And uh tons of tables in this one. Uh the next thing we want to do is obvious do a SQL query, right? So we say, okay, we're going to do a new query, right? So I'm going to you know select okay, surname, first name, last name, city, and then here's my schema. Uh here we do get some autocomplete, right? So we can select this view here. We can run that and we'll get the result down here. And what I really like about the results view is that there's these export options, right? So we could export as CSV. We could export as JSON and we could export to Excel, right? And so, you know, typically I would use the like save as CSV and be like, okay, sales uh and then I can look through all that data there. So, that's pretty cool. Uh we always also can always see our query history here. So, you can see like some of the stuff I do fails. That's okay. I'm okay with failure in life. Uh but we can go through and see everything that we've done with the extension. So we don't have to worry about forgetting any query that happened in case there was like a really complex query and we'll do some more complex queries. So talking about that let's do a little GitHub co-pilot integration. So what's really nice people ask me like oh what's the difference between this extension and some of the other extensions. Uh you know this is all the all the functionality I've shown so far is what you can get out of it like a lot of the existing database extensions but this one also has very tight uh integration with GitHub copilot which we can use in a few different ways. So, first we can just right click and say chat with this database, right? And so it says okay, we're connected, right? So, I'm going to be like, um, actually, I'm just going to grab my grab my question because it's like really complicated. And here we go. There we go. Okay. All right. So, it's a film film database and we're trying to get it to calculate the total revenue. We also want to do some group by and uh get the formatting out so we can both get it to come up with the queries and then also answer things based on the data. So if you wanted to actually have it do a little data analytics reflection it can do that too because of course it is it is an LLM. Um so it's you know it's doing a combination of coming up with those queries and then and then looking at them. So here uh let me go ahead and look at uh open the query. Okay, so this is where query history is really helpful because every query that the co-pilot uh LLM comes up with is going to end up in your query history and you can actually see also which model wrote it. So you might like play around with different models, see whether one of them is better at uh at writing SQL queries than others. Uh and here you can actually look at the query. Like this is a fairly complicated query. I'm glad I didn't have to like type that from scratch, you know, especially here on stage in front of all you. And then we can look at the results. So these are it all nicely formatted them and uh you know calculated all these sums and then it also shared some observations there. So that's that's pretty helpful. Uh but now of course GitHub Copilot has agent mode. So let's check out how we could use this in agent mode. Uh so I'm gonna go and pick a different database for agent mode. So I've also got an local a local one that has postgis set up on it so that we could do um you know do more uh stuff with geographic data. Uh so with this one I'm just gonna make sure that that's there. All right. And let me close some things. Okay. So this time I'm going to switch to agent mode. And I'm not going to add PGSQL. I'm just in agent mode. So agent mode knows that it has PGSQL tools that it can call if it thinks it's appropriate. Uh so the first thing I want is for is to make a um a new database. So let's say uh make an observations database in let me make that bigger in the local coast GIS ARM server. Okay. So it is working on it. Let's see what it comes up with. All right. So it says connecting to the server. So here it's using a tool from the extension and you can you expand it. You can see what you know arguments it does. You can see the result. So that one worked. Uh now it's creating the database. So we can see everything that it's doing there. And uh it said it executed successfully. So that's good. Now it's connecting and uh and it's enabling posts. Uh that's funny. I didn't ask it to do that, but I probably realized I probably wanted that. I think it's already enabled. It's probably going to get a message. Okay. Yeah, maybe it needed enabling. Okay. Oh, because we had to do it on each database. I forgot you enable on the database level. Yeah. See, it remembered for me. All right. And there. So, it did it. It made the new database. It connected and then enabled the extension. Right. So, we can do all those steps. Now, let's have it actually populate that database with uh with some data. So, I have some data locally that I pulled from I naturalist, which is a citizen science site. Um, so I'm going to say like, okay, I take the data in a file and use it to make a table in the observations database called uh Pennsylvania. These are all Pennsylvania uh spottings of insects. That's what I downloaded. Very specific. I know. Um otherwise, the problem is I was like downloading like the files were like way too big. Okay. So, yeah, it's going to look at the CSV and see the structure of the file. And with anytime it's like asking me to continue, I can always tell it to always accept, right? Like every time in copi when you're agent mode, if there's like a thing it wants to do, you can just say, "Oh, you're always allowed to do that." Right? But the first time it ever does any particular type of command, it will say, "Hey, do you want to, you know, do you want to do this?" Um, and, uh, you can decide. Uh, so, okay, it found it, you know, it described, it figured out what was in that CSV. I can show you the CSV here. Um, so it's, you know, looked at it, figured out the columns. It says it's creating a table. It also decided to add a geometry column for the latitude longitude coordinates. geometry is a special kind of column that you can use when you're doing postGIS which can make your geographic queries be more efficient and uh and so it's it's taking care and it did a bulk load of the CSV and I don't even know how did it even do that. It just knows how to do that. Great. Okay, that's a tool that is in that's in the uh PGSQL and now it's going to verify the data. So we'll we'll run that there. So, one thing that I like about GitHub copilot agent mode is that it does do a lot of like reflection and verification. Like even when you're doing like Python and you've got like local pi test, it'll go and like run the pi test and be like, hey, do the test still work? If not, like it tries to really keep iterating. And so here it says, okay, it looks like it was successfully loaded. We've got 6,000 observations. We've got 92 unique species. And it says it's now, you know, ready for queries, right? So now I can go back to my extension and I'll go ahead and let me see just refresh. Let's see. Can I refresh here? Um, one thing that sometimes I have issues with is the refresh. So in that case, this is something they're working on now. I just do a disconnect and a reconnect. Uh, and there we go. We can see it. I'm going to go ahead and visualize the schema. And we can see that it made um you know it made the it made the schema with the latitude and longitude and then it made a special column that it came up with called location which is geometry. So now going back to agent mode I'm going to say like okay uh what are the top insects observed in Pittsburgh or like we'll say maybe like within five miles of Pittsburgh, Pennsylvania. Uh so here I'm relying on it knowing the coordinates for Pittsburgh which generally I don't think you should rely on an LM to know coordinates. I think it would be better to use a geocoding uh server. So what I could do is like bring you know GitHub co-pilot sports MCP server. So I could bring in a geocoding MCP server and then it could use that to get like really reliable coordinates. However, Pittsburgh is a fairly well-known city. So this is probably correct. Like while it's going, we can just check PA, latitude, longitude. Uh 40, 79, 40, 79. Did it get it exact? I think it probably did. Here we've got decimal versus degrees. But um so in this case, it was able to figure it out, but we could combine this with geocoding servers in order to, you know, be able to handle any arbitrary address, right? Um oh, and it got a SQL syntax error. So it's it apologized. Always a nice thing when yours apologize. Uh, so then it's going to modify the query and redo it. I wonder what its issue was. Uh, well, hopefully it figures that out. Okay, there we go. Thompson. Okay. Yeah. And this is what I uh saw before. So, when you're wandering around here, you're going to see that western honeybee spotted lantern fly. Um, let's see. Do we got pictures here? Whoa, that looks like a tree. All right, let's see. I did I was testing this last night. Oh, there we go. That actually does look like a bo. Yeah. Um because the image there's images in the CSV. Uh but like it depends like which of the ones it like schools. Um that's that is a honeybee. That honey bee has had a hard time in life. Um but that is a honeybee. Yeah. So Oh, I love zebra swallow tail. Sorry. Just little. I love Oh, that's so great. It's a swallow tail next to a swallow tail. It's like Oh my god. Okay. Sorry. Okay. Uh so so yeah so there we got back uh we got back those results it did the query and once again we can look at all of the queries that it did in our query history and be like okay so what was that actual query so uh you know here it used the latitude longitude it got back the rad to be honest I've actually never used postgis so this is my first time seeing a postis query but I'm guessing that did a radius uh query there so this is cool because like I I actually haven't used this extension before I use PG vector more often. So, even though I'm new to this extension, I'm able to see like how to actually uh work with it. So, we got uh so we got back that and it did all that. Okay. So, we got all of that back there. All right. So, then um so then we've seen a lot of that integration. What I haven't showed you is how, you know, how do you actually like make a new connection, make a new server. So, one thing I like about this one is that they have built in uh a built-in function that can create a local Docker server. Because when I was talking with the team when they were developing it, I was like, listen, it's so annoyingly hard to like make a new Postgress server and then like use PG admin locally. And they're like, okay, well, we'll just make it easy. So, they added this thing where we could do a local Docker server. And so, look at this. It checks if Docker's installed and then check if it's actually running because those are the two big issues that people often have with Docker is like, do I have it? And is it actually successfully running? And then we can say like okay our connection name will be be like okay local uh pg docker 2 oh doc docker 2 okay and that'll use the same container name. I just use the default password which is postgress and it can save the password in the VS code secret. So that means it's not going to be saved in plain text. So VS code does have a special mechanism for saving secrets which is not plain text. Um since some of you you can also connect to a predict production database using this you know same similar uh UI uh we can say which database we want and then there's also advanced uh so you can change the port you can also change the image so I actually I had to use this when I was doing the postJS because I'm on a Mac M1 which is an ARM device and the uh the default uh wasn't working just because of the complexities with installing um onto the ARM So I use a I found a Docker image that was optimized for PostJS on the Mac and change that image name there. Uh so sometimes that's useful. Most times you could just go with the default Postgress and BFK. All right. So then we can go and say create and so that's going to go and run a bunch of Docker commands to to make that and we can see that. And presumably if I went to my docker and my actual docker desktop and looked we actually see the containers that I've made. Right? So these are all containers that were made from the extension. Uh and then you know and then we can just mess with that the way we were doing before. Uh we can also just do a connection to any arbitrary server that you have. Right? So that would be the add new connection button. So you could connect with a parameters right? You could connect with a connection string if you just have that whole connection string. You just paste it in. If you are connected to Azure, then you can browse Azure and then um No. So, here I'll be like, okay, there's my subscription. Uh here I don't even remember which resource group it's in. Let's see. Did I put it here? Uh I I have a lot of ones, but that's not the one I wanted. But I already actually have it connected. So, I already have it connected here. Uh but this is what you would do is you'd find your find your database, and then it would set up the connection. and you could test it and save it. So there I have the Azure an Azure one connected. Now when you have an Azure Postgress server connected you have even more functionality because you can actually query the Azure Postgress information and get information about your Azure server. Uh so that's another advantage is that if you are you can use it totally use this extension for Postgress on other servers and with local Postgress but if you are using Azure obviously this extension is for Microsoft we want to make it easy to use that. Uh so once again we can go ahead and say chat with this database and that says okay how can I assist you? So be like all right um uh what is are the compute and storage requirement uh cap I don't know capabilities of my server. Okay. So, it's going to connect and actually uh I this is something you this is a bug that they actually reported to the co-pilot team where sometimes you see the system message like in the actual prompt. It's f it's going to still work. It's just funny that you can see system message. So, this this just has to do with how co copilot agent mode is implemented. Um, and now you can like kind of see behind the behind the matrix there. Uh, so you can see like what it's actually doing, right? We're like querying to figure out some stuff and then we get back the results. So it says like, okay, this is how big your database is. Uh, you're running a smaller one. Um, you know, do you want me to do anything else? And be like, okay, well, um, what extensions are in my allow list? So these are the extensions I'm allowed to enable. uh Postgress extension so we can connect there and and we're seeing that system message. You know what I want to do actually is do I'm going to switch models. So I've been using cloud35 sonnet the whole time. Um but the I've been recommended to use the GBD4 uh 41 uh I think 41 or 40. Uh they all kind of handle tool calling slightly differently. So I bet we wouldn't see that as much with that. Wow. Look at all Oh yeah. So this is the output. We're actually seeing the output stream back that is being streamed back to the agent and uh so you can see that that's that's coming back with all the these are all like the possible extensions that could be enabled on an Azure Postgress server. Oh, I I hear I need to ban a wipe PG surgery PG disc an uh so disk ann has anyone heard of disk an okay so disk an okay have uh so with PG vector a lot of times I use PG vector PG vector is useful if you want to do vector search vector similarity it can do efficient vector similarity using the HNSW algorithm or the IVF flat algorithm those are the two big algorithms that people use generally HNSW is better if you're going to be doing like a lot of updates of the data otherwise you could use IBF flat. Um now diskn is another algorithm that came out of Microsoft research because they're trying to do like super efficient uh vector search and it it I don't exactly understand how it works because it's kind of on the hardware level like uh they figured out a way of making even more efficient vector search and they've started bringing disk in to all of the different Azure properties. So you can do it with Azure Postgress, you can do it with Cosmos, etc. So it's just it's another algorithm for efficient vector search and so you would have to enable it and then you'd be able to use it. Okay. So if I got back the response and says all right this is what you currently have. You've got PG cron PG off. There's some Azure specific ones and then it says you know what you could do. Uh okay so that's pretty cool. So, let's just say like, yeah, we could change to a different model. Uh, but like, okay, can the PG Vector extension be installed? Let's see. Uh, buying. Uh, and okay, it says it's on there. Uh, I'll say yes. I was having issues getting to actually do it. I think that my servers are actually set up with certain roles so that it was not able to do it. Oh, it worked this time. Wow. That's actually never worked for me. Uh, that's great. So, I don't know why it worked this time. That's great. I thought my Postgress server was like set up in a super strict permissive way. So, it was actually able to run that on the server. And that's the idea is that not only can you get information about your servers, but you can actually run SQL queries uh remotely as well, right? Um, so yeah, that is a lot of what I wanted to show. Went through that kind of quickly. I'm happy to show uh anything after uh that I uh that I skipped over or went quickly but you know basically it's a very fullfeatured database extension and it gets you that additional co-pilot integration and the tighter Azure integration and that would be you know the reason to move to it. Uh if you do have the previous Postgress extension from Microsoft as you can see was rated 1.5 stars. So that was apparently not a very good extension and was a bit embarrassing for all of us. Uh this so this is now considered deprecated. So we want everybody who's using that old one to go ahead and move over to the new one which currently has a whopping five stars out of eight reviews. So we'll have to like see how those even out over time. Um but yeah, we're hoping that this is a much better experience and uh give us a good basis. So once again, please do download it, try it, and then if you have any issues at all or ideas about how to make it better, just go ahead and post in this issue tracker here and share your feedback with us. Thanks. All right, thank you everyone. [Applause]