HN Explorer

Show HN: SQL Dry Runs with SQL Simulator (tribalknowledge.tech)

45 points by westonOG ↗ HN
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...

27 comments

[ 3.5 ms ] story [ 46.4 ms ] thread
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.

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.
westonOG
How does this work when you need to test your script against multiple databases and multiple schemas? Is this for an enterprise size application?
quickthrower2
I am interested in your use case where you have a situation with multiple databases, possible with different schemas, where you want to run the same sql on all of them. Maybe some kind of data lake? I have not come across this. But admittedly I have not worked in enterprise (I take to mean thousands of employees)
westonOG
In enterprise environments this is pretty common. I've worked at places with over 100 developers and 30 testers. So you have a situation with hundreds if not thousands of databases and then multiple schemas in each database. So imagine trying to manually validate if your script will work on all those databases and against all those schemas. How difficult of a task that is.
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.
gwn7
I'm sure it can't match the flexibility & features your program offers, but a hacker's way would be providing that functionality via a one liner targeting their database. Assuming that dbs.txt contains a list of connection strings and migration.sql contains a set of SQL commands, an example for Postgres would be:

  cat dbs.txt | xargs -I% sh -c 'cat migration.sql | sed "1ibegin;" | sed "\$arollback;" | psql %'
Don't get me wrong, I'm not trying to shit on your product; just wanted to share the way I accomplish basically the same goal. Congrats on your release and hope it gets the attention it deserves.
westonOG
Don't worry I have a thick skin. It's better for people to tell me what they don't like or if there is a better way to do something than to say they like the product when they really don't. The really nifty thing with my program is you can see what the data would look like. I can't tell if your script does that.
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.
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?
remram
As far as I know, it's only been used by me, once.
westonOG
Out of curiosity, What kind of marketing have you done so far?
remram
This comment above is it so far. I am not trying to get make it popular, I just thought it was relevant to this thread and might help somebody else.
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.

joshSzep
First priority, IMHO, is to support running on Linux and Apples.

For an open source business model consider "open core". Open source the core engine, and charge for additional features. I would recommend SQL Server and Oracle be paid enhancements as companies in that ecosystem are used to paying for tools. Keep postgres/MySQL/SQLite, etc open source as they are tools that developers using open source would be using.

Then charge through the roof for the additional features. I recommend at least two tiers: a lower price for access to the premium features, and an "Enterprise" tier which adds in support guarantees.

Don't do a one-time payment either. Make it a subscription to receive new updates etc.

The low tier could be ~$50/month, the high tier can be "contact for pricing": but if contacted start the discussion at $1000/month. Then allow them to negotiate a lower price, perhaps with your absolute floor being $500/month.

This is all armchair advice, so take it with a grain of salt. But I hope it is helpful.

westonOG
OK, Thanks for the info. I will keep that in mind.
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.

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 :-)
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.