Show HN: SQL Dry Runs with SQL Simulator

[westonOG]

My name is Weston Goodwin. I created a tool called SQL Simulator which allows you to do Dry Runs with your SQL Scripts. The reason I created the tool was because I wanted to verify that my SQL scripts did what I thought they would do without executing them against the database.

It is similar in concept to Redgate SQL Clone or Windocs. If you are not familiar with these tools they make clones of your database. The key difference with SQL Simulator is, it only makes a clone of the database objects affected by your sql script, not the entire database. Nor does it copy the entire table (if your sql has where clauses). Once those database objects have been cloned, SQL Simulator then executes your script against the cloned database it created.

This software is only compatible with Oracle and SQL Server at the present time. My plan is to make it compatible with more databases in the future.

For a more in depth technical overview click here: https://docs.tribalknowledge.tech/ratifier-tutorials/sql-sim...

You can download the software here: https://www.tribalknowledge.tech/download.html

You can read the documentation here: https://docs.tribalknowledge.tech/ratifier-tutorials/sql-sim...

[hamilyon2]

I feel ambivalent about your work. First, I was very interested. The thing is your tool is a good approximation of what I am trying to do at my current work. So naturally, I was interested: maybe I learn something, maybe I can use your work. Maybe I'll even buy your solution if it fits my needs.

The next I know, tool is closed source, tool is windows executable (?) and the third and most important, pricing page is 'contact us'. There is no way I am trying your tool or contacting you for a quote.

[westonOG]

Hi, Thanks for you response. Here are my responses to your responses

"tool is closed source" I am open to changing to open source but I do not understand the business model yet.

"tool is windows executable" I created my program with C# and java. I had a sql server dev try to run it but he didn't have java installed on his computer. Apparently they have an intense dislike for java and avoid using programs that need java like the plague. LOL I guess I can't win either way?

"pricing page is 'contact us'" There is no lockout feature of the program that stops working after X days if you don't buy it. Also I am a 1 person startup. I am extremely flexible on pricing. I am not sure what people are willing to pay for it, so it is hard to put a price on it.

[peter_l_downs]

Hi Weston, congratulations on launching. You've done a great job of explaining what the project is and how it works. I'm not in the ecosystem you've built this for so I can't comment too much on the project itself, but nice work communicating it.

EDIT: after reading through the linked docs a bit more, I have to say that they are quite confusing. I'm not sure what the difference is between Ratifier, Kulvert, and SQL Simulator. I'd recommend finding a potential user and watching them go through the setup/install process and see where they have questions.

If anyone is interested in something similar for postgres, I recently released https://github.com/peterldowns/pgtestdb. It uses template databases and advisory locks to give each test its own unique database with a near-zero marginal cost for each additional test. Combined with a ram/tmpfs-backed postgres server that is tuned for performance, it goes extremely fast.

Currently just for golang but I'm planning on releasing equivalent-capability libraries for Python and Typescript over the next month. If anyone has any thoughts/comments/feedback/suggestions I'd be extremely thankful.

[westonOG]

Thanks for responding. I apologize for the confusion, but part of that is because I am just learning how to market this product. So, SQL Simulator is a feature of both Ratifier and Kulvert. Ratifier is meant to be a data validation or QA tool. Where as Kulvert is a CI\CD pipeline for SQL Scripts. You would use the SQL Simulator in the Ratifier to test your changes before you check them in. You would use Kulvert to apply your changes to the database if and only if SQL Simulator found no problems with the SQL Script or other logic checks.

I may need to create separate gitbooks for each of the programs so that would reduce the confusion. I hope you will find success with your product.

[gwn7]

For dry runs on RDBMS' I usually put the statements I want to test between transaction begin and a rollback statements. Maybe dump some data before rolling back to make analyses and/or comparisons.

I guess this tool probably has more utility though.

[westonOG]

Hi, thanks for responding. This tool automates the tasks you stated in your comment. Instead of having to write specialty code every time you need to test your sqls you can click a button and have the tool set up it up for you. Also what happens if you need to test your SQL against multiple database(ie IUAT/Model Office/Production) and/or multiple schemas? This is just one click in my program.

[ohlookabird]

I do the same and transactions work well most of the time. I use ZFS as filesystem for my DBMS (Postgres), which allows me to do consistent snapshots. Being able to rollback has been helpful for testing complex or large queries as well.

[gavinray]

This is pretty cool -- one thing I think you may want to highlight more is the SQL analysis.

In your demo video, when you run the following statement:

  update payroll
  set payroll_status = = 'X'
  where ...

In the simulation DB schema, it picks up the FK from (payroll.employee_id <-> employee.id) and creates the employee table + inserts the related employee records for the affected payroll records in the parent statement.

That's pretty nifty.

---

On an unrelated note, I think that creating an open-source version of this on the JVM using tools like jOOQ, SchemaCrawler, and Apache Calcite could be a fun weekend project.

[westonOG]

Thanks for the feedback. I use Java to do the static code analysis, C# for the front end and DB connections. One of the biggest issues I've had is trying to explain what it does and how it does it. If I say too much I scare people away. So I am not sure how much information to give at once. As for coding it over a weekend, it's taken me a few years to get it to this point I doubt I could re-write it that quick :-)

[simonw]

It's not obvious which databases this software is compatible with.

After clicking around a bit I found https://docs.tribalknowledge.tech/ratifier-tutorials/sql-sim... which indicates it works with both Oracle and SQL Server.

[westonOG]

Thank you for your feedback. I added it to my comment. Where else should I add this information?

[remram]

I had this exact use cases a few months back: a big SQLite DB I didn't want to duplicate (and use twice the storage) before testing a script, on a filesystem without copy-on-write.

I wrote a fuse tool to provide copy-on-write for the file instead: https://github.com/remram44/cowblock

[westonOG]

OK, Thank you for the feedback. How much traction have you been able to get for your project so far?

[sigg3]

I like how you try to cram as many stock photos as you possibly can into the rather sparse project brochure type page. Why no screenshots?

Cool project though. Since it's proprietary I'm less likely to pick it up, but I wish you the best.

[westonOG]

Hi, I appreciate the feedback. Which page are you referring to that has stock photos? I am not exactly sure what I need to show people. This program does a lot of batch processing and most of the questions I get are usually related to how does it work.

[SoftTalker]

if it's using the query and "where" clause to get data from the live environment to set up the "dry run" environment, it's not exactly a "dry run."

A poorly formed query or WHERE conditions can put a potentially disabling load on a database server.

[westonOG]

You are correct. But if someone ran that DML in production it would have the same effect but worse. I am thinking of ways to reduce this issue in the future. I have not settled on a solution just yet.