pgdbf VS slonik

My Internet claim to fame was writing a program to get people off of FoxPro to PostgreSQL: https://github.com/kstrauser/pgdbf

FoxPro was nifty in many ways, but nightmarish outside the “single person running the app with the database on their local hard drive” setup. The moment you tried to put the database files on a file share (which is how you used it as a network DB), it was a world of locking pain. And a fun fact: the client libraries were single threaded to the point that you could only run one query at a time per machine. If you had 2 apps running at once, only one of them could be querying at any given time.

Sigh, if only. OK, so the rest of the story was that the original app was written in Visual FoxPro. I was hired to build a web frontend for it. Well, turns out 1) there is, or at least was, no native VFP client for Unix, and 2) the Windows client was single threaded per host, so you couldn't even farm the connectivity out to a Windows process. After struggling with this for a while -- a cluster of Win XP hosts running a single-threaded VFP client and serving results to a Linux webserver via XMLRPC, as was the custom at the time -- I finally said "screw this, let's brute force it." That turned into https://github.com/kstrauser/pgdbf, which is an app to convert a VFP table into a PostgreSQL table. We had it running on a cron job, which worked fine because the website was read-only and it was the VFP app that was actually writing to the tables. It was alright to have up to a few hours of latency between the VFP view of the data and the PostgreSQL view of it.

When the company later committed to rewriting the VFP app in a sane language, they wrote it to run directly against PostgreSQL. That was quite a few years after I'd started there, though.

By orders of magnitude, my most popular open source release was a project to help people migrate from FoxPro to PostgreSQL: https://github.com/kstrauser/pgdbf

FoxPro was cool for desktop apps, but couldn’t make the leap to networked clients, where “networked” was more than “has access to the file share where the database files live”.

In the early 2000s I was hired to write a website that published reports from data stored in a Visual FoxPro database. A not-so-fun fact I learned: the VFP database libraries are single-threaded at the OS level. That is, you couldn’t run more than one query on the same machine at the same time, even in different processes. One would block until another finished. In a fit a panic and madness, I ended up writing an XMLRPC service (“which was the style at the time”) in Python, deploying it to multiple old Windows XP desktops we had laying around, and writing a database adapter for the web server that would send queries to those servers round-robin. Need more parallelism? Add another Windows XP box running my janky little service. It was awful, but it let us ship the project.

Later I wrote pgdbf so that we could run a cron job that would copy all our data out of FoxPro into PostgreSQL so that I could code against a real multi-user database that was vastly better in every way. By accident, I released it at a time when the world was wondering how they were going to migrate from FoxPro to something else. Turns out VFP was wildly popular in South America, and pgdbf turned out to be wildly popular there too, which let to me getting lots of email in Spanish and Portuguese and offers to come talk at user groups. I turned those down because what was I gonna say, “yeah, it was painful for me, too. Anyway, here you go and good luck!”?

And sometimes invest instead in learning a technology rather than hide it: for example slonik encourages you to write normal SQL queries by making SQL templating easier and safer. In turn, your IDE would be able to understand those queries and give you support based on the database schemas you actually have.

I'd push you to consider using postgres, slonik or similar for database queries. With these libraries, you just write SQL, but they perform input sanitization for you. So you can safely write:

You can already use postgres with Slonik.

https://github.com/gajus/slonik#user-content-slonik-how-are-...

It is not going to be the default because it is way slower.

https://github.com/gajus/slonik/actions/runs/6616647651

Test node_version:18 test_only:postgres-integration is taking 3 minutes.

Test node_version:18 test_only:pg-integration is taking 38 seconds.

For those uninitiated, Slonik is a battle-tested SQL query building and execution library for Node.js. Its primary goal is to allow you to write and compose SQL queries in a safe and convenient way. Now, let's see how it pairs with Express.js.

I am the maintainer of Slonik and I am trying to understand what portion of this sub-users are using Slonik vs other libraries, and if they are using anything else – what are their reasons for it.

It's nice that they implemented string templates essentially exactly the same way Javascript template literals and tag functions work. They even give an example of using it to create a prepared statement (e.g. DB."SELECT * FROM foo WHERE bar = \{inputParam}") which is exactly what many NodeJS libraries due, e.g. Slonik https://github.com/gajus/slonik, like sql`SELECT * FROM foo WHERE bar = ${inputParam}`;

I've found libraries like Zod useful when interacting with external data sources like a database. Slonik[1] uses Zod to define the types expected from a SQL query and then performs runtime validation on the data to ensure that the query is yielding the expected type.

I don't think it's necessary to use Zod/runtime validation everywhere, but it's a nice tool to have on hand.

[1]https://github.com/gajus/slonik

Demonstrate how easily and accidentally one can make an SQL injection with these:

https://github.com/porsager/postgres

https://github.com/gajus/slonik

Have you tried Slonik (https://github.com/gajus/slonik)? It won't generate types from queries automatically, but it encourages writing SQL vs. a query builder and allows type annotations of queries with Zod. Query results are validated at runtime to ensure the queries are typed correctly.