Creating an AI Agent that can access your SQL database is very easy with Magic, regardless of what database you have. The process can be accomplished without coding, using no-code and low-code constructs. The steps are as follows:
- Connect your cloudlet to your database by using the Manage / Databases menu item.
- Use the endpoint generator to generate a CRUD API wrapping your tables.
- Create a machine learning type and choose "AI Agent" as your flavour. If you don't have this flavour, you can get it by installing the OpenAI plugin in Manage / Plugins.
- Use Hyper IDE and click the flash icon on the module that was automatically generated by the backend generator.
- Create embeddings for your type.
When you are done with the above, you have an AI Agent that can create, read, update, and delete records in your database. This is often referred to as CRUD, and is everything you need to access and administrate your database. In theory, CRUD is everything you need for a database application, and by wrapping CRUD inside an AI Agent, you've basically got an AI Agent that is a "database AI app".
1. Connect your cloudlet to your database
Assuming your database can be accessed over the internet, you can easily connect your cloudlet to it by providing Magic with your connection string. This can be done from the Manage / Databases menu item by choosing "External", for then to give Magic your connection string. This should resemble the following.
Notice, when you paste in your connection string you need to exchange its "database" value with {database}. This allows Magic to dynamically change the database name to retrieve meta information from your system schema. This should work for roughly 90% of all databases I've tried, for SQL Server, MySQL, and PostgreSQL. However, some managed database providers have "non-standard" default schemas, such as for instance DigitalOcean, making things more difficult. If Magic can't retrieve meta information from your database, you're basically out of luck, and you'll have to manually create your CRUD Hyperlambda code.
1b. Create an SQLite database (optional)
If you don't have an existing database, you can choose to install one of the SQLite plugins from Manage / Plugins - Or, alternatively create a new SQLite database from scratch. If you want to use a plugin database, I suggest you use the Chinook plugin, since it's a fairly good quality schema, in addition to that it comes with a bunch of SQL snippets you can play with. Below is a screenshot of how the Chinook database looks like in SQL Studio.
Notice, you can also create a database entirely from scratch by first creating the database using Manage / Databases, for then to edit the database schema using SQL Studio. SQL Studio allows you to visually design your database and visually create new tables, columns, foreign keys, etc. You can see how this looks like below.
2. Generate a CRUD backend API
Once you've got a database, you can use the endpoint generator to create a CRUD API wrapping it. The endpoint generator will create CRUD API endpoints for every single table in your database 100% automatically, and you can configure it a million ways, including adding authorisation requirements to it, changing its default URLs, creating log items when endpoints are executed, etc. The endpoint generator resembles the following.
The endpoint generator is able to retrieve all tables, their columns, primary keys, and foreign keys automatically - And use this meta data as its source for creating CRUD endpoints automatically for you. For the Chinook database it will typically create around 3,000 lines of code, but with larger databases it can sometimes create hundreds of thousands of lines of code, 100% automatically.
Notice, if you choose to generate endpoints for one table at the time, you will see a lot more configuration options. If you've got special needs, this will give you more fine grained configuration options for your resulting endpoints.
Why not use PostgREST or OData?
At this point the observant reader might ask the above question. The answer is that with PostgREST, OData, and similar tools, you're not ending up with code. The Magic endpoint generator creates code for you. Each CRUD endpoint is then encapsulated in its own Hyperlambda file.
This has some huge advantages for you, since it allows you to add business logic to each individual endpoint according to your requirements. With PostgREST or OData this is not possible. Having a CRUD endpoint generator solves a lot of problems, but typically it only solves 80% of the problem - And with OData and PostgREST it becomes almost impossible to solve the remaining 20%. With Hyperlambda and Magic you can easily solve the remaining 20% by simply editing the resulting code.
For the record, the same problem exists with GraphQL-based solutions, as in they make it difficult to add business logic to your endpoints.
3. Create a Machine Learning type
When you're done with the above, you need to create a machine learning type. This is fairly straight forward and can be done in the Manage / Machine Learning component. Click the "Add" button, and choose "AI Agent" as your flavour. If you don't have the AI Agent flavour, you'll need to install the OpenAI plugin in Manage / Plugins, for then to come back to the machine learning component afterwards and create your type. This should resemble the following.
The AI Agent flavour just creates a default "System Message" for you, that becomes base line instructions to the LLM, teaching it how to execute AI functions. You can leave the rest of the settings with their default values.
4. Generate AI Functions
Once done with the above, you need to generate "AI Functions" for each Hyperlambda file in your module folder. The process is 100% automated, and will read meta information from each Hyperlambda file in your folder recursively, and create RAG training data for each file, that becomes an instruction to OpenAI about how to execute these functions on your cloudlet. This can be done from Hyper IDE by selecting your module folder, hover over it until you can see the flash icon, and clicking the flash button.
When you click the flash button, a modal window will open up, allowing you to choose your Machine Learning type. Select the newly created type here, and click "Generate".
5. Create embeddings for your type
At this point there's only one remaining step, which is to create embeddings for your type. This can be done from Manage / Machine Learning. Click "Vectorize" on your type, which will start the process. When it's done, it should say "Done" at the bottom of the terminal window with green text. This should resemble the following.
Testing your CRUD AI Agent
Your AI Agent is now connected to your database, and you can easily perform any CRUD operation towards it. The easiest way to test your agent, is by installing the "AI Expert System" in Manage / Plugins. The AI Expert System is basically a generic LLM UI, allowing you to play with any of your Machine Learning types, and prompt these as you see fit.
Once you've installed the AI Expert System you can access it at your cloudlet's root URL. Click the button in the top / right corner of your dashboard that has your cloudlet's URL as its text, and click the "Frontend" button in the modal window that shows up. This should bring you to the AI Expert System's frontend, allowing you to log in with your cloudlet's root username and password. Once you've logged in, you can have the AI Agent execute any CRUD function towards your database, such as illustrated below.
The above green "Success" parts is the point in the conversation when the AI Agent invokes your AI Function to insert a new artist into your database.
How it works
To understand what happens is to realise that each training snippet created as we generated AI functions for each Hyperlambda file in our folder is basically a RAG database record. Later when the user is prompting the AI Agent, the system will use vector semantic search to match the question towards these records, which will result in that it finds the record that resembles the following.
The above training snippet again is an instruction to the LLM that explains how to respond when the user wants to create a new artist. This ensures that the LLM will return something resembling the following.
To create a new artist with the name "Thomas Hansen's Jazz Ensemble,"
I will proceed with the following details:
- **Name**: Thomas Hansen's Jazz Ensemble
Since you haven't provided an ArtistId, I will create the record without it,
assuming it will be auto-generated. Let's proceed.
___
FUNCTION_INVOCATION[/modules/chinook/Artist.post.hl]:
{
"Name": "Thomas Hansen's Jazz Ensemble"
}
___
Inside the cloudlet's middleware again, we're executing the Hyperlambda file when the LLM returns something resembling the above. The LLM returns the full path to the Hyperlambda file we need to execute, in addition to the arguments to it. The way to logically think about the above is that it becomes a "function invocation" - And since Hyperlambda files can be executed as if they were functions, that's exactly what the middleware does for you. If you look at the file associated with the above AI Function in Hyper IDE, you will find something resembling the following.
// Create inserting one record into your Artist table in your chinook database taking ArtistId, Name
.arguments
// ArtistId column value
ArtistId:long
// Name column value
Name:string
// Meta data description for endpoint
.description:Create inserting one record into your Artist table in your chinook database taking ArtistId, Name
// Type of endpoint
.type:crud-create
// Opening up database connection.
data.connect:[generic|chinook]
database-type:sqlite
// Parametrising our create invocation.
add:x:./*/data.create/*/values
get-nodes:x:@.arguments/*
// Creating our record.
data.create
database-type:sqlite
table:Artist
return-id:bool:false
values
// Returning the correct status code.
response.status.set:201
// Returning result of above invocation to caller.
unwrap:x:+/*
return
result:success
This process allows the AI Agent to execute any Hyperlambda code you need it to execute, allowing the LLM to become capable of executing any arbitrary function you can create. Since Hyperlambda is Turing complete, this implies you can create an AI Agent that can do everything a human being can do with a computer, allowing you to arguably automate anything that somehow can be expressed using computer functions.
If you prefer watching a video of me demonstrating the process, you can find a YouTube video below.
Wrapping up
In this article we created an AI Agent that can perform any CRUD operation towards any SQL database. We looked at how RAG training data works, and illustrated how the AI Agent could use VSS to match training snippets, which it again will use as instructions to the LLM, teaching the LLM how to execute functions in our cloudlet. In addition we looked at how to connect your cloudlet to your database, and also create an SQLite database from scratch.
If you're interested in knowing more about Magic Cloud and how to create AI Agents, you can find its documentation below.
Top comments (0)