DEV Community

[Comment from a deleted post]
Collapse
 
geshan profile image
Geshan Manandhar

Some of the things in no particular order:

  • Cross db joins
  • Downtime-less schema changes
  • Locking levels and transactions
  • Cascade delete
  • SQL flavors
Collapse
 
waylonwalker profile image
Waylon Walker

🍜 diddo sql flavors

Collapse
 
aarone4 profile image
Aaron Reese

Not being able to use windowed values (rank, lead lag etc) to filter in the base query, but the query engine is quite happy when you wrap the query in a WITH CTE AS (...) and then filter on windowed field when you SELECT * FROM CTE.

Collapse
 
helenanders26 profile image
Helen Anderson

I'd agree with all of those too :)

SQLLines has a free tool that I find myself coming back to when I need to translate between flavours - sqlines.com/online

Collapse
 
geshan profile image
Geshan Manandhar

Sqlines is a good one.

Collapse
 
waylonwalker profile image
Waylon Walker • Edited

Sounds like a cool tool, doesnt seem to work with my simple example. I will definitely keep it bookmarked to try when I have a real example.

postgresql

select * from table limit 10

Should convert to

Oracle

select * from table fetch first 10 rows only
 
helenanders26 profile image
Helen Anderson

I used it when migrating from SQL Server to Postgres and found it handy in that particular use-case.

Dates were a a real drag:

SQL Server

SELECT DATEADD(day, 1, GETDATE()) 

Postgres

SELECT INTERVAL '1 day' + NOW() 

The AWS Schema Conversion Tool is a slicker, although paid, version of the same if you are using their services.

 
geraldew profile image
geraldew

In retrospect I could have just said "date handling" and walked away from the subsequent vortex of possible discussion. At my workplace I keep a wiki page with stock clippings of date literals, date constants and date expressions for the different dialects I use. I barely read/notice them now, I just trust my decades younger self to have ensured each was right and copy-paste them into place. Younger me never worked out INTERVAL syntax, so none of them have that.

Collapse
 
aarone4 profile image
Aaron Reese • Edited

The only thing worse than cascade delete is ending up with orphaned records. If no-one specifies the foreign key relationship in the database you can end up with order lines with no order address, or orders with no customer. I agree that RI (referential integrity) can be frustrating and perhaps engines could be smarter about checking the scope of the transaction but I'd rather have the delete fail than result in corrupted data.

Collapse
 
davidrushton55 profile image
David Rushton

Downtime-less schema changes
+1

Desires state can generate unexpected/unwelcome scripts, while script based approaches can quickly become painful to manage.

Collapse
 
dmfay profile image
Dian Fay • Edited

Migra looks very promising, at least at smaller scales: make changes locally, diff against production, tweak as needed, and ship a single migration script instead of keeping old/intermediary states around forever.

 
geshan profile image
Geshan Manandhar

Good one.