GraphQL, Pothos & SQLite: a perfect match

Exploring how GraphQL and SQLite give us a strong foundation for building production applications, with a great developer experience.

1. GraphQL, Pothos & SQLite: a perfect match Matthias Le Brun bloodyowl.io
2. Matthias Le Brun software engineer lead AI hater chief shitpost of fi cer who am I Paris.JS organizer ReScript core team Putain de Code co-founder bsky: @bloodyowl.io
3. so, what stack for a new GraphQL API?
4. query App { viewer { id profile { avatarUrl posts(first: 10) { id content } } } }
5. query App { viewer { id profile { avatarUrl posts(first: 10) { id content } } } } SELECT * FROM user WHERE user_id = ?
6. query App { viewer { id profile { avatarUrl posts(first: 10) { id content } } } } SELECT * FROM user_profile WHERE user_profile_id = ?
7. query App { viewer { id profile { avatarUrl posts(first: 10) { id content } } } } SELECT * FROM user_profile_posts WHERE user_profile_id = ? ORDER BY created_at DESC
8. network calls
9. database network mitigations 1. data-loader cache and group by depth 2. over-fetch based on common access patterns 3. add cache layer between the application and the database
10. fi g. 1: modern software architecture aka «turn function calls into HTTP requests, get billed 3x the cost by AWS, hire dozens of people just to manage the complexity»
11. database SQLite aka: "you probably won't need more for quite a while" has the reputation of being a toy database because we don't reassess technology when hardware evolves dramatically (ie. SSD being slightly faster than hard drives)
12. SQLite WAL Write Ahead Logging understand: reads and writes no longer block each other
13. SQLite limitations → size of your physical storage → hard limit on 281TB for a single database → no long synchronous transactions → 200K read/s → 50K writes/s table with indexes, M1 chip (requires a few fl ags)
14. disaster recovery service to run on your server that incrementally backs up your database over S3, SFTP
15. maintainability how can we make maintaining a big schema manageable?
16. two approaches schema fi rst code fi rst
17. two approaches schema fi rst code fi rst
18. schema builder aka: "oh wow they did think about everything I could need"
19. what is pothos? → schema builder → built over graphql-js → fully type-safe → plugins
20. 1. de fi ne a context type export type RequestContext = { sql: SQL auth: AuthContext | undefined logger: Logger t: Translator req: BunRequest<string> }
21. 2. create a schema builder const GraphqlSchemaBuilder = new SchemaBuilder<{ Context: RequestContext // ... and de fi ne its context type
22. 3. initiate a query/mutation root fi eld GraphqlSchemaBuilder.queryType({}) GraphqlSchemaBuilder.mutationType({}) export { GraphqlSchemaBuilder }
23. 4. de fi ne some GraphQL types const userStatusValues = { Active: { value: "active" }, Deactivated: { value: "deactivated" }, } satisfies Record<string, { value: UserStatus }> export const GraphqlUserStatus = GraphqlSchemaBuilder.enumType("UserStatus", { values: userStatusValues, })
24. 5. de fi ne some GraphQL object refs export const GraphqlUserRef = GraphqlSchemaBuilder .loadableObjectRef<User, string>("User", { load: async (ids: Array<string>, context) => getManyUsersByIds(ids, context.sql), sort: (user) => user.user_id, }) refs represent the object where dependencies don't become circular in ESM data-loader plugin makes it trivial, you can either resolve from the object model or its identi fi er
25. 6. implement GraphQL object export const GraphqlUser = GraphqlUserRef.implement({ fields: (t) => ({ id: t.exposeID("user_id", { nullable: false }), createdAt: t.field({ type: "DateTime", nullable: false, resolve: (user) => new Date(user.created_at), }), fullName: t.exposeString("full_name", { nullable: false }), email: t.exposeString("email", { nullable: false }), status: t.expose("status", { type: GraphqlUserStatus, nullable: false }), }), })
26. 7. expose your object somewhere GraphqlSchemaBuilder.queryField("viewer", (t) => t.field({ type: GraphqlUser, // we don't use auth scopes there in order for the auth process to check // without error if there's a currently logged viewer resolve: (parent, args, context) => context.auth?.type === "User" ? context.auth.userId : null, }), )
27. niceties: error management GraphqlSchemaBuilder.objectType(CloseAccountError, { name: "CloseAccountError", interfaces: [GraphqlError], fields: (t) => ({ message: t.string({ nullable: false, resolve: (parent, args, { t }) => t("api.CloseAccountError.message"), }), }), }) map an error subclass to an object type
28. niceties: error management GraphqlSchemaBuilder.mutationField("closeAccount", (t) => t.withAuth({ user: true }).field({ type: GraphqlSensitiveOperationRef, errors: { types: [CloseAccountError], dataField: { name: "sensitiveOperation", }, }, // ... de fi ne "domain errors" in mutation fi elds
29. niceties: error management union CloseAccount = | CloseAccountError | CloseAccountSuccess | ForbiddenError | UnexpectedError | ValidationError type Mutation { # ... closeAccount(input: CloseAccountInput!): CloseAccount # ... } get domain errors in your schema contract
30. niceties: permissions management canViewAccount: async (accountId) => { if (accountId == undefined) { return true } if (context.auth != null && context.auth.type === "User") { const member = await context.accessControl.getAccountMember({ userId: context.auth.userId, accountId, }) return ( member?.status === "active" && member?.can_view_account_bool === true ) } return false }, // ... de fi ne custom permission checks with arguments
31. niceties: permissions management // ... number: t.exposeString("account_number", { nullable: false, authScopes: (parent) => ({ $any: { canViewAccount: parent.account_id, permission: "accounts_read", }, }), }), // ... fi lter on $any or $all strategies
32. other plugins → federation → subgraphs → zod validation → relay → prisma
33. performance how fast does this stack run?
34. performance 20 columns table with 1M rows, no optimization, on a single core (Intel Xeon E3 1220) on a 30€/month machine query AccountTransfers( ... ) { __typename account(input: $account) { __typename id transactions(first: 10) { __typename pageInfo { __typename hasNextPage endCursor } edges { __typename node { __typename # ... } } } }
35. testing how about having tests that verify behavior? and ideally without having to instantiate 30 modules per suite
36. testing comfort → consuming a GraphQL API → simple assertions → isolated run → fl at
37. testing comfort: consuming GQL gql.tada TypeScript plugin, works without extra compilation step (some commands needed to cache when having a lot of occurrences though)
38. testing comfort: consuming GQL const closeAccountMutation = api.gql( /* gql */ ` mutation CloseAccount($input: CloseAccountInput!) { closeAccount(input: $input) { __typename ... on CloseAccountSuccess { sensitiveOperation { id url } } ... on Error { message } } } `) inlined in your tests!
39. testing comfort: isolation const sql = new SQL(":memory:", { safeIntegers: true }) embedded databases make isolation virtually free (PGLite also does a great job with a decent performance penalty)
40. test("Close account", async () => { using api = testApi() const accountId = await createTestAccount(api) // ... const account = await api.query( accountQuery, { input: { id: accountId }, first: 10, }, api.tester, ) assertIsDefined(account.account) assertIsDefined(account.account.iban) }) new app context, cleans up at the end of the function execution assertions re fi ne the type of the value for subsequent lines
41. testing experience
43. conclusion 1. reassess technology when hardware evolves also don't let the cloud make us forget that it's just computers underneath
44. conclusion 2. tradeoffs are contextual over-anticipation can kill velocity, decide for a near future, not a distant one
45. conclusion 3. developer experience makes quality easier to get reduce accidental complexity, focus on the real one
46. Matthias Le Brun frontend engineer lead AI hater chief shitpost of fi cer thank you! Paris.JS organizer ReScript core team Putain de Code co-founder bsky: @bloodyowl.io