> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ditto.live/llms.txt
> Use this file to discover all available pages before exploring further.
# INSERT
When using DQL's INSERT command, you can add new documents using JSON objects:
```sql DQL theme={null}
INSERT INTO your_collection_name
DOCUMENTS ([document1]),([document2]), ([document3]), ...
[ON ID CONFLICT [FAIL | DO NOTHING | DO UPDATE | DO UPDATE_LOCAL_DIFF]]
```
* INSERT INTO is the name of the collection from which you want to retrieve the data.
* `DOCUMENTS ([document1]), ([document2]), ([document3]), ...` represent the documents being inserted.
* **Alternative:** You can use `VALUES` instead of `DOCUMENTS` - they are functionally equivalent
* `[ ON ID CONFLICT [FAIL | DO NOTHING | DO UPDATE | DO UPDATE_LOCAL_DIFF]]` is an optional clause that allows for defining a policy if the ID already exists in the local data store. The default is to throw an error (`FAIL`).
### VALUES vs DOCUMENTS
Both `VALUES` and `DOCUMENTS` keywords work identically in INSERT statements. You can use either based on your preference:
```sql DQL theme={null}
-- Using DOCUMENTS
INSERT INTO cars DOCUMENTS (:car1), (:car2)
-- Using VALUES (equivalent)
INSERT INTO cars VALUES (:car1), (:car2)
```
Both syntaxes support:
* Single or multiple documents
* Parameters or literal objects
* Arrays of documents
* `ON ID CONFLICT` clauses
* `INITIAL` keyword for default data
**Mixing single documents and arrays in one statement (v5+):** Any parameter in the `VALUES` or `DOCUMENTS` list can be either a single document object or an array of document objects. When a parameter resolves to an array, each element is inserted as a separate document.
```sql DQL theme={null}
INSERT INTO collection VALUES(:single),(:array)
```
With parameters `{"single": {"a": 1}, "array": [{"a": 2}, {"a": 3}]}`, this results in **3 documents** being inserted.
In Ditto, excluding fields from your payload doesn't remove the existing data from the system.
To remove a specific field from a document, use an explicit `UPDATE` statement and `UNSET` that field. (See [UPDATE](/dql/update))
## **INSERT Document**
```swift Swift theme={null}
await ditto.store.execute(
query: "INSERT INTO cars DOCUMENTS (:newCar)",
arguments: ["newCar": ["color": "blue"]]);
```
```kotlin Kotlin theme={null}
ditto.store.execute(
"INSERT INTO cars DOCUMENTS (:newCar)",
mapOf("newCar" to mapOf("color" to "blue")))
```
```javascript JS theme={null}
await ditto.store.execute(
"INSERT INTO cars DOCUMENTS (:newCar)",
{ newCar: { color: "blue" } }
);
```
```java Java theme={null}
ditto.store.execute(
"INSERT INTO cars DOCUMENTS (:newCar)",
Collections.singletonMap("newCar", Collections.singletonMap("color", "blue")));
```
```csharp C# theme={null}
var args = new Dictionary();
args.Add("newCar", new { color = "blue" });
await ditto.Store.ExecuteAsync(
"INSERT INTO cars DOCUMENTS (:newCar)",
args);
```
```cpp C++ theme={null}
std::map> args;
args["newCar"] = {{"color", "blue"}};
ditto.get_store().execute(
"INSERT INTO cars DOCUMENTS (:newCar)",
args);
```
```rust Rust theme={null}
let query_result = ditto
.store()
.execute_v2((
"INSERT INTO cars DOCUMENTS (:newCar)",
serde_json::json!({
"newCar": {
"color": "blue"
}
}),
)).await?;
```
```dart Dart theme={null}
await ditto.store.execute(
"INSERT INTO cars DOCUMENTS (:newCar)",
arguments: {"newCar": {"color": "blue"}},
);
```
## INSERT with Multiple Documents
You can insert multiple documents in a single INSERT statement by providing multiple parameters or by passing an array of documents.
### Using Multiple Parameters
```swift Swift theme={null}
await ditto.store.execute(
query: "INSERT INTO cars DOCUMENTS (:car1), (:car2)",
arguments: [
"car1": ["color": "blue"],
"car2": ["color": "red"]
]);
```
```kotlin Kotlin theme={null}
ditto.store.execute(
"INSERT INTO cars DOCUMENTS (:car1),(:car2)",
mapOf(
"car1" to mapOf("color" to "blue"),
"car2" to mapOf("color" to "red")
))
```
```javascript JS theme={null}
await ditto.store.execute(
"INSERT INTO cars DOCUMENTS (:car1),(:car2)",
{
car1: { color: 'blue' },
car2: { color: 'red' }
});
```
```java Java theme={null}
Map> args = new HashMap<>();
args.put("car1", Collections.singletonMap("color", "blue"));
args.put("car2", Collections.singletonMap("color", "red"));
ditto.store.execute(
"INSERT INTO cars DOCUMENTS (:car1),(:car2)",
args);
```
```csharp C# theme={null}
var args = new Dictionary();
args.Add("car1", new { color = "blue" });
args.Add("car2", new { color = "red" });
await ditto.Store.ExecuteAsync(
"INSERT INTO cars DOCUMENTS (:car1),(:car2)",
args);
```
```cpp C++ theme={null}
std::map> args;
args["car1"] = {{"color", "blue"}};
args["car2"] = {{"color", "red"}};
auto result = ditto.get_store().execute(
"INSERT INTO cars DOCUMENTS (:car1),(:car2)",
args).get();
```
```rust Rust theme={null}
let query_result = ditto
.store()
.execute_v2((
"INSERT INTO cars DOCUMENTS (:car1), (:car2)",
serde_json::json!({
"car1": {
"color": "blue"
},
"car2": {
"color": "red"
}
}),
)).await?;
```
```dart Dart theme={null}
await ditto.store.execute(
"INSERT INTO cars DOCUMENTS (:car1),(:car2)",
arguments: {
"car1": { "color": "blue" },
"car2": { "color": "red" },
},
);
```
### Using Array Parameters
You can also insert multiple documents by passing an array of objects as a parameter. This is particularly useful when you have a dynamic list of documents to insert.
```swift Swift theme={null}
let cars = [
["color": "blue", "year": 2020],
["color": "red", "year": 2021],
["color": "green", "year": 2022]
]
await ditto.store.execute(
query: "INSERT INTO cars VALUES (:cars)",
arguments: ["cars": cars]
)
```
```kotlin Kotlin theme={null}
val cars = listOf(
mapOf("color" to "blue", "year" to 2020),
mapOf("color" to "red", "year" to 2021),
mapOf("color" to "green", "year" to 2022)
)
ditto.store.execute(
"INSERT INTO cars VALUES (:cars)",
mapOf("cars" to cars)
)
```
```javascript JS theme={null}
const cars = [
{ color: "blue", year: 2020 },
{ color: "red", year: 2021 },
{ color: "green", year: 2022 }
];
await ditto.store.execute(
"INSERT INTO cars VALUES (:cars)",
{ cars }
);
```
```java Java theme={null}
List> cars = Arrays.asList(
Map.of("color", "blue", "year", 2020),
Map.of("color", "red", "year", 2021),
Map.of("color", "green", "year", 2022)
);
ditto.store.execute(
"INSERT INTO cars VALUES (:cars)",
Collections.singletonMap("cars", cars)
);
```
```csharp C# theme={null}
var cars = new List
When using array parameters, each object in the array will be inserted as a separate document. You can combine array parameters with literal document parameters in the same INSERT statement.
## INSERT JSON-serialized Document
Starting with SDK 4.8, Ditto provides a convenient way to insert JSON-serialized documents using the `deserialize_json()` function. This allows you to directly insert string-encoded JSON data into your collections without manually parsing it first.
`deserialize_json()` also works in `UPDATE` statements to set fields from JSON strings. See [UPDATE with deserialize\_json](/dql/update#update-with-deserialize_json) for examples.
```swift Swift theme={null}
await ditto.store.execute(
query: """
INSERT INTO cars
DOCUMENTS (deserialize_json(:jsonData))
ON ID CONFLICT DO UPDATE
""",
arguments: [ "jsonData": "{\"_id\": \"123\",\"color\": \"blue\"}" ])
```
```kotlin Kotlin theme={null}
ditto.store.execute("""
INSERT INTO cars
DOCUMENTS (deserialize_json(:jsonData))
""",
mapOf("jsonData", "{\"_id\": \"123\",\"color\": \"blue\"}"))
```
```javascript JS theme={null}
await ditto.store.execute(`
INSERT INTO cars
DOCUMENTS (deserialize_json(:jsonData))`,
{ jsonData: "{\"_id\": \"123\",\"color\": \"blue\"}" });
```
```java Java theme={null}
ditto.store.execute(
"INSERT INTO cars DOCUMENTS (deserialize_json(:jsonData))",
Map.of("jsonData", "{\"_id\": \"123\", \"color\": \"blue\"}"));
```
```csharp C# theme={null}
var args = new Dictionary();
args.Add("jsonData", "{\"_id\": \"123\",\"color\": \"blue\"}")
await ditto.Store.ExecuteAsync(
"INSERT INTO cars"
+ " DOCUMENTS (deserialize_json(:jsonData))",
args);
```
```cpp C++ theme={null}
std::map args;
args["jsonData"] = "{\"_id\": \"123\",\"color\": \"blue\"}";
auto result = ditto.get_store().execute(
"INSERT INTO cars DOCUMENTS (deserialize_json(:jsonData))",
args).get();
```
```rust Rust theme={null}
let json_string = r#"{"_id": "123", "color": "blue"}"#.to_string();
let query_result = ditto
.store()
.execute_v2((
"INSERT INTO cars DOCUMENTS (deserialize_json(:jsonData))",
serde_json::json!({
"jsonData": "{\"_id\": \"123\",\"color\": \"blue\"}"
}),
)).await;
```
```dart Dart theme={null}
final newCar = '{"_id": "123", "color": "blue"}';
await ditto.store.execute("""
INSERT INTO cars
DOCUMENTS (deserialize_json(:jsonData))""",
queryArgs: {"jsonData": "{\"_id\": \"123\",\"color\": \"blue\"}"},
);
```
## INSERT with ID Conflict Handling
By default, the INSERT operation throws an error if an existing document with the same ID exists in the local Ditto store.
However, Ditto allows some flexibility by allowing you to choose between ignoring the conflict (`DO NOTHING`) or updating existing documents (`DO UPDATE`) when a conflict occurs during an INSERT operation:
```sql DQL theme={null}
ON ID CONFLICT [FAIL | DO NOTHING | DO UPDATE | DO UPDATE_LOCAL_DIFF]
```
In this syntax:
* `FAIL` (default) will cause an error to be thrown if a document with the same `_id` currently exists in the local data store.
* `DO NOTHING` will make the statement succeed with no action taken.
* `DO UPDATE` will perform a value update on every field in the provided document, even if the value is the same. This means all fields provided will be replicated to other peers regardless of whether the values actually changed.
* `DO UPDATE_LOCAL_DIFF` (SDK 4.12+) will only update fields whose values differ from the existing document. This is more efficient than `DO UPDATE` when you want to avoid unnecessary replication of unchanged values.
### DO UPDATE
Use `DO UPDATE` when you want to update all fields in the document regardless of whether the values have changed. This is useful when you want to ensure all fields are replicated to other peers, even if the values are the same as the existing document.
For example, inserting or updating a car — if there is a conflict (ON ID CONFLICT), execute the `DO UPDATE` conflict resolution policy:
```swift Swift theme={null}
let newCar = [
"_id": "123",
"color": "blue"
]
await ditto.store.execute(
query: """
INSERT INTO cars
DOCUMENTS (:newCar)
ON ID CONFLICT DO UPDATE
""",
arguments: [ "newCar": newCar ])
```
```kotlin Kotlin theme={null}
var newCar = mapOf(
"_id" to "123",
"color" to "blue"
)
ditto.store.execute("""
INSERT INTO cars
DOCUMENTS (:newCar)
ON ID CONFLICT DO UPDATE
""",
mapOf("newCar", newCar))
```
```javascript JS theme={null}
const newCar = {
_id: "123",
color: "blue",
};
await ditto.store.execute(`
INSERT INTO cars
DOCUMENTS (:newCar)
ON ID CONFLICT DO UPDATE`,
{ newCar });
```
```java Java theme={null}
ditto.store.execute(
"INSERT INTO cars DOCUMENTS (:newCar) ON ID CONFLICT DO UPDATE",
Collections.singletonMap("newCar", Collections.singletonMap("color", "blue")),
)
```
```csharp C# theme={null}
var args = new Dictionary();
args.Add("newCar", new { _id = "123", color = "blue" })
await ditto.Store.ExecuteAsync(
"INSERT INTO cars"
+ " DOCUMENTS (:newCar) ON ID CONFLICT DO UPDATE",
args);
```
```cpp C++ theme={null}
struct Car {
std::string _id;
std::string color;
};
// ...
std::map args;
args["newCar"] = {"123", "blue"};
auto result = ditto.get_store().execute(
"INSERT INTO cars DOCUMENTS (:newCar) ON ID CONFLICT DO UPDATE",
args).get();
```
```rust Rust theme={null}
let query_result = ditto
.store()
.execute_v2((
"INSERT INTO cars DOCUMENTS (:newCar) ON ID CONFLICT DO UPDATE",
serde_json::json!({
"newCar": {
"_id": "123",
"color": "blue"
}
}),
)).await?;
```
```dart Dart theme={null}
final newCar = {
"_id": "123",
"color": "blue",
};
await ditto.store.execute("""
INSERT INTO cars
DOCUMENTS (:newCar)
ON ID CONFLICT DO UPDATE""",
queryArgs: {"newCar": newCar},
);
```
### DO UPDATE\_LOCAL\_DIFF
Use `DO UPDATE_LOCAL_DIFF` when you want to update only the fields that have actually changed. Unlike `DO UPDATE`, which updates every field regardless of whether the value changed, `DO UPDATE_LOCAL_DIFF` compares the incoming document against the existing local document and only updates fields with different values.
This is useful when:
* You want to minimize unnecessary replication traffic
* You're frequently re-inserting documents where most fields remain unchanged
* You want to avoid triggering sync for unchanged data
```swift Swift theme={null}
let newCar = [
"_id": "123",
"color": "blue",
"mileage": 5000
]
await ditto.store.execute(
query: """
INSERT INTO cars
DOCUMENTS (:newCar)
ON ID CONFLICT DO UPDATE_LOCAL_DIFF
""",
arguments: [ "newCar": newCar ])
```
```kotlin Kotlin theme={null}
var newCar = mapOf(
"_id" to "123",
"color" to "blue",
"mileage" to 5000
)
ditto.store.execute("""
INSERT INTO cars
DOCUMENTS (:newCar)
ON ID CONFLICT DO UPDATE_LOCAL_DIFF
""",
mapOf("newCar", newCar))
```
```javascript JS theme={null}
const newCar = {
_id: "123",
color: "blue",
mileage: 5000,
};
await ditto.store.execute(`
INSERT INTO cars
DOCUMENTS (:newCar)
ON ID CONFLICT DO UPDATE_LOCAL_DIFF`,
{ newCar });
```
```java Java theme={null}
Map newCar = new HashMap<>();
newCar.put("_id", "123");
newCar.put("color", "blue");
newCar.put("mileage", 5000);
ditto.store.execute(
"INSERT INTO cars DOCUMENTS (:newCar) ON ID CONFLICT DO UPDATE_LOCAL_DIFF",
Collections.singletonMap("newCar", newCar));
```
```csharp C# theme={null}
var args = new Dictionary();
args.Add("newCar", new { _id = "123", color = "blue", mileage = 5000 })
await ditto.Store.ExecuteAsync(
"INSERT INTO cars"
+ " DOCUMENTS (:newCar) ON ID CONFLICT DO UPDATE_LOCAL_DIFF",
args);
```
```cpp C++ theme={null}
struct Car {
std::string _id;
std::string color;
int mileage;
};
// ...
std::map args;
args["newCar"] = {"123", "blue", 5000};
auto result = ditto.get_store().execute(
"INSERT INTO cars DOCUMENTS (:newCar) ON ID CONFLICT DO UPDATE_LOCAL_DIFF",
args).get();
```
```rust Rust theme={null}
let query_result = ditto
.store()
.execute_v2((
"INSERT INTO cars DOCUMENTS (:newCar) ON ID CONFLICT DO UPDATE_LOCAL_DIFF",
serde_json::json!({
"newCar": {
"_id": "123",
"color": "blue",
"mileage": 5000
}
}),
)).await?;
```
```dart Dart theme={null}
final newCar = {
"_id": "123",
"color": "blue",
"mileage": 5000,
};
await ditto.store.execute("""
INSERT INTO cars
DOCUMENTS (:newCar)
ON ID CONFLICT DO UPDATE_LOCAL_DIFF""",
queryArgs: {"newCar": newCar},
);
```
## INSERT with INITIAL Documents
INSERT allows you to set specific documents as default data using the INITIAL DOCUMENTS action.
*Initial documents* are the documents inserted at the beginning of time and are viewed by all peers as the same INSERT operation. This allows multiple peers to independently initialize the same default data *safely*, so regardless of the individual peer's starting point.
When inserting, the initial documents DO NOTHING if the document ID already exists in the local Ditto store. The ON ID CONFLICT policy cannot change this behavior.
```sql DQL theme={null}
INSERT INTO your_collection_name
INITIAL DOCUMENTS ([document])
```
In this syntax:
* `your_collection_name` is the name of the collection from which you want to retrieve the data.
* `[document]` represents the document.
For example, setting up default data by inserting the given car details as an initial document:
```swift Swift theme={null}
let newCar = [
"_id": "123",
"color": "blue"
]
await ditto.store.execute(
query: """
INSERT INTO cars
INITIAL DOCUMENTS (:newCar)
""",
arguments: [ "newCar": newCar ])
```
```kotlin Kotlin theme={null}
var newCar = mapOf(
"_id" to "123",
"color" to "blue"
)
ditto.store.execute("""
INSERT INTO cars
INITIAL DOCUMENTS (:newCar)
""",
mapOf("newCar", newCar))
```
```javascript JS theme={null}
const newCar = {
_id: "123"
color: "blue",
};
await ditto.store.execute(`
INSERT INTO cars
INITIAL DOCUMENTS (:newCar)`,
{ newCar });
```
```java Java theme={null}
ditto.store.execute(
"INSERT INTO cars INITIAL DOCUMENTS (:newCar)",
Collections.singletonMap("newCar", Collections.singletonMap("color", "blue")));
```
```csharp C# theme={null}
var args = new Dictionary();
args.Add("newCar", new { _id = "123", field1 = 0 })
await ditto.Store.ExecuteAsync(
"INSERT INTO cars INITIAL DOCUMENTS (:newCar)",
args);
```
```cpp C++ theme={null}
struct Car {
std::string _id;
std::string color;
};
// ...
std::map args;
args["newCar"] = {"123", "blue"};
auto result = ditto.get_store().execute(
"INSERT INTO cars INITIAL DOCUMENTS (:newCar)",
args).get();
```
```rust Rust theme={null}
let query_result = ditto
.store()
.execute_v2((
"INSERT INTO cars INITIAL DOCUMENTS (:newCar)",
serde_json::json!({
"newCar": {
"_id": "123",
"color": "blue"
}
}),
).await?;
```
```dart Dart theme={null}
const newCar = {
"_id": "123"
"color": "blue",
};
await ditto.store.execute("""
INSERT INTO cars
INITIAL DOCUMENTS (:newCar)""",
{"newCar": newCar},
);
```
## INSERT Document with MAP Type
### Disable Strict Mode
In 4.11+ and `DQL_STRICT_MODE=false`, **collection definitions are no longer required.**
[Read more](/dql/strict-mode)
```swift Swift theme={null}
let arguments: [String: Any] = [
"newCar": [
"_id": "123",
"properties": [
"color": "blue",
"mileage": 3000
]
}
];
await ditto.store.execute(
query: "INSERT INTO COLLECTION cars (properties MAP) DOCUMENTS (:newCar)",
arguments: arguments);
```
```kotlin Kotlin theme={null}
val arguments = mapOf(
"newCar" to mapOf(
"_id" to "123",
"properties" to mapOf(
"color" to "blue",
"mileage" to 3000
)
)
)
)
ditto.store.execute("""
INSERT INTO COLLECTION cars (properties MAP)
DOCUMENTS (:newCar)
""",
arguments)
```
```javascript JS theme={null}
const newCar = {
_id: "123",
properties: {
color: 'blue',
mileage: 3000
}
}
await ditto.store.execute(`
INSERT INTO COLLECTION cars (properties MAP)
DOCUMENTS (:newCar)`,
{ newCar });
```
```java Java theme={null}
Map properties = new HashMap<>();
properties.put("color", "blue");
properties.put("mileage", 3000);
Map newCar = new HashMap<>();
newCar.put("_id", "123");
newCar.put("properties", properties);
ditto.store.execute(
"INSERT INTO COLLECTION cars (properties MAP) DOCUMENTS (:newCar)",
Collections.singletonMap("newCar", newCar))
```
```csharp C# theme={null}
var subMap = new {
color = "blue",
mileage = 3000
};
var insertArgs = new Dictionary();
insertArgs.Add("newCar", new { _id = newId, properties = subMap })
await ditto.Store.ExecuteAsync(
"INSERT INTO COLLECTION cars (properties MAP) DOCUMENTS (:newCar)",
insertArgs);
```
```cpp C++ theme={null}
struct Properties {
std::string color;
int mileage;
};
struct Car {
std::string _id;
Properties properties;
};
// ...
std::map args;
args["newCar"] = {"123", {"blue", 3000}};
ditto.get_store().execute(
"INSERT INTO COLLECTION your_collection_name (properties MAP)"
+ " DOCUMENTS (:newCar)",
args).get();
```
```rust Rust theme={null}
let query_result = ditto
.store()
.execute_v2((
"INSERT INTO COLLECTION cars (properties MAP) DOCUMENTS (:newCar)",
serde_json::json!({
"newCar": {
"_id": "123",
"properties": {
"color": "blue",
"mileage": 3000
}
}
}),
)).await?;
```
```dart Dart theme={null}
final newCar = {
"_id": "123",
"properties": {
"color": 'blue',
"mileage": 3000,
},
};
await ditto.store.execute("""
INSERT INTO COLLECTION cars (properties MAP)
DOCUMENTS (:newCar)""",
arguments: {"newCar": newCar},
);
```
* INSERT INTO is the name of the collection from which you want to retrieve the data.
* `DOCUMENTS ([document1]), ([document2]), ([document3]), ...` represent the documents being inserted.
* **Alternative:** You can use `VALUES` instead of `DOCUMENTS` - they are functionally equivalent
* `[ ON ID CONFLICT [FAIL | DO NOTHING | DO UPDATE | DO UPDATE_LOCAL_DIFF]]` is an optional clause that allows for defining a policy if the ID already exists in the local data store. The default is to throw an error (`FAIL`).
### VALUES vs DOCUMENTS
Both `VALUES` and `DOCUMENTS` keywords work identically in INSERT statements. You can use either based on your preference:
```sql DQL theme={null}
-- Using DOCUMENTS
INSERT INTO cars DOCUMENTS (:car1), (:car2)
-- Using VALUES (equivalent)
INSERT INTO cars VALUES (:car1), (:car2)
```
Both syntaxes support:
* Single or multiple documents
* Parameters or literal objects
* Arrays of documents
* `ON ID CONFLICT` clauses
* `INITIAL` keyword for default data