Next-Level SQL in VS Code: GitHub Copilot, Containers & more | Data Exposed

Key Takeaways

GitHub Copilot and VS Code MSSQL extension. Learn all about AS mode, agent mode, slash commands, and more this week on data expose. [Music] Hi, I'm Anna Hoffman and welcome to this episode of Data Exposed. Today we have a super exciting developer oriented episode where we're going to be talking about VS Code, specifically the MSSQL extension and some exciting stuff happening in that space. And to do that, I've brought on Carlos who is the product leader for all of our efforts in VS Code. And Carlos, welcome back to the show. >> Yeah, thanks for having me again. >> It's great to have you. And we'll put a link in the description below for folks if you want to see the previous episode we did with Carlos. Look, one of the last things we hinted at was the road map around some AI stuff. So, I'd love to just get right into it. What's new in the MSSQL extension? What's this GitHub copilot thing and how does it work for us? And anything else cool you got up your sleeves. >> Yeah, absolutely. So, yeah, super excited. As you said, last time you asked me about it and this time I'm super honored to be here and be able to show you how this works. So yeah, this episode is going to be hopefully this episode is going to be mostly focusing on GitHub copilot. So yeah, let me show you how it works because that's the best way to understand how this experience works in the extension. Obviously, I already have the extension installed and I have a couple of databases here. My main database I will be or my main connection I will be using today is called GitHub Copilot. So that's the profile name in in this local container which is a container I have running on my machine. I has m I have multiple databases as you can see. Uh so if we uh go straight to try to work with GitHub copilot there are many ways you can do it. Uh if you're very familiar with g with githop copil vs code you might open the the the window here and start engaging with copilot. Uh we introduce a different way as well in case you really want it. uh you can have like the context of a database set from the very beginning just by doing a right click. So let's do it. So right here this is object explorer and I'm going to do a right click and you can see that there are a couple of options here. One says GitHub copilot mode. So that's what I'm going to go with or I can go with GitHub copilot in agent mode. When I select this mode, let me just move things around. You can see that I get um this greeting message saying hey I'm ready here to help you. The context of my connection is given by the database I selected. Uh so yeah it's basically ready to go as any chat participant. Uh you need to engage by using the at right so in this case it's at msql but you don't have to type it over and over again. It's just like by default there because I started like this session in that context. So yeah, that's kind of a getting started step. Uh from here I can just start interacting with my database. In this case, you can see that I'm connected to library here. And this is something very important and I need to mention this the context of my of GitHub copilot to understand the database is given by this open editor. So if I close the editor, that's not going to work. Of course, agent mode is a little bit different and I will show you that we don't have that requirement for agent mode. But for at least for ask mode that's required. Uh so yeah I'm ready to go and I can ask simple question like list all my tables. Uh very simple I know but yeah sometimes you don't feel like using OE right because I can just do it like just clicking here in OE and look at yeah so but sometimes you don't feel like I just want an answer really quick >> and in in that regards and uh you asked me something at the beginning like what uh is this about like what we're doing with GitHub propot it's not just about answering the questions here right so it's not just about that so it can help you to for example if you want to write queries if you want to create or generate mockup data for your application and your your seating your tables and your databases that's something you can do you can understand about existing projects and that's something a very common scenario lots of developers are always um facing with is like I need to get up to speed with this project what do I do and here I have a I would say a good example of that um somebody told me Hey Carlos, in this library database there is a store procedure >> and right now I'm just clicking here in OE and you see that I have a store procedure. I'm not very good at TSQL. I'm more an RM guy. I'm more an application development guy. So how do I understand what's going on with this store procedure? I have multiple options here. The first one I can just simply scrape out the object, right? So here's my object and I can just start reading the TSQL understanding. Excuse me. What's going on? Or I can do something very simple here. You will see that after I select the entire object and I do right click, I got this option that says MSSQL copilot. And there are three options I can select from here. One is to explain the query. The second is to rewrite the query. And the third, as you can see, is to analyze the query performance. So this is exactly what I was talking about. like I'm not very familiar. How can I get more with Gitco Copilot? And this is it. So you can see that after I ask to copilot to assist me reading this store procedure and understand what's going on, I get an an explanation of a business logic. What is the purpose of this store procedure? What is the business logic? Because again, I'm not very into TSQL. So I need to understand how this translates when I probably use something different. Uh so it's super handy as you can see it it it require it provides a lot of information even example about the outputs and everything but this is not what I want right so I want to understand for sure but I want to solve a problem. So if I select this other option that says analyze query performance, it will be different. The result that I will get from GitHub copilot is different. And behind the scenes, what we're doing is to um you can think about this as a shortcut. Uh we're setting the context for copilot about what we want and and we're saying, hey copilot, please provide us with an analysis, provide us with some recommendations about this store procedure. So it's kind of a template that will provide you uh all the information about that store procedure. Uh so let me yeah sorry the screen >> no worries >> I just reach again. There we go. Um so that's I was saying that would allow me to understand what is the problem and come up with some solutions. So you see potential bottlenecks over there, optimization suggestions and of course this is very important here Anna like I cannot blindly make these changes. I need to review the changes here. Copilot can make mistakes and this is super important for our developers out there that yes this is good but we need to test it right. So we need to make sure we do a right call. But yeah, you can see that very easily I can get information about my database, my schema, uh understand like potential problems. Um so you might be wondering as mode, right? So we have agent mode. Yes, we we support both. That's a question we always get like, hey, do you guys support agent mode? Yes, we do. Before I jump into agent mode, I want to show you something very cool. >> Cool. Again, yeah, I'd love to take a look. And while you get your screen back up, uh, I think the scenario there, which I love, is like, hey, I have this database I've inherited or I'm using for my application, maybe it's slow and this just makes it that much easier for me to kind of investigate what's going on or learn about the database. So, I love it. I love to see it. So, that's ask mode. So, then when it comes to agent mode, how does that work? >> Yeah. So, before we jump into, let me show you something else we just introduced during the last release and it's super cool. It's very easy. You might be wondering, okay, you're working with a single database. How do I change databases, right? So, I can just simply change the context here on this editor and that would be it. But we don't want we don't feel like users to do it. So, we introduce slash commands. So, slash commands, you can think about it as shortcuts. So, for example, I have a shortcut here that says change database. So if I uh select that option, I can uh change the context of my of my session here to a different database. And I'm now connected to Pokey Wiki. So I can start asking questions. Again, I'm not very familiar with the database. Probably I have a list of functions here that will allow me to understand what is in this database. So I can start exploring the schema. So you see that I'm not typing anymore. So I'm not typing as I when I when we started this session I was typing list my tables list like I can do that using slash command. So it's super simple super handy. So I I really like it. >> Um so yeah so speaking about agent mode uh agent mode is a little bit different and I think I have an scenario here. So let me close everything so we can make some space. All right. So for agent mode, uh I have this kind of a scenario that is very common when you're working on an application. This case this application is about Fabcon. So it's a very simple FabCon application that will list the speakers and the agenda and uh it started as a napkin or white word idea and this is it. This is what I was given like Carlos we want to build this and I was like okay I can probably work on the front end very easily and get a front end created and and the the instruction I was given is like yeah we want to use SQL database in fabric for backend database we want to use graphql and I was like okay so let me start by designing the app designing the API so I can start like creating different diagrams so I have bunch of diagrams here mermaid diagrams that represent presents the flow and the components of my app. So you see like oops let me just move around here. Yeah I have a nextjs and my nextjs application is up and running and I want to create a graphql endpoint to uh return the data. So I have a very detailed plan here and if I want to copilot to assist me building this I have to provide that information to copilot and that's a perfect scenario for agent mode. So let me switch to agent mode. All right. So as I said this is the perfect scenario for agent mode but I have to provide agent mode with all the information and one of the key things I have here and I forgot to mention that is my schema design. So um remember everything started with a concept. This is a proof of concept and idea. I have my uh schema here represented in this diagram. So why not providing that information to GitHub profiler in agent mode to help me build this back end for me. So if we engage on agent mode, let's uh create a new session here and we're going to delete the a chat participant because we don't need it anymore. I can and let me do full screen here. I can uh and I have this very detailed prompt that provides GitHub pilot the information I want to share. Uh for example, I'm saying this is my project. I have the front end already built in. Uh my goal is to provide this experience and um you can use and this is very important. You can use remember I show you the flow design and I also show you the uh the schema here and also I have a screenshot um so I can open this screenshot really quick. This is a screenshot of my app. My app is running but I feel like it's easier for me to provide a screenshot to GitHub copilot just to build the schema for me. So if I drop that let me just the schema design whiteboard. Yeah if I drop that context to GitHub copilot it will understand what I want and I'm saying yeah my backend database is going to be SQL database in fabric and everything else. And right now yes it got the the the idea these are the core entities. those entities will be translated to data database tables. So with the next prompt right here, this is when I'm going to start building things from the database side. And I'm saying and this is very important Anna >> depending of what you want to do. You can go with TSQL, you can go with RM. It's up to your personal preference. Here as I said at the beginning, I'm feel more comfortable with RM. So why not, right? So I'm saying I want to use SQLite for my database. I want to use AD authentication. Here are the different models I want you to create. So if after I submit the request here for GitHub copilot, GitHub copilot is going to start building that back end for me. But there is one thing I don't have a database. Remember I I show you this I I have multiple databases and I don't have a database created here. So yeah, Copilot will start like building the back end, providing me the code and everything. How do I can like create that database really quick? >> So I have multiple options of course. One I want to show you which oops it's it's super cool. Right now it's saying that this session is taken. We'll have to make a pause here for Copilot to finish all this stuff. Sorry about that. And then I can ask that question about the database. Um, let me see if I can find um I'll try to find a transition. Uh, what we might need to wait a little bit for Copilot to finish creating all these different models I asked for, but once it's done, we'll come back and we'll ask Copilot to assist us creating the database and and digging a little bit more into um the details of this database that we want to build. All right. So, GitHub Copilot is done creating all the different files I need to include in my project to make it work with an RM. We can see here that it created um SQLite schema for my speakers table sessions and agendas. So, this is looking good. So, I got the code created but I don't have the database as I mentioned. So, I can ask here copilot to assist me creating the database for me in local mode. So remember I I have this local container. So I can simply say using MSSQL tools and this is super important in agent mode you have different tools right. So for MSSQL you will see that we have a collection of different tools which are basically actions that you can do run queries connect remember what I show you on slash commands very similar very similar uh so agent is going to use those tools and I'm going to say using MSQL tools create oops create a new database call it favcon I'm not as you can see I'm I'm super ambiguous on my on my prompt here and that's on purpose because I want GitHub copilot in agent mode to figure out where is my database and everything else. So you see the first thing it's doing is listing the connections and this is using what you have here >> on your OE on your profiles and right now I'm going to say yeah list all the different connections. Um, it's simple as listing the different profiles and identifying which one to pick to connect to a database. And right now you can see it says okay uh I see that you have a local you're connected to a SQL server and let me just go to full screen here. Um this is the port and yeah I can allow this and this is super important. We uh documented this. It's up to you to allow GitHub copilot to do certain things. you can say yes do this on this session allow the workspace level for now I would say like allowing this session so yeah all good um connect to this local um database and let's see if it's capable to creating the database and right now this is another very cool thing that I really like about keep copilot that is telling you what it's going to do so right here it says it says yeah I'm going to run this query so yeah that's that field says for me like this totally I'm totally fine time. So, it's up to the user to reject or approve the different options that Copala is going to take. And right now, apparently, it's done. And if I click allow once again, it should list the different databases. That's what it's trying to do. Like it created the database. And now, if we explore what was done here, it was basically returning the list of databases. And so, yeah, super easy. Um, now you can see that I have a database called Fabcon. So if I decide from here I can start like building my app and pushing those migrations that were created using SQL to my schema synchronizes the schema back and forth. But what happens when you're working on on this project and this is the last one a project that already exists. Remember the scenario I shared before like I have this existing like project I don't know much about it. How do I get up to speed really quick? There's one thing really cool here that you can do to and remember we were like listing stuff understanding different objects on my on my database but why not like looking at that schema using a visual diagram. So if I go here and I said show me the schema uh diagram for the pokei wiki database my favcon database is it was created but has nothing. It's empty right. So I'm going to use this other database to for a different project just for an example to show you that you can achieve even more with agent asking questions but also taking advantage of different or existing features in the MSQL extension as a schema designer. So voila you can see like it was super easy. I just asked show me the schema. Boom. I got the schema. >> Can I make changes here? >> Yes you can. Uh so yeah this is how you can take advantage of GitHub copilot in agent in uh in ask in agent mode. We have other features uh that hopefully we'll be able to show you in show here in the future or in case you are up to I can do it right now. It's simple. It's super simple. >> Okay. You you you have me I'm enthralled. Show me show me what else there is but quick so we can >> Oh yeah, absolutely. So uh we have something new uh remember I I created a database using uh copilot right >> but I need something I need a container to create that database. So we introduced these two new experiences in the extension where you can create a container without leaving VS code. So right here with this um wizard it is going to identify if Docker is running if Docker is ready to go configure and with a just a few clicks you can create a local container and the same goes for a SQL database in fabric. So I'm not going to create a container just going to go through the experience. So you can see with basically two clicks um right here my my account is already here I can just provide a database name a profile name and after filling those uh required parameters here I'm all set. So whether you decide to use GitHub proper to assist you working with local stuff or also probably things on the cloud, you can take advantage also of this new feature in the MSQL extension to deploy or provision databases locally using containers or SQL databasing fabric. So yeah, super quick. >> Awesome. I love it. I learned uh a lot today and I'm so excited to go try it out myself uh in VS Code MSSQL and I hope other folks are as well. Carlos, I know we're over time, but I have just one more question I have to ask you. Question of the people, and that's around Azure Data Studio retirement. How should people think about >> VS Code MSSQL and what we're bringing from ads versus what we're not and and and and how they should think about that? >> Yeah, that's a very interesting question. I'm glad you asked that question because I believe many of our users had the same question. So as we put it on our rope and road map, our goal is to is not to MSQL to be a life forlike replacement for ads. Some of the experiences that you uh were familiar in ads are being ported to the extension already and we are still working to some of the experiences that many users are asking for. for example, backup and restore uh backpack or the other is import flat files into a single table. So yes, rest assured that we're working on some of the experiences but as I said it's not a like for like it's a different take. Why? Because we want to provide a different experience tailored to developers for example the container stuff git propillet. So, uh, prioritizing different features we're going to, uh, land into the extension allows us to do more for you. Uh, and this is all based on feedback. So, yeah, rest assured that we will do our best to bring all these experiences you love into the MSQL extension. Uh, and it will happen before we retire ideas for sure. >> Awesome. Great. Well, Carlos, thanks again so much for coming on the show. to our viewers. If you like this episode, go ahead, give it a like. Leave us a comment and let us know what your favorite thing was and what you plan to do with GitHub Copilot and MSSQL. And we'll put some links in the description for you to learn more. And we hope to see you next time on Data Exposed. [Music]