Support table and column comments modeled in Prisma

[bill-coulam]

Problem

The relational database engines I am familiar with (Oracle, postgres, SQL Server, etc.) all have an internal "data dictionary" that contains metadata about all the schema and data objects, like tables, views, materialized views, indexes, columns, etc. They all allow comments on these objects to be declared using DDL statements such as:

COMMENT ON COLUMN mytable.mycolumn IS 'This is what this column holds. Here is the history. Here is a caveat and a weird case. etc.'

Prisma does not support documenting the schema in the database's internal data dictionary.

Data definitions are critical. I can't begin to express how important this is for future engineers on a given project, for data engineers, report writers, data scientists, InfoSec, legal, data catalog/glossary tools and efforts, governance, etc.

Suggested solution

Add an attribute to the Prisma modeling spec, something like @comment("Here is my awesome comment about this data item.") that can be placed to the right of each column, or parameterized version like @comment("my_table_name", "Here is the full business definition of this table and how it is used.") for a table or enum. When Prisma finally supports views and materialized views, those should allow comments as well.

Take each @comment string and generate and run a comment DDL statement to insert it into the underlying DB data dictionary, right after creating the table or data object. If the underlying DB engine doesn't support comments, then Prisma would forego generating and running the comment statements. In this case, the definitions in the Prisma model could still be useful for the team and data catalog tools.

Alternatives

As of now, my teams have to write manual DDL scripts, and run them after Prisma has done its model migration into the DB schema. It is not ideal and clunky.

Additional context

If I weren't in a hurry, I'd investigate all the databases for which you have built native connectors, to see if which ones support data dictionary comments.

[yi-ge]

good idea!

[janpio]

Potentially related issues:

• Migrate comments to the database: Comment support for Prisma migrate #4818
• Introspect comments from the database: prisma pull should add /// comments based on postgres's COMMENT #8896

[StringKe]

Does anyone know if the implementation of @comment needs to change the datamodel => transform related code in the prisma-engines warehouse?

ref: https://github.com/prisma/prisma-engines/tree/master/libs/datamodel/core/src/transform/ast_to_dml/db/attributes

[yi-ge]

@StringKe I am also trying to do this, +weixin?

[StringKe]

@StringKe I am also trying to do this, +weixin?

prisma/prisma-engines#2737

The more basic functionality has been implemented within this PR.
已经在这个 PR 内实现了比较基础的功能。

[janpio]

Prisma Schema Language already supports comment that are added to the internal data structures with triple slash comments: https://www.prisma.io/docs/concepts/components/prisma-schema#comments What does not happen yet, is that we introspect comments from databases and render them into triple slash comment, or that we persist triple slash comments to tables or columns when migrating the Prisma schema to a database schema.

The suggested @comment attribute per model or field would clash with that, so an alternative solution might be to just reuse these existing comments to store the same information. Or is there a reason why this might be a bad idea?

(On the other hand, we might also replace triple slash comments with @comment instead as it is a potentially better way to connect comments to their "objects")

[StringKe]

I recommend using the @comment method.

@comment is a comment in the database
/// is to generate typescript type comments

[janpio]

That would be very explicit 👍 What if you want these to be the same though? Shouldn't database descriptions for example make their way into the Prisma Client after an initial prisma db pull?

[xiangnanscu]

@janpio strongly recommend using @comment method! The /// is implicit.

[StringKe]

That would be very explicit 👍 What if you want these to be the same though? Shouldn't database descriptions for example make their way into the Prisma Client after an initial prisma db pull?

Yes, the in-database comment should be added to the prisma schema on initialization

[raarts]

I'm very afraid I'll end up writing two comment lines for each field, one for TypeScript, and one for the database documentation. Experience tells me, that in practice these two are quite often the same. And in exceptional cases can be worded such, that they're useful in both contexts.

So how about a global directive: generate_db_comments_from_slash_comments (please come up with a better name). And the occasional @comment directive to override this on a per field/table basis.

[bill-coulam]

I agree with @raarts
Best if a data object or field is described only once, and a global directive tells Prisma whether to also generate an in-database comment, with the option to override if for some odd reason the description in the database needed to read differently than the one in typescript.

[boblauer]

I'm very afraid I'll end up writing two comment lines for each field, one for TypeScript, and one for the database documentation. Experience tells me, that in practice these two are quite often the same. And in exceptional cases can be worded such, that they're useful in both contexts.

If you have the comment inline in a @comment method, why would you also need the TS comment?

[raarts]

@boblauer I was responding to @StringKe , who said:

I recommend using the @comment method.

@comment is a comment in the database
/// is to generate typescript type comments

The TS comments are presumably used for generating comments in IDE's TS help.
To which I said: they serve the same purpose and I expect them generally to be the same.

[boblauer]

The TS comments are presumably used for generating comments in IDE's TS help.

I wouldn't expect that to be the case. When you're using the actual prisma models, they're completely separate from the prisma schema, that was simply the blueprint to generate those models. I can't see any of the comments in my schema file when I use the actual prisma models in VS Code, they're only visible if I look at the schema file itself.

If there was a @comment function, I would assume that would make it easier for Prisma to also generate TS comments within the models themselves, which would be the best of both worlds.

[raarts]

I checked and indeed the triple slash comments end up in the index.d.ts file of the generated code:

prisma file:

model Event {
  id            Int       @id @default(autoincrement()) /// unique id for this event

generated typescript:

export type Event = {
  /**
   * unique id for this event 
   */
  id: number

[boblauer]

Interesting, maybe it wasn't working for me because i had the comment on it's own line above the column.

If the @comment function also added a TS comment that would get rid of the need for 2 separate comments.

[raarts]

True, but /// comments are already accepted, have an infrastructure behind them, and are used for other purposes in various plugins. Moving to @comments would not be backwards compatible.

And to me, using triple slashes already are intuitive and organic.

[janpio]

(No need to continue this discussion, we are aware of the different potential or real approaches to define comments in Prisma Schema and will consider both when we look into resolving this issue. Right now we have no further opinion than that triple slash comments are already implement to solve another use case.)

[MaximusMcCann]

PLEASE 🙏

[markthepixel]

Really need this, it's such a pain without it. Save us time please!!!!!

[unstoppable-allensun]

Can this please be revisted? Our team could really use this feature.

[sudoryan]

I think this is a great idea and would really save me a lot of time

[gristow]

This issue is also a challenge for us. Because Prisma is not the only way we interact with our database, we would prefer to rely on comments within the database as the single source of truth for schema documentation.

I am wondering whether writing a prisma generator might be a possibility for getting this functionality? I have not taken a close look at the generator API, so, my real question is: in theory, could a generator introspect a database for comments, and add those to the jsdoc style comments that prisma outputs in prisma generate?

(Alternately, I suppose a standalone program could be written to parse the prisma schema file, query the database for comments, and then add them to the schema file. This feels much less clean to me, however.)

[StringKe]

This issue is also a challenge for us. Because Prisma is not the only way we interact with our database, we would prefer to rely on comments within the database as the single source of truth for schema documentation.

I am wondering whether writing a prisma generator might be a possibility for getting this functionality? I have not taken a close look at the generator API, so, my real question is: in theory, could a generator introspect a database for comments, and add those to the jsdoc style comments that prisma outputs in prisma generate?

(Alternately, I suppose a standalone program could be written to parse the prisma schema file, query the database for comments, and then add them to the schema file. This feels much less clean to me, however.)

I have implemented a similar feature if you need it, you can check this pr to maintain a version yourself.

prisma/prisma-engines#2737

Also I have uploaded a parsing support @stringke/mmg which you can use to get the parsed results and generate your own sql

[gristow]

Thanks, @StringKe. We wouldn't want to rely on a non-supported version of the core prisma engine, or a non-supported change to the prisma schema language. But if I have some time I may look at whether it would be possible to use some of your work to create a prisma generator that would do this instead.

[Jyrno42]

A very rudimentary generator that I added into my project that fits our use-case. May be useful for others too.

#! /usr/bin/env ts-node

/**
 * This is a custom generator for Prisma that generates comments for all models fields and
 *  handles creating a migration file for them when comments change.
 * 
 * The comments are generated from the documentation field in the Prisma schema (e.g. the /// comments
 *  in the schema file).
 * 
 * It works based on a lock file of all comment statements. When it detects that the comments have 
 * changed (by comparing the sha256 hash of them), the commited lock file will be updated. In addition,
 * a new migration file will be created with all comments.
 * 
 * For our purposes its not a big issue since running the sql statements that add comments
 *  should be cheap anyway.
 * 
 * This is a workaround to have https://github.com/prisma/prisma/issues/8703 before it is implemented
 * in Prisma itself.
 */

import { promises as fs } from 'fs';
import { createHash } from 'crypto';
import debug from 'debug';

import { parseEnvValue, getDMMF } from '@prisma/internals';
import { EnvValue, GeneratorOptions, generatorHandler } from '@prisma/generator-helper';

const debugLog = debug('prisma:generate-comments');

async function generateModelComment(model: any): Promise<string[]> {

    const modelName = model.dbName ?? model.name;

    const commentStatements: string[] = [];
    
    model.fields.forEach((field: any) => {
        if (!field.documentation) {
            return;
        }

        debugLog(`Generating comment for ${modelName}.${field.name}...`);

        const escapedComment = field.documentation?.replace(/'/g, "''") ?? '';

        const commentTemplate = `COMMENT ON COLUMN "${modelName}"."${field.name}" IS '${escapedComment}';`;
        commentStatements.push(commentTemplate);
    });

    return [
        `-- Model ${modelName} comments`,
        '',
        ...commentStatements,
        ''
    ];
};

async function fileHash(file: string, allowEmpty = false): Promise<string> {
    try {
        const fileContent = await fs.readFile(file, 'utf-8');

        // now use sha256 to hash the content and return it
        return createHash('sha256').update(fileContent).digest('hex');
    } catch (e: any) {
        if (e.code === 'ENOENT' && allowEmpty) {
            return '';
        }

        throw e;
    }
}

async function lockChanged(lockFile: string, tmpLockFile: string): Promise<boolean> {
    return await fileHash(lockFile, true) !== await fileHash(tmpLockFile);
} 

export async function generate(options: GeneratorOptions) {
  const outputDir = parseEnvValue(options.generator.output as EnvValue);
  await fs.mkdir(outputDir, { recursive: true });

  const prismaClientProvider = options.otherGenerators.find(
    (it) => parseEnvValue(it.provider) === 'prisma-client-js',
  );

  const prismaClientDmmf = await getDMMF({
    datamodel: options.datamodel,
    previewFeatures: prismaClientProvider?.previewFeatures,
  });

  const promises: Promise<string[]>[] = [];

  prismaClientDmmf.datamodel.models.forEach((model: any) => {
    debugLog(`Generating comment for ${model.name}...`);
    promises.push(generateModelComment(model));
  });

  const allStatements = await Promise.all(promises);

  const tmpLock = await fs.open(`${outputDir}/.comments-lock.tmp`, 'w+');

  await tmpLock.write('-- generator-version: 1.0.0\n\n');

  // concat all promises and separate with new line and two newlines between each model
  const allStatementsString = allStatements.map((statements) => statements.join('\n')).join('\n\n');

  await tmpLock.write(allStatementsString);
  await tmpLock.close();

  // compare hashes of tmp lock file and existing lock file
  // if they are the same, do nothing
  // if they are different, write tmp lock file to lock file
  // if lock file does not exist, also write tmp lock file to lock file
  const isChanged = await lockChanged(`${outputDir}/.comments-lock`, `${outputDir}/.comments-lock.tmp`);

  if (isChanged) {
    await fs.copyFile(`${outputDir}/.comments-lock.tmp`, `${outputDir}/.comments-lock`);

    // when lockfile changed we generate a new migration file too
    const date = new Date();
    date.setMilliseconds(0);

    const dateStr = date.toISOString().replace(/[:\-TZ]/g, '').replace('.000', '');
    const migrationDir = `prisma/migrations/${dateStr}_update_comments`;

    console.log(`Lock file changed, creating a new migration at ${migrationDir}...`);

    await fs.mkdir(migrationDir, { recursive: true });

    await fs.copyFile(`${outputDir}/.comments-lock`, `${migrationDir}/migration.sql`);
  } else {
    console.log('No changes detected, skipping creating a fresh comment migration...');
  }

  // always delete tmp lock file
  await fs.unlink(`${outputDir}/.comments-lock.tmp`);

  console.log('Comment generation completed');
}


generatorHandler({
  onManifest() {
    return {
      defaultOutput: 'comments',
      prettyName: 'Prisma Database comments Generator',
    };
  },
  onGenerate: generate,
});

E: generateModelComment needs to probably be updated if your using more complex things in your schema like custom fields names. We are not doing that so this fits our case well.
E2: Fixed sql syntax issue

[janpio]

@gristow Generally, a generator only gets some information from the schema, and then can run any code it wants and use that information there. I don't know if that is what you are looking for. Introspection (that reads the database to get information) is a different system all together for example.

[woss]

@Jyrno42 thanks so much for this solution, this was the critical part to enable almost-full functionality of postgraphile.

For others who might want to know how to use this here is a quick overview of how I use it:

1. save the snippet in the src/commentsGenerator.ts
2. remove shebang (#! /usr/bin/env ts-node) if you have local ts-node and not global
3. add this to your schema.prisma (or your schema file name) . this assumes you have ts-node in package.json

generator comments {
  provider = "ts-node src/commentsGenerator.ts"
}

4. add the scripts to the package.json

{
  // ...
  "scripts": {
    // ...
    "dev:deploy": "prisma migrate deploy",
    "dev:migrate": "prisma migrate dev && pnpm dev:deploy"
    // ...
  }
  // ...
}

5. now you can on development just run it like pnpm dev:migrate and it will add the comments as well

[Jyrno42]

You're welcome @woss. If you (or anyone else) wants to turn this into a Gist or an actual package then feel free. All I ask is that maybe I can get an honorary mention somewhere (and you send a link to the thing here so I can change my project to use it too down the line).

[woss]

@Jyrno42 i already have it as a package in my project. if there is an interest I will spend a bit more time and publish it as cjs and esm flavors.

OFC you get the mention and link to the source code as I already did in the file I have :)

I would like to know the following: can we somehow tap into the migration process to ditch the deploy?

[wl1092212424]

Generate field comments for prisma

1. Overview

1. Thank you prisma for a great job
2. Thanks to the author @Jyrno42, I modified the code based on the code he provided
3. Thank @ woss provide [detailed steps] (Support table and column comments modeled in Prisma #8703 (comment))
4. Because this code is compatible with prisma, the latest version does not work, and it is compatible with PostgreSQL, I use mysql8.0
5. Use as few dependencies as possible (remove @prisma/internals and debug)

2. Needs

1. Add comments to the model below
2. The database must be mysql
3. Adapt prisma version 5.0

/// this is my table comment
model MyTestTable {

  postId      Int      @id @default(autoincrement()) @map("post_id") /// primaryKey
  postTitle   String   @map("post_title") /// postIdid
  postContent String   @map("post_content") /// postContent
  createdAt   DateTime @map("created_at") /// createdAt
  updatedAt   DateTime @map("updated_at") /// updatedAt

  @@map("my_test_table")
}

Note that both are snake names in the database and hump names in the model

3. Code

#! /usr/bin/env ts-node

/**
 * This is a custom generator for Prisma that generates comments for all models fields and
 *  handles creating a migration file for them when comments change.
 *
 * The comments are generated from the documentation field in the Prisma schema (e.g. the /// comments
 *  in the schema file).
 *
 * It works based on a lock file of all comment statements. When it detects that the comments have
 * changed (by comparing the sha256 hash of them), the commited lock file will be updated. In addition,
 * a new migration file will be created with all comments.
 *
 * For our purposes its not a big issue since running the sql statements that add comments
 *  should be cheap anyway.
 *
 * This is a workaround to have https://github.com/prisma/prisma/issues/8703 before it is implemented
 * in Prisma itself.
 */

import { promises as fs } from "fs";
import { createHash } from "crypto";

import { Prisma } from '@prisma/client'


// import { parseEnvValue } from '@prisma/client';
import { GeneratorOptions, generatorHandler } from "@prisma/generator-helper";
import { snakeCase } from "lodash";

// const debugLog = debug('prisma:generate-comments');

async function generateModelComment(model: any): Promise<string[]> {
  
  const modelName = model.dbName ?? model.name;

  const commentStatements: string[] = [];

  const tableComment = model.documentation
  // comment table
  if (tableComment) {
    let tableCommentStr = `ALTER TABLE ${modelName} COMMENT '${tableComment}';`;
    commentStatements.push(tableCommentStr);
  }

  // comment fields
  model.fields.forEach((field: any) => {
    if (!field.documentation) {
      return;
    }

    // debugLog(`Generating comment for ${modelName}.${field.name}...`);

    const escapedComment = field.documentation?.replace(/'/g, "''") ?? "";

    // const commentTemplate = `COMMENT ON COLUMN "${modelName}"."${field.name}" IS '${escapedComment}';`;
    
    let fieldName = snakeCase(field.name) 

    const commentTemplate = `SET @column_type = ( SELECT column_type FROM information_schema.COLUMNS WHERE table_name = '${modelName}' AND column_name = '${fieldName}' );
    SET @alter_sql = CONCAT( 'ALTER TABLE ${modelName} MODIFY COLUMN ${fieldName} ', @column_type, ' COMMENT ''${escapedComment}''' );
    PREPARE alter_statement  FROM @alter_sql;
    EXECUTE alter_statement;
    DEALLOCATE PREPARE alter_statement;`
    commentStatements.push(commentTemplate);
  });

  return [`-- Model ${modelName} comments`, "", ...commentStatements, ""];
}

async function fileHash(file: string, allowEmpty = false): Promise<string> {
  try {
    const fileContent = await fs.readFile(file, "utf-8");

    // now use sha256 to hash the content and return it
    return createHash("sha256").update(fileContent).digest("hex");
  } catch (e: any) {
    if (e.code === "ENOENT" && allowEmpty) {
      return "";
    }

    throw e;
  }
}

async function lockChanged(lockFile: string, tmpLockFile: string): Promise<boolean> {
  return (await fileHash(lockFile, true)) !== (await fileHash(tmpLockFile));
}

export async function generate(options: GeneratorOptions) {
  const outputDir = "./migrations";
  await fs.mkdir(outputDir, { recursive: true });

  const prismaClientProvider = options.otherGenerators.find((it) => "prisma-client-js" === "prisma-client-js");


  const promises: Promise<string[]>[] = [];
  Prisma.dmmf.datamodel.models.forEach((model: any) => {
    // debugLog(`Generating comment for ${model.name}...`);
    promises.push(generateModelComment(model));
  });

  const allStatements = await Promise.all(promises);

  const tmpLock = await fs.open(`${outputDir}/.comments-lock.tmp`, "w+");

  await tmpLock.write("-- generator-version: 0.0\n\n");

  // concat all promises and separate with new line and two newlines between each model
  const allStatementsString = allStatements.map((statements) => statements.join("\n")).join("\n\n");

  await tmpLock.write(allStatementsString);
  await tmpLock.close();

  // compare hashes of tmp lock file and existing lock file
  // if they are the same, do nothing
  // if they are different, write tmp lock file to lock file
  // if lock file does not exist, also write tmp lock file to lock file
  const isChanged = await lockChanged(`${outputDir}/.comments-lock`, `${outputDir}/.comments-lock.tmp`);

  if (isChanged) {
    await fs.copyFile(`${outputDir}/.comments-lock.tmp`, `${outputDir}/.comments-lock`);

    // when lockfile changed we generate a new migration file too
    const date = new Date();
    date.setMilliseconds(0);

    const dateStr = date
      .toISOString()
      .replace(/[:\-TZ]/g, "")
      .replace(".000", "");
    const migrationDir = `prisma/migrations/${dateStr}_update_comments`;

    console.log(`Lock file changed, creating a new migration at ${migrationDir}...`);

    await fs.mkdir(migrationDir, { recursive: true });

    await fs.copyFile(`${outputDir}/.comments-lock`, `${migrationDir}/migration.sql`);
  } else {
    console.log("No changes detected, skipping creating a fresh comment migration...");
  }

  // always delete tmp lock file
  await fs.unlink(`${outputDir}/.comments-lock.tmp`);

  console.log("Comment generation completed");
}

generatorHandler({
  onManifest() {
    return {
      defaultOutput: "comments",
      prettyName: "Prisma Database comments Generator",
    };
  },
  onGenerate: generate,
});

As with @woss, I'm just talking about what's new

tips:

Set the module in tsconfig.json to CommonJS so that your ts-node can execute

[EloB]

What is the chances of this feature reaching core? I model my database with Prisma but I consume it using postgraphile and they use "smart tags". This feature would really fit that :)

[janpio]

Chance of us building this is very high, but there is no timeline for when that happens.

[EloB]

@wl1092212424 Did you mange to get @@schema to work with this script? I can't find schema in the script.

[samislam]

Any updates on this?

[zC4sTr0]

Any updates on this issue? Seems like there's a PR prisma/prisma-engines#2737 on this already
This feature would be much appreciated @janpio @Jolg42

[divmgl]

Yep, in need of PostgreSQL schema comments to clearly delineate when a column should be used over another (they're both JSON columns).

[Mateusnasciment]

alguma atualização ?

[johannbuscail]

Any update @janpio ?

[janpio]

No, otherwise we would have posted an update here @johannbuscail.

[daifuyang]

please do it

[normancarcamo]

Thank you authors for such a great job, I'm in love with Prisma!

But... comments are necessary for DBs though, I hope this can be taken into consideration in the future because they are needed.