Edit

Share via

Quickstart: Use the schema explorer and designer

In this quickstart, you learn how GitHub Copilot assists developers in designing, understanding, and evolving database schemas with context-aware suggestions. Whether you're building from scratch or reverse-engineering existing tables, GitHub Copilot streamlines the process across SQL and object-relational mapping (ORM) frameworks, making schema work faster, smarter, and easier to maintain.

This section covers both creating new schemas from scratch and working with existing databases. You can use GitHub Copilot to generate code-first schema definitions, update objects, or reverse-engineer and explore existing databases.

Schema creation

Create basic schema

Write a SQL script to create a new schema named `blog` for a blog application. The schema should include three tables: `Posts`, `Comments`, and `Users`. Each table must have appropriate primary keys, and the necessary foreign key relationships and constraints should be defined.

Modify schema

Add a new column named `LastModified` of type `datetime` to the `Posts` table in the `blog` schema. Generate the updated SQL script reflecting this change, including the full definition of the modified schema.

It isn't needed to create the schema, but it would be great if you could use the script generated and run it to validate the accuracy of the generated code. The following section continues using this new schema called `blog`.

Create schema with relationships and constraints

Generate a Prisma schema for a blog application using my current database. The schema should define a new database schema named `blog` and include tables for `posts`, `authors`, and `comments`, with appropriate relationships and constraints.

Create migration script to add a column

Generate a Prisma migration to add a column called `LastModified` (`datetime`) to the `Post` table.

Reverse engineer an existing database

Reverse engineer the current database and generate `CREATE TABLE` statements for all tables in the `SalesLT` schema.

Summarize a table structure

Summarize the structure of the `SalesLT.Product` table in natural language.

Generate a Python model

Generate a `models.py` (Django) file that reflects the structure of the `SalesLT.Customer` table.

Generate Entity Framework Core context and models

Generate an Entity Framework Core DbContext and model classes for the `SalesLT` schema.

Create model definition and associations

Create a Sequelize model definition for the `SalesLT.Product` and `SalesLT.Category` tables with appropriate associations.

Generate an entity from a table

Generate a TypeORM entity for the `SalesLT.Customer` table, including primary key and indexed fields.

Generate a migration script to create a new table

Generate a `knex.js` migration script to create the `SalesLT.SalesOrderHeader` table with `OrderDate`, `CustomerID`, and `TotalDue` columns.

Define relationships

Define a relational script with foreign key references

Write SQL to define a one-to-many relationship between `Users` and `Posts` in the `blog` schema. Ensure the foreign key in `Posts` references `Users(UserId)`.

Add a table to a schema with foreign key references

Add a `Categories` table to the `blog` schema and update the `Posts` table to include a nullable foreign key referencing `Categories(CategoryId)`.

Update a database to add a table and update columns

Write SQL to update the `Users` table to include a `RoleId` column and create a new `Roles` table. Define a foreign key relationship and enforce that every user must have a role.

Identify foreign key relationships for a table

Identify and describe all foreign key relationships that involve the `SalesLT.SalesOrderHeader` table.

Replace a foreign key with a many-to-many relationship

Write a SQL script that removes a foreign key between `Posts` and `Categories` in the `blog` schema and replaces it with a many-to-many relationship using a new join table.

Generate mapping between two tables

Write Prisma relation mappings between `Customer`, `SalesOrderHeader`, and `SalesOrderDetail`.

Update a data model

Update a Sequelize model to include a `hasMany` and `belongsTo` relationship between `Customer` and `Order`.

Schema validation

Suggest constraints for sensitive data

Suggest constraints for a table storing user passwords (for example, special characters and length limits).

Validate data type constraints

Confirm that the `Name` column in `SalesLT.ProductCategory` doesn't use `nvarchar(max)` and has a reasonable maximum length constraint.

Validate primary key constraints

Check whether the `SalesLT.Address` table has a primary key and all required fields defined.

Validate auditing records for tables

Generate a SQL script to validate that all tables in the `SalesLT` schema include a `CreatedDate` or `ModifiedDate` column.

Define a model and include validation logic

Define a SQLAlchemy model for the `Customer` table and include validation logic using Pydantic or custom Python validators before inserting into the database.

Add data annotations for format validation

Add data annotations in an Entity Framework model to ensure fields like `Email` and `PhoneNumber` follow specific formats.

Share your experience

To help us refine and improve GitHub Copilot for the MSSQL extension, use the following GitHub issue template to submit your feedback: GitHub Copilot Feedback

When submitting feedback, consider including:

  • Scenarios tested – Let us know which areas you focused on, for example, schema creation, query generation, security, localization.

  • What worked well – Describe any experiences that felt smooth, helpful, or exceeded your expectations.

  • Issues or bugs – Include any problems, inconsistencies, or confusing behaviors. Screenshots or screen recordings are especially helpful.

  • Suggestions for improvement – Share ideas for improving usability, expanding coverage, or enhancing the GitHub Copilot's responses.

Related content

  • Last updated on