Skip to main content

PostGraphile

  • graphile/crystal
    • Crystal Monorepo for Grafast, PostGraphile, pg-introspection, pg-sql2
    • postgraphile
    • GraphQL over PostgreSQL
    • 强调 DB 设计 - 先 DB 后 GraphQL
    • TypeScript+NodeJS 实现
    • 可以独立使用也可以嵌入到 NodeJS 使用
      • 官方推荐作为库使用,因为方便自定义和加插件
# https://www.graphile.org/postgraphile/usage-cli/

# 开发环境参数
postgraphile \
--subscriptions \
--watch \
--dynamic-json \
--no-setof-functions-contain-nulls \
--no-ignore-rbac \
--no-ignore-indexes \
--show-error-stack=json \
--extended-errors hint,detail,errcode \
--append-plugins @graphile-contrib/pg-simplify-inflector \
--export-schema-graphql schema.graphql \
--graphiql "/" \
--enhance-graphiql \
--allow-explain \
--enable-query-batching \
--legacy-relations omit \
--connection $DATABASE_URL \
--schema app_public

# 生产环境
postgraphile \
--subscriptions \
--retry-on-init-fail \
--dynamic-json \
--no-setof-functions-contain-nulls \
--no-ignore-rbac \
--no-ignore-indexes \
--extended-errors errcode \
--append-plugins @graphile-contrib/pg-simplify-inflector \
--disable-graphiql \
--enable-query-batching \
--disable-query-log \ # our default logging has performance issues, but do make sure you have a logging system in place!
--legacy-relations omit \
--connection $DATABASE_URL \
--schema app_public

embded

const express = require('express');
const { postgraphile } = require('postgraphile');

const app = express();

/* Example middleware you might want to put in front of PostGraphile */
// app.use(require('morgan')(...));
// app.use(require('compression')({...}));
// app.use(require('helmet')({...}));

app.use(
postgraphile(process.env.DATABASE_URL || 'postgres:///', 'public', {
// ...
pgSettings: {
statement_timeout: '3000',
},
}),
);

app.listen(process.env.PORT || 3000);

最佳实践

  • 外键添加索引
  • RLS (Row Level Security) 如果要用建议每个表都启用
  • 表授权 SELECT/DELETE,列授权 INSERT/UPDATE
    • 列授权 SELECT 会导致无法优化,不能 SELECT *, RETURNING *, 不能将行作为函数参数
  • 尽量 LANGUAGE sql, 避免 LANGUAGE plpgsql
  • Trigger 添加数字前缀,因为是按序执行的
    • _200_do_a_thing / _800_do_something_else

Schema