Sometimes we have to change some data to test something or because they are inconsistent due any wrong development.

For change that if the table do not have a detail screen we have to create a logic in a timer and execute it, or use a advanced query using commit in the end, which is not very known.

The suggestion is to run a single action directly from service studio with some logic to update these records or do whatever you want, without the need to create a timer or a screen to do that!

Or if not possible to run an action, some way to change the data easily in service studio.
Just for dev environments! 

Usefull!

Just letting you change the data in sql grid.

It would be really handy to be able to add, edit and delete data directly in Service Studio via the View Data for entities.

Up to very recently, developers were able to write a 'Commit' statement in order to have changes in the database done without publishing, as such:

Although the 'Test' feature is temporary, and rolls the changes back, because there was a commit statement, these changes would be permanent. Which meant a developer could do hot fixes without publishing. 

This ability was removed, probably as a security measure. 

I understand that this may pose a serious risk in Production Environments, but it was already possible to block the test feature entirely, and thus block the 'Commit' injection with it. So before, we had two choices.

Allow the test feature + Commit ; Don't allow the test feature ( which blocks Commit )

Now we have:

Allow the test feature ( But don't allow a commit ) ; Don't allow the test feature.

If security was the main issue, blocking the Test feature itself should solve it right?

Our alternative is to create a screen, and put the hot fixes on actions. Is there any difference between creating a screen, publishing it, running the action, deleting the screen and publishing again; and Testing a query with commit?

Looking forward to hearing from you.

There is another difference. Allow Testing allows to Test. Delete is not as important as testing queries.

I understand you miss that feature (I read it in the forum and heard it on the User Group from several people) but there are different issues here.

You may block Test Data to protect client privacy. That's it. There is awareness that data can't be used.

When you do a Commit, there is no registry on OutSystems of how the data disappeared. The query was tested and deleted. It was a mistake? No one saw.

If you are forced to publish a page/action, you leave a trail. There is awareness that a page exists where data can tampered. Not only you think twice before pressing the Run Query, but the page itself can validate what type of query is running.

For me, that page makes as much sense as the custom CRUD actions where you save dates and users automatically, create a log of interactions and validate the data and the operation.

Some businesses may not need it, but it is a best practice.

Indeed, a trail of evidence is left when publishing the screen.

 If that is the only thing keeping a Commit instruction from being a feature, perhaps a trail can be added. Whenever you test a query, a log can be created saying user X ran Query: 'XXXXX' at YYYY-MM-DD HH:mm:ss.

I understand the security issue, but hopefully a middle group can be reached where the development team can still manipulate the database without publishing. 

Removing it makes sense for a production environment where you need to leave a trail of evidence that you tampered with data, but for a Dev or Tst environment not having this feature is doing more harm than good, I mean how often you need to delete data from a table because you created a bug? or you created a field that was text but you want to convert it to integer now?
Now we have to create a screen, with a button and a screen action just to fix this, or a timer and run it... 

Now I miss this feature. I think it’s a very useful in lower environments.

+1, OutSystems, please make our work more easy not more difficult. 

+1 for the idea. Good one.

Maybe have it only on environments that aren't in production mode.

The Forge has this great component SQL Sandbox created by Leonardo Fernandes, that allows you to execute SQL statements directly on the Platform Database. It even allows you to do so on External Databases.

Check it out if you haven’t already!

Regards,

Nordin

I would be nice to have SQL Query tool for the Service Studio.  

• This tool should be also optimized for OutSystems entities only.
• When you go as new developer at old project, it would be many times handy to browse data directly with SQL tool instead of use Aggregate.
• It should also allow do all the basic database operations like INSERT, UPDATE, SELECT, DELETE, DROP AND CREATE etc.
• Tool should be also easy or easier as Aggregate :) with SQL export support.

Why:

• Currently, when you are at environment that you do not have SQL access is a little bit pain and you end up loosing time,

Hi Sampsa,

You already have a SQL widget that alows you to do your queries.

Regards,

Abílio Matos

Hi Abilio, I know and I have used those do for DB specific tasks. 

It seems to current way to do things, but using those are slower than I would just have SQL tool for it. 

One of the core messages of the OutSystems is a productivity and SQL Query tool would bring that on many cases.

If you are on the PaaS offering you can request direct SQL access to your databases. We find this to be extremely handy and alleviates the need to build something as powerful as SSMS into Outsystems studio. 

There are limits to the access you have this way, for example you can view and modify your data but you can't update schema etc.

John, that is good to know. The case that I'm working on this week is just so schedule wise so short that Integrated SQL Query tool would have really saved my time.

Good Idea
Should be really nice a simple tool do perform some query more easily without the need to define the output and be able to export the result without you have to install additional tools where you need to setup the Outsystems database connections.
Every developer has some needs sometime to query some tables as well during the working.
Maybe a small icon on Data tab that open a new window to allow you do it.

Again, I have a use case. Where I'm at environment and I need to do some data cleaning. No direct SQL access:

I need to create now Aggregates to fetch correct rows .. oh no .. I have too much data even though I did already filter by user. I need to do still more specific filtering. I just need to find out primary keys for these three rows and delete those, but doing that requires too many steps with aggregates etc. aarggh

The tool would be great also if you could schedule SQL scripts on deploy.

If you have a customer with a Sentry, you are not allowed to connect with Database Magement Tool for SQL data conversions, etc..

Hey Sampsa,

There is already a workaround for this, using testquery and aggregates preview, and we don't want to bring all the complexity of these external tools to Service Studio (this would also be a problem in some of our customers, for example using Sentry).

Because of this we don't have plans to tackle this.

Thanks for your feedback!

Hello everybody,

Currently it is not possible to create/update/delete records in place in the data view section of an aggregate or in an advanced SQL query. For example, if I want to quickly add some (test) data to my table for testing out my query, then I must write a separate query to do that.

It would be helpful to be able to do this for example from a context menu option. It would be very nice to see this in Outsystems.

Here is an example:

Actually, this should be part of SQL query tool (if there would be one).

https://www.outsystems.com/ideas/5903/sql-query-tool-for-the-service-studio

 This merged had no sense, you merged a simple request which was to rollback a feature that was introduced a few months ago  with an idea which is already marked as "Out of the scope" and high complexity. You are basically shutting us up. 

Exactly, I have the same thoughts

Sorry you feel that way, I have un-merged it. What you DON'T see is that the merge system gives me limited capabilities, including not making it obvious that the target idea was "out of scope". No one was trying to "shut you up". I was trying to make sure your idea got the attention it deserved by rolling in all of the votes. That said, the idea you posted is for nearly the exact same concept as this one, the only difference being that this one wants it to be separate from "test query", and the end goal (being able to do SQL work directly through service studio) is exactly the same. So it was a fairly reasonable merge.

J.Ja

Hello,

It would be great if there is any SQL Query builder to do CRUD operations on the tables within Service Studio. This will help to insert or manipulate the data during development and testing.

Please let me know if this feature is already available within Service Studio.

Thanks

For Developing environments, we could be able to update/create/delete the cell information or the complete row in the Aggregates directly. 

This will make the development much faster because sometimes we just need to update or delete some content to continue.

For example, have a toolbar like the image bellow or we could right click and have some options.

+1

good idea, this avoids a workaround of sometimes creating extra buttons or screens just for testing purposes if you just want to delete one record. However i feel this should only be available for environments where the administrator explicitly allows it.

+1

I think that OutSystems is working already on this. I agree that this should only be available to development environments

+1

+1

Nice to have if the Outsystems Service Studio has a database server explorer so that it easier to manipulate a record in the database. Maybe this only applicable to DEV/Staging environment. What do you think?

+1

I am expecting in outsystems that the user can interact with their data in the database with a tool or by adding an option to add delete or modify the records in the table will be cool.

Like the image below,

Hi,

I think is a good idea, but, in your view, DB cleaner fills in your requirements?

We have many db utilities in forge that, probably, fills our needs.

But, it's a good idea.

Best regards,

Ricardo Pereira

Yes, Thanks for your comment.

Instead of forge component or any other external libraries, If we have these functions in our service studio it will be very very helpful.

we  Aggregate table data to truncate or drop table option used for work experience 

Hi Team,

As of now, we don't have any provision to update Entity data or execute any database query without publishing Module.

what we have to do now is below steps:

1. Create an action to call SQL with query/CRUD action of entity
2. Call the Action form Screen flow
3. Publish the module
4. Run application and hit the flow

Problem Statement:

Problem 1:  We are not able to insert/update a record on the fly in OutSystems entities in any environment

Problem 2:  This becomes bottleneck in production environment where we cannot see the data as well, from service studio or anywhere. This is specially required when resolving any production issue.

Hi Preeti,

Just do what most of us for install the SQL Sandbox forge component.

Regards,

Daniel

Hi Preeti

The Daniel give a good workaround advise, but use it with caution!

Regards

When creating software from scratch, developers spend a lot of time creating database records in order to performe unit tests. This are big time consuming use cases if the developer does not have excel files to upload data from. 

In order to help developers, Service Studio could have an option to create data in the database, similar to the option that allows update database data that will feature in the new Service Studio.

This would be really helpful in order to accelerate developing time and consequent unit tests, leading to an even faster product delivery.

I have came across use case like sometime i wanted to remove one record from the entity which i have created by mistake. To delete that record there should be some kind of functionality on View Entity data screen to delete one or more particular records from the entity.