Dart · shelf: models and migrations

import 'dart:io' show Platform; import 'package:postgres/postgres.dart'; /// One connection pool for the whole app, created the first time it's used./// Opening a fresh connection on every request leaks sockets; a pool keeps a/// few open and hands them out as needed.final Pool db = Pool.withEndpoints(  [    Endpoint(      host: _env('PGHOST', 'localhost'),      port: int.parse(_env('PGPORT', '5432')),      database: _env('PGDATABASE', 'dartpets'),      username: _env('PGUSER', 'postgres'),      password: _env('PGPASSWORD', 'postgres'),    ),  ],  settings: PoolSettings(    maxConnectionCount: 5,    // postgres 3.x REQUIRES SSL by default. A local Postgres has no TLS, so    // turn it off unless PGSSLMODE asks for it (hosted databases will).    sslMode: _env('PGSSLMODE', 'disable') == 'disable' ? SslMode.disable : SslMode.require,  ),); /// Read an environment variable, or use the local-development default.String _env(String key, String fallback) => Platform.environment[key] ?? fallback;

CREATE TABLE IF NOT EXISTS turtles (  id   SERIAL PRIMARY KEY,  name VARCHAR(30) NOT NULL,  age  INTEGER NOT NULL);

import 'package:postgres/postgres.dart'; class Turtle {  // Session is implemented by both Pool and Connection, so either works here.  final Session db;   Turtle(this.db);   // Each row comes back as a List of values; toColumnMap() turns it into  // {'id': 1, 'name': 'Filbert', 'age': 5}, which jsonEncode understands.  List<Map<String, dynamic>> _rows(Result result) =>      result.map((row) => row.toColumnMap()).toList();   // all turtles  Future<List<Map<String, dynamic>>> getAll() async {    final result = await db.execute('SELECT * FROM turtles ORDER BY id');    return _rows(result);  }   // one turtle, or null if the id doesn't exist  Future<Map<String, dynamic>?> getOne(int id) async {    // Sql.named lets us write @id; `parameters` fills it in safely (no SQL injection)    final result = await db.execute(      Sql.named('SELECT * FROM turtles WHERE id = @id'),      parameters: {'id': id},    );    return _rows(result).firstOrNull;  }   // insert one turtle and return the new row (RETURNING * gives us the id)  Future<Map<String, dynamic>> insert({required String name, required int age}) async {    final result = await db.execute(      Sql.named('INSERT INTO turtles (name, age) VALUES (@name, @age) RETURNING *'),      parameters: {'name': name, 'age': age},    );    return _rows(result).first;  }   // update one turtle and return the updated row, or null if the id doesn't exist  Future<Map<String, dynamic>?> update(int id, {required String name, required int age}) async {    final result = await db.execute(      Sql.named('UPDATE turtles SET name = @name, age = @age WHERE id = @id RETURNING *'),      parameters: {'id': id, 'name': name, 'age': age},    );    return _rows(result).firstOrNull;  }   // delete one turtle and return what was deleted, or null if the id doesn't exist  Future<Map<String, dynamic>?> destroy(int id) async {    final result = await db.execute(      Sql.named('DELETE FROM turtles WHERE id = @id RETURNING *'),      parameters: {'id': id},    );    return _rows(result).firstOrNull;  }}

import 'package:firstproject/db/connection.dart';import 'package:firstproject/models/turtle.dart'; void main() async {  final turtles = Turtle(db);   final wilbert = await turtles.insert(name: 'Wilbert', age: 5);  print(wilbert); // {id: 2, name: Wilbert, age: 5}   final id = wilbert['id'] as int;  print(await turtles.getOne(id));  print(await turtles.update(id, name: 'Dilbert', age: 6));  print(await turtles.destroy(id));  print(await turtles.getAll()); // back to just Filbert   await db.close(); // close the pool so the program can exit}

import 'dart:convert'; import 'package:shelf/shelf.dart';import 'package:shelf_router/shelf_router.dart'; import '../db/connection.dart';import '../json.dart';import '../models/turtle.dart'; class TurtleController {  // one model, sharing the app-wide pool  final Turtle turtles = Turtle(db);   Router get router {    final router = Router();     // GET /turtles    router.get('/', (Request request) async {      return jsonResponse(await turtles.getAll());    });     // GET /turtles/<id> — the regex only matches digits, so int.parse can't throw    router.get('/<id|[0-9]+>', (Request request, String id) async {      final turtle = await turtles.getOne(int.parse(id));      if (turtle == null) return jsonResponse({'error': 'turtle not found'}, status: 404);      return jsonResponse(turtle);    });     // POST /turtles  with a JSON body {"name": "...", "age": 5}    router.post('/', (Request request) async {      final body = jsonDecode(await request.readAsString()) as Map<String, dynamic>;      final created = await turtles.insert(name: body['name'] as String, age: body['age'] as int);      return jsonResponse(created, status: 201);    });     // PUT /turtles/<id>    router.put('/<id|[0-9]+>', (Request request, String id) async {      final body = jsonDecode(await request.readAsString()) as Map<String, dynamic>;      final updated = await turtles.update(int.parse(id), name: body['name'] as String, age: body['age'] as int);      if (updated == null) return jsonResponse({'error': 'turtle not found'}, status: 404);      return jsonResponse(updated);    });     // DELETE /turtles/<id>    router.delete('/<id|[0-9]+>', (Request request, String id) async {      final deleted = await turtles.destroy(int.parse(id));      if (deleted == null) return jsonResponse({'error': 'turtle not found'}, status: 404);      return jsonResponse(deleted);    });     return router;  }}

import 'package:firstproject/controllers/turtle_controller.dart'; // Configure routes.final _router = Router()  ..get('/', _rootHandler)  ..get('/echo/<message>', _echoHandler)  ..mount('/first', FirstController().router.call)  ..mount('/turtles', TurtleController().router.call);

curl http://localhost:8080/turtlescurl -X POST http://localhost:8080/turtles -H "Content-Type: application/json" -d '{"name": "Wilbert", "age": 5}'curl http://localhost:8080/turtles/2curl -X PUT http://localhost:8080/turtles/2 -H "Content-Type: application/json" -d '{"name": "Dilbert", "age": 6}'curl -X DELETE http://localhost:8080/turtles/2curl http://localhost:8080/turtles/999