Is this Stupid? What do you think? lazarus / 16-Databases ---------------------------------------------------------------------- EganSolo · 2025-04-18 06:31 I've got a project keeping its data in 24 CSV files. These tables each have a primary key, an integer, and a name, which is required and must be unique within each table (but could be duplicated across tables).  I created a SQLite database that amounted to 2.2M, but then I questioned why I needed a database. Keeping in mind that: I've never been fond of the database-aware components since I don't like hard-wiring the UI to the DB.It's a single-user project.It'll see slow growth. I would be surprised if any of the tables reach two thousand rows. Given all that, I'm considering the following approach: Create a Tcsv_database class that Keeps the data in 24 tables consisting of a TStringGrid + some additional metadataKeeps an index for all the names based on TStringList. Each entry would have an associated object that would hold The value and a secondary list that tracks the spot of that value and its ID in every table it appears in. Since the tables are normalized, I'd have to handle the views in code, but it's not as onerous as it may seem since the views are relatively straightforward to build. I like the simplicity of this setup. It's easy to implement and easy to read. Are there glaring gotchas I need to think of? ---------------------------------------------------------------------- Thaddy · 2025-04-18 06:59 I don't understand 1. ---------------------------------------------------------------------- cdbc · 2025-04-18 07:08 Hi Hmmm, fragile setup?!? Regards Benny ---------------------------------------------------------------------- d4eva · 2025-04-18 07:10 Quote from: EganSolo on April 18, 2025, 08:31:26 amI've never been fond of the database-aware components since I don't like hard-wiring the UI to the DB.It's a single-user project.It'll see slow growth. I would be surprised if any of the tables reach two thousand rows. Famous last words Don't try to re-invent the SQLite. And you don't have to "hard-wire the UI to the DB". Have the database and UI separated. ---------------------------------------------------------------------- MarkMLl · 2025-04-18 07:18 Quote from: EganSolo on April 18, 2025, 08:31:26 amI've never been fond of the database-aware components since I don't like hard-wiring the UI to the DB. That's rubbish. Even if you put the DB-aware controls on the form at design time you can set up all the credentials etc. at runtime. And you can set it up for multiple database types if you want, subject to SQL etc. compatibility: I've done this for PostgreSQL + Firebird as an example. MarkMLl ---------------------------------------------------------------------- CharlyTango · 2025-04-18 07:30 In such a case, I would always favour the use of a DBMS over flat file data. If it is a single user, SQLite is a good choice. You would then have to programme all the query options that you have with SQL, which is not worth the effort. You also don't have to use database-sensitive controls and can do everything manually. In your case, you only have the illusion that you are not hardwiring your data, because you are only choosing between a flat file variant and a DBMS. Both are hardwired as long as you do not programme a dynamic intermediate layer that is able to handle different file formats (for flatfile) or table structures (for SQL) ---------------------------------------------------------------------- JD · 2025-04-18 09:53 Quote from: EganSolo on April 18, 2025, 08:31:26 amI've got a project keeping its data in 24 CSV files. These tables each have a primary key, an integer, and a name, which is required and must be unique within each table (but could be duplicated across tables).  I created a SQLite database that amounted to 2.2M, but then I questioned why I needed a database. Keeping in mind that: I've never been fond of the database-aware components since I don't like hard-wiring the UI to the DB.It's a single-user project.It'll see slow growth. I would be surprised if any of the tables reach two thousand rows. As someone who used to write VBA code to maintain data in Excel spreadsheets, my humble opinion is using a database like SQLite makes your work easier to manage, easier to retrieve information and above all enforce data integrity and data security. I have however written code to export the data to Excel/CSV files (using the fpspreadsheet library) to share with others if necessary. Even for small volumes of data, I prefer to use a proper database. Just my two cents.  Cheers, JD ---------------------------------------------------------------------- EganSolo · 2025-04-26 00:30 While I appreciate the passionate and committed ( ) responses, I would like to offer a different perspective: When considering a problem, I tend to list the key architectural qualities that I consider important for that effort and let these qualities drive my choice. In this case, the folks who replied are squarely against that idea, and their positions center on four qualities: 1. Ease-of-use             : SQLite (as an example) + DB-Components are easier to use. They are proven and battle-tested 2. Ease-of-Management: Relying on SQLite makes it easier to manage the application. 3. Speed of development: By leveraging these components, coding will be faster. 4. Speed of refactoring   : Rewriting SQL statements is much easier than coding. These qualities are worthy of consideration. Nevertheless, there's another side to this coin: 1. Uniform debugging: Free Pascal doesn't treat SQL statements as first-class. It can't compile or debug them. By foregoing the DB, I'm able to leverage the IDE to its fullest 2. Transparency: My data is in CSV files. I can open them in a text file if I so wish, or leverage the full power of Excel 3. Reduction of bugs I don't understand: When using (or misusing) a component, I run into bugs I don't understand because I'm not the maintainer of the code or because I'm missing the author's original intent. By keeping the back-end of the app lightweight, I reduce these issues. You don't think that's important? Check the SQLite threads in the forum  In this case, these last three trump the deck for me. Once I'm done writing this app, I'll return here and let you know how it went (the good, the bad, and the ugly). Cheers, and thank you for your sincere and passionate statements. I always look forward to hearing what you have to say. ---------------------------------------------------------------------- TRon · 2025-04-26 00:41 Quote from: EganSolo on April 26, 2025, 02:30:33 amReduction of bugs I don't understand: When using (or misusing) a component, I run into bugs I don't understand because I'm not the maintainer of the code or because I'm missing the author's original intent. By keeping the back-end of the app lightweight, I reduce these issues. You don't think that's important? Check the SQLite threads in the forum  Do note that most of these threads stem from users that ran into issues which did not bother to a) do proper research before starting (comprehension) and b) do not understand the concept of SQL databases in general. As an additional note SQL statements /can/ be very daunting if you try to encapsulate complicated queries. Sometime people do not understand basic concepts and differences as f.e. dropping components on a form vs creating them at runtime or setting properties at design-time vs at runtime (you fell for that as well). Just like any tool/solution also SQL is not the (only) holy grail. It depends on the project. ---------------------------------------------------------------------- dbannon · 2025-04-26 01:09 Quote from: EganSolo on April 26, 2025, 02:30:33 am... 1. Uniform debugging: Free Pascal doesn't treat SQL statements as first-class. It can't compile or debug them. By foregoing the DB, I'm able to leverage the IDE to its fullest 2. Transparency: My data is in CSV files. I can open them in a text file if I so wish, or leverage the full power of Excel 3. Reduction of bugs I don't understand: When using (or misusing) a component, I run into bugs I don't understand because I'm not the maintainer of the code or because I'm missing the author's original intent. By keeping the back-end of the app lightweight, I reduce these issues. You don't think that's important? Check the SQLite threads in the forum  Nicely reasoned arguments Egan, I thank your for your polite and focused contribution. I think I'd dispute item 1. - in an ideal world, you don't need to debug any deeper than the SQL level. But that assumes your SQL is perfect ! I was once a database manager, before the term existed. SQL was new then, relatively standard and I was, perhaps, reasonably good at it. If that person, me, 35 years ago was starting your project now, SQL would be the way to go. But me, now, I have forgotten it all, its changed anyway, I would certainly go with plain files. Or, maybe JSON ?  Not xml. David ---------------------------------------------------------------------- egsuh · 2025-04-26 03:21 MS Access? It does not need to write SQL directly. Just create links. It's not easy either. ---------------------------------------------------------------------- Handoko · 2025-04-26 05:52 Using SQL connection + dropping data aware components on a form may sound great, as it follows the slogan "rapid application development". But in reality, it may introduce headache and make the program harder to maintain because the source is no longer purely code. We cannot maintaining it by using text searching feature. We need to select the related components and click the its properties to see the settings. Data aware database components are good for prototyping. When working on real database programs, I write my own module for doing connections, reading, editing/writing, deleting and data validation. I do not use DBNavigator, DBEdit, DBCombobox, nor any data aware components. User forms only fetch and send data to the module I wrote. By doing so, it centralizes all the database related issues on a single location. Maintaining and debugging become much easier. ---------------------------------------------------------------------- paweld · 2025-04-26 06:10 You didn't write what the purpose of this program is, and I think this also affects the answer. Although already based on what you have written like everyone above I recommend switching to SQLite. On the plus side: - you have one file rather than 24 (or even later more) - easier to manage, archive - for reporting you don't have to modify the program every time you want to add a new report or modify an existing one, all you need is a simple query editor - easier to pull data if there are relationships between tables, of course you need to know a little SQL. On the downside: - if your “editor” is MS Excel, then here you have to create the interface yourself to manage (add / edit / delete) data. Also, I don't know what the deal is with these DB-Aware components - there is no obligation to use them if you use databases, you can still rely on standard controls ---------------------------------------------------------------------- Nicole · 2025-05-05 12:25 Ten years ago I was in exactly the same situation. I had tables and tables of ASCII-format. It was a hard decision and a long way. I changed to database. Today I use Firebird, Flamerobin and IBX. I am that glad I did it.  No, it was not easy, but I am glad. Here are the advantages I am happy about: The data are much more compact. Not sure how much, but may be a factor 10 or even 100. The backup of all my code and the database is done within 8 seconds a day. I save the data back to the year 2000. You say, your data are accurate, - however there happens that quickly an error. A database refuses a wrong entry. cvs allows it. I work alone as well. You carry errors with you for years. Nobody cares, if you do not find it. The possibilities of a database are beyond compare, because you can use SQL. SQL is an extremely sophisticated tool, quick, easy, code can be written by machines and you can ask nearly with it. You get information about your data you have not dreamed of. cvs is damaged that quickly. And you are not aware of. One wrong click into the data, - destroyed. The possibilities how database-data can be drawn and written are prefab. You write yourself preshaped pipelines into your database and build it like lego. E.g. I can say, "give me all information you can find about one stock and write them to a nicely formated string". The function behind searches for old quotes, daily quotes, news, ticker, dividend, grabs some server for updates and uses SQL for this. What I write is: "RichEdit.Lines.Add (GetSTOCKInfo of....);" Work done. May be you want to write some interfaces, which read your csv-data and write them into your database. So you have a smooth change. You mentioned the good portability. All my environment is in a Win 7 VM. So I  transport my Lazarus and my source code with the database. ---------------------------------------------------------------------- EganSolo · 2025-09-13 02:14 Well, it's done. Recap: I asked if it was a stupid idea to forgo using a database as a back-end and rely on csv files instead. I asked that question on April 26. Nearly six months and 35,000 lines of code later, the minimum viable product (MVP) version of my program is up and running. I don't want to bore you with lots of details, so I'll be brief. Use-case: I needed a replacement for Protégé 3.5, an anthology management software that I used to manage the ancillary data of a High-Fantasy epic I've been writing and publishing under a pen name. In case you're curious, it's called The Epic of Ahiram. It's three times the size of The Lord of the Rings (I'm not bragging -- there are much larger stories) and has hundreds of characters, objects, locations, events, etc, that I needed to track to preserve the story's consistency. Protégé 3.5 did a great job, but it was discontinued, and I needed a replacement. The newer version went in a direction that didn't serve my needs. The salient characteristics of the program I needed to write were: -A small dataset (currently at two megs). Mostly textual. -High relational density. The program needs to track relations between characters, objects, locations, sayings and a dozen other concepts. -Flexible design: the beauty of Protégé 3.5 is that it allows you to modify your classes, fields and forms on the fly. MVP implemented a form editor that allows me to change the editor of a class on the fly, but the classes and fields are hardwired and map to the classes and fields I used in Protégé 3.5. Selected MVP features Powerful search capabilityPowerful query capabilityFlexible data import/export.Timed save of the data.Auto-backup on app startup Implementation Details The back-end consists of an array of TStringGrids, one per table. Currently, there are 32 tables, so 32 TStringGrids. The DB unit is a little over 10k in code length, but the portion of the code for CRUDs operations is less than 1,000 lines—the rest deals with searching, and communication with the mid and front end. Overall, the loading, storing, creating and deleting data operations are less than 10% of the overall code. Would using a DB engine have led to a faster time-to-market? Perhaps, but it would have been marginal. As an aside, I once wrote a full-blown sequencer for a real- time event bus, and I wrote it in SQL, using ORACLE's own Java framework. I know SQL and I love it, but I love even more the KISS principle that allowed me to debug my code end-to-end and keep clarity on the way operations were implemented. MVP--by definition--is meant as a quick-and-dirty first implementation to get you up and running. My code is mostly clear, but some confusing bits and pieces are hardwired to the data. That will change in the next iteration. Would I switch to a DB engine? At this point, I don't see the advantage. Could I switch to a DB engine? Absolutely, since the core DB code is well contained. Conclusion Was not using a DB engine the right solution? It's _a_ right solution. It's not the only one, but given my constraints and the use case I'm solving for, it ended up being a good fit. Could that change in the future? Absolutely, but for now, I'm relatively satisfied with the result. ---------------------------------------------------------------------- 440bx · 2025-09-13 02:33 I just wanted to say that, generally speaking, using a DB such as PostgreSQL is good for the flexibility it offers. Particularly when the number of ways the data can be queried is "unlimited". OTH, if the dataset is relatively small, say less than 512MB _and_ the number of ways the data needs to be queried is limited and known in advance then having the data in flat files, loading them when needed and _indexing_ them dynamically in memory offers an in-memory database that is usually blazingly fast and is a good option for a single user application that uses data that will be queried a known number of ways. ---------------------------------------------------------------------- dbannon · 2025-09-13 03:26 Wow, that is interesting !  I have often wondered how (good) authors keep each character's personality distinct, and sometimes, even, evolving. Sadly, I do not purchase from Amazon. But thanks Egan for an insight into the process. Davo ---------------------------------------------------------------------- CharlyTango · 2025-09-13 18:04 @EganSolo Give ZMSQL a try https://wiki.lazarus.freepascal.org/ZMSQL You can get it by OPM and it seems to be exactly what you're looking for. A "database" based on csv files in memory with the possibility to query by SQL statements ---------------------------------------------------------------------- marcov · 2025-09-13 20:24 Quote from: EganSolo on April 26, 2025, 02:30:33 amThese qualities are worthy of consideration. Nevertheless, there's another side to this coin: 1. Uniform debugging: Free Pascal doesn't treat SQL statements as first-class. It can't compile or debug them. By foregoing the DB, I'm able to leverage the IDE to its fullest 2. Transparency: My data is in CSV files. I can open them in a text file if I so wish, or leverage the full power of Excel 3. Reduction of bugs I don't understand: When using (or misusing) a component, I run into bugs I don't understand because I'm not the maintainer of the code or because I'm missing the author's original intent. By keeping the back-end of the app lightweight, I reduce these issues. You don't think that's important? Check the SQLite threads in the forum  4. Any application that operates on a very large % of the dataset is not for a standard database (dedicated in memory databases however might work). I have several applications that do several forms of analysis on multi gigabyte datatsets (up to 10GB with indexing) that usually touch every byte each query. I don't use binary formats rather than CSVs, but principle is the same.   Some of the query also need context, e.g. if a query yields a result "x", I also want the rows before and after that match the same entity. Maybe this is old fashioned nowadays, but the base framework was from a case with about 300MB DBF datasets on a P4, in a time that even 1GB was a quite extreme luxury.  I hooked into the application server to create a logfile of its actions that fed an oracle database, allowing me to creating a shadow dataset, based on last weeks dump + mutations.  The dump was from Oracle to DBF for an older system. This app is probably the reason why TDBF is part of FPC now :-) The consultants for the application server (with Oracle database) spelled doom and gloom (scalability, correctness etc etc), but the fact was that my analysis took 1m for data loading and 4s for the query. Their (Java J2EE) application ran for 3-4 hours, and frequently crashed and had to be restarted. Some days they didn't even manage a result (they ran once per quarter for the official billing, I ran mine weekly for statistics for the management and running projects). Scalability was truly not a problem, the dataset was naturally partitioned (by muncipality), and could have easily been made more scalable by pulling some more P4s out of the dumpster and investing Eur 200 in extra memory for them and running the program on multiple machines with subsets of the dataset, and a simple combining program. Moral of the story: yes, RDBMS is the sane default. But never close your eyes for the exceptions. Very large result sets or a very variable analysis (hard to pre-index, needing lots of temp tables in the SQL way) might be predictors for such exceptions. ---------------------------------------------------------------------- EganSolo · 2025-09-13 21:51 Quote from: CharlyTango on September 13, 2025, 08:04:27 pm@EganSolo Give ZMSQL a try https://wiki.lazarus.freepascal.org/ZMSQL You can get it by OPM and it seems to be exactly what you're looking for. A "database" based on csv files in memory with the possibility to query by SQL statements Thanks, CharlyTango! I'll seriously consider it post MVP. Right now, I'm on a tight schedule to get Book 8 of the Epic out by end of year. Nevertheless, ZMSQL looks promising. ---------------------------------------------------------------------- Zvoni · 2025-09-15 07:18 What about a "hybrid"-approach? Keep your data in your csv flat files, but use the "InMemory- Database"-feature of sqlite? In a nutshell: On Program startup (or whenever you read your flat files), instead of throwing the Data into that Array of StringLists, create an InMemory-sqlite-DB, throw your Data into those "virtual" tables (ForeignKeys supported!!), use SQL to your hearts content, on Program shutdown, serialize back to flat files. You can even use UserDefinedFunctions like Regex (search this forum for my sample-code) ---------------------------------------------------------------------- EganSolo · 2025-09-15 08:53 Hi Zvoni,   The approach you proposed is sound and has many advantages, which other contributors have highlighted.   I guess the one quality I wanted to preserve was code simplicity: keeping the entire code in Object Pascal means that, from a debugging standpoint, each line is a first-class citizen, capable of being inspected and debugged. Mixed code with SQL and Object Pascal means that to debug SQL, I have to rely on external means, stepping away from the IDE. Having extensive experience with this mixed model, I wanted to know what would happen if I kept the entirety of the code in Object Pascal.   My experience suggests that for a small data footprint, that approach works well from a time-to-market and performance standpoint. As things stand, I understand a large portion of the code. Furthermore, by overusing TStringGrid instead of other facilities to manage CSV files, I've kept my dependencies on components to a minimum, which meant that I had a greater chance to avoid mystical bugs, that is bugs in components I didn't write and I don't understand.   Are the qualities I chose to optimize for the best in all cases? Certainly not! I understand and respect the choices made by other coders implementing different use cases or who wish to optimize other qualities. Knuth called his classic tomes "The Art of Computer Science" to highlight the choices we make.   Am I fully sold on my current approach? No. My code is highly encapsulated, and the effort to switch the TstringGrid-based backend with a SQLite one would take no more than ten days (with testing) to complete.    When I started, I set out to find out if this approach is foolish, and, at this point, I would say the jury is still out, with a leaning toward "No. It depends..." ---------------------------------------------------------------------- Zvoni · 2025-09-15 09:47 Quote from: EganSolo on September 15, 2025, 10:53:12 amHi Zvoni,   The approach you proposed is sound and has many advantages, which other contributors have highlighted.   I guess the one quality I wanted to preserve was code simplicity: keeping the entire code in Object Pascal means that, from a debugging standpoint, each line is a first-class citizen, capable of being inspected and debugged. Mixed code with SQL and Object Pascal means that to debug SQL, I have to rely on external means, stepping away from the IDE. Having extensive experience with this mixed model, I wanted to know what would happen if I kept the entirety of the code in Object Pascal.   My experience suggests that for a small data footprint, that approach works well from a time-to-market and performance standpoint. As things stand, I understand a large portion of the code. Furthermore, by overusing TStringGrid instead of other facilities to manage CSV files, I've kept my dependencies on components to a minimum, which meant that I had a greater chance to avoid mystical bugs, that is bugs in components I didn't write and I don't understand.   Are the qualities I chose to optimize for the best in all cases? Certainly not! I understand and respect the choices made by other coders implementing different use cases or who wish to optimize other qualities. Knuth called his classic tomes "The Art of Computer Science" to highlight the choices we make.   Am I fully sold on my current approach? No. My code is highly encapsulated, and the effort to switch the TstringGrid-based backend with a SQLite one would take no more than ten days (with testing) to complete.    When I started, I set out to find out if this approach is foolish, and, at this point, I would say the jury is still out, with a leaning toward "No. It depends..." I did read through the Thread, and i got your reasons for doing it your way. Don't get me wrong: In the end you have to be happy with your solution. But: I wouldn't be surprised, if the majority of your code is (basically) rewriting a "database-logic": Adding, Updating, Deleting, Finding "records" etc. By using a real database-engine, you wouldn't need that, nevermind gaining automagically DRI (Data Referential Integrity) and other powerful features As for "debugging code which isn't Pascal": You would only have to "debug" your SQL-Statements, which is pretty easy for SQLite: for design use an external tool like DB-Browser for SQLite, design everything, test your SQL's, copy all Statements from there, put it in your Lazarus-Project as String-Constants. Use them in a logical order (e.g. creating Parent-Table before child- table etc.) Done (more or less). But as you said: Your choice, and we have to respect that (which i do) ---------------------------------------------------------------------- BeniBela · 2025-09-15 12:18 I used CSV for all my data but then I thought I would modernize it and switched to XML. (and I had to change my files anyway because I wanted to add new columns and my CSV files didn't have a header. And I was using Delphi 4 which didn't have a UTF-8 string) but that made everything worse, I should have stayed with CSV but one can discuss the separators. Commas are the worst, there is really no reason to use commas. It is probably best to actually learn ASCII. There were already separator characters just for this purpose: GS, RS and US (U+001C~U+001F)