Feature: db.insert function

[MatthewAry]

Is your feature request related to a problem?

There is no easy way to insert multiple records. This can made easier by creating a new method specifically for performing insert operations.

Describe the solution

A new method could be added to the SDK which could have the following signature:

db.insert(table: str, data: Union[Dict, List[Dict]], options: Optional[?])

This would allow you to insert a singular record or a list of records.

Alternative methods

Currently the method that we are using is to either generate a prepared string to do an insert statement:

db.query(```
INSERT INTO company {
	name: 'SurrealDB',
	founded: "2021-09-10",
	founders: [person:tobie, person:jaime],
	tags: ['big data', 'database']
};```)

or

db.query(```
INSERT INTO person [
   {id: "person:jaime", name: "Jaime", surname: "Morgan Hitchcock"},
   {id: "person:tobie", name: "Tobie", surname: "Morgan Hitchcock"},
];```)

SurrealDB version

1.0.0+20230913.54aedcd for macos on x86_64

surrealdb.py version

surrealdb.py 0.3.1 for macOS on x86_64 using Python 3.11.2

Contact Details

No response

Is there an existing issue for this?

• I have searched the existing issues

Code of Conduct

• I agree to follow this project's Code of Conduct

[maxwellflitton]

Hey @MatthewAry thank you for the suggestion. We are working on the rust/python client and we also want to ensure that the features across all clients are going to be the same. This does not mean that a batch insert is a bad idea, we will put it to the team and work the feasibility of supporting the batch inserts in general. However, I appreciate that this is not helpful to you right as this time, so below is a function that will generate the SQL statement for a batch insert based off a list of dicts:

def construct_batch_insert(table_name, records):
    """
    Construct a batch insert SQL query for a custom SQL syntax as provided in the example.
    
    Args:
    table_name (str): The name of the table into which records will be inserted.
    records (list of dict): A list of dictionaries, each representing a row to insert.

    Returns:
    str: A SQL query string for batch insertion.
    """
    # Start the query with the table name
    query = f"INSERT INTO {table_name} [\n"
    
    # Add each record as a dictionary string
    record_strings = []
    for record in records:
        # Convert each record dictionary to a string in the format: {key: "value", key: "value"}
        record_str = ', '.join([f'{key}: "{value}"' for key, value in record.items()])
        record_strings.append(f"   {{{record_str}}}")
    
    # Join all record strings with a comma and a newline
    query += ',\n'.join(record_strings)
    
    # Close the bracket for the record list and end the query with a semicolon
    query += "\n];"
    
    return query

# Example usage
records = [
    {"id": "person:jaime", "name": "Jaime", "surname": "Morgan Hitchcock"},
    {"id": "person:tobie", "name": "Tobie", "surname": "Morgan Hitchcock"}
]

# Generate the query
query = construct_batch_insert("person", records)
print(query)

The code will give the following printout:

INSERT INTO person [
   {id: "person:jaime", name: "Jaime", surname: "Morgan Hitchcock"},
   {id: "person:tobie", name: "Tobie", surname: "Morgan Hitchcock"}
];

This means that your batch insert can be the following:

db.insert(construct_batch_insert("person", [
    {"id": "person:jaime", "name": "Jaime", "surname": "Morgan Hitchcock"},
    {"id": "person:tobie", "name": "Tobie", "surname": "Morgan Hitchcock"}
]))

Does this approach suit you for now while we work on incorporating batch inserts?

[MatthewAry]

This is great. Thank you for making this. I know that is not a native solution and that developing one is not feasible right now but this does meet my needs for now!

[kakalos12]

Not sure if this helps when someone needs but the function will not work with version 2 because Surrealdb will not eagerly parse the string to the record type, and it will not work with types like array, object .. Here is my updated code (This code is tested in my project, and is generated by Chatgpt, so use it at your own risks)

def construct_batch_insert(table_name, records):
   """
   Construct a batch insert SQL query for a custom SQL syntax as provided in the example.

   Args:
   table_name (str): The name of the table into which records will be inserted.
   records (list of dict): A list of dictionaries, each representing a row to insert.

   Returns:
   str: A SQL query string for batch insertion.
   """

   def format_value(value):
       """Recursively format values, handling strings, numbers, lists, and dicts."""
       if isinstance(value, str):
           if value.startswith("<record>"):
               # Keep this pattern as unquoted
               return value
           else:
               # Wrap other strings in quotes
               return f'"{value}"'
       elif isinstance(value, (int, float)):
           # Return numbers as-is
           return str(value)
       elif isinstance(value, list):
           # Recursively format lists
           return "[" + ", ".join([format_value(item) for item in value]) + "]"
       elif isinstance(value, dict):
           # Recursively format dicts
           return "{" + ", ".join([f"{k}: {format_value(v)}" for k, v in value.items()]) + "}"
       else:
           # Default case for other types
           return repr(value)

   # Start the query with the table name
   query = f"INSERT INTO {table_name} [\n"

   # Add each record as a dictionary string
   record_strings = []
   for record in records:
       record_str = []
       for key, value in record.items():
           formatted_value = format_value(value)
           record_str.append(f"{key}: {formatted_value}")

       # Join the key-value pairs and format as a dictionary string
       record_strings.append(f"   {{{', '.join(record_str)}}}")

   # Join all record strings with a comma and a newline
   query += ",\n".join(record_strings)

   # Close the bracket for the record list and end the query with a semicolon
   query += "\n];"

   return query