I would like to burn my CDs into the music library on my MacBook Pro and view DVD's there also.

If you do not need to take the player with your while traveling, I recommend a tray-loading model with an independent power supply for greatest reliability. I am considering buying this one for a new iMac we are getting:

https://eshop.macsales.com/item/OWC/MR3USD24/

You will need a USB-A to USB-C adapter similar to this for any new Mac :

OWC USB-A to USB-C (USB 3) Adapter Cable (use USB-A devices on USB-C)

or this:

Anker USB C Adapter (2 Pack),High-Speed Data Transfer, USB-C to USB 3.0 Female Adapter for MacBook Pro 2020, iPad Pro 2020, Samsung Notebook 9, Dell XPS and More Type C Devices

The above OWC drive works for travel as well but, if it is too bulky or heavy for your needs, you can get a compact bus-powered drive. There are tray-load compact models. I recommend one whose data/power cable is replaceable, i,e.,not permanently attached to the device. A cat of puppy chewin the cable manea s new device is the cable is not replacement. Lots of drive examples on Amazon if you search for "External CD/DVD drive"

Apple no longer sells its USB SuperDrive; it was a badly outdated design anyway, so I have no hesitation in recommending you go after-market for one.