Build a CRUD RESTful API with Grails 6 and Microsoft SQL Server

Grails 6 introduces a modern development experience with enhanced support for REST APIs, persistence, and database integration. In this tutorial, you’ll learn how to build a CRUD RESTful API using Grails 6 and Microsoft SQL Server, leveraging features like the create-restapi command, GORM entities, and @Resource for automatic endpoint generation. We’ll walk through connecting to SQL Server, configuring the datasource in application.yml, and exposing domain classes via REST—all using Grails' simplified, module-free architecture.

Prerequisites

• JDK 17+

• Grails 6 CLI 

  sdk install grails 6.2.3
  sdk u grails 6.2.3

• Microsoft SQL Server (local or remote instance). We are using Docker version. 

  docker pull mcr.microsoft.com/mssql/server:2022-latest
  
  docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=YourStrongPassword1" -p 1433:1433 --name sqlserver -d mcr.microsoft.com/mssql/server:2022-latest

Step 1: Create a New REST API App

Run the following command to create a new Grails 6 app with SQL Server support:

grails create-restapi my-rest-app --features=sqlserver,gorm-hibernate5
cd my-rest-app

Run this Grails app for the first time.

./gradlew bootRun

Step 2: Configure SQL Server in application.yml

Open grails-app/conf/application.yml and update the datasource settings:

dataSource:
  pooled: true
  driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
  url: jdbc:sqlserver://localhost:1433;DatabaseName=MyDatabase
  username: my_user
  password: my_password
  dialect: org.hibernate.dialect.SQLServer2022Dialect

environments:
  development:
    dataSource:
      dbCreate: update
  test:
    dataSource:
      dbCreate: update
  production:
    dataSource:
      dbCreate: none

Step 3: Add SQL Server JDBC Driver

Edit build.gradle and add the following dependency:

dependencies {
  runtimeOnly 'com.microsoft.sqlserver:mssql-jdbc:11.2.0.jre17'
}

Then refresh dependencies:

./gradlew clean
./gradlew compile

Step 4: Create a Domain Class

Generate a new domain class named Book:

grails create-domain-class Book

Update grails-app/domain/Book.groovy:

package my.rest.app

grails.rest.Resource(uri = '/books', formats = ['json', 'xml'])
class Book {
    String title
    String author

    static constraints = {
        title blank: false
        author blank: false
    }
}

The @Resource annotation automatically exposes CRUD endpoints for the Book domain.

Step 5: Run the Application

We will test the REST API using `CURL` from the terminal or command line. Run the Grails application using this command.

./gradlew bootRun

Test the REST API endpoints:

Create a new book:

curl -X POST http://localhost:8080/books \
  -H 'Content-Type: application/json' \
  -d '{"title":"Grails Guide", "author":"Djamware"}'

Get all books:

curl http://localhost:8080/books

Get a single book:

curl http://localhost:8080/books/1

Update a book:

curl -X PUT http://localhost:8080/books/1 \
  -H 'Content-Type: application/json' \
  -d '{"title":"Updated Title", "author":"Djamware"}'

Delete a book:

curl -X DELETE http://localhost:8080/books/1

Optional: Add a Custom Controller

If you want custom logic, create a controller:

grails create-controller Book

Then extend RestfulController:

package my.rest.app

import grails.rest.RestfulController

class BookController extends RestfulController<Book> {
    static responseFormats = ['json', 'xml']
    BookController() { super(Book) }
}

Conclusion

You’ve now built a complete CRUD RESTful API using Grails 6 and Microsoft SQL Server. Thanks to the simplified standalone architecture and GORM REST support, you can rapidly scaffold REST endpoints, connect to SQL Server, and extend logic as needed.

You can find the working source code on our GitHub.

That's just the basics. If you need more deep learning about Groovy and Grails, you can take the following cheap course:

• Mastering Grails. A Comprehensive Grails Course.
• Groovy Scripting for Developers / Testers
• Introduction to JVM Languages Clojure, Kotlin, and Groovy

Thanks!