This page shows you how to invoke online predictions from a
Cloud SQL instance.

Cloud SQL lets you get online predictions in
your SQL code by calling the
`ml_predict_row()` function.

## Before you begin

Before you can invoke online predictions from a Cloud SQL instance,
you must prepare your database and select an appropriate ML model.

### Prepare your database

1. To prepare your database,
   [set up integration between Cloud SQL and Vertex AI.](https://docs.cloud.google.com/sql/docs/postgres/integrate-cloud-sql-with-vertex-ai.md.txt)

2. Grant permissions for database users to use the `ml_predict_row()` function
   to run predictions:

   1. Connect a `psql` client to the primary instance, as
      described in [Connect using a a psql client](https://docs.cloud.google.com/sql/docs/postgres/connect-admin-ip.md.txt).

   2. At the `psql` command prompt, connect to the database and grant permissions:

      ```sql
      \c DB_NAME

      GRANT EXECUTE ON FUNCTION ml_predict_row TO USER_NAME;
      ```

      Replace the following:
      - <var translate="no">DB_NAME</var>: the name of the database for which you're
        granting permissions

      - <var translate="no">USER_NAME</var>: the name of the user for whom you're granting
        permissions

### Select an ML model

When you call the `ml_predict_row()`
function,
you must specify the location of an ML model. The model that you specify
can be one of these:

- A model that's running in the [Vertex AI Model Garden](https://docs.cloud.google.com/vertex-ai/docs/start/explore-models).

  The `ml_predict_row()`
  function supports
  invoking predictions only on tabular or custom models.
- A Vertex AI model with an active endpoint that you have Identity and Access Management (IAM) permission to access.

  Cloud SQL doesn't support [private endpoints](https://docs.cloud.google.com/vertex-ai/docs/predictions/using-private-endpoints)
  for getting online predictions.

## Invoke online predictions

You can use the `ml_predict_row()`
SQL function
to invoke online predictions against your data.

The format of the function's initial argument depends on whether the ML model
that you want to use is in the Vertex AI Model Garden
or is an endpoint running in a Google Cloud project.

### Use a model in the Vertex AI Model Garden

To invoke an online prediction using an ML model that's running in the
Vertex AI Model Garden, use the following syntax for the
`ml_predict_row()`
SQL function:
`sql
SELECT ML_PREDICT_ROW('publishers/google/models/MODEL_ID', '{ "instances": [ INSTANCES ], "parameters":
PARAMETERS }');`

<br />

Make the following replacements:

- <var translate="no">MODEL_ID</var>: the ID of the ML model to use (for example, `gemini-2.0`)

- <var translate="no">INSTANCES</var>: the inputs to the prediction call, in JSON format

- <var translate="no">PARAMETERS</var>: the parameters to the prediction call, in JSON format

> [!NOTE]
> **Note:** You must store the ML model in the same project and region as your Cloud SQL instance.

```sql
SELECT ML_PREDICT_ROW('publishers/google/models/MODEL_ID', '{ "instances": [ INSTANCES ], "parameters":
PARAMETERS }');
```
For information about the model's JSON response messages, see [Generative AI foundational model reference](https://docs.cloud.google.com/vertex-ai/docs/generative-ai/model-reference/overview#models). For examples, see [Example invocations](https://docs.cloud.google.com/sql/docs/postgres/invoke-online-predictions.md.txt#examples).

<br />

### Use a Vertex AI model endpoint

To invoke an online prediction using a Vertex AI model endpoint, use the following syntax for the `ml_predict_row()`
SQL function:
`sql
SELECT ML_PREDICT_ROW('endpoints/ENDPOINT_ID', '{ "instances": [ INSTANCES ], "parameters":
PARAMETERS }');`

<br />

Make the following replacements:

- <var translate="no">ENDPOINT_ID</var>: the ID of the model's endpoint

- <var translate="no">INSTANCES</var>: the inputs to the prediction call, in JSON format

- <var translate="no">PARAMETERS</var>: the parameters to the prediction call, in JSON format

> [!NOTE]
> **Note:** The endpoint must be located in the same project and region as your Cloud SQL instance.

For information about the model's JSON response messages, see
[PredictResponse](https://docs.cloud.google.com/vertex-ai/docs/reference/rest/v1/PredictResponse).

### Example invocations

The following example uses
[Gemini 2.5 Flash](https://docs.cloud.google.com/vertex-ai/generative-ai/docs/model-reference/gemini), available
in the Model Garden, to generate text based on a short prompt that's
provided as a literal argument to `ml_predict_row()`
:

```sql
SELECT
  json_array_elements(ml_predict_row('projects/PROJECT_ID/locations/us-central1/publishers/google/models/gemini-2.5-flash:streamGenerateContent',
    '{ "contents": [ { "role": "user", "parts": [ { "text": "CloudSQL" } ] } ] }'))-> 'candidates' -> 0 -> 'content' -> 'parts' -> 0 -> 'text';
```

> [!NOTE]
> **Note:** You can use Gemini foundation models only in regions where Vertex AI supports Gemini.

The response is a JSON object. For more information about the format of the
object, see
[Response body](https://docs.cloud.google.com/vertex-ai/generative-ai/docs/model-reference/gemini#response_body).

The next example modifies the previous one in the following ways:

- The example uses the contents of the current database's `messages.message`
  column as input.

- The example demonstrates the use of the
  `json_build_object()` function
  as an aid to formatting the function's parameters.

```sql
select ml_predict_row('projects/PROJECT_ID/locations/us-central1/publishers/google/models/gemini-2.5-flash:generateContent', json_build_object('contents', json_build_array(json_build_object('role', 'user', 'parts', json_build_array(json_build_object('text', message)))))) from messages;
```

For every row in the `messages` table, the returned JSON object now contains
one entry in its `candidates` array.

Because the response is a JSON object, you can pull specific fields from it:

```sql
select ml_predict_row('projects/PROJECT_ID/locations/us-central1/publishers/google/models/gemini-2.5-flash:generateContent', json_build_object('contents', json_build_array(json_build_object('role', 'user', 'parts', json_build_array(json_build_object('text', message)))))) -> 'candidates' -> 0 -> 'content' -> 'parts' -> 0 -> 'text' FROM messages;
```

For more example arguments to `ml_predict_row()`, see
[Try the Vertex AI Gemini API](https://docs.cloud.google.com/vertex-ai/docs/generative-ai/start/quickstarts/api-quickstart).

## What's next

- [Build generative AI applications using Cloud SQL](https://docs.cloud.google.com/sql/docs/postgres/ai-overview.md.txt)

- [Integrate Cloud SQL with Vertex AI](https://docs.cloud.google.com/sql/docs/postgres/integrate-cloud-sql-with-vertex-ai.md.txt)

- [Understand an example of an embedding workflow](https://docs.cloud.google.com/sql/docs/postgres/understand-example-embedding-workflow.md.txt)

- [Build LLM-powered applications using LangChain](https://docs.cloud.google.com/sql/docs/postgres/langchain.md.txt)