SQLite and Turso

Learn With Jason S8E3 Feb 13, 2025

It’s hard to beat SQLite for quickly setting up a database on a project — but can it handle “real” projects? Glauber Costa teaches us how it works in production.

Read the transcript

Captions provided by White Coat Captioning (https://whitecoatcaptioning.com/). Communication Access Realtime Translation (CART) is provided in order to facilitate communication accessibility and may not be a totally verbatim record of the proceedings.

JASON: Hello, everyone, and welcome to another episode of Learn With Jason. Today is a snow day in Portland, I couldn't make it into the studios, so we are coming to you live from my living room. This is my bar. So, I'm excited, because today we are doing an episode that, because my schedule has been wild, we had to reschedule a couple times. I'm really, really excited to learn about this, though. You may have seen the buzz about things like SQLite, you may have seen that companies like Turso have been showing up everywhere, and today we are going to get a whole overview of what it is, why it's cool, and how we can bring it into our own projects from the man himself, please welcome to the stage Glauber Costa.

GLAUBER: I'm going all right, man, thank you for having me. We've been trying to schedule this, you're right, since December last year. So here we are. And a lot has happened since then, so it's a great time.

JASON: Yeah, it's funny, every time I do the show, we're either just past a big launch, leading up to a big launch, and what I love about it, I'll reach out to you in six months, hey, let's do it again and there's going to be so much more to talk about. Before we get too much into the tech, I want to talk a little bit about you. For those who aren't familiar with you and your work, can you give us a background on who you are and what you do?

GLAUBER: When I'm talking to people from a web background, and I'm assuming a lot of the folks watching your show come from this background and web application, mobile development, I always like to just Epp take the obvious out of the way. I don't even know JavaScript, man. I actually learned what an iframe is last year. So, go easy on me, if we get there. I was actually in a conference in Salt Lake City, one of those conferences by Kent, and I've been making a lot of friends in the community, it's a fantastic community, but I don't come from this background. If anything ever and one of the things that happens, by the way, in different industries, is people call the same thing by different names. If there's any miscommunication, just let me know. As I said, I've been learning a lot and been very active in the web community, but I personally don't come from this background. I got started in my career in tech in depending how you count, around the year 2000. So, I wasn't working or anything like that, but, you know, I was already doing open source work. Does that count? I think it does. I've seen the 2001 bubble up close. I've seen the pop, I was actually in university at the time. And around 2003, I made my first contribution to the Lenox kernel. That was the thing I was passionate about. Still in school at university, I always loved low level kind of stuff. I can't explain why. I think you will relate to that. Most of us can't explain what draws to something. The thing for me I like to do, I remember the day I learned that Linux was open source, and then I got a .h file, which isn't what I should be reading, I should be the .c file, if you're familiar with C at all. I was trying to understand what was going on, and I didn't, because I was just a guy that was learning C at the time, but mesmerized with the fact that I could see the source code for the operating system that a lot of people were using around me right there. So I got super passionate about it, contributed to you know, started contributing to the Linux kernel. I got a very warm welcome, so I would never forget my first contribution to the Linux kernel, because that's like a first kiss, you'll never forget. It was this magical moment where I sent a very simple fix to the EXT2 file system, and then I heard from one of the maintainers that I managed to introduce three bugs into lines of code, he has never seen anything like that before in his whole life. And he wished that people like me would never get access to a keyboard again. So, that was my welcome to Linux, right.

JASON: That sounds about right. Very on brand.

GLAUBER: People sometimes ask me, oh, you know, is this true, you know, the thing people hear about Linux. Yes, my first experience was that. All viral. The maintainer off the virtual file system saying, essentially, I think you should never get close to a keyboard again. So, very supportive community. Today we talk a lot about inclusion and how to get people into your community and how to grow your community. At the time, Linux was actually, yeah, F you, man, get out of here. But I did not hear his advice. It was a very, you know, impactful moment for me. Holy you feel terrible, terrible, like your whole world is like crumbling down. And it was something like now that we're talking about it, I spent a lot of time after I wrote the patch, just going through it, because, you know, first time, and you get this response, it's like you want to crawl somewhere. You know, here I am, and not long after that, I think a year and a half after so, I kept doing it, and I would keep sending contribution here or there. Mostly I started doing things at home. So, I wrote a file system, and you can actually not write to the file system, could only read stuff that was already there just to learn. I wrote a bunch of kernel modules and a driver for a modem, your audience, if composed of more younger side may not even know what a modem is, but that's the thing that did the bizarre sounds and got you connected to the Internet back then. So, you know, I've done a bunch of work like that. Eventually, I got hired by Red Hat for those contributions and that's when my career really started, because that's when I started getting real problems to work on with guidance and with mentorship and not really just a file system that you can't write to. Then I started working with problems mostly in my first many years around virtualization, which was emerging tech. Today, people do virtual machines all the time, although containers became more popular. I worked with that, too, later. But I started working with virtualization and tackled a lot of interesting problems like time keeping with virtualization. I still remember the first challenge that I had, people got me this bug, which is like when you run Red Hat enterprise Linux on a virtual machine, time sometimes goes backwards and all the applications go nuts. You know, let's figure out why, what to do, what the solutions are. It took me two years to fix that bug.

JASON: So, I see that you've spent your entire career only going after the easy problems.

GLAUBER: They would come after me, man. They were hard problems, but hard in a different way, hard in a different way, because I you should see me trying look, before that, before that, I actually did start of my career, but I did some websites with PHP, some HTML, in 2001. I find Linux a lot easier. In a sense, because it's just like my mental model versus others' mental models, organizing on the screen, parsing response, dealing with strings... that's the kind of thing I never felt at home, but understanding the hardware, how timekeeping works, how to interface the hardware was the things that I liked. My career gravitated towards that. Look, I think we want to focus today on SQLite and other things, but I'm happy to talk and tell stories, I love doing this, about the Linux part of my career. People always get curious. But after that, you know, just so week complete the arc, after that, I joined the company and that was the start of the company, that was the first start of company that I was at. And pretty much the only one except for Turso. I was always a very loyal person, so I spent like ten years doing Linux, ten years on Turso startup, and now let's hope ten years doing Turso and then I'm going to retire somewhere. That's the plan. Or maybe 20 years doing Turso, who knows? But I joined the company and for around two years we worked on a problem that a problem with a product that didn't quite work. For those who are familiar, this is called uni kernels, which is a very specialized kernel. We spent two years developing that, didn't go anywhere. Fantastic companies today. Prisma, by the way, sure your audience is familiar with Prisma. They just launched Prisma Postgres, which is based on uni kernel technology. So, this is the thing we were doing in 2012. This is what allowed Prisma to do what they are doing. You know, we work with that in 2012, maybe we were just a little bit ahead of our time, you know, didn't quite work back then, or maybe we just did not know how to operationalize it. We'll never know. But this is, you know, having a little bit of a comeback through fantastic companies like Unicraft, they are trying to push this concept again. But it didn't work and the company pivoted to a database company. That's how I got into the database space. The other day I was chatting with a friend, he was telling me, look, Glauber, I had a little bit of an orthodoxed career, I have used NoSQL before SQL. I got introduced to NoSQL and only then learned SQL, which is unorthodox. A lot of people learn SQL, SQL is what everybody learns, if you have a need later, you go into the more esoteric thing. Look, that doesn't compare to my career, because I actually wrote a NoSQL database before I learned any of that. I didn't know how to use SQL or NoSQL, I wasn't a user, because again I'm coming from the complete other side of the spectrum from the Linux kernel. For me, the database was a boring application that runs on top of the kernel and does stuff, but we spent ten years, almost ten years, in this company. And the company was called Silla. Sylla is a NoSQL database designed to run petabytes of data and stuff like that. It is something you would use when you have a tremendous amount of data. And this is how I got exposed to databases. I had a very short stint at Datadog and then I'm here at Turso.

JASON: Yeah, yeah. So, I mean, what a I love when a career is basically built on, like, I found a thread to tug at and I couldn't not tug at it, right, and followed down the rabbit hole until you end up at something maybe you never would have predicted when you started, but, you know, here you are, right.

GLAUBER: When the company announced they were going to pivot to a database company, my first instinct was to leave. I don't want to work you know, I have no interest in databases. It's still enough for me, but I wanted to keep working in operating systems, kernels, and things like that. But, thankfully, I didn't. And I got other offers and I interviewed, but at the end of the day, I really like the people, really like the company, and then I stayed. And I'm very happy with how that developed, because, of course, that put my career in a completely different direction. I learned something that I would not otherwise learn, and even with SQLite now, it's still a database, but it's the market is so different. The kind of people just imagine what I just described Sylla as being good for, petabytes of data with billions of requests there's no intersection between the use cases there and SQLite, it's zero. So, it's great. Even if you're not learning new technology, you're learning new applications, communities, use cases, all of that. So, I'm very happy with the way things progressed so far.

JASON: Yeah. So, that's actually I think a good little caveat or good segue into let's talk a little bit about SQLite in general. So, SQLite is deducing from the name, a flavor of SQL. So, using it, I imagine querying is going to feel very similar to Postgres, MySQL, things like that. Sorry, my do not disturb didn't turn on. Okay. So, doing that, what is the use case for SQLite. When would I reach for this over Postgres, MySQL, or another SQL like Mongo?

GLAUBER: SQLite is actually fairly, fairly similar to Postgres. It's all SQL, so you're right. The relational nature is there, but if you compare the syntax, for example, if you're coming from Postgres, you're going to feel a lot more at home with SQLite and vice versa. If you're coming from MySQL, you'll see a lot more of a difference and this is a syntax difference. Not conceptual. But SQLite is a database that exists in a file. The whole database, you know, not going to get too technical about it, because I think the database is also the code, but the data in the database is really just in a file, right. And you don't have in the way the SQLite was originally used, the way the SQLite again, today it goes in different directions, and we can get there, as well. That's one of the things that my company does, tries to push the envelope of what SQLite is being used for. But traditionally, SQLite doesn't require any network round trips, because it is a database in a file, so you just talk to it directly, and because of that, it is what is usually called an in process database, or by another name an embedded database. I accept both names. In process, because it runs inside your process. Embedded because it runs embedded in your application. Both ways to call it are correct. So, initially, like initially, SQLite exists for around 20 years. Initially, the use cases are things like, look, there were a lot of military use cases in the beginning. I have a military device that doesn't have any network connectivity, it needs a database right there. I'll put SQLite there. Again, because it doesn't have any network, it doesn't need a server. If you're going to use Postgres locally or MySQL locally, what you're going to do is start Postgres server or start the MySQL server, you're going to connect to that through the network, which can be local host, but still the network, and SQLite is not like that. SQLite is really just like you have a file, and you issue SQL commands to and from this file. And the code is embedded in your application. So, saw a lot of uses in the beginning in IoT devices, in military applications, and aircraft, and smart watches. So, you have always that element of like we're going to put this thing into small things. And the reason you would do this is that the alternative for those people, like those people would never it is absurd to think that in any device that you have, your toaster, you know, there are toasters these days with Internet connectivity. It's amazing, but you will not run a server process in those devices. You will not start a long lived server process. So, your alternative would be to open a file and write things to and from this file, which is what everybody did. But that's all beautiful. Writing files, it's something that I've done a lot in my career as a database writer, or as an operating system writer. It's full of caveats. If you write to this file and don't flush the right way, the data disappears. You still want to parse the things in this file, so you what are you writing, text, you need to read the text, write line by line. So, the idea there is just put a SQL database that you can connect to through the application without, you know, no spanning servers, no network. Now, what happened in the last, I don't know, five years, something like that, is that a lot of people started noticing that, man, this is actually more than I need. Even for web use cases, because I'm going to have my API server, right, and again, the definition of small changed a lot in the past five years. Your cell phone today has a lot more resources than super computers had when people were putting SQLite in those small devices. I think people started noticing, levels in the PHP community and DHH in the community, people like that started noticing, look, man, turns out that it's an offer of what I need. The advantage of things like Postgres over MySQL, imagine how incredibly fast this database is. You probably heard about the N plus 1 problem in a database, which is if I have something that was supposed to do a join but didn't do a join or any other query, your application level query can become end queries to the database. I do a query, find out the users, now I need to go and query each one of those users individually. And this is very slow. Now, this is a non issue with SQLite, because there's nothing what makes querying a database slow when talking about transactional databases, this is the network round trip, right. Each network round trip is 5 milliseconds, 6 milliseconds, whatever. Each one of those things is 5 milliseconds. In SQLite, each query is like 200 microseconds, and that's already your query. So, it is very fast. It is very fast. It can do everything locally, and then people started noticing, look, we can use the same technology to do our web database, to do the things that we want to do. It is a very flexible database. It's a database that has, you know, very easy to work with. You can just put the data have a CI pipeline, just upload the database file and don't have to spun up a Postgres container to run your testing. You can just test against SQLite file. So, what we're seeing is just a lot more people, what if I could use this for the web, what if I could use this for my other things that are not embedded devices and this is essentially what is driving the SQLite renaissance, so to speak.

JASON: And I think one of the things that I think is a giant benefit, but also I think one of the reasons that people have sort of dismissed it, is the incredibly fast startup, like if I want to greenfield a project with SQLite, I don't have to go figure out where I'm going to host a MySQL database. I don't have to worry about designing onboarding for my team. I can literally convert the database file to my repo, send it to my teammate, and they have the database in the form it was in when I committed my code. Which I don't think there's another way to do that, that's that easy. You don't have to install drivers, or you're not

GLAUBER: Don't have to install drivers, right, because the database itself is the driver. When you go to your JavaScript package and do import, you know, better SQLite 3 or the code that comes in that package is the database.

JASON: Yes, but what I mean is more like I'm not going to home brew to figure out how to set up Postgres on my team, or configuring a Docker container. I NPM install SQLite the same way I would NPM install React. And from a developer standpoint, I'm off to the races in ten seconds, whereas with a database, I might spend half a day setting up MySQL or Mongo DB or something like that because it's not part of my normal flow.

GLAUBER: Not even like an NPX install, run the binary, install the server. Again, the database is already there, in the driver, and locally the database is the combination, getting really technical, SQLite database is the combination of the code that comes in your driver, with the file that holds the data. That's it. And you just if you don't have the file, a file would be created for you. When you do the first create table or whatever. And if you have the file, I mean as you said, it becomes trivial to just put this on CI or put this on the repo, do CI, share with the people in your product, do whatever you want. But it's super, super, super easy. So, there's no other database that is as easy to get started with than SQLite. And then the idea is you keep using, right.

JASON: Well, yeah, and I think the thing so, there's one piece there that's extremely cool, which is other databases had to do a lot of work to figure out how to do something like branching, because to branch, all the stuff involved in taking a copy of your database and making sure that it was available to everybody when you open a pull request or whatever. Oh, in SQLite, I just git check out, make some changes, make a branch, and branch my database, right.

GLAUBER: I'll get a little bit more technical here, you know, to give you the full extent of how beautiful that is. Branching is a feature that's becoming very common in databases, right. And we implemented branching for Turso in I think it took around three hours of engineering work. It was essentially it was essentially you get the database file and then you copy the database file somewhere, and I have a branch of the database. Now, the eagle eyed among you will notice, well, it's not really a fair comparison, because if you have other database vendors, they can do branching at the page level, and then doing the branch it has copy on writing, you know, that branching can become really complex, because you might want to make sure, for example, that it happens instantaneously. If I copy a file, if I copy a 10 gigabyte file first of all, it doesn't take that long in modern hardware, but I have layers, layers, layers of complexity. Guess what, Linux file systems, and I wrote a lot of their code, support copy on write. So, not all of them, but in particular, the file system which is what we use internally a lot at Turso supports copy on write and this is technology that exists for 20, 30 years in the Linux kernel. So, if you are implementing branching, you'll copy a 100 gigabyte file, and that copy will not use any extra space on disc, and that copy will be done in less than a second, because the file system itself supports copy on writing, which is the technology that any other database company would use to implement branching. So, it just interacts with the operating system in a nice way, because it uses the abstraction the operating system uses already, which is a file.

JASON: That then brings, I think, the one major pushback I hear when you bring up SQLite. People think of it as a file in the codebase. Obviously, that won't scale, is sort of what you hear. You hear a lot of things about, like, well, it's not going to scale the way that something like Postgres will scale, or you're going to run into complexity issues later, or, you know, there's always kind of pushback. Well, it's good to start with, but you're not going to finish with it. And it sounds like this is very much not the case, but I don't know if I understand why. So, can you talk a little bit about that?

GLAUBER: You know, I I'm a very transparent person, and I sometimes don't like the reality is, I'm old, right. I don't like to say that, but so, over time I think I gained a little bit that's what happens when you age. You stop being able to do a lot of the things that you used to do well in your youth, but you gained this nuance that if you're truly good at it, which I'm not, it can pass as wisdom. I don't think you need a beard for that, white preferably, but I don't like too much when those discussions are having being had in black and white terms. Does it scale, does it not scale, because it all depends. What I don't want to do is just dismiss those things as not true, because they are true. But in many cases, they are not true in a way that matters. In some cases it does matter, sure, go use something else.

JASON: Always tradeoffs.

GLAUBER: Always tradeoffs, but I also want to say those things aren't necessarily things that fundamentally matter. And there's a difference there. Because if there is something fundamental from your architecture, what that means is they can never fix it. If just a tradeoff you're left with, right. So, an example of that is in NoSQL there is a lot being said about the cap theorem and the cap theorem is a very fancy way, because one of the ways you command respect in this industry is by speaking in very fancy terms. You learn that over time. Say the cap theorem and people think you're a genius or some shit like that. It's a very fancy way of saying that, like, things are either consistent or available. If you have a distributed system, and if you want all of your nodes to always have the same data, right, not going to be available if a node fails, because if a node fails, that node cannot have possibly the latest data. And if you want your system to always work, might be some of the nodes don't have the latest data, right. It's a fancy way of saying that. Distributed systems are built to operate either in the C side of the spectrum or the A side of the spectrum. Like either you're designed to be always on, which was the case, for example, with Sylla, the NoSQL database in which I worked for ten years. And what that means is they are not going to be consistent, you know. If you need consistency, you're not going to use the database. Or you're designed to be always consistent, which means you're not going to be available. If you have a network issue. That's it, nothing you can do. It's a fundamental tradeoff. There's absolutely nothing that you can do and you have to live with that. In a lot of the things, and this is why we created a fork of SQLite, that's how our company was born. A lot of the tradeoffs that SQLite have, we don't believe they are fundamental. We believe they are an implementation detail. What that means is yes, SQLite as it is today forces those tradeoffs on you, but it doesn't mean that they are fundamental. It means that there are things we can do at the code level, we can improve the database, not to have those tradeoffs anymore. So, I want to start by acknowledging one tradeoff that is very, very true. So, I'm going to three things. One is the tradeoff. Why I don't think it matters for a lot of people, but for some people do. And in which way you can fix it. SQLite is, I believe the right term is dog shit, dog shit performance. That is the technical term, I presume. The way it happens is you cannot write two things concurrently to the same database file. It does not matter if they are going to different tables, it doesn't matter if they are going to different rows. It just does not matter. Every database has only one writer at the time. Not too long ago, if that writer was active, you also couldn't read from the database, and that essentially means it's unusable for the web. Today, in 2025 SQLite, you can read from the database while you have that write locked, so that's fine. There are many use cases in which you'll read a lot. But if you're writing, only one concurrent writer and that's the end of story. People used to come that's that really is terrible. That said, I had lots of people coming from the Turso community in the beginning, hey, don't have a product built on SQLite, and people would ask us I heard SQLite is terrible for write heavy applications. Can I use your product for write heavy applications, and because I was new to this, I would say no. I want to be truthful to people and SQLite is not designed for write intensive applications, but over time I got into the habit of asking people what do you mean by write heavy application? Because, again, the company that I worked for before, Sylla, it was a NoSQL database, and the first benchmark that we released, you know, the way that was in 2015 when we announced the database to the world. The first benchmark was how to do a million writes per second in a single server and that could scale horizontally. We had use cases think about log ingestion, things like that, tens of millions, hundreds of millions of writes per second. So, SQLite never, ever, there's no way you can do this with SQLite, it's just impossible.

JASON: Right, we're not using SQLite to build the next Snowflake.

GLAUBER: No, and not using SQLite to do financial transaction processing or anything like that. I would ask people sometimes, a lot of them web developers, what do you mean by write heavy and people would say things, oh, we have writes almost every second. SQLite can handle that just fine, right. There are times in which we have two people writing to the database at the same time. SQLite can handle that just fine, because the write is not just going to magically fail. They get queued and what happens is one of them takes 100 milliseconds, the other takes 200 milliseconds, because it got later. It's okay. Right. So, if you're doing 400 writes per second, 500 writes per second, et cetera, you can still use SQLite just fine. If you think about, for example, an online store that is processing payments, you don't have 500 customers buying stuff per second, right. So, there are many use cases, there are many use cases, where even the worst part of SQLite, which is the write performance, which again is, by the standard of measurement of the industry, dog shit, it's fine. It's fine. But it is the thing that people stumble upon the most, because it's not just the write performance. It takes a lock on your database. If your transactions are very complex, if your transactions take ten seconds, you know, one write takes ten seconds. I log the database, read something, call an API, during those ten seconds, nobody else writes to this database, right. So, if you have use cases where you write a lot, you have use cases where your transactions are complex with a lot of logic, SQLite will not be the database for you. But that is not a fundamental problem. There are ways, and again, we started our company with a fork of SQLite. We actually now have a much more ambitious project, which is a complete rewrite of SQLite in Rust, and in that rewrite we are fixing a lot of those issues. So, again, but at this point in time that is a tradeoff they have to live with.

JASON: Sure, sure, sure. And I think that's the sort of thing that's like in every tool you're going to know as you get more familiar with it, it really shines in this use case and it really doesn't in this use case, and that's what makes us professionals that aren't being you know, this is why we get paid to do what we do, we're supposed to learn and then make decisions to get the best outcome, regardless of the tools. We're not paid to be like fan boys of our favorite flavor of database and everything else sucks.

GLAUBER: Some people are. Some people are.

JASON: But you were talking about wisdom, and I think part of the wisdom of gaining seniority in the industry is you start to realize that every tool is just a tool. And if you start to look at one thing as your everything tool, then you will start to cause pain for you and your team, because nothing is perfect in all scenarios. You have to look at the scenario and then choose the tools that fit that scenario. So, it sounds like

GLAUBER: pretty perfect, with the exception of statements and

JASON: Fair enough. You know, with that in mind, it sounds like for a pretty broad variety of web applications that are operating at standard scales, even large scales, maybe not necessarily the Amazon or Walmart scale, but like the average, you know, average business, even a busy business, will be able to function on a SQLite database.

GLAUBER: Yeah, and the first I think one of the key things that I wanted to challenge in the word scale, when I talk about wisdom, already scale is a word that can mean many, many, many things. And I already put a huge ridge in there. Look, what's scale mean? Write scalability and read scaling. SQLite scales very, very well for reads. You can do millions of reads per second in a properly sized setup with SQLite. That is fine. Right. And trust me, there are use cases out there in which you can do more than a million reads per second out of your database. And that is the territory where I would not look into SQLite. I probably wouldn't look at Postgres either, but, you know, but maybe Postgres can get there, you know, with some level of tweaks. Writes is where SQLite really suffers. So, when you say scale, I think that is an important thing. If you want to guarantee infinite scalability, which is what a lot of people mean when they say scale. You can't be using like you're fairly restrictive with your choices, because now you have to use something like Sylla that has an architectural guarantee that you can scale horizontally, essentially, forever. Now, you do get into practical limitations, but, you know, if you take infinite scalability out of the equation, because most people, quite honestly, they will never need infinite scalability. It is a problem that Meta has and Google has and some people have, but it's a fairly uncommon problem.

JASON: Even a site like "The New York Times" probably isn't hitting millions of reads per second. Like, they are hitting a lot of reads per second, because they get a lot of traffic, but probably not millions. It feels like the sort of thing where you really have to be doing like a data like analytics kind of product where you're pulling in logs in the billions before it really starts to become like infinite scalability problems.

GLAUBER: There are time series, there are machine learning problems. There are lots of problems like that. Event tracking and things in which things can scale very well, but look, if you take out the infinite scalability out of the equation, which I think we should, now we're left with three axis. The size of your data, the read performance, and your write performance. So, those are not the same thing. SQLite scaled just fine in some of those and terrible in another. So, writes are terrible. In terms of storage size, you can get over a terabyte just fine. Not petabytes, but terabytes just fine. My platform does not support larger than a terabyte. The largest we can get is 100 gigabytes, but that is a business decision of focusing on specific workloads. SQLite itself could, and we want to get there in the future. SQLite itself could get to that level of a couple of terabytes. You will not get to petabytes. Again, if you have a scenario, look, whenever I get you but if you're hosting user profiles, you'll never get to a petabyte of data. You know, it's a state of the problem. You know, you don't have to you know if you're going to get there or not. Reads, again, you can get to the hundreds of thousands just fine. You know, with you can still get your couple of million, you might have to do some engineering there, but you can. Reads is where SQLite scaled the best. So, the use cases, wrapping this up, where SQLite really excels, are the use cases where you are reading a lot from the database, and the database is not very large. And the reason for that to matter is that, look, in practice, the database is a file. So, you're going to be having to push that file from place to place, and you can deal with a terabyte file, but it starts to get tricky. A lot of what my company does is try to solve those problems, but still. Let's say 10 to 50 gigabytes of data max. Imagine concentric circles. That's the zone of excellence where SQLite kicks ass. Then the zone of doable around it, then no touch. Which is the last one. Which will be the write heavy applications with billions of reads and petabytes of data. Forget about it. The zone of doable is where a lot of interesting things happen. Then the zone of excellence, heavy writes in 10 to 50 gigabyte databases.

JASON: Heavy reads.

GLAUBER: Heavy reads. Apologize if I said something else. Gaslight if I said something else.

JASON: I want to go ahead and play with this a little bit while we still have time, so let me get my screen going here. And I'm going to get this up on screen, I'm going to get us into the right configuration here, so that people can see us.

GLAUBER: Oh, I have a live transcript.

JASON: Yes, this is the live transcript. We have had Ashly from White Coat Captioning here with us all day. This is available at LWJ let me just you know what, why don't I just throw the link right into the chat here. You can get these captions by visiting this link right here. And if anybody is interested in sponsoring the accessibility of Learn With Jason, hit me up, because I just lost my sponsors. We are talking to Glauber today, so I'm going to throw a link to your GitHub, because there's all sorts of fun, interesting stuff in there. And then we are talking about SQLite, specifically, right now. Talking about SQLite kind of abstract, and we have made a couple mentions of let me get this made a couple mentions of Turso and we're going to talk about that a little bit more as we get further in to the episode. So, for somebody like me, who has, I would say, little to no experience with really building out the SQLite database. I've used them a bit, I've tried stuff like Astro DB was built on Turso, I was playing with that. I've done a little bit with some other stuff, but I've never really, like, dug into this. What should I do as a first timer? How do we get started?

GLAUBER: Yeah. So, I just want to mention, because this is something that a lot of people misunderstand, but remember I said the SQLite database is a combination of the code, which is what comes in your NPM package or whatever, and the file, right. That said, you can actually home brew something, your smart watch has it, everything has it. SQLite has a shell. And that shell is essentially, just so you understand what it is, because some people think the shell is SQLite, so I would have to install the shell. No. The shell is a database shell that has the database code inside it, same way the JavaScript application would, but it's a very useful thing, because if you just want to, for example, inspect the database, if you want to say, hey, what are the contents of this file, you don't have to write custom JavaScript code or in any other language to check that. I mean, you have a shell that has the database code on it, and then you can connect to that. So, if you were to get started, I would suggest, for example, you get the SQLite shell, and then we can start playing with that. And I bet my life that you I said life, not wife. I saw my wife going through here, oh, he's betting me again. But this time I said life. I said you have the SQLite shell. So, the name of the binary is SQLite 3. You have it. Everybody has it. Pervasive. SQLite has trillions of databases out there in the open. And, so, you just type SQLite 3 and you are using the SQLite shell. Now, this is an in memory database, as it says there, right. Very useful by the way for a lot of applications. A lot of applications, like CI, this is beautiful. You just do your stuff in memory, right. It's even faster than a file. You can throw away at the end.

JASON: This is yeah, that's what I was just thinking, stuff like session tracking or if I'm doing a complicated thing, and I don't necessarily want to pass that data between all of my functions, I could store temporary things and know that it all gets destroyed at the end. Never thought about that till just now and that's huge.

GLAUBER: If you are doing this with JavaScript, you would have a DB object, right. That represents the in memory database. You can pass the object around and issue SQL queries in transient data and it's incredibly fast. Again, because it's all in memory. It's beautiful. If you want to if you want to and maybe quit the session, you can just control C or just so we can have permanents and show that, as well. If you want to do SQLite 3 again but now pass the name of a file, and you can pass whatever. The file doesn't have

JASON: Yeah just going to do a folder, so we're working in one place and I can share everything that we do. So, I'm going to do SQLite 3 and do I need to create the file first?

GLAUBER: No, say Jason .DB. .DB, after all, is just a convention. Now, this is no longer an in memory database. Now everything you do is persistent in the file and that's a file, for example, that you can pass around. Now, another beautiful thing of SQLite is that SQLite is what I call the JavaScript of databases. It is very, very loosely typed. So, your columns don't even need types. It is super simple. Again, the objective, the goals of SQLite is just do stuff, right, which is very in line with JavaScript. Just get stuff done, let's get stuff done and get it out of the way. The simpler syntax to create a table of all would be in SQLite. So, you will write, for example, create table, then you give the table a name. And then parentheses, and just the name of a column. You don't have to specify type, name, boom, there you go. Semicolon, enter, and you've got your table.

JASON: What the hell was that? Apparently, pushed a button and a video started playing in my headphones. Okay. Sorry.

GLAUBER: At least I haven't heard, so I'm assuming the audience haven't heard, as well.

JASON: Good, yeah. Semicolon.

GLAUBER: Enter, and you create a table. Now you're going to do insert into testing, like you would in Postgres or anything like that. Values and then name. Yeah, you do have to say the name of the column, so you say there is a parentheses before values.

JASON: Oh, oh, oh. Right, right. One of these, then one of these.

GLAUBER: Dot dump, if you do a dump, this is a dump of the database you can use to pass this around. This is a list of commands that you would have to do to just recreate the same database somewhere else, right.

JASON: This you know, there's this thing I talk about a lot, like tacit knowledge and tacit knowledge transfer. And this is one of those moments for me, where I've never used .dump before and this just changed my life. This is one of those things. I have written this out by hand a million times and I could have just done this.

GLAUBER: It's actually funny that you have so much alcohol behind you and this is what changed your life, you know. Here's what I want to now start making use this opportunity to make this distinction very clear in the viewers' mind, because .dump is a shell command. All the dot commands the SQLite shell command, if you try .dump in your JavaScript better SQLite 3 or whatever driver you're using, it will not work, because .dump is a command of the SQLite shell. You could write the SQLite shell in JavaScript and then what does .dump do? It will face that with transactions committed at the end and put all the commands in there that you have, you know. But it's a shell thing. That doesn't work in yeah, Diego, nice to see you here, man. Diego is one of our contributors to our rewrite of SQLite in Rust.

JASON: Very cool.

GLAUBER: Very cool. .dump is a shell command and if you do .help, you'll see all the shell commands. Output into JSON, CSV, sorry, output the things to a file, or there's a bunch, you know, a bunch of stuff. One of my favorite ones is attach, which allows you to query two databases at the same time. If you have two files, you can join across the two databases in two different files with the attach command, which is, again, a shell functionality. So, there are all sorts of things. Timer to figure out how much if you are rewriting the SQLite shell in JavaScript, how do you do a timer, performance now, query, another performance now, and boom. Those are all shell things.

JASON: Got it, got it. Go ahead, sorry.

GLAUBER: I was just going to say, if you want to try a select statement, that is going to be in your driver. The select statement is part of the language. Just select star from testing or name, right. That is the driver, and the shell commands are shell commands.

JASON: Got it. Got it. Yeah, then if we do another one all of this work

GLAUBER: Values.

JASON: Then we can run it again. And now we get two names, right. So, this is, like, you know, standard stuff. If I can you know, always takes me three times to figure out how to write a SQL query, but I always get there. This is great, because, as you said, now if I leave here and list, we've got our DB. And if I run this command again... it's all still here. So, that's so freaking cool, because to do this in any other setup, we would have needed drivers, would have needed connection strings, would have needed all this stuff. I know how to do it, but I have to look it up every time, set up environment variables, all these extra steps. You can do this so quickly is huge, and we can put this right into a JavaScript app and have assistance in our apps immediately without any of that other stuff.

GLAUBER: That is exactly right. That is exactly right. By the way, when I said that SQLite is the JavaScript of databases, let's imagine, for example, I was Elon Musk's son, in which case my name being two would be perfectly a valid thing. If you try to insert the number two in there, you will see you do just fine.

JASON: Two? Not quoted?

GLAUBER: Yeah, exactly. It's a number now.

JASON: It works.

GLAUBER: SQLite has a new mode, which is called strict mode, which you can add first of all, you can add types just fine. If you want to create another table, just to demonstrate that, for whatever name. Those are not even strict types, because they will not be enforced, they are just there. They are more like a suggestion. Not here to tell you how to live your life kind of thing. You can put you will have blobs, you can call name a blob or whatever. You can have text. You can have integer, and you can have real. So, those are the things you can have. You can create, for example, four things.

JASON: Is it like this or spelled out?

GLAUBER: Both int and integer are accepted.

JASON: Okay, what was the last, blob, text, int?

GLAUBER: Text and reel.

JASON: Like a float? Got it.

GLAUBER: In SQLite, this is called an affinity. Now this will expect a number, but it will try to auto convert and that's where it gets funny, just like JavaScript. Sometimes the conversions are fun.

JASON: Yeah, so if I do something like we'll do a test and then say five.

GLAUBER: I don't remember. Yeah, did not even fail. Yeah. I think did I lose you? Oh, God, I think I lost you. Nod if you can hear me, Jason. Can you hear me? I cannot hear you. What the hell? I hear stuff. I hear stuff from my computer.

JASON: Today. What's going on?

GLAUBER: You're back. Welcome back, Jason.

JASON: Oh, you got me. I don't know what's going on with this. It's being very difficult. Can everybody hear me all right and Glauber, you're not getting mic blowback or anything?

GLAUBER: I can hear you, I can hear you.

JASON: Okay. Great. Then let's keep it rocking. Okay, so, I did this. I ran this query, and we can see even though I tried to put a string into an integer, it did, in fact, keep the string. So, just like JavaScript.

GLAUBER: The JavaScript of database. Now, you can, and honestly I don't even know how. You can choose strict typing. So, when you're using strict typing, you will be the equivalent of using TypeScript. It will essentially that query would have failed, that insert.

JASON: Okay, cool. Very cool. So, we've got type affinities. We have the ability to very quickly stand up a database, we can get data into it. Is there anything else you want to show while we're in the shell?

GLAUBER: Look, man, stuff that there is a I don't even know if I remember how to do it, but SQLite has a bunch of interesting functions. JSON, there's JSON B, et cetera. But as I said, now you're getting out of what's in my head and you have to go look at documentation.

JASON: Yeah, yeah, yeah.

GLAUBER: That communicates well what we do, right.

JASON: I feel like I've done yeah, because you can do custom types and stuff, which is really interesting.

GLAUBER: Yeah, custom type expressions. There are a lot of things SQLite can do. Another interesting thing are triggers. Triggers, for example, when you insert something, your trigger code is going to be called, so you add this, you know, you can write to other tables, which is how you would do a materialized view. It's a full SQL database. It's not a toy in any way, shape, or form. It has those limits that we talked about, but you can do a lot with that.

JASON: Yeah, I know I've done this, and I cannot remember how it works. Anyways, I'm doing something wrong, but there's some really interesting stuff here that you can do with like generating really powerful queries and return types. A lot of the stuff that I thought was unique to GraphQL, like alias columns, being able to change output on the way out, it was all just because I hadn't looked deeply enough into how SQL queries worked.

GLAUBER: And by the way, by the way, if you want to experiment with that, don't take my word for it, SQLite is so pervasive, because, again, trillions of databases out there. It's in the tutorial for every single every single piece of code, you know, it's perfect for tutorials, right. You have to set up nothing, as you mentioned.

JASON: Right.

GLAUBER: Good side effect of that, I have a very good friend, one of my best friends now called Claude, who is really good at writing SQLite queries, right. So, because it's in the training data for every single AI since forever, it's really good at generating SQLite queries. For example, at Turso, we do all of our PI with SQLite, of course. And for the love of me, I do not know how to write those incredibly complex queries that would show me queries that take like 15 seconds to run. I just ask Claude, hey, look, I want to know all the users that in the past three months created at least one database before. And I run the code and it just works every single time. It's very good for LMs.

JASON: Got it, okay. And we can probably show that really quickly if we go in here and say, like, write a query to hold the name and count from the typed table in SQLite as a JSON object. Let's see how it does here.

GLAUBER: JavaScript

JASON: SQL query instead. Did it do anything different? It did. JSON group array, JSON object. So, look how freaking cool that is.

GLAUBER: Yeah.

JASON: Yeah, so, it kind of did a little bit of chaos at the bottom because I didn't give it clear instructions, but this query would work. If I go back out to my thing here and I run the SQLite again... look.

GLAUBER: There you go.

JASON: Look at that. Okay. So, that is one of those things where I knew what I wanted and I know that it's right when I see it, but I would never remember this off the top of my head. So, that just saved me some irritating Googling.

GLAUBER: That's right. And this is a fantastic use of AI technology in my book, because, again, and Claude knows SQLite really well, because it's in every single piece of documentation of everywhere, everywhere in the planet, every software project, so it can generate really, as I said, I can't show it just because it's going to take me a lot of time to find, but we have a lot of very complicated queries to extract like what's going on in the growth of the company. It is all based on SQLite. We use SQLite for everything, and I don't know if I had to before LLMs, there was this one guy that worked for us that knew SQL very well. He was always like, hey, look, can you write this query for me, and three days later he'd come with this very complicated query with five joins and CTEs and window functions and all of that. Now I get that done in five minutes.

JASON: That's really, really, really cool. Okay, so, that's SQLite. So, SQLite we haven't touched anything proprietary yet. Literally, everything that's happening, you can do on any machine, it's open source, it's all just going to work. If you don't have a SQLite shell, you can install one, but you probably already have one. And if you don't want to use the shell, you can NPM install what is it, better SQLite 3?

GLAUBER: The other day, I actually logged SSH into a machine that did not have the SQLite shell, and I told that story around the camp fire as a horror story to a group of friends. You know, better SQLite 3 is one of the drivers that a lot of people use, yeah.

JASON: So, this is one you can install into your project to get started

GLAUBER: You can also use lib SQL, which is our fork of SQLite that gave birth to Turso, right. So, there is the lib SQL driver, which is a compact package with better SQLite 3 and also the client which is the one that is called libSQL client that is used more for the HTTP things that we added.

JASON: This one here?

GLAUBER: That's right. Never remember if it's a slash or a dash.

JASON: This feels like a good segue into talking a bit about Turso, because here's this wonderful Turso logo that you've got. So, where does SQLite end and Turso begin? Why does Turso exist?

GLAUBER: Turso exists because we were bored and wanted to found the company.

JASON: Got it.

GLAUBER: There's nothing else we were looking for. In fact, it is half true, because we were bored. Not bored, but we're masochists, so we were having our life was too good. How do we destroy that, let's start a company. But we were using SQLite, we were using SQLite for our company, which had nothing, you know, wasn't called Turso, had nothing of that. And we did hit a lot of those issues of how do I run this in production, you know, it's impossible to use SQLite from something like faired workers, because, yeah, definitely can't do things the easy way. One of the best ways to destroy your life is start a company. If the company goes well, actually get a lot of that back. But it's not guaranteed in any way, shape, or form, and you're miserable throughout the way. But anyway, if we were if I was looking for an easy life, I would be a lawyer, stuff like that. We put a fork of SQLite out, which is libSQL. That's why the name is not the same. We created this fork of SQLite, wrote the manifesto. All of that maybe let's save for another day, but once that went super well, we decided to essentially abandon what our company was doing and create the Turso platform instead. So, the Turso cloud is our product at Turso, and it is designed to essentially offer quality of life things for SQLite. You don't have to be a SQLite user before you are a Turso user. Maybe and a lot of our users are like that. Maybe all you wanted was a very affordable, very easy, very trivial SQL database in the cloud, right. So, you can do that. But we also do a lot of things, for example, like backups and syncing for your SQLite database. So, there is a beta feature, and just because it's beta, I want to talk more about it to get people excited. There's a beta feature in which all of that, that you wrote to your SQLite database, would just be automatically copied to our cloud. You would be able to do things like pointing time restore and move the database to any point in time before in the past. You would be able to create clones of this database, and you would be able to create copies of this database in other devices that are kept in sync automatically, right. So, you can have, for example, imagine you have a fleet of let's not even talk about mobile devices, which is one of the use cases for that, but imagine you have a fleet of five servers that are your API servers. You can put a copy of the SQLite database in each of those and then the Turso platform will keep them all in sync. And now you have again, if your database is under 10 gigabytes or so, that actually works pretty well. If your database changes very often, that doesn't work well, because you have to pay a lot to sync that, but if you are within the use cases I mentioned before where SQLite is super good at, you don't have a ton of writes, you know, then it's very affordable and very easy to do this. And now you have your micro second queries everywhere. That would be the Turso platform. I don't know if you have a Turso account or not. If you don't, even better. I can sign up.

JASON: I think I may have one, but let me find out. I have to click this button, got it.

GLAUBER: Are you a human, though?

JASON: Negative, I'm a meat popsicle. Let's see. Yeah, just looking around. So this is a... probably put it in... let's see... does it matter? I'm going to say Atlanta. We're just going to rock with it.

GLAUBER: That database is going to be created on where we are hosting our database service today. We had recently announced we were moving to AWS, but that's not done yet and complete. You can create I would love one of the beautiful things about AWS is it is a lot faster. So, you would be able to create this database in less than one second. This is the onboarding, try to guide you through this, but just give you a database. So, created your account, now you have your demo app, et cetera. You can skip that and we'll

JASON: All right, I'll skip this part. We'll go to the dashboard.

GLAUBER: Now you have your database. You can have 500 databases. This is the free plan. So, for free, you can only have 500 databases. If you pay us $29 a month, you can have 10,000 databases. If you pay us $500 a month, you can have unlimited databases. And those are use cases this is super good for the use cases you want databases coming up and down all the time, right. So, you will be able to just create databases at will. Look, there are many ways you can play this. We can play a bit with the UI or CLI. I can show what syncs the databases, that would have to be a CLI thing, as well. Feel free to just explore.

JASON: Yeah, so, I think maybe what would be good is let's set up like a really quick project and maybe what we can do is use the Vite starter project so we can store our account across different sites.

GLAUBER: I know what Vite is, by the way, I learned it.

JASON: You know what, we're learning together. I love it. So, let me clear this I'm just going to clear this folder out, so that we can clone in here. Then I'm going to... create Vite. Now NPM install. How do I get this to cancel? There it is. Okay. So, I'm going to need to install, I assume, is it like Turso or

GLAUBER: That libSQL client that you saw.

JASON: Do I need anything else?

GLAUBER: No.

JASON: Installing that. I'm going to get init, so I have this looking a little more... there we go. A little better. And now I have I've got my app TSX and in here we now have a basic project running, and if we look at the browser... we have this count. So, what I'd like to do is have this count get tracked in Turso. So, for me to do that, I have the libSQL package now, I've got the client here, and I want to hook that up to my Turso database, and I have a hunch that if I go back here it's going to tell me exactly how to do that. Let's see.

GLAUBER: The code the TypeScript examples showing the onboarding, right. So what I would point if you go to the docs, docs.Turso.tech... yeah, you go to client SDKs or quick start, I guess. Quick start remember, we don't assume TypeScript, right, in the beginning. So, a lot of that yes, you would have to go there. This is quick start for...

JASON: Okay. So, I have an existing database.

GLAUBER: The URL you're going to get from the UI if you want. So, just to you're going to need

JASON: Here.

GLAUBER: Token, yeah. And the token. And you put this on environment variables. Turso URL, Turso token.

JASON: Got it. I'm going to create a .env here. It was a Turso database URL.

GLAUBER: You have yeah, you have this in the beginning.

JASON: Then I needed the auth token. Okay.

GLAUBER: You can also get that from the UI.

JASON: Also get from the UI, which I had up here. I want to read and write, and we'll just we're just going to let everybody see it this time. Don't do anything weird, y'all. Is Vinny even here to enjoy this?

GLAUBER: If you had time to copy the token...

JASON: Okay, so, now I have my token setup, we've installed the client already. Now I need to set up a client.

GLAUBER: That's right, that's right. By the way by the way, if URL, as you notice, is an Internet URL, like a network URL, so you're using Turso for the network. If you do a file URL, file column, you're talking to the file, so you can do all your testing in a file still, right. As I said, Turso has upcoming modes you're syncing to and from a file. So, you can still use a file. I think it's too complicated to do within that, but I do want to clarify you can still be talking to SQLite file just fine, if that.

JASON: We could create a slash DB.

GLAUBER: For example, yeah.

JASON: Okay, that's great. Then, let's see, one question I have, I am currently in a public this is client side.

GLAUBER: No, you should do this on the server.

JASON: I should do this on the server side. I've created a problem for myself, because Vite is all client side unless I add some extra stuff to it. Why don't we

GLAUBER: That I did not know. See, I learned something today.

JASON: I'm going to you know what I'm going to do, I'm going to bail entirely, and we're going to remove this folder. Actually, what I'm going to do first is I'm going to copy this .env, and then I'm going to NPM create we'll just use next, because... doesn't that work? Next app. Does anybody know how Next works? I'm making mistakes. You know what, I know how to do this in Astro, so I'm going to do it in Astro. This is fine. We're going to make this function. So, we're going to call this libSQL Turso. And let's make it an empty project.

GLAUBER: SLI is beautiful, by the way.

JASON: So good, they've done an excellent job on it. I do want to install the dependencies. Come on, little buddy. I want to initialize that. We want it to be strict. Good, good, good. Then we're going to do an NPX Astro. Add React. Because then I can do the counter example really fast. What is happening? Oh, I'm not in the okay. Now I can do NPX Astro add React. Yes, we'll do all of those things, and I'm going to NPM .dev. That's going to put us at 4, 3, 2, 1. Okay, so, here's our basic site. And when I come out here into our basic site what have I done here? Open folder. Here, here. Oh, my goodness. What... okay. So, now we have our basic site setup. We've got our pages here, and what I'm going to do is I'm going to create a component encounter .ESX.

GLAUBER: somewhere so you don't forget.

JASON: Oh, yeah, good call.

GLAUBER: I was about to see you pasting something from somewhere, then you lose that.

JASON: Yep, that is exactly what I would do. Then we're going to get React. Then we're going to export const counter equals let's see how fast I can do this from memory. Const account, set count equals use state zero. And then we're going to return a button that onClick is going to set count to be count plus one. And that is going to show count. That should be our button, and then on the Astro side, I'm going to import counter from components counter. And then out here I'm going to say counter and client only or actually we'll say client idle. And then I'll actually start my server again. Okay, so, there we go. We've got our basic setup here, and then I need to create an API we can do this a lot of different ways, but the way I know is easiest is we're going to create a file called counter .es and this is going to be an API route, and I'm going to copy/paste this from the Astro docs, rather than trying to call the resource route. Data route. Nope. End point. There we go. In an end point, we can export a function of get, which does all of that good stuff. And then up here I want to get these bits. Here's our Turso client. And these are import.Meta in Astro. So, now libSQL/client. And we can do that good bit there. All right. So, now we have our client. We have our client loaded into an end point, and that end point is going to allow us to make a query. And, so, we can say like Turso dot

GLAUBER: Execute is likely better. I would just go to the docs, because you're reading something, right?

JASON: I am reading something.

GLAUBER: Yeah, that will be execute.

JASON: Okay, so, we would want that to be data equals and did we create a table on this one? I don't think we created our table yet.

GLAUBER: The easiest is whenever you are writing, I don't know in which route you're going to write, put a create table if not exists before, and that's how we recommend people do this.

JASON: Okay, okay. So, then we can export async function post and that's going to be our run there. And we'll say result is going to be

GLAUBER: Just create table if not exists, everything else, this will only be executed once and that's the easiest way, you don't have to see it or do anything.

JASON: Not exists and I think we'll just do users and that's going to have, like can we do like an auto incrementing primary key, is it just

GLAUBER: Yeah, you can. Yeah. Again, I don't fully remember the syntax if you have to specify. Ask Claude. That's my recommendation. You can, yeah. You have to specify auto increment and I never remember if it's a single word, two words, has this or that. Yeah.

JASON: Doesn't exist that has an auto incrementing ID and a name no, we need a count field that is an integer. Called count. Forgot what we were doing. Trying to track this count. So, create table. If not exist, count.

GLAUBER: All increment, one word.

JASON: Count, also an integer. Hell yeah, Claude, thank you. So I'm going to get rid of my half written query, and then we're going to write

GLAUBER: I would put in two different executes.

JASON: Okay.

GLAUBER: Now you have alternative, if you're doing two things, you should be doing a transaction, et cetera, so the easy is do one and then do the other.

JASON: Gotcha. Let's do that then. Write a SQLite query to insert or to increment the count in the count table. Man, I love

GLAUBER: Yeah, didn't specify that very well. You want that given an ID. The one doesn't do that, right.

JASON: I guess I'm realizing I specified this ID and there was literally no reason for me to do that, because all we're actually tracking is the count.

GLAUBER: The count.

JASON: Oh, we can make it do a primary key and all that stuff.

GLAUBER: Which, again, it's good to show, but yeah.

JASON: So, we'll do result two and await Turso.execute, throw one of these in there. So, this then should give us our count updating. And then when we select count from count, what we should get back is our data, and what

GLAUBER: Going to get the data in a specific format. Again, the docs should help you with that, but you can also just execute, consult, and go from there. What you're going to get that's why people use a lot of ORMs, especially on the web. The results you get there is pretty raw. You're going to get the list of rows, you know, with the name things, but I would just print it somewhere and then you can actually see what the result is.

JASON: Yeah, let's do it. So, what I'm going to do is I'm actually going to curl x HTTP local host... 4321/API/counter. Wait. What is that... oh, wait. That's why. So, there we go. So, this should have inserted a row.

GLAUBER: Yes, exactly.

JASON: Then if I get rid of this, this will get, and it's

GLAUBER: Get something like that, yeah.

JASON: There's our row.

GLAUBER: When using an ORM, it's going to beautify this for you and specific types on the schema, but the database driver is returning this raw thing for you, right.

JASON: Uh huh. So, we could do something like count is data.rows at zero, I think. Let's try. Almost. I almost got there. So, we could I think actually just get rid of this. Perfect. Look at us go. So, now we'll get our count back, and we can just use that as our update. And then here, we would want to get a body, if I can figure out how to how does one get that? I want

GLAUBER: Jason, I do need to go in four minutes.

JASON: We are so close to done here.

GLAUBER: Yeah, this is great, man.

JASON: Okay, let me see if I can get this in one minute. We need come on. Request. And console log our request and figure out what's in there. That didn't log what I wanted at all, did it? Oh, come on. I guess it doesn't really matter. We need to increment and it's not incrementing. I've written bad SQL here, I think.

GLAUBER: Yeah, because if you're getting the current counter, then you don't need to do the select, right. You would just

JASON: Right, right. We could just throw it in. Does this just work, if I do it that way? Yeah, no, I'm wrong. Okay. We're out of time. This was on me

GLAUBER: Got almost there, man. Got almost there. Got almost there.

JASON: But I think this is really this is really cool that what we were able to do here is we got data running in our app, because, you know, again, what we're doing here is like this is a live end point. So, if I hit my counter, we get back our current count, and if I could figure out how to update that count, because right now my SQL query doesn't work, then we would be able to every time we hit this end point get a new count and use that to update our use. So in our counter we'd be able to run the query, get the value, put it into the use state and the reason it's in an API, is because we were doing it on the client side. If they were the server side we could hit the database directly.

GLAUBER: That's right. The one thing I always like to drive home is you are now using the Turso cloud over HTTP. If you were developing this in that URL thing there, you can try that, you'll see that it works. Put a file name and leave the token blank and talking to the local select file.

JASON: That's so cool, yeah. Really, really cool stuff. And I love I love that this is so accessible, even if you don't feel like you're a database expert, you can still just get in here and try, you know, and that tip about using Claude to write the queries is actually that feels like it's kind of a game changer, because that's the thing that takes me the most time is Googling, okay, how do I do this, this, or this.

GLAUBER: This query, by the way, especially for the people in the audience, you shouldn't be using an insert. You should be using an update. But you're going to get there in two, three more interactions with Claude. Hey, look, I need an actual existing value of data, et cetera. So, it's brilliant. It's brilliant. With SQLite, I think it's a little bit further than others. Just because there's so much data and so much documentation, that all the LLMs generate code for that super well.

JASON: All right. So, I'm going to throw links up to SQLite, Turso, and your GitHub. Anywhere else you want people to go before we wrap up?

GLAUBER: Look, follow me on X. I am GLCST. Just Glauber Costa without some of the vowels, some of them. Follow me, I'm always there. I have 3.45 children, I updated that today. In a couple of months, hopefully that will get to four. I'm updating the counter as we go.

JASON: I love it, I love it. Glauber, thank you so much for spending time with us today.

GLAUBER: My pleasure, man.

JASON: And we are going to come back next week we're doing a part two of the test driven development thing that we started with Cory Speedman, really excited to continue on that. Check out the website for upcoming schedule and updates on all the things that we make over here. Until next time, y'all, take care. Have fun, be nice to each other. We'll see you next time.

GLAUBER: Thank you, bye bye.

Supporters

Become a supporter to see yourself here on future episodes!