Menurut tinjauan StackOverflow 2022, 46% pembangun profesional menilai MySQL sebagai pangkalan data paling popular untuk digunakan di ruang kerja. Sebagai pengaturcara, kita tidak perlu diganggu dengan menulis kod berulang kali yang sebaliknya tersedia, teguh dan diuji dengan baik.
Namun masalah ini masih berterusan dalam ruang REST API, walaupun proses pelaksanaannya berada pada tahap ini dalam hafalan masa, berulang dan terdedah kepada ralat dan pengawasan. Pengawasan ini mahal kerana beberapa sebab:
Nasib baik bagi kami pengaturcara, platform DreamFactory boleh membebaskan pasukan anda dengan mudah daripada semua masalah ini dan banyak lagi dengan menawarkan penyelesaian terpusat untuk dokumentasi, penjanaan dan keselamatan API. Dalam tutorial ini, anda akan mengetahui betapa mudahnya membina, mengamankan dan menggunakan API REST untuk pangkalan data MySQL anda.
Daftar Kandungan
API MySQL, ialah satu set fungsi, protokol dan alatan yang membenarkan pembangun berinteraksi dengan pangkalan data MySQL secara pemprograman daripada aplikasi atau skrip mereka.
API ini menyediakan cara untuk aplikasi menghantar pertanyaan ke pangkalan data MySQL, mendapatkan semula data, mengubah suai data dan melaksanakan pelbagai operasi pangkalan data. Ia bertindak sebagai lapisan perantara antara kod aplikasi dan pelayan pangkalan data, memudahkan komunikasi yang lancar dan manipulasi data.
API MySQL biasa termasuk MySQL Connector/ODBC, MySQL Connector/J (untuk Java), MySQLi (untuk PHP) dan banyak lagi, setiap satunya disesuaikan dengan bahasa pengaturcaraan atau rangka kerja tertentu.
MySQL API memainkan peranan penting dalam pembangunan perisian dengan memudahkan interaksi antara aplikasi dan pangkalan data MySQL.
API ini bertindak sebagai 'di antara', mengabstraksi kerumitan operasi pangkalan data dan menyediakan antara muka piawai untuk pembangun. Kepentingan mereka terletak pada membolehkan aplikasi menyimpan, mendapatkan semula, mengubah suai dan mengurus data dengan cekap.
MySQL API menawarkan keserasian dengan pelbagai bahasa pengaturcaraan dan rangka kerja, membolehkan pembangun bekerja dengan MySQL menggunakan alatan pilihan mereka. Mereka meningkatkan keselamatan dengan menawarkan ciri seperti kenyataan yang disediakan dan pengesahan input untuk melindungi daripada kelemahan keselamatan biasa seperti suntikan SQL.
Ciri pengoptimuman prestasi seperti pengumpulan sambungan dan caching membantu meningkatkan kecekapan interaksi pangkalan data. Kemudahalihan yang diberikan oleh API MySQL bermakna aplikasi berpotensi beralih kepada sistem pangkalan data yang berbeza tanpa baik pulih kod utama, manakala keupayaan berskalanya menjadikannya sesuai untuk mengendalikan beban kerja yang meningkat.
API MySQL mendapat manfaat daripada sokongan meluas komuniti pembangun MySQL, memastikan banyak sumber, perpustakaan dan alatan untuk pembangun.
DreamFactory boleh menjana API REST untuk pelbagai pangkalan data, antaranya MySQL, Microsoft SQL Server, Oracle, PostgreSQL dan MongoDB.
Untuk berbuat demikian, anda akan log masuk ke antara muka pentadbiran DreamFactory, navigasi ke Services dan kemudian masukkan antara muka penciptaan perkhidmatan dengan mengklik pada butang Create yang terletak di sebelah kiri skrin. Dari sana anda akan memilih jenis perkhidmatan MySQL dengan menavigasi ke API Generation > Pangkalan data, kemudian pilih MySQL daripada pilihan pangkalan data kami (lihat tangkapan skrin di bawah).
Next you’ll be prompted to provide a name, label, and description (below screenshot). The latter two are used just for reference purposes within the administration interface, however the name value is particularly important because as you’ll soon see it will comprise part of the API URL.
Finally, click on the Config tab. Here you’ll be prompted to provide the database connection credentials (see below screenshot). This should really be nothing new; you’ll supply a host name, username, password, and database. Additionally, you can optionally specify other configuration characteristics such as driver options, the timezone, and caching preferences. For the purpose of this tutorial I’ll stick to the required fields and leave the optional features untouched.
With the credentials in place, just press the Save button at the bottom of the screen, and believe it or not the REST API has been generated!
Along with the API, DreamFactory will also auto-generate an extensive set of interactive Swagger documentation for your API. You can access it by clicking on the API Docs tab located at the top of the administration interface, and then selecting the newly generated service by name. You’ll be presented with 44 endpoints useful for executing stored procedures, carrying out CRUD operations, querying views, and much more. For instance the following screenshot presents just a small subset of newly generated MySQL REST API endpoints!
All DreamFactory-generated APIs are automatically protected by (at minimum) an API key. You can optionally authenticate users using basic authentication, SSO, or Directory Services (LDAP and Active Directory). Furthermore, you can associate each API key and/or user with a *role* which determines exactly what services the user is allowed to access. Not only that, you can restrict interactions to a specific database table or set of tables, a specific endpoint(s), and even restrict which HTTP methods are allowed.
Securing the users responsible for interacting with the MySQL database on behalf of your APIs is an incredibly important part of the API development and integration process. See our other recommendations on Improved Data Security with MySQL Privileges and DreamFactory
As an example, let’s create a new role which restricts the associated API key to interacting with a single table in a read-only fashion within the newly created MySQL API. To do so, navigate to the Roles tab, and click the Create button. You’ll be presented with the interface found in the below screenshot.
Next, click the Access tab. This is where you’ll define what the role can do. In the below screenshot you’ll see I’ve limited the role to interacting with the MySQL service, and within that service the role can only interact with the _table/employees* endpoint via the GET method. We’re on lockdown baby!
Save the role by clicking the Save button. Now we’ll create a new API key and associate the key with this role. To do so, click on the API Keys tab located under teh API types dropdown and select API Keys, and then click the ‘+’ button. Assign your new App a name and description, ensure it is set to Active, and then assign it the default role of MySQL just as I’ve done in the below screenshot. Regarding the App Location setting, presuming you plan on interacting with the API via a web or mobile application, or via another web service, then you’ll want to select “No storage required”.
Press the Save button and you’ll be returned to the Apps index screen where the new API key can be copied! Copy the key into a text file for later reference.
We have one final configuration step before being able to test the API from outside the DreamFactory administration interface. You’ll need to enable CORS (Cross-Origin Resource Sharing) for the new API. You can find it under the Config dropdown and selecting CORS. For purposes of demonstration, you can set the default CORS setting as I’ve done in the below screenshot, which will allow API-restricted traffic from all network addresses:
With the API generated, API key and associated role created, and CORS configured, you’re ready to begin interacting with the API via a client! I like to use Insomnia for HTTP testing on MacOS, however another popular solution is Postman.
In the following screenshot I’m using Insomnia to contact the /api/v2/_table/employees endpoint using a GET request.
Recall that we’ve locked down this API key to only interact with the /api/v2/_table/employees/* endpoints using the GET method. So what happens if we try to POST to this table? A 401 (Unauthorized) status code is returned, as depicted in the following screenshot:
Obviously, DreamFactory isn’t the only solution available. Another way you can create a MySQL REST API is by manually hand coding it, however it takes considerably longer and is more difficult. There are are also other concerns to worry about when hand crafting an API, such as API security. Here are a few brief tips on how you can start manually coding your next API.
The first step to building a MySQL REST API is to set up a web server, such as Apache or Nginx, which will handle incoming HTTP requests from clients and respond with appropriate content. You’ll also need to install PHP on the server, which is a server-side scripting language that can interact with MySQL to retrieve or store data. Finally, you’ll need to install MySQL, which is a relational database management system that can store and manage data in tables. Once you have all these components set up, you’ll be ready to start building the REST API.
A database is a collection of related data that is organized in a structured way, such as in tables with columns and rows. MySQL is a popular relational database management system that allows you to create and manage databases and tables.
To create a database in MySQL, you can use the CREATE DATABASE command in the MySQL command-line interface or a tool like phpMyAdmin.
To connect to the MySQL database from PHP, you can use one of several extensions such as MySQLi or PDO. Once you have established a connection, you can execute SQL queries to retrieve data from the database. The SQL queries should be designed to retrieve the data you want to expose through the API, and should be parameterized to prevent SQL injection attacks.
Once you have retrieved the data from the database, you can format it as JSON using PHP’s built-in functions. JSON is a lightweight data format that is easy to parse and is supported by many programming languages.
PDO is a powerful and flexible extension that provides a consistent interface for accessing databases in PHP. PDO can be used with a variety of database drivers, including MySQL, and provides features such as prepared statements and parameter binding, which can help to prevent SQL injection attacks.
To use PDO with MySQL, you’ll need to create a new PDO object and pass in the connection details for your database. You can then prepare an SQL statement using the prepare() method, bind any parameters using the bindParam() or bindValue() methods, and execute the statement using the execute() method. Finally, you can fetch the results of the query using methods such as fetch(), fetchAll(), or fetchColumn().
The json_encode() function is a built-in PHP function that can be used to convert data into a JSON-formatted string. You can pass any data type to the function, such as an array or object, and it will be converted to a JSON string that can be easily consumed by client applications.
Once you have the JSON-formatted string, you’ll need to output it to the client using the appropriate HTTP headers. In PHP, you can set the headers using the header() function, which allows you to specify the content type and other metadata about the response. For example, to output JSON data, you can set the Content-Type header to "application/json".
By outputting the data in JSON format and setting the appropriate HTTP headers, you can ensure that your REST API is easily consumable by client applications and adheres to standard web conventions.
HTTP requests are the way that clients (such as web browsers or mobile apps) communicate with your API to request data or perform other actions. To handle these requests, you’ll need to write PHP code that can process the request and return an appropriate response.
PHP provides several functions that can be used to access information about the current request, such as $_SERVER, $_GET, $_POST, and $_REQUEST. These variables contain information such as the HTTP method (e.g. GET or POST), the URL path, and any query parameters or request body data.
To handle different types of requests, you’ll need to write PHP code that can analyze the incoming data and determine what action to take. For example, if the client is making a GET request to retrieve data from the API, your code might execute a SQL query to retrieve the data and return it as a JSON response. If the client is making a POST request to create a new record, your code might insert the new data into the MySQL database and return a success message.
To implement error handling and validation in your API, you’ll need to write PHP code that can detect and respond to errors and validate user input. PHP provides several built-in functions that can be used for error handling, such as trigger_error() or set_error_handler(). For validation, you may want to use regular expressions, PHP filters, or custom validation functions.
To deploy your API, you’ll need to upload the PHP files and any other required files (such as CSS, JavaScript, or images) to your web server. Depending on your web server configuration, you may need to modify your .htaccess file to ensure that the API routes correctly.
Once your API is deployed, you can test it using tools like cURL or Postman. These tools allow you to send HTTP requests to your API and inspect the response data. You can use these tools to test different types of requests (such as GET, POST, PUT, or DELETE), test error handling and validation, and ensure that the API is returning the correct data in the correct format (such as JSON or XML).
When testing your API, it’s important to pay attention to performance, security, and usability. You should test your API under different conditions (such as high traffic or slow network connections) to ensure that it performs well and remains stable. You should also test your API for security vulnerabilities (such as SQL injection or cross-site scripting), and ensure that it’s easy to use and understand for clients.
Here’s an example PHP code for a simple REST API that retrieves data from a MySQL database:
Believe it or not, we’ve only scratched the surface in terms of what DreamFactory can do for you. If you’d like to see our SQL Server, Oracle, or MongoDB connectors in action, or would like to watch how easy it is to convert a SOAP service to REST without writing any code, why not schedule a demo with our engineering team! Head over to https://www.dreamfactory.com/products and schedule a demo today!
Other Databases DreamFactory Supports:
The post How to Create a MySQL REST API in 6 Easy Steps first appeared on DreamFactory Software- Blog.
以上是Cara Mencipta MySQL REST API dalam Langkah-langkah asy的详细内容。更多信息请关注PHP中文网其他相关文章！
Perbezaan antara teg artikel dan teg bahagian
python dibungkus ke dalam fail boleh laku
Apakah sebab kegagalan untuk menyambung ke pelayan?
penggunaan transactionscope
Bagaimana untuk menggunakan arahan mencari untuk mencari fail dalam linux
Bolehkah percikan api Douyin dinyalakan semula jika ia telah dimatikan selama lebih daripada tiga hari?
Koleksi lengkap tag HTML
Apakah perbezaan antara mysql dan mssql
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
