Next-Level SQL in VS Code: GitHub Copilot Custom Instructions, Plan Mode & Skills | Data Exposed

Hi, I'm Anna Hoffman and welcome back to Data Exposed. Today, I'm joined by Carlos once again. Carlos, thanks so much for coming back on the show. Yeah, thank you so much for having me again. Yeah, it's great to have you and you always enlighten us with what's new in MSSQL extension for VS Code, how we can take advantage of the new things landing in GitHub Copilot or just ramp up on those things in general, especially as it relates to SQL databases and development. So, today's no exception. I know last month was a big release for the team. I'd love for you to just take us through like what's top of mind, what do you think we should know, what's new, and really anything else cool that you have up your sleeves. Yeah, absolutely. So, today's going to be a little bit different. I will be covering one of those new features for sure, which is our schema designer integration with GitHub Copilot. We have more demos hopefully available on demo playlist, so you probably can check that offline. But today, I'll say the focus is is going through some of the most common questions we receive about GitHub Copilot, how it behaves, how it can get advantage of some of the specific scenarios that applies to some some sometimes to a specific team. So, yeah, I'll do a mix and match, I would say, probably covering the basics as well showing you some of the new features. I love it. Let's take a look. All right, so let's go ahead and get started. So, right here you see that I have the MSSQL extension open. I'm already connected. I am going to be working today in the scope of this task manager database. It's empty right now. I have nothing, right? So, one of the the I'll say most requested common questions that we got is like I'm engaging with GitHub Copilot here and the answers I'm getting are not great. So, right here you see that I started this session in GitHub Copilot without context whatsoever. No context and I just asked blindly create a user's table, a projects table, and help me with that. The problem here is that the code we're going to get is going to be good. Right now, you see that I got something, but the problem is that GitHub Copilot can't see my connection, can't see the database. It's just like just acting using the training data from the model. And it's doing a great job. If we look at what was created here, it's not bad, I would say. You see that I got my two tables, but I see a few problems here, especially if I have a standard in a team. Uh for example, let's say my team doesn't wants to use int for primary keys. Maybe we want to use big int. Another problem I see here is that is using underscore for the the column names. I see that is giving some names to the the foreign keys. A big big red flag for me here is like it's not using the fully qualified name for the object, so it's not the schema.object. And by the way, Anna, this is very interesting and also important to mention that this applies what I'm going to show you applies to ORMs, applies to any different code. So, it's not just for databases, but I wanted to make sure the audience understands here like what you get is nice, but it's not yours, right? So, this is not the way I work with my team. It's it's good, but let's take a look at how we can influence GitHub Copilot to do something tailored to my needs, okay? So, let me switch here to a different folder. Same thing. You see that I have the same database. It's still empty and everything. But what I'm going to start by doing here is by creating a file that's called the custom instructions. So, from here, you click on this gear icon. And by the way, this UI is constantly changing. The VS Code team is regularly changes, so maybe next time you click it's going to be a little bit different. So, here you see that I have instructions. From here, I can control all the different customizations that are available for me in GitHub Copilot. I'm going to focus first in instructions. From here, I can generate instructions based on what I have in this project. Right now, it's empty, so I'll get nothing. So, I'm going to click here and select new instructions for workspace. That means that the instructions I'm creating right now applies to this folder and this folder only. I can create instructions for my user. I can create instructions at GitHub repo level as well. So, this has like multiple layers. I'm going to go with the basics here just to go with GitHub and then I'm going to call it T-SQL convention and press enter. And by that, I get this little nice um format or template that I have to fill out. Obviously, I have my own, so let's zoom in really quick here. And you see that I am defining that this custom instruction is going to apply to any T-SQL code. Again, this is up to you. You can do it for ORM. You can do it for many different things. I'm giving a little bit of details here about my database environment, what I want to use for local development, what I want to use for cloud. But very important here, it's it's this is how powerful these custom instructions are. I'm saying, "Hey, I want the T-SQL code to be compatible no matter what." And that's that's nice. That is very important and I am calling out here, whenever you use specific features, I want you to add a comment. I want to make sure the team is is aware of that. Here's something very interesting. I'm saying, "I want a template. I want you to include a header." That's very important for documentation purposes, so you can do that stuff. And obviously, I'm even including how I want every single script to start. And if I scroll down, you'll see something interesting here. Remember from the previous example, we got column like user_id and as a developer, I don't like that. I use camel case. I'm very familiar with camel case for everything, so I want T-SQL and my code for my database to be camel case. And more here, you see nvarchar, big int, more information about the strings and I some stuff that I want GitHub Copilot to avoid. So, really interesting what you can do. So, let's go ahead and click on here just to get out of here. Let's close this. All right, so I'm going to paste exactly the same instruction I gave to GitHub Copilot before. So, let's do it here and let's go full screen. And there is going to I'll I'll expect GitHub Copilot to do this right here. What you see happening, Anna, is reading the custom instructions that I created. So, that means that the result that I'm going to get, the output that GitHub Copilot's going to generate is going to be different. It's not going to be the generic T-SQL code that I got last time. So, it looks like it's done. So, let's take a look really quick here and let's close everything and let's just inspect one. It's probably too big. And the result is nice. It gave me the header that I was expecting to have, these commands to make sure like this is the standard. But let's take a look at this one. Remember I said this might be a not a good thing, might cause some issues. Right now, I get the fully qualified name, schema.object. I got my big int that I mentioned. My constraints are created exactly the way I want. So, this is nice. And again, I'm not even connected at this point. So, if you want to work offline and you want to work on make sure you include those custom instructions for your team to make sure all these standards are applied, this is how you can do it. Um so, >> great. I actually had not done this before, but it makes a ton of sense and I know we have something similar in SSMS. Except the difference here is like this is scoped for in in your case, I think you did the entire workspace, but you could also do the entire user or the repo like you were saying. So, that's pretty cool. Yeah, so that's exactly case. With that, I just created a two objects on my database and I'm ready to evolve this project. But this time I'm going to use a combination of what is called a PRD, which is very popular product requirements product requirements document. I'm going to use GitHub Copilot agent mode and I'm going to combine that with the schema designer. Okay, so let me create my PRD really quick. Let's start by creating a new file here and let's save it. And let's say this is my requirements yeah, requirements .md. And with that, I have my PRD. And because this is an empty file, we can take a look at the file using um a markdown preview. So, right here is a specification of my project. So, I'm giving more information to GitHub Copilot to assist me better. I'm defining what is the technology stack. You see that I have a specific application and architecture that includes my database, everything else. A little bit about what I'm looking for for the database just by providing this diagram. And here's some very small specification for the different features I want to include. So, this is really nice and at this point or we have this file created and we have the tables created on our project. So, what I want is GitHub Copilot to read through this specification and help me create a plan. So, let's do it. Let's open GitHub Copilot here again. And let's um say here, and this is something another trick, and I am passing here the context of my requirements and saying, "Hey, GitHub Copilot, use this in plant mode, read the document, and give me a recommendation of my data model." So, with that, we'll expect GitHub Copilot to use all the different things, the custom instructions, the PRD, look at uh what I have right now, and give me a good recommendation of what I can implement on my project. So, this is going to take a few seconds. So, we'll give um few moments for GitHub Copilot to work and cook. And while this is running, Carlos, just a a question to make sure I understand, like, basically in this scenario, you created the requirements PRD doc, but you also have your instructions that you showed us earlier. Is that going to influence this as well? Yes, that is correct. So, because I have this folder, so you can see that my folder is here, when GitHub Copilot it starts building something, it's going to go through the different instructions I have created at workspace level level, it's going to obviously work on the plan based on these requirements, but it's not going to forget and go off script here. So, yeah, everything is tied together. And we'll see hopefully an example of even a more advanced uh capability I'm going to show once we are done with this. So, it looks like, woo, it's done. Let's take a look. So, we have our plan. It's very large, as you can see, and the plan is here. I can just simply click and and and take a look at the plan. It's saying, "Hey, it looks like, yeah, there are some tables that exist. You have some files. This is my recommendation." It's a lot. All right. So, I have my plan. This is great. So, I can uh use this in context of my next next ask for GitHub Copilot. Let's make sure we're in agent mode. And from here, I'm going to say, "Hey, using this plan, open schema designer or use a schema designer, and implement the different tables and relationships that are you are suggesting to my database." Remember what we discussed at the beginning? No context. GitHub Copilot not able to see what I have in this case. I'm explicitly asking to connect and use a schema designer, connect to my database, and implement these changes. That's pretty cool. So, while this is running, it's going to use them as the SQL extension to open the schema designer, at least that's what we would expect. Yeah, right now, you can see that it's happening. So, GitHub Copilot what when he uses agent mode, it will evaluate the different tools that are available for the workspace, and then figure out the answer. And right here, if I click this button, everything is happening so fast. So, if I click this button and open the tools, you see that the tools for SQL Server MS SQL are here. It's using these tools. It's using the tool to connect. Uh it's using the tool to open a schema designer. And right now, it's uh almost done. And as you can see, it's it's doing it. It it just opened schema designer. What I would expect next is GitHub Copilot to implement those changes, and it's going to create a to-do. Right here, we have it. It's a to-do is adding the different tables. It just understand, "Oh, you have like two tables. I need to create relationships." So, yeah, we'll see what happens here, and everything was built, let's say, based on the custom instructions, also the PRD. So, this is a spec-driven development, and when I have full control of what GitHub Copilot is doing, I'm influencing with the standards, influencing with the the the diagram. So, yes, these address some of the questions we got before of how I can make sure GitHub Copilot generate something that is fully mine. And right now, let's close this real quick, because it's like again, it's happening we see that it's happening. What is happening here is that it's almost done with the to-do. Uh but what that is happened here, we can uh open schema designer. Let me just do here. And I want to show you everything. So, we have a lot. We have a lot here, and let me do you rearrange. So, you see that we have two tables before. We have the uh projects and users table, and GitHub Copilot went ahead and used schema designer to propose a few tables. When I'm saying propose, it's on purpose, because I don't know if you noticed that I have 14 changes here, but 13 changes here. This is how users can have control what they want to do. When you get this, it's not yours yet until you accept the changes. So, you'll see from this panel that I have 14 different changes I have to go through and and and approve. So, the first one, if we just click this one, if I do double click, it's going to zoom in and focus on the change. This is the first change. This is a modification to the users table. Um it's adding a new column. If I click here, it's saying, "Hey, you have a new table that we're adding." And it's saying goes over and over and over again. So, this is how you have full control. Of course, you can take a look at the changes from here. They have a visual representation and everything, a chronolo- chronologic representation. New table comments, new project members. This is a modification, it's a different color. It's using um a very familiar, I would say, um labels for developers uh that uses source control. I can check the schema diff. This is really powerful. Yeah, really powerful. I see that it's it's making a change for the users table. Um and and then all the different tables I have. This is nice. This is good for me. So, I guess I'm good with all the changes, so I can accept all the changes. And with that, take a look at what happened here. Let me go here. Instead of like GitHub Copilot changes went to zero, because now these are my changes, because I accepted that. Uh so, that means that this is not yet applied to my schema. Um my schema is still This is a a a design phase. So, I have to accept the changes and then apply the changes. Uh so, right here, it's up to me. Let's go ahead and um apply these changes, and let's move to the next scenario real quick. Yeah, so, last thing I want to show you is real quick is a skill. Um so, I already created these off-camera, but it's very very similar to what I did with custom instructions. The only difference here is that the skill is specialized in a a a a this scenario I want to introduce. And this scenario is about vector databases. Uh vector data types, excuse me. So, with vector vector data types, and we have our schema here, I can uh after I have created my skill, I can call my skill using a slash command. You see that I have a skill called vector search. And then ask Copilot to do something very specific. This is going to combine the custom instructions. This is going to combine um like all the knowledge that GitHub Copilot has so far for this session, but also implement what I'm asking here specifically for uh this vector data type in terms of the skill. So, the result is what we have here, and as you can see, Anna, it's basically what we discussed before. We have the header, we have the data types, everything. This is my project according to my standards and everything else. So, this is nice. Nice. Amazing. Awesome. Carlos, I have learned so much. Uh if you were to wrap all these takeaways up for us, you know, kind of how would you summarize? Yeah, absolutely. So, I have these slides right my slide ready here. Um so, basically what we talk about is like when you have no context and you start a project, GitHub Copilot is going to do its best to provide you a good answer with all the training data. Obviously, you can influence that using custom instructions, so it's going to learn your specific standards. When you engage into a more, let's say, advanced or complex scenarios, you can use plant mode, you can use PRDs, you can use many different tools to organize the ideas and build a plan. So, then when you have a plan, you can use a specialized tools like the one I showed you today, the schema designer, that connects to a database, understand the schema, it's going to apply all those best practices, and all those guidelines for your project. And then when you have something like almost done, you can even go further and use the skills to have uh control over domain specific tasks. In this case, I want to do something very specific for vector, and that's how I achieved that goal. Awesome. I love it. This is amazing, Carlos, and I love your summary down at the bottom that says, "Same model, same GitHub Copilot, same Microsoft SQL database, and the context is what's changing everything." Carlos, I learned so much today. I'm sure our viewers did as well. Uh for our viewers, we'll put some links in the description We'll put some links in the description, as well as a link to the uh playlist that Carlos talked about earlier, so you can go all to your heart's content, all the cool demos that the team is working on in the extension. Uh if you like this episode, go ahead give it a like, leave us a comment, let us know what you think. And we hope to see you next time on Data Exposed.