Check out a free preview of the full Complete Intro to SQLite course

The "Flexible Typing" Lesson is part of the full, Complete Intro to SQLite course featured in this preview video. Here's what you'd learn in this lesson:

Brian highlights SQLite's flexible typing. For example, an INTEGER column will allow values that are also REAL, TEXT, and BLOB. While this can have unintended consequences, it does allow most SQL queries written for other databases (like MySQL, Postgres, etc.) to coerce to a TEXT data type.

Preview
Close

Transcript from the "Flexible Typing" Lesson

[00:00:00]
>> Brian Holt: We at this point have gone almost everything that we've covered so far. Pretty generally applicable to Postgres, my SQL, Oracle, Snowflake, any one of those things. Now, we're gonna start getting a little bit more into what is more peculiar to SQLite. Yeah, basically for the rest of the time here.

[00:00:28]
>> Brian Holt: So we talked a bit about this, but, SQL is not rigid at all about its typing. In fact, it's the opposite, it's intentionally flexible about its typing. What's nice about this is like you can almost always take queries for other databases and not have any problems with them.

[00:00:47]
Whereas, like sometimes even mysql and postgres, which are somewhat similar in many ways, will fight each other about types and how you extract them and things like that. But just yeah, be aware if you limit something to 250. 55 characters, it can still be two gigabytes, and SQLite does not care.

[00:01:06]
They did add a strict table function in 2021. It makes its SQLite slightly more opinionated about it. But even they say like, we don't think this is a good idea, but we cave to peer pressure essentially. Yeah, where is it?
>> Brian Holt: I think it's done at the end.

[00:01:28]
If you insist on rigid typing, SQLite supports this development using strict tables. If you find a real world use case prevented that would have prevented a bug in application, let us know, because we don't believe you, [LAUGH] is essentially what they say. Here, which I thought was just the funniest thing when I was reading through this.

[00:01:45]
Yeah, embrace freedom, not the kind of freedom I typically think of for sure. Yeah, cool. Anyway, you can read their treatise here. They're smarter computer scientists than me, so maybe they have a point. They just that'll like it.
>> Speaker 1: Can you Leverage, like, ORMs and TypeScript to sort of do that enforcement from you, for you sort of at the application layer?

[00:02:14]
>> Brian Holt: Maybe I don't know if Prisma or Drizzle have a built into ' but like truly important that you really wanna your emails to be 200 characters, or less or something like that. You are gonna have to write it in app code.
>> Speaker 1: Okay.
>> Brian Holt: Yep, there's no timestamp that really does actually bite you in the ass sometimes having no times type.

[00:02:40]
Just seems like a massive miss to me. But they don't have any sort of timestamp. They do have a bunch of functions that will operate on times for you. So, you just store it as a string and then you pull it out and you do conversions on the fly and then put it back.

[00:02:54]
If it sounds messy, it's because it is messy.
>> Brian Holt: I'm very good friends with the maintainers of moment and date functions and they're not fans of how they choose to handle times in SQL. I'll put it that way. No Booleans, zeros and ones, true and false are just aliases of 0 and 1.

[00:03:16]
I wish I'd always thought that was funny.

Learn Straight from the Experts Who Shape the Modern Web

  • In-depth Courses
  • Industry Leading Experts
  • Learning Paths
  • Live Interactive Workshops
Get Unlimited Access Now