raw sql, truncate, migrations

[codingedgar]

Description

• Is it possible to execute raw SQL? (targeted to DB)
• How can I truncate a Table? (Postgres)
• Is any companion lib for Intended use with migrations?

[Thorium]

Currently this is a small single tool. So there is no raw SQL execution nor migrations.

Creating a raw-sql connection manually shouldn't be many lines of F#, but then you'll have to know database specific language and you'll lose strong typing. Something like this:

let ExecuteSql (query : string) parameters =
    async {
        use rawSqlConnection = new MyWhateverDatabaseConnection(cstr)
        do! rawSqlConnection.OpenAsync() |> Async.AwaitIAsyncResult |> Async.Ignore
    //  Console.WriteLine (query)
        use command = new MyWhateverDatabaseCommand(query, rawSqlConnection)
        parameters |> List.iter(fun (par:string*string) -> command.Parameters.AddWithValue(par) |> ignore)
        if rawSqlConnection.State <> System.Data.ConnectionState.Open then rawSqlConnection.Open()
        let! affectedRows = command.ExecuteNonQueryAsync() |> Async.AwaitTask // Async.Catch and Choice2Of2 logging
        rawSqlConnection.Close()
        match affectedRows with
        | 0 ->
            // Logging: "ExecuteSql 0 rows affected: {query} {System.Diagnostics.StackTrace(1, true).ToString()}"
            ()
        | x ->
            // Logging: "ExecuteSql " + x + " rows affected: " + query
            ()
    }

But beware, that is a poison, I've seen it before:

• SQL is easier to write than LINQ because errors are not catch on compile time, so developer rather write buggy code that compiles, to close their backlog/task-list items.
• That will cause your SQL to start to mix with your business logic, more and more is written with raw SQL. You suddenly don't find the logical errors on compilation time.
• Some developers use transactions, but some forget to use transactions with raw SQL, causing both deadlocks and the data being non-consistent.

I rather recommend to create views and stored procedures and query those with SQLProvider.

[codingedgar]

Thanks for replying so fast 👍

I looked into this project precisely for the types and compile time errors, when I saw there is no "truncate" keyword i thought that stored procedures was the way to go as well, but I also need migrations, I found that this lib offers no migrations but maybe I could write something on top of it, but for that I still need raw SQL, not all pgsql is supported.

My question was more around how to compensate when there isn't a keyword (like truncate), though that as it uses Npgsql that there should be a escape hatch to write raw SQL with Npgsql interface, and extend the syntax.

To be more succinct: what can I do when I need to write SQL that does not appear in this list (without stored procedures)

[Thorium]

What you mean by "truncate is not supported"?

Can you give example SQL that is not possible do with current LINQ?

[codingedgar]

Disclaimer: I'm indeed new to F# and the whole .NET ecosystem, so i know little F# and close to none LINQ.

Gonna use this answer here, they answer that LINQ would do context.ExecuteCommand("TRUNCATE TABLE Entity"); which if i'm not mistaken is just raw SQL. I'm looking for this option in this library, this way I can use safe queries (most of the time) and untyped queries or target specific database/complicated queries.

I see that with your response use command = new MyWhateverDatabaseCommand(query, rawSqlConnection) is possible to pass a string, not sure if MyWhateverDatabaseConnection is indeed sql.GetDataContext().

Besides the Good Practices of using raw SQL, I just wanted to know how to do it, because I find the Querying page a subset of psql.

Right now I went with raw Npgsql with a wrapper lif for F# (Don't know enough F# to use interop with C# 😅) because I need to truncate, create tables and other common SQL commands used in migrations, ie:

let truncate connection =
    connection
    |> Sql.query "TRUNCATE last_used_hashtag RESTART IDENTITY"
    |> Sql.executeNonQuery

Not that I need or use a migration framework/lib (mostly because of my lack of knowledge of a good one in F#), but I'm used to control the whole database from my app, honestly I just don't know any better. I'm used to blue-green deployments with automated migrations, and I don't know how to do it in F#, this lib seems nice and I'm sure I would be writing more simple queries as the Querying page operators support than complicated/dangerous raw queries, I just wanna know how to do it properly, calling stored procedures/functions means that the procedures are in the DB and if they're there they came with a Migration.

Hope that I explained enough my use case (be able to use the whole psql even if it means losing type-safety), and sorry for the confusion, but I don't know if the example code of your previous answer is for this library or just a generic way to do raw sql in any db.

[TheJayMann]

My solution to this (and what tends to be simpler for me than managing raw SQL strings in my application) is to build views for queries that are more complicated than what I can create using F# Linq, and stored procedures for commands to the database, which also follows the CQRS pattern. I then keep all of the DDL which maintains the views and procedure as it's own project, typically part of the same solution, so that it can be tracked in source control, same as the application itself, and have the databases updated at the same time as the application when using CI/CD.

As far as migrations go, there are a few products, such as flyway, which allow creating migrations as set of updating sql scripts which are maintained by source control. For me, given that I'm using SQL Server, I use DacPac projects, which are state based, rather than migration based, as I found it easier for our development.

[Thorium]

Yes, currently SQLProvider is not your tool to do migrations. There are specific tools for doing just migrations and I'd use those for migrations...

SQLProvider is good for queries, and can do ORM-style of tasks (like insert-update-delete-execute), but it doesn't support creating or dropping tables or columns. SQLProvider has very identical syntax and user experience over different databases.

1) Database user permissions. In the most common case your query-user shouldn't have permissions to modify the database schema structure. Now, if we would like to add some multi-database-format permission-checking-system "do you have a permission to drop a table", that would get quite complex.

2) The schema: To achieve the strong typing SQLProvider reads the database schema at design-time (and compilation time) and caches it. So if your schema varies with migration time "now this table is there and now soon it won't", then doing strong typing over time (or version numbers) would again be very complex.

Edit: My ExecuteSql was just an example of direct connection to SQL not being hard, that is not related to SQLProvider.

[codingedgar]

@Thorium Oh that's a good explanation, thank you so much for taking the time.

Now I understand the intended use of SQLProvider 🎯

So I could divide between Migrations/Low Level database stuff with Npgsql/other tools, and use SQLProvider with the services that behave as "users" of the DB per se, that do insert-update-delete-execute, and take full advantage of SQLProvider where it is supposed to be used 👍

Thank you so much for explaining 💛

[codingedgar]

@TheJayMann Yes! that is exactly what I do in other stacks 🎯

I'm just too knew in F#, and thought that I could use one tool to do some of that pipeline until I get to discover a better one that does it properly, I was starting from the beginning with migrations and testing (that require some manipulation of the tables), also to do ES/CQRS, I'm focus on the projections right now.

I'll take it to explore flyway and DacPac , never heard of the DacPac, interesting concept 🤔.

Guess that learning a language agnostic tool for a common concept like migrations is better, thank you for you insight 💛

[Thorium]

The latest version supports Dacpac.