Apple DVD-ROM drives are ATAPI and not SCSI.
You will need a drive which is supported by MacOS, or that is supported by a third-party driver software like
this one.
Additionally, you will want to get a drive which is compatible with the built-in driver embedded in ROM. This will allow you to boot from the drive, if needed.
Here is where to begin researching drives that meet both of these criteria.
Here is a searchable database which will allow you to look up specific drive models to see if they are Mac-compatible.
Now, with all this being said, my recommendation is that you contact a local Apple repair place and simply order the drive as a replacement part. You will get guaranteed compatibility this way.
Good luck!