Building a CRUD REST API with Dart and SQLite

Step 1: Set Up the Dart Project

Run the following command to create a new Dart project:

dart create dart_sqlite_api
cd dart_sqlite_api

Step 2: Add Dependencies

Edit pubspec.yaml and add:

dependencies:
  shelf: ^1.4.0
  shelf_router: ^1.1.4
  sqlite3: ^2.0.0
  path: ^1.8.3

Run:

dart pub get

Step 3: Set Up SQLite Database

Create a new file named database.dart inside the bin directory:

import 'package:sqlite3/sqlite3.dart';

class DatabaseHelper {
  late Database db;

  DatabaseHelper() {
    db = sqlite3.open('users.db');
    _createTable();
  }

  void _createTable() {
    db.execute('''
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE
      )
    ''');
  }

  void insertUser(String name, String email) {
    db.execute('INSERT INTO users (name, email) VALUES (?, ?)', [name, email]);
  }

  List<Map<String, dynamic>> getUsers() {
    final results = db.select('SELECT * FROM users');
    return results
        .map((row) => {'id': row['id'], 'name': row['name'], 'email': row['email']})
        .toList();
  }

  void updateUser(int id, String name, String email) {
    db.execute('UPDATE users SET name = ?, email = ? WHERE id = ?', [name, email, id]);
  }

  void deleteUser(int id) {
    db.execute('DELETE FROM users WHERE id = ?', [id]);
  }

  void closeDatabase() {
    db.dispose();
  }
}

Step 4: Create CRUD API

Create a file named server.dart inside the bin directory:

import 'dart:convert';
import 'package:shelf/shelf.dart';
import 'package:shelf/shelf_io.dart' as io;
import 'package:shelf_router/shelf_router.dart';
import 'database.dart';

// Initialize Database
final dbHelper = DatabaseHelper();

// Get all users
Response getUsers(Request request) {
  final users = dbHelper.getUsers();
  return Response.ok(jsonEncode(users), headers: {'Content-Type': 'application/json'});
}

// Add a user
Future<Response> addUser(Request request) async {
  final payload = jsonDecode(await request.readAsString());

  if (!payload.containsKey('name') || !payload.containsKey('email')) {
    return Response.badRequest(body: 'Missing required fields');
  }

  dbHelper.insertUser(payload['name'], payload['email']);
  return Response.ok(jsonEncode({'message': 'User added successfully'}), headers: {'Content-Type': 'application/json'});
}

// Update a user
Future<Response> updateUser(Request request, String id) async {
  final payload = jsonDecode(await request.readAsString());

  if (!payload.containsKey('name') || !payload.containsKey('email')) {
    return Response.badRequest(body: 'Missing required fields');
  }

  dbHelper.updateUser(int.parse(id), payload['name'], payload['email']);
  return Response.ok(jsonEncode({'message': 'User updated successfully'}), headers: {'Content-Type': 'application/json'});
}

// Delete a user
Response deleteUser(Request request, String id) {
  dbHelper.deleteUser(int.parse(id));
  return Response.ok(jsonEncode({'message': 'User deleted successfully'}), headers: {'Content-Type': 'application/json'});
}

void main() async {
  final router = Router()
    ..get('/users', getUsers)
    ..post('/users', addUser)
    ..put('/users/<id>', updateUser)
    ..delete('/users/<id>', deleteUser);

  final handler = Pipeline().addMiddleware(logRequests()).addHandler(router);

  final server = await io.serve(handler, 'localhost', 8080);
  print('Server running on http://${server.address.host}:${server.port}');
}

Step 5: Run the API Server

Start the server by running:

dart run bin/server.dart

Step 6: Test the API

1. Get All Users

curl -X GET http://localhost:8080/users

2. Add a New User

curl -X POST http://localhost:8080/users -H "Content-Type: application/json" -d '{"name": "John Doe", "email": "john@example.com"}'

3. Update a User

curl -X PUT http://localhost:8080/users/1 -H "Content-Type: application/json" -d '{"name": "John Updated", "email": "john_updated@example.com"}'

4. Delete a User

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

Conclusion

You have successfully built a CRUD API in Dart using SQLite and the shelf web framework. This API allows you to create, read, update, and delete users in an SQLite database. Dart can be a powerful tool for backend development beyond Flutter.

Happy coding! 🚀