In the realm of modern software development, the ability to seamlessly store and retrieve data is paramount. Whether you’re building a web application, a mobile app, or any other software solution, managing the interaction between objects and databases can quickly become complex and time-consuming.
That’s where Object-Relational Mapping (ORM) comes into play, offering a powerful toolset to bridge the gap between your application’s objects and the underlying database tables.
You can find the complete source code in https://github.com/nikosportolos/dart_api_demo.
In this tutorial, we delve into the fascinating world of Object-Relational Mapping in Dart, with a particular focus on utilizing the Dart package called Prisma ORM. Dart, known for its versatility and performance, has gained significant traction in recent years, becoming a go-to language for building cross-platform applications. By leveraging Prisma ORM, a robust and feature-rich ORM framework designed specifically for Dart, you can unlock an array of benefits, including increased productivity, improved code organization, and simplified data persistence.
Furthermore, we’ll introduce you to the ‘dart_frog’ package, a potent tool for crafting Dart servers that expose APIs. With ‘dart_frog’, you can effortlessly construct a server that interacts with your SQL Server database, seamlessly integrating with Prisma ORM. This synergy between Prisma ORM and ‘dart_frog’ empowers you to create a powerful backend infrastructure that efficiently retrieves data from your SQL Server.
Whether you’re an experienced Dart developer seeking to optimize your data access layer with SQL Server integration using Prisma ORM and ‘dart_frog’, or a newcomer eager to delve into the world of ORM, this tutorial is tailored for you.
Join us on this enlightening journey as we demystify Object-Relational Mapping in Dart and demonstrate how it can unlock new possibilities for your projects. Let’s dive in!
Object Relational Mapping (ORM) is a technique used in creating a “bridge” between object-oriented programs and, in most cases, relational databases.
Put another way, you can see the ORM as the layer that connects object-oriented programming (OOP) to relational databases.
Code First (or model-first) allows you to create a new model and then generate a database schema based on that model.
Database First allows you to reverse engineer a model from an existing database schema.
Microsoft SQL Server is a relational database management system (RDBMS) that supports a wide variety of transaction processing, business intelligence and analytics applications in corporate IT environments.
SQL Server can be installed on premises or in the cloud
The Microsoft Open Database Connectivity (ODBC) interface is a C programming language interface that makes it possible for applications to access data from a variety of database management systems (DBMSs).
ODBC is a low-level, high-performance interface that is designed specifically for relational data stores.
Prisma is an open source next-generation ORM.
It consists of the following parts:
Prisma Client
Auto-generated and type-safe query builder for Node.js & TypeScript.
Prisma Migrate
Migration system.
Prisma Studio
GUI to view and edit data in your database.
Most Popular Databases
Prisma supports PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, MongoDB and CockroachDB.
Type-safe
Prisma Client is a query builder that’s tailored to your schema. The API is designed to be intuitive, both for SQL veterans and developers brand new to databases. The auto-completion helps you figure out your query without the need for documentation.
Human-readable
Prisma schema is intuitive and lets you declare your database tables in a human-readable way — making your data modeling experience a delight. You define your models by hand or introspect them from an existing database.
It’s open source
Already used by other languages/frameworks
Supports the most popular and widely used databases
Allows us to connect a Dart/Flutter app directly to a database which isn’t already supported by other packages, like SQL Server
It adds one extra layer between the Dart/Flutter app and the database, which we have no control on.
Performance (?)
- Install Node.JS
- Install Prisma CLI
Prisma CLI is a command-line tool officially provided by Prisma for managing Prisma projects.
It helps you generate Prisma clients, generate the models required by Prisma clients, and perform database migrations.
npm i prisma- Initialize the Prisma project
A Prisma project is a project that contains the Prisma client and the models required by the Prisma client.
You can initialize a Prisma project using the Prisma CLI.
npx prisma init- Initialize the Prisma project
You will get a .env file that contains configuration information for the Prisma project.
DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA"and a prisma/schema.prisma file containing the models required by the Prisma client.
generator client {
provider = "dart run orm"
}
datasource db {
provider = "sqlserver"
url = env("DATABASE_URL")
}- Install the required Dart packages
The orm package is a Dart version of the Prisma client for interacting with the Prisma engine.
dart pub add ormInstall build_runner and json_serializable to generate the necessary files.
dart pub add build_runner -d # required
dart pub add json_serializable -d # required
dart pub add json_annotation # optional, but recommended- Modify the generator configuration in the schema.prisma file
generator client {
provider = "dart run orm"
}- Add a data model in the schema.prisma file
model User {
id String @id(map: "PK_User") @db.NVarChar(36)
email String @unique @db.NVarChar(100)
first_name String @db.NVarChar(100)
last_name String @db.NVarChar(100)
alias String? @db.NVarChar(150)
}
- Use the Prisma CLI to push the user-defined schema to the database
npx prisma db push- Create a table in your database
- Use the Prisma CLI to pull the schema from database and update the schema.prisma file
npx prisma db pullPrisma Migrate is an imperative database schema migration tool that enables you to:
Keep your database schema in sync with your Prisma schema as it evolves and
Maintain existing data in your database.
Prisma Migrate generates a history of .sql migration files, and plays a role in both development and deployment.
npx prisma db migrate- Use the Prisma CLI to generate the prisma client
npx prisma generate- Use build_runner to generate the models
dart run build_runner buildimport 'package:orm/logger.dart';
import 'prisma_client.dart';
final prisma = PrismaClient(
stdout: Event.values, // print all events to the console
datasources: Datasources(
db: 'postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA',
),
);
main() async {
try {
final user = await prisma.user.create(
data: UserCreateInput(
name: 'Alice',
email: 'alice@prisma.pub',
),
);
print(user);
} finally {
await prisma. $disconnect();
}
}A fast, minimalistic backend framework for Dart 🎯
View the full documentation here.
Advantages of dart_frog:
Built for Speed
Create new endpoints in just a few lines and iterate blazingly fast with hot reload.
Lightweight
Minimize ramp-up time with our simple core and small API surface.
Powered by Dart
Tap into the powerful Dart ecosystem with Shelf, DevTools, testing, and more.
# 📦 Install the dart_frog cli from source
dart pub global activate dart_frog_cliUse the dart_frog create command to create a new project.
# 🚀 Create a new project called "my_project"
dart_frog create my_projectNext, open the newly created project and start the dev server via:
# 🏁 Start the dev server
dart_frog devCreate a production build which includes a DockerFile so that you can deploy anywhere:
# 📦 Create a production build
dart_frog buildimport 'dart:async';
import 'dart:io';
import 'package:api_data_source/api_data_source.dart';
import 'package:dart_frog/dart_frog.dart';
FutureOr<Response> onRequest(RequestContext context) async {
switch (context.request.method) {
case HttpMethod.get:
return _get(context);
case HttpMethod.post:
return _post(context);
case HttpMethod.delete:
return _delete(context);
case HttpMethod.head:
case HttpMethod.options:
case HttpMethod.patch:
case HttpMethod.put:
return Response(statusCode: HttpStatus.methodNotAllowed);
}
}
Future<Response> _get(RequestContext context) async {
final dataSource = context.read<UserDataSource>();
final users = await dataSource.readAll();
return Response.json(body: users);
}
Future<Response> _post(RequestContext context) async {
final dataSource = context.read<UserDataSource>();
final user = User.fromJson(
await context.request.json() as Map<String, dynamic>,
);
return Response.json(
statusCode: HttpStatus.created,
body: await dataSource.create(user),
);
}
Future<Response> _delete(RequestContext context) async {
final dataSource = context.read<UserDataSource>();
await dataSource.deleteAll();
return Response(statusCode: HttpStatus.noContent);
}You can find the complete source code in https://github.com/nikosportolos/dart_api_demo.