3 ms·

These sqlc-style libraries are a great solution to the problem of “make running a query as easy as calling a function”, but I’ve always thought SQL’s lack of c

• boxed 1y ago
  I haven't worked with many ORMs, but Django's is imo very good in tackling the lack of composability in SQL.
• jitl 1y ago
  There's two different approaches to solving sql composability issues: 1. Compiles-to-SQL domain specific languages. This category spans from ORM DSLs embedded in another programming language, like Ruby/Rail's ActiveRecord/AREL or Django's ORM; to stand-alone text-based languages like PRQL Pipelined Relational Query Language" (https://prql-lang.org https://prql-lang.org) that a compiler program converts to SQL text or SQL files. The downside to the DSL option is that it requires practitioners be fluent in both the SQL query they want, and in the DLS language - to know how to obtain the SQL query in the DSL. 2. Query fragment literals in the caller programming language, like sql`name = ${name}` in TypeScript (eg https://github.com/gajus/slonik https://github.com/gajus/slonik). These are usually thin abstraction over concatenating a `{ queryText: string[], queryArgs: T[] }` structure. The author only needs to be fluent in SQL, and in the caller language, but do less to save you from the expressive limitations of SQL itself. I've found query fragment composition to be the sweet spot. Easy SQL queries remain trivial to express and understand, since it's Just SQL: sql`SELECT * FROM block WHERE id = ${args.id}` But you can DRY up repetition in the codebase through regular function calls. Abbreviated example from Notion's client code: function selectOfflinePageMetadata(args: { userId: string }) { return sql` SELECT offline_page.id, offline_page.space_id, offline_page.download_status, offline_page.last_downloaded_at, offline_page.last_downloaded_version, offline_page.last_downloaded_sync_cursor, offline_page.target_sync_cursor, CASE WHEN EXISTS ( SELECT 1 FROM offline_action WHERE offline_action.impacted_page_id = offline_page.id AND offline_action.origin_page_id = offline_page.id AND offline_action.autosync_type = 'not_autosynced' ) THEN 1 ELSE 0 END AS is_explicitly_offlined_origin, CASE WHEN EXISTS ( SELECT 1 FROM offline_action WHERE offline_action.impacted_page_id = offline_page.id AND offline_action.origin_page_id = offline_page.id AND offline_action.autosync_type = 'created_offline' ) THEN 1 ELSE 0 END AS is_offline_created_origin, CASE WHEN EXISTS ( SELECT 1 FROM offline_action WHERE offline_action.impacted_page_id = offline_page.id AND offline_action.origin_page_id = offline_page.id AND offline_action.autosync_type = 'frecent' ) THEN 1 ELSE 0 END AS is_autosynced_origin FROM offline_page WHERE offline_page.meta_user_id = ${args.userId} ` } function selectOfflinePageById(args: { userId: string pageId: string }) { const { userId, pageId, } = args return sql` SELECT * FROM (${selectOfflinePageMetadata({ userId })}) WHERE id = ${pageId} ` } function selectOfflineAutosyncedOrigins(args: { userId: string spaceId: string }) { const { userId, spaceId, } = args return sql` WITH offline_page_metadata AS ( ${selectOfflinePageMetadata({ userId })} ) SELECT offline_page_metadata.* FROM offline_page_metadata WHERE space_id = ${spaceId} AND is_autosynced_origin = 1 ORDER BY last_downloaded_at ASC ` } I'm not sure if it solves your "view problem", but it does a pretty good job for _my_ view problem.