shaundiggity
DTDrizzle Team
•Created by pato on 6/21/2024 in #help
When and how to perform transactions
Not sure I fully understand your question, pato, so I'll just answer the best I can. The most popular use of transactions are used with regular SQL statements, and centers around insert/update/delete statements. If you are doing a single insert or a single update or single delete then really there is no need for a transaction. But in some cases we might need to insert a row and update two rows atomically, meaning we want all 3 of these to succeed together and if one of them fails we want to rollback the entire set of changes. This is where transactions come into play.
You would want to start a transaction and then do the insert and 2 updates then commit the transaction. Your changes to the db wouldn't be visible to other db clients until the commit is done. If you want to get your hands oh-so-slightly dirty with transactions check out this online demo that I created. https://onecompiler.com/mysql/42hwz9kwv It is a MySQL example where two transactions are used. Feel free to edit and run it. It doesn't deal with DDL statements, just inserts. (Side note, I'm not clear if drizzle-kit's migrations for postgres are able to be used with transactions or not, so that is part of the reason I didn't give an example with postgres DDL) If you can grasp how this online example works, then it will help you to understand how drizzle's implementation of transactions works a bit better.
4 replies
DTDrizzle Team
•Created by Lateef Idris on 6/20/2024 in #help
Drop DB
I haven't tried it but I'm guessing you could just use the
sql function: await db.execute(sqldrop database db_name_here;);
You might also considering running a truncate on all your tables instead of a drop/create database, that way you don't have to run the migration again.2 replies
DTDrizzle Team
•Created by pato on 6/21/2024 in #help
When and how to perform transactions
For the three main databases that Drizzle supports, I believe all of them support doing transactions for regular SQL statements (insert, select, delete, etc). However, when it comes to DDL statements (create table, drop table, etc) I believe it is dependent on the specific database. I'm pretty sure that MySQL does not support wrapping DDL statements in a transaction since every DDL statement has an automatic commit which we users can't do anything about, but I think that Postgres allows DDL statements to be wrapped in a transaction. Hope that helps.
4 replies